[SOLVED] Could somebody help me with this code please...

joshgarrod · June 27, 2007

This is a PHP search script which customers use to search our table for products we have in stock. However, there is an error if one where to search a phrase with more than one word. Below I have quoted the errors and shown my script:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/file/sites/www.myurl.co.uk/Search.php on line 58

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/file/sites/www.myurl.co.uk/Search.php on line 76

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Search for spares</title>
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif}
-->
</style>
</head>

<body>
<h2 class="style1">Search</h2> 
<form action="<?=$PHP_SELF?>" method="post" name="search" class="style1">
Seach for: <input type="text" name="find" /> in 
<Select NAME="field">
<Option VALUE="ID">Stock ID</option>
<Option VALUE="StockNumber">Stock number</option>
<Option VALUE="StockDescription">Stock description</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
<p class="style1">
  <? 
//This is only displayed if they have submitted the form 
if ($searching =="yes") 
{ 
echo "<h2>Results</h2><hr>"; 

//If they did not enter a search term we give them an error 
if ($find == "") 
{ 
echo "<p>You forgot to enter a search term"; 
exit; 
} 

// Otherwise we connect to our Database 
mysql_connect("localhost", "ssfjdg", "idffhh") or die(mysql_error()); 
mysql_select_db("sto21") or die(mysql_error()); 

// We preform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Now we search for our search term, in the field the user specified 
$find_array = explode(" ", $find);
$find_sql = "";     // init
foreach ($find_array as $temp)
   $find_sql .= "LIKE '%$temp%' OR ";
$find_sql = substr($find_sql, 0, -3);  // throw away last OR statement

$data = mysql_query("SELECT * FROM stock WHERE upper($field) $find_sql");

//And we display the results 
while($result = mysql_fetch_array( $data )) 
{ 
echo "<b>Stock ID:   </b>";
echo $result['ID']; 
echo "<br>"; 
echo "<b>Stock number:   </b>";
echo $result['StockNumber']; 
echo "<br>"; 
echo "<b>Stock description:   </b>";
echo $result['StockDescription']; 
echo "<br>"; 
echo "<b>Price:   </b>";
echo $result['ItemPrice']; 
echo "<br>"; 
echo "<hr>";
} 

//This counts the number or results - and if there wasn't any it gives them a little message explaining that 
$anymatches=mysql_num_rows($data); 
if ($anymatches == 0) 
{ 
echo "Sorry, but we can not find an entry to match your query<br><br>"; 
} 

//And we remind them what they searched for 
echo "<b>Searched For:</b> " .$find; 
} 
?>
</p>
</body>
</html>

trq · June 27, 2007

Echo your query prior to execution so you can try and debug the problem.

Also, the reason your actually seeing the errors is becasue you have failed to check the query has succeeded before attempting to use it results. This is just poor coding practice, and alot of people do it. Anything that might return false, should be enclosed in an if() to check.

joshgarrod · June 27, 2007

Ok, and how would I do that. I have only just started learning PHP and MySQL. Thanks

per1os · June 27, 2007

<?php
//etc above...
$find_sql = substr($find_sql, 0, -3);  // throw away last OR statement
echo "SQL Was: " . $find_sql . "<br />"
$data = mysql_query("SELECT * FROM stock WHERE upper($field) $find_sql") OR DIE("SQL Was: " . $find_sql . "<br />ERROR: " . mysql_error());

//And we display the results

See what that outputs.

trq · June 27, 2007

Instead of placing your query directly into mysql_query(), place it in a variable, from here you can echo it for debuging.

<?php

  $sql = "SELECT * FROM stock WHERE upper($field) $find_sql";
  // debug sql.
  die($sql);
  $data = mysql_query($sql);

?>

As for the checks, the typical syntax for a slect query should be....

<?php

  $sql = "SELECT foo FROM bar";
  if ($result = mysql_query($sql)) {
    if (mysql_num_rows($result)) {
      // It is now safe to use $result.
    } else {
      // No results found.
    }
  } else {
    // Query failed. Good place to debug, eg;
    echo "Query failed<br />" . $sql . "<br />" . mysql_error();
  }

?>

joshgarrod · June 27, 2007

The first bit doesn't work. It prints the code with the search strings in it? Are there any other ways?

per1os · June 27, 2007

We are asking you to post us what the screen is printing out, as chances are the issue lies within the query.

joshgarrod · June 27, 2007

I already have but I will again:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sto21/sites/www.stowmarketcaravans.co.uk/Search.php on line 70

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sto21/sites/www.stowmarketcaravans.co.uk/Search.php on line 88

Sorry, but we can not find an entry to match your query

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Search for spares</title>
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif}
-->
</style>
</head>

<body>
<h2 class="style1">Search</h2> 
<form action="<?=$PHP_SELF?>" method="post" name="search" class="style1">
Seach for: <input type="text" name="find" /> in 
<Select NAME="field">
<Option VALUE="ID">Stock ID</option>
<Option VALUE="StockNumber">Stock number</option>
<Option VALUE="StockDescription">Stock description</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
<p class="style1">
 <? 
//This is only displayed if they have submitted the form 
if ($searching =="yes") 
{ 
echo "<h2>Results</h2><hr>"; 

//If they did not enter a search term we give them an error 
if ($find == "") 
{ 
echo "<p>You forgot to enter a search term"; 
exit; 
} 

// Otherwise we connect to our Database 
mysql_connect("localhost", "sser1", "ixser8") or die(mysql_error()); 
mysql_select_db("ersser") or die(mysql_error()); 

// We preform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Now we search for our search term, in the field the user specified 
$find_array = explode(" ", $find);
$find_sql = "";     // init
foreach ($find_array as $temp)
  $find_sql .= "LIKE '%$temp%' OR ";
$find_sql = substr($find_sql, 0, -3);  // throw away last OR statement

$data = mysql_query("SELECT * FROM stock WHERE upper($field) $find_sql");
 $sql = "SELECT * FROM stock";
 if ($result = mysql_query($sql)) {
   if (mysql_num_rows($result)) {
     // It is now safe to use $result.
   } else {
     // No results found.
   }
 } else {
   // Query failed. Good place to debug, eg;
   echo "Query failed<br />" . $sql . "<br />" . mysql_error();
 }


//And we display the results 
while($result = mysql_fetch_array( $data )) 
{ 
echo "<b>Stock ID:   </b>";
echo $result['ID']; 
echo "<br>"; 
echo "<b>Stock number:   </b>";
echo $result['StockNumber']; 
echo "<br>"; 
echo "<b>Stock description:   </b>";
echo $result['StockDescription']; 
echo "<br>"; 
echo "<b>Price:   </b>";
echo $result['ItemPrice']; 
echo "<br>"; 
echo "<hr>";
} 

//This counts the number or results - and if there wasn't any it gives them a little message explaining that 
$anymatches=mysql_num_rows($data); 
if ($anymatches == 0) 
{ 
echo "Sorry, but we can not find an entry to match your query<br><br>"; 
} 

//And we remind them what they searched for 
echo "<b>Searched For:</b> " .$find; 
} 
?>
</p>
</body>
</html>

per1os · June 27, 2007

Your not listening. Post the query that was outputted to the screen.

joshgarrod · June 27, 2007

Sorry, I am not sure what that is if it isn't anything to do with the code or the errors outputted to the screen?

per1os · June 27, 2007

<?php
//etc above...
$find_sql = substr($find_sql, 0, -3);  // throw away last OR statement
$sql = "SELECT * FROM stock WHERE upper($field) $find_sql";
echo "SQL Was: " . $sql . "<br />";
$data = mysql_query($sql) OR DIE("SQL Was: " . $sql . "<br />ERROR: " . mysql_error());

//And we display the results

Post the results when that is implemented. We need to see the actual SQL Query, you know the thing that goes to the database and grabs the data? Note the SQL Was: and the ERROR: part, paste everything that was outputted from that.

For easy copy and paste here is the above implemented into your code.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Search for spares</title>
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif}
-->
</style>
</head>

<body>
<h2 class="style1">Search</h2> 
<form action="<?=$PHP_SELF?>" method="post" name="search" class="style1">
Seach for: <input type="text" name="find" /> in 
<Select NAME="field">
<Option VALUE="ID">Stock ID</option>
<Option VALUE="StockNumber">Stock number</option>
<Option VALUE="StockDescription">Stock description</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
<p class="style1">
  <? 
//This is only displayed if they have submitted the form 
if ($searching =="yes") 
{ 
echo "<h2>Results</h2><hr>"; 

//If they did not enter a search term we give them an error 
if ($find == "") 
{ 
echo "<p>You forgot to enter a search term"; 
exit; 
} 

// Otherwise we connect to our Database 
mysql_connect("localhost", "sser1", "ixser8") or die(mysql_error()); 
mysql_select_db("ersser") or die(mysql_error()); 

// We preform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Now we search for our search term, in the field the user specified 
$find_array = explode(" ", $find);
$find_sql = "";     // init
foreach ($find_array as $temp)
   $find_sql .= "LIKE '%$temp%' OR ";
$find_sql = substr($find_sql, 0, -3);  // throw away last OR statement
$find_sql = substr($find_sql, 0, -3);  // throw away last OR statement

$sql = "SELECT * FROM stock WHERE upper($field) $find_sql";
echo "SQL Was: " . $sql . "<br />";
$data = mysql_query($sql) OR DIE("SQL Was: " . $sql . "<br />ERROR: " . mysql_error());

  $sql = "SELECT * FROM stock";
  if ($result = mysql_query($sql)) {
    if (mysql_num_rows($result)) {
      // It is now safe to use $result.
    } else {
      // No results found.
    }
  } else {
    // Query failed. Good place to debug, eg;
    echo "Query failed<br />" . $sql . "<br />" . mysql_error();
  }


//And we display the results 
while($result = mysql_fetch_array( $data )) 
{ 
echo "<b>Stock ID:   </b>";
echo $result['ID']; 
echo "<br>"; 
echo "<b>Stock number:   </b>";
echo $result['StockNumber']; 
echo "<br>"; 
echo "<b>Stock description:   </b>";
echo $result['StockDescription']; 
echo "<br>"; 
echo "<b>Price:   </b>";
echo $result['ItemPrice']; 
echo "<br>"; 
echo "<hr>";
} 

//This counts the number or results - and if there wasn't any it gives them a little message explaining that 
$anymatches=mysql_num_rows($data); 
if ($anymatches == 0) 
{ 
echo "Sorry, but we can not find an entry to match your query<br><br>"; 
} 

//And we remind them what they searched for 
echo "<b>Searched For:</b> " .$find; 
} 
?>
</p>
</body>
</html>

Run that and paste what is outputted to the screen.

joshgarrod · June 27, 2007

error:

SQL Was: SELECT * FROM stock WHERE upper(StockDescription) LIKE '%BUNK%' OR LIKE '%BOARD
SQL Was: SELECT * FROM stock WHERE upper(StockDescription) LIKE '%BUNK%' OR LIKE '%BOARD

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 'LIKE '%BOARD' at line 1

joshgarrod · June 27, 2007

It also doesnt work for one word now either??

per1os · June 27, 2007

Wow that was easy huh?

Alright now it is time to fix that sql statement:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Search for spares</title>
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif}
-->
</style>
</head>

<body>
<h2 class="style1">Search</h2> 
<form action="<?=$PHP_SELF?>" method="post" name="search" class="style1">
Seach for: <input type="text" name="find" /> in 
<Select NAME="field">
<Option VALUE="ID">Stock ID</option>
<Option VALUE="StockNumber">Stock number</option>
<Option VALUE="StockDescription">Stock description</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
<p class="style1">
  <?php
//This is only displayed if they have submitted the form 
if ($searching =="yes") 
{ 
echo "<h2>Results</h2><hr>"; 

//If they did not enter a search term we give them an error 
if ($find == "") 
{ 
echo "<p>You forgot to enter a search term"; 
exit; 
} 

// Otherwise we connect to our Database 
mysql_connect("localhost", "sser1", "ixser8") or die(mysql_error()); 
mysql_select_db("ersser") or die(mysql_error()); 

// We preform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Now we search for our search term, in the field the user specified 
$find_array = explode(" ", $find);
$find_sql = "";     // init
foreach ($find_array as $temp)
   $find_sql .= "upper($field) LIKE '%$temp%' OR ";
// note only need to do the substr once, the second time was removing the other %'
$find_sql = substr($find_sql, 0, -3);  // throw away last OR statement 

$sql = "SELECT * FROM stock WHERE $find_sql";
echo "SQL Was: " . $sql . "<br />";
$data = mysql_query($sql) OR DIE("SQL Was: " . $sql . "<br />ERROR: " . mysql_error());

  $sql = "SELECT * FROM stock";
  if ($result = mysql_query($sql)) {
    if (mysql_num_rows($result)) {
      // It is now safe to use $result.
    } else {
      // No results found.
    }
  } else {
    // Query failed. Good place to debug, eg;
    echo "Query failed<br />" . $sql . "<br />" . mysql_error();
  }


//And we display the results 
while($result = mysql_fetch_array( $data )) 
{ 
echo "<b>Stock ID:   </b>";
echo $result['ID']; 
echo "<br>"; 
echo "<b>Stock number:   </b>";
echo $result['StockNumber']; 
echo "<br>"; 
echo "<b>Stock description:   </b>";
echo $result['StockDescription']; 
echo "<br>"; 
echo "<b>Price:   </b>";
echo $result['ItemPrice']; 
echo "<br>"; 
echo "<hr>";
} 

//This counts the number or results - and if there wasn't any it gives them a little message explaining that 
$anymatches=mysql_num_rows($data); 
if ($anymatches == 0) 
{ 
echo "Sorry, but we can not find an entry to match your query<br><br>"; 
} 

//And we remind them what they searched for 
echo "<b>Searched For:</b> " .$find; 
} 
?>
</p>
</body>
</html>

Try that and see what happens. If all works remove the echo "SQL Was:" portion and you are good.

joshgarrod · June 27, 2007

fantastic works a treat, thanks a lot, sorry for my idioticness, lol

per1os · June 27, 2007

I would also remove the OR DIE portion if it is working before you post it to a page, that can be potentially dangerous if someone could see your table scheme.

joshgarrod · June 27, 2007

Genius my friend

Sign In

[SOLVED] Could somebody help me with this code please...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information