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: [PHP-WIN] 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. 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>. 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 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
