last part of search code (display results from generated table list)

[87dave87]

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'); 
?>

[o3d]

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";

 

 

[87dave87]

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?

[87dave87]

any ideas? im still stuck with this.