Page 1 of 1
multiple table search query
Posted: Tue Mar 18, 2003 7:17 am
by ridwan
I have a php page which needs to search multiple tables depending on what the user selects is that possible and if so am I on the right track with this statement?
Code: Select all
$strSQL = "SELECT * FROM $tableSelect WHERE * LIKE '%$text%' ORDER BY їContentId]";
I know it will not work b'cos of the asterisk but that is what my problem area seems to be.
By the way there are 4 possible tables to search from which each have their own amount of different columns and info.
Thanks all
Posted: Tue Mar 18, 2003 8:47 am
by daven
Code: Select all
<?php
switch($table_name){
case 'Table1':
$where_clause="Condition1=value1 AND condition2=value2 AND etc";
case 'Table2':
$where_clause="Condition1=value1 AND condition2=value2 AND etc";
...
etc.
}
$SQL="SELECT * FROM ".$table_name." WHERE ".$where_clause." ORDER BY ".$order_clause;
?>
unsure on how to use given solution
Posted: Wed Mar 19, 2003 6:52 am
by ridwan
thanks for your assistance

; but I am a bit unsure on how to use your given solution in respect of the condition part so I used your solution in the following way:
Code: Select all
switch($tableSelect)
{
case 'companylist':
$where_clause="first";
}
//query table for information
$strSQL="SELECT * FROM $tableSelect WHERE $where_clause LIKE '%$text%' ORDER BY їContentId]";
$result= odbc_exec( $dbConnection, $strSQL);
It works fine but is there a way to add more columns to the $where_clause string; as it needs to look in multiple columns and if I try to add another column sperating the values with a comma I get an error

.
By the way I didn't use the condition part b'cos the user will type the value in so it isn't predetermined and can range to any value the user types in.
Posted: Wed Mar 19, 2003 8:45 am
by daven
To have multiple things in your WHERE clause, you need to use AND/OR. ex: SELECT * FROM table WHERE condition1=value1 AND (condition2=value2 OR condition3=value3) etc. Condition/value pairs are just the column and input values. If your table had a column called table_ID, then a where clause could be: WHERE table_ID=5 OR table_ID>15. Sorry for the ambiguous wording.
I would also suggest having the variables concatenated into the string, rather than being a part of it. Good code practice, and there are instances where including variables in a string can cause problems.
$strSQL="SELECT * FROM ".$tableSelect." WHERE ".$where_clause." LIKE '%".$text."%' ORDER BY [ContentId]";
sorted
Posted: Wed Mar 19, 2003 9:10 am
by ridwan
well that sorted me out thanx a bunch
