Problem with simple MySQL search script

[jonathen]

Hi,

I need some help here! I have been playing with this script, part of which I found in a tutorial and have been modifying. It almost works - I have confirmed it connects to my db, and selects the correct db, but cannot confirm if it selects the correct table (even though I am selecting the correct db) Also, if I do not enter anything into the search field on the prior page, it returns the correct error.

However, the problem is this: If the script does not complete the array after connecting, it will return an error (Doh!) which it returns EVERY time!

This indicates there is something wrong with the code - something PHP cannot do. But I really don't know what it is - I have checked and double checked my code and the fields/db/user/pass/host and still am getting the same error with no results. Any ideas? Here is the entire page code:

<?php
include("$DOCUMENT_ROOT/includes/header.php");
?>

<?php 
 if ($search)   // perform search only if a string was entered. 
   { 
    mysql_connect(localhost,user,pass) or die ("Problem connecting to Database"); 
    mysql_select_db(mydb) or die( "Unable to select database");
    $srch="%".$search."%"; 
    $query = "SELECT * from exhibitions WHERE date LIKE '$srch' || exhibition  LIKE '$srch' || address LIKE '$srch'  

|| time  LIKE '$srch' || contact LIKE '$srch' || notes LIKE '$srch'"; 

$result = mysql_db_query("search", $query); 

if  ($result) 
{ 
echo "Exhibition Search Results:<br><br>"; 

    while ($r = mysql_fetch_array($result)) {     // Begin while 
        $date = $r["date"]; 
        $exhibition = $r["exhibition"]; 
        $address = $r["address"]; 
        $time = $r["time"]; 
        $contact = $r["contact"]; 
        $notes = $r["notes"]; 
echo "<table width=\"75%\" border=\"0\" cellspacing=\"0\" cellpadding=\"3\" bordercolor=\"#000000\">";
echo "<tr>";
echo "<td class=\"exhibitdate\"><strong>$date</strong></td>";
echo "</tr>";
echo "<tr>";
echo "<td><strong>$exhibition</strong></td>";
echo "</tr>";
echo "<tr>";
echo "<td>$address</td>";
echo "</tr>";
echo "<tr>";
echo "<td><strong>Time:</strong> $time</td>";
echo "</tr>";
echo "<tr>";
echo "<td><strong>Contact:</strong> $contact</td>";
echo "</tr>";
echo "<tr>";
echo "<td>$notes</td>";
echo "</tr>";
echo "</table>";
echo "<br>";
        }                  // end while 
        echo "</table>"; 
     } else { echo "Doh!"; } 
   } else { 
   echo "Search string is empty. <br> Go back and type a string to search"; 
   } 

?> 

<?php 
include("$DOCUMENT_ROOT/includes/footer.php"); 
?>

[hand]

Add an OR statement with mysql error codes to your query, may be you'll get better informations

$result = mysql_db_query("search", $query)
   or print "&gt;&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

Your Connect String: It's better to quote connect informations.

mysql_connect("localhost","user","pass") or die ("Problem connecting to Database"); 

Your DB Selection: It's better to quote DB name

mysql_select_db("mydb") or die( "Unable to select database");

To which database you want to connect? At the beginning your are defining "mydb" and later on you try a select against "search"

instead of mysql_db_query() you can take mysql_query()

$result = mysql_query($query)
   or print "&gt;&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

http://www.php3.de/manual/en/function.mysql-query.php

[jonathen]

Thanks for that - the problem was that it wasn't selecting the correct db - why I had "search" instead of "mydb" in $result = mysql_db_query("mydb", $query) I will never understand, lol!

ta very much!