> Greetings again, Neil. Nice to hear from you. > > > > ________________________________ > From: Niel Archer <not@xxxxxxxxxx> > To: php-windows@xxxxxxxxxxxxx > Sent: Tuesday, November 15, 2011 8:13:51 PM > Subject: Re: No errors reported -- but no positive data either. > > Hi Bill > > could you supply the database structure for all the tables in your > project. I suspect you are needlessly duplicating data or access to it > > I should just mention that I am not without knowledge of principles of > normalization. I have never felt at home using tables that relate back > only by numeric index values. This especially is true when it comes to > filling in data. The colour, uv photos, photomicros, "more photos" and > colour scans all have to be filled with the addresses of their pictures > of those that do exist while remaining empty where no photos or scans > exist. > > The location where the camera icon is stored was one of the fields in > the main species table, making it easy to arrange the code but at the > expense of adding additional fields.I thought it was time that I pay > attention to at least some measure of normalization by starting new > tables to keep these various address sets in their own table. > > This move to additional tables have, of course made coding more complex. > I have much to learn and practice before I am proficient with joins but I was > able to first list all UV photo addresses to show with a query. Remember that > there is NOT a UV photo for each wood. Only 10 - 15% of species glow > under UV. Therefore the index numbers are not identical to those in the > full 'species' file. > > Next, you cannot use variables in a query as you can with PHP. Therefore > I picked one species name that I know has a matching UV photo. Once > that was done, the output (using phpMyAdmin by the way) was the > expected UV photo location address. It worked! That told me that I > had the sql query correct. > > To place the UV icons beside each species that do have a photo stored, > each actual storage address is not important --- only that there is a UV > photo stored for that species. A UV icon then needs to be placed so > users understand there IS a UV photo to view for that species. > > and seeing the structure may help to suggest a better one. For example > you seem to be storing the species_name twice, once in the main table > and again in the uvphotos table. That should never be necessary with a > > Neil, I am aware that one of the advantages of relational databases is > having one place to update data. In this case, though, if I added new > species into the species table, not all of them have UV fluorescence and > therefore do not all have to be in the uvphotos table. If I took a new > UV fluorescence photo of some wood and did not find a record for it > in the uvphotos table .... I would add it then. > > relational Db. Other ideas may also be obvious on seeing the current > structure. > > Niel, why would you wish to have the schema for all the tables in the > taxa database? There are 27 tables. Some are backup tables, others are > for the purpose adding species data from other sources while yet others > were started for me for purposes that are not at all connected to use in > operation of the programming files in the website. Why? Because seeing the schema answers questions a description can't. Mostly it allows me to envisage everything in a much clearer (and vastly quicker) way, than trying to decipher it from the PHP ;-) > There are only 5 tables that are used in the whole website. Tables > sci_order, sci_family and sci_genera are for higher organization levels > than species --- and totally irrelevant to the problems we are trying to > solve. > > That leaves only the 'species' and 'uvphotos'tables that are at all > relevant. I don't mind supplying that. Do you want just the schema or > do you want full data enclosed? Remember ......... the species table has > over 15,000 records. It is large. phpMyAdmin can export the full tables > but perhaps it is my lapsing memory I do not remember the best way to > offer you a copy of the structures only of these tables <blush, sorry>. Yes, the 5 main table's schema would be fine. Sample data would be nice, but not a good idea to send it through this list ;-) If you feel like sending some directly to me that would be okay. > I found that an internal query is needed inside the while look that forms all > the listiings of each species. Each reiteration to list a botanical name means > that the query has to be repeated inside this while loop because the species > name changes. The UV query works fine in phpMyAdmin so I am finding it > hard to find why the data does not show positive for the woods that do > fluoresce. I'm not so sure it is needed. You should be able to do it with a join in the original query and have PHP check if it's empty or not in the loop. This is why I'd like to see the schema to see if that's a good way to proceed ;-) > I hope this fills you in a bit more. How do I list the structures of the two tables > without including all the data? > > Waiting with patience, > > Bill > > > -- > Niel Archer > > > -- > PHP Windows Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- Niel Archer -- PHP Windows Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
