87dave87 Posted February 15, 2007 Share Posted February 15, 2007 Hi, I have been trying to search multiple tables in my database (I have 60+ tables in one database) for records that are LIKE what the user has typed into a search box. I have generated a table list using SHOW TABLES, I am struggling on the last part to display the results, the area that I need help with is the PHP code which starts with 'if (isset($_POST['emusearch']) && strlen(trim($_POST['emusearch'])) > 0)' I am trying to use 'show tables' in the row[0] 'select emulator, version, os, platform, details from ".$row[0]." in $search_query, see the third RED php section in the code below. The error which occurs in the browser when trying to run is: Error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where emulator LIKE '%stella%' OR platform LIKE '%stella%' order by platform asc' at line 1 select emulator, version, os, platform, details from where emulator LIKE '%stella%' OR platform LIKE '%stella%' order by platform asc, emulator asc Full page code is: - <? include($_SERVER['DOCUMENT_ROOT'] . '/includes/head.php'); include($_SERVER['DOCUMENT_ROOT'] . '/includes/dbconnect.php'); if (empty($_POST['emusearch'])) { echo "<meta http-equiv='refresh' content='0; URL=/noresults.php'>"; exit(); } else { $query = mysql_query('show tables'); $result = $query or die(mysql_error()); $searchresults = array(); while($row = mysql_fetch_array($result)){ $search_query = "select emulator, version, os, platform, details from ".$row[0]." where emulator LIKE '%".mysql_real_escape_string($_POST["emusearch"])."%' OR platform LIKE '%".mysql_real_escape_string($_POST["emusearch"])."%' order by platform asc, emulator asc"; $search = mysql_query($search_query); while ($row2 = mysql_fetch_assoc($search)) { // new $searchresults[] = $row2; // new } // new } if(count($searchresults) < 1) { echo "<meta http-equiv='refresh' content='0; URL=/noresults.php'>"; exit(); } else { } } ?> <table width="600" border="0"> <tr> <td> <table width=600 border=0 cellpadding=0 cellspacing=0> <tr> <td> <img src="/images/searchresults.jpg" width=300 height=30 alt="Search Results"></td> <td> <table border="0" cellpadding="0" cellspacing="0" width="290" height="30" background="/images/barmid.jpg"> <tr> <td> <div align="right"> <? echo "<span class='footer'>"; echo $num_rows; echo " "; echo $_POST['emusearch']; if ($num_rows == "1") { echo " emulator found</span>"; } else { echo " emulators found</span>"; } ?> </div></td> </tr> </table> </td> <td> <img src="/images/barend.jpg" width=10 height=30 alt="Search Results"></td> </tr> </table> </td> </tr> <tr> <td><table width="100%" border="0" cellpadding="0" cellspacing="0"> <tr class="tablehead"> <td width="240">Emulator</td> <td width="120">Version</td> <td width="120">OS</td> <td width="120">Platform</td> <td width="120">Details</td> </tr> <? if (isset($_POST['emusearch']) && strlen(trim($_POST['emusearch'])) > 0) { $search_query = "select emulator, version, os, platform, details from ".$row[0]." where emulator LIKE '%".mysql_real_escape_string($_POST["emusearch"])."%' OR platform LIKE '%".mysql_real_escape_string($_POST["emusearch"])."%' order by platform asc, emulator asc"; if(!$rs = mysql_query($search_query)) { echo "Error<br>".mysql_error()."<br>".$search_query; exit(); } else { $num_rows = mysql_num_rows($rs); while ($emulators = mysql_fetch_row($rs)) { echo "<tr>"; foreach ($emulators as $field) { echo "<td>$field</td>\n"; } echo "</tr>\n"; } } } ?> </table> </table> <? require_once('includes/search.php'); require_once('includes/foot.php'); ?> Link to comment https://forums.phpfreaks.com/topic/38623-last-part-of-search-code-display-results-from-generated-table-list/ Share on other sites More sharing options...
o3d Posted February 15, 2007 Share Posted February 15, 2007 Double check that your query works by substituting a static table name, I have a suspition that the table name is blank, and mysql will return with that error message " query failed near where..." If that is not the problem, then I don't know. Btw, it is better practice to populate a "global" variable for the table name. You use $row[] and you are allready out of the while. So that might cause a problem. $search_query = "select emulator, version, os, platform, details from ".$row[0]." where emulator LIKE '%".mysql_real_escape_string($_POST["emusearch"])."%' OR platform LIKE '%".mysql_real_escape_string($_POST["emusearch"])."%' order by platform asc, emulator asc"; Link to comment https://forums.phpfreaks.com/topic/38623-last-part-of-search-code-display-results-from-generated-table-list/#findComment-185372 Share on other sites More sharing options...
87dave87 Posted February 15, 2007 Author Share Posted February 15, 2007 I have double checked and it works fine, although if I type in 2 records it shows this: - "Error Column 'version' in field list is ambiguous select version, os, platform, details from windows_genesis, mac_atari2600 where emulator LIKE '%gens%' OR platform LIKE '%gens%' order by platform asc, emulator asc" How would I get around that? Surely searching through multiple tables is a regular thing to do when making a search script? Link to comment https://forums.phpfreaks.com/topic/38623-last-part-of-search-code-display-results-from-generated-table-list/#findComment-185385 Share on other sites More sharing options...
87dave87 Posted February 16, 2007 Author Share Posted February 16, 2007 any ideas? im still stuck with this. Link to comment https://forums.phpfreaks.com/topic/38623-last-part-of-search-code-display-results-from-generated-table-list/#findComment-186230 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.