[PHP] Select from 24 tables
Hi List, How do I select data from 24 table in my database. Each one is identical in structure layout being Id,name,list I want to select where like $_POST[var] from a form all of the tables but I am having trouble :( I thought making a var string like $string = table1,table2,table3,.; And doing (select * from $string where list like \%$_POST[var]%\); Would work but I get a MySql error which say Column: 'list' in where clause is ambiguous I am stumped so I ask the list for help or advise please. Any advise is very much appreciated and I thank you in advance for any help or pointers. Thank you Dave C --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.672 / Virus Database: 434 - Release Date: 28/04/2004 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Select from 24 tables
Dave Carrera wrote: Hi List, How do I select data from 24 table in my database. Each one is identical in structure layout being Id,name,list I want to select where like $_POST[var] from a form all of the tables but I am having trouble :( I thought making a var string like $string = table1,table2,table3,.; And doing (select * from $string where list like \%$_POST[var]%\); Would work but I get a MySql error which say Column: 'list' in where clause is ambiguous I am stumped so I ask the list for help or advise please. Any advise is very much appreciated and I thank you in advance for any help or pointers. Thank you Dave C SELECT * FROM dbname.table1, dbname.table2, dbname.table3, ...etc WHERE table1.list LIKE '%$_POST['var']%', table2.list LIKE '%$_POST['var']%', table2.list LIKE '%$_POST['var']%', ...etc -- By-Tor.com It's all about the Rush http://www.by-tor.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Select from 24 tables
Dave Carrera wrote: Hi List, How do I select data from 24 table in my database. Each one is identical in structure layout being Id,name,list The first thing you need to do is reorganize your database schema and put all of this into one table. You can see what a pain it is having 24 similar tables already and it's only going to get worse. You could probably use a UNION to join all of the tables together in your query, but I doubt it's going to very efficient. You can't select from a list of tables the way you're trying to, though. Last option is putting your query in a loop and executing it 24 different times, but you _really_ need to just fix the database structure now. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Select from 24 tables
John W. Holmes wrote: Dave Carrera wrote: Hi List, How do I select data from 24 table in my database. Each one is identical in structure layout being Id,name,list The first thing you need to do is reorganize your database schema and put all of this into one table. You can see what a pain it is having 24 similar tables already and it's only going to get worse. You could probably use a UNION to join all of the tables together in your query, but I doubt it's going to very efficient. You can't select from a list of tables the way you're trying to, though. Last option is putting your query in a loop and executing it 24 different times, but you _really_ need to just fix the database structure now. Yeah, better what John said than what I said. ;) -- By-Tor.com It's all about the Rush http://www.by-tor.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Select from 24 tables
This isn't always desirable, or even possible. I once designed a database to hold characteristics for a series of 70 different tests. There were about 50 different characteristics used in various combinations for each test. Each characteristic could be one of many values. So the characteristics tables all looked like this: id, name, value And the test tables looked like this: id, name, value And the mapping of characteristics to test looked like this: test_id, characteristic_id This is actually a gross oversimplification, but you get the idea. Displaying test results typically required joining 30-40 tables together, but since each characteristic table was small (10-15 entries), performance was acceptable. So do as John Nichel first suggested, and do this: select c1.id as characteristic1_id, c2.id as characteristic2_id from characteristic1 c1, characteristic2 c2 etc. cheers, Travis John W. Holmes wrote: Dave Carrera wrote: Hi List, How do I select data from 24 table in my database. Each one is identical in structure layout being Id,name,list The first thing you need to do is reorganize your database schema and put all of this into one table. You can see what a pain it is having 24 similar tables already and it's only going to get worse. You could probably use a UNION to join all of the tables together in your query, but I doubt it's going to very efficient. You can't select from a list of tables the way you're trying to, though. Last option is putting your query in a loop and executing it 24 different times, but you _really_ need to just fix the database structure now. -- Travis Low mailto:[EMAIL PROTECTED] http://www.dawnstar.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Select from 24 tables
This isn't always desirable, or even possible. I once designed a database to hold characteristics for a series of 70 different tests. There were about 50 different characteristics used in various combinations for each test. Each characteristic could be one of many values. So the characteristics tables all looked like this: id, name, value And the test tables looked like this: id, name, value In my experience, it's usually a safe assumption that if you have a bunch of tables all structured identically and used in similar ways, you should probably merge them all into a single table with an extra column that corresponds to whatever differentiating characteristic used to distinguish your original tables. I.e., go with John Holmes' suggestion before you're really up the creek. - michal migurski- contact info and pgp key: sf/cahttp://mike.teczno.com/contact.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Select from 24 tables
Michal Migurski wrote: In my experience, it's usually a safe assumption that if you have a bunch of tables all structured identically and used in similar ways, you should probably merge them all into a single table with an extra column that corresponds to whatever differentiating characteristic used to distinguish your original tables. I.e., go with John Holmes' suggestion before you're really up the creek. Up what creek? You didn't really provide any technical justification for your suggestion. In my experience, the best way to deliver a quality application is to start with a fully-normalized database schema, then de-normalize it slightly here and there if performance is really a problem. But I've rarely had to do that. In most cases, an upgrade to a faster server with more memory solves the problem more quickly and cheaply than an application re-write would. On the other hand, I've been called in many times to deal with application problems that arise from using mashed-together schemas such as the one you propose. John W. Holmes wrote: The first thing you need to do is reorganize your database schema and put all of this into one table. You can see what a pain it is having 24 similar tables already and it's only going to get worse. The pain only occurs when writing the SQL statements to join the tables. I don't think it's a good idea to optimize the database schema for the sake of the programmer, who only has to write the SQL one time. Later, when the customer wants to fix bugs, or add enhancements, the pain is far greater. That's usually when I get called in, long after the original programmer has flown the coop. The only times I ever purposely deliver denormalized applications such as you suggest are when the customers are asking for quick-and-dirty stopgap solutions. Then it makes sense to optimize for the application writer. However, I have found that stopgap solutions have a way of becoming permanent. These days, I usually turn down such jobs. cheers, Travis -- Travis Low mailto:[EMAIL PROTECTED] http://www.dawnstar.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Select from 24 tables
Up what creek? You didn't really provide any technical justification for your suggestion. Up the creek of having to call for help on php-general because you have 24 identical tables you need to join, having never performed a join before. The pain only occurs when writing the SQL statements to join the tables. I don't think it's a good idea to optimize the database schema for the sake of the programmer, who only has to write the SQL one time. I wholeheartedly disagree. :) I think that optimizing for the sake of the programmer, the bug fixer, and the possible inheritor of the project is a /great/ idea. Servers keep getting faster, while the human attention span and rate of familiarization with code stays approximately constant, so optimizing your development time is at least as sensible as optimizing SQL select performance, which is probably better handled through well-chosen indexes anyway. Optimizing for the programmer usually translates into thinking about the humans who will need to interpret your code, and making it easy to pick up the gist of your intent. A 24-table join is not a typical characteristic of what I'd consider a well-planned DB schema, whose meaning can be quickly grokked by a newcomer to the project. If all those 24 tables store the same kind of data, then there's no reason to split them up. Of course, we haven't seen the specific usage of those tables in this thread, but I'm basing my posts on the assumption that it's better for the OP to have a marbles table with a color column, than tables named red_marbles, blue_marbles, 'green_marbles, etc. If that's not the kind of data we're talking about, then I stand corrected, and John Nichel's initial response is all that's needed. A huge table count is often evidence of a need for some refactoring. - michal migurski- contact info and pgp key: sf/cahttp://mike.teczno.com/contact.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Select from 24 tables
Hi Michal, Getting back to the original posting, I'm basically saying that I don't think it's a good idea to structure or restructure a database for the sake of simplifying a few SQL statements. Especially if the data being joined is in separate tables for a good reason. Regarding optimizing for programmer/bug-fixer/etc, after thinking about it, I realized that what constitutes optimization really depends a lot on your development situation, so I'll refrain from further comments on that. I probably shouldn't have brought it up, sorry. cheers, Travis Michal Migurski wrote: Up what creek? You didn't really provide any technical justification for your suggestion. Up the creek of having to call for help on php-general because you have 24 identical tables you need to join, having never performed a join before. The pain only occurs when writing the SQL statements to join the tables. I don't think it's a good idea to optimize the database schema for the sake of the programmer, who only has to write the SQL one time. I wholeheartedly disagree. :) I think that optimizing for the sake of the programmer, the bug fixer, and the possible inheritor of the project is a /great/ idea. Servers keep getting faster, while the human attention span and rate of familiarization with code stays approximately constant, so optimizing your development time is at least as sensible as optimizing SQL select performance, which is probably better handled through well-chosen indexes anyway. Optimizing for the programmer usually translates into thinking about the humans who will need to interpret your code, and making it easy to pick up the gist of your intent. A 24-table join is not a typical characteristic of what I'd consider a well-planned DB schema, whose meaning can be quickly grokked by a newcomer to the project. If all those 24 tables store the same kind of data, then there's no reason to split them up. Of course, we haven't seen the specific usage of those tables in this thread, but I'm basing my posts on the assumption that it's better for the OP to have a marbles table with a color column, than tables named red_marbles, blue_marbles, 'green_marbles, etc. If that's not the kind of data we're talking about, then I stand corrected, and John Nichel's initial response is all that's needed. A huge table count is often evidence of a need for some refactoring. - michal migurski- contact info and pgp key: sf/cahttp://mike.teczno.com/contact.html -- Travis Low mailto:[EMAIL PROTECTED] http://www.dawnstar.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Select from 24 tables
* Thus wrote Travis Low ([EMAIL PROTECTED]): Hi Michal, Getting back to the original posting, I'm basically saying that I don't think it's a good idea to structure or restructure a database for the sake of simplifying a few SQL statements. Especially if the data being joined is in separate tables for a good reason. The problem is that we're not talking about normalization in this case. The OT post is using 24 tables for each letter of the alphabet (Dont ask me how that number was derived.) So each table represents the same exact scructure. Curt -- I used to think I was indecisive, but now I'm not so sure. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
