Problem with database search!!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mingo
Forum Newbie
Posts: 9
Joined: Fri Nov 22, 2002 3:46 am
Location: London

Problem with database search!!!

Post by mingo »

I am trying to do a database search using the following script:

<html>
<head>
<title>Database Search Results</title>
</head>
<body>
<h1>Database search</h1>
<?
if (!$searchtype || !$searchterm)
{
echo "You have not entered search details. Please go back and try again.";
exit;
}

$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);

@ $db = mysql_pconnect("ccc", "ccc", "ccc");

if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}

mysql_select_db("ccc");
$query = "select * from ccc where ".$searchtype." like '%".$searchterm."%'";
$result = mysql_query($query);

$num_results = mysql_num_rows($result);

echo "<p>Results: ".$num_results."</p>";

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "<p><strong>".($i+1).". First Name: ";
echo stripslashes($row["FirstName"]);
echo "</strong><br>Last Name: ";
echo stripslashes($row["LastName"]);
echo "<br>Phone: ";
echo stripslashes($row["Phone"]);
echo "<br>Email: ";
echo stripslashes($row["Email"]);
echo "</p>";
}

?>

</body>
</html>

This is the message I get:

Warning: Supplied argument is not a valid MySQL result resource in results.php on line 29

and this is line 29: $num_results = mysql_num_rows($result);

what can be wrong???

Thanks a lot!!
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Assuming that $searchterm and $searchtype are returned from a form and that you are using PHP 4.1 or above try the following:

Code: Select all

<html> 
<head> 
<title>Database Search Results</title> 
</head> 
<body> 
<h1>Database search</h1> 
<?php

if (empty($_POST['searchtype']) || empty($_POST['searchterm'])) { 
	echo 'You have not entered search details. Please go back and try again.';
	exit; 
} 

$searchtype = addslashes($_POST['searchtype']); 
$searchterm = addslashes($_POST['searchterm']); 

@$db = mysql_pconnect('ccc', 'ccc', 'ccc') or die(mysql_error()); 
@mysql_select_db('ccc') or die(mysql_error());

$query = "SELECT FirstName, LastName, Phone, Email FROM ccc WHERE ".$searchtype." LIKE '%".$searchterm."%'"; 
$result = mysql_query($query) or die(mysql_error().'<p>'.$query.'</p>'); 

$num_results = mysql_num_rows($result); 

echo '<p>Results: '.$num_results.'</p>'; 

for ($i=0; $i<$num_results; $i++) { 
	$row = mysql_fetch_array($result); 
	echo '<p><strong>'.($i+1).'. First Name: '; 
	echo stripslashes($row['FirstName']); 
	echo '</strong><br>Last Name: '; 
	echo stripslashes($row['LastName']); 
	echo '<br>Phone: '; 
	echo stripslashes($row['Phone']); 
	echo '<br>Email: '; 
	echo stripslashes($row['Email']); 
	echo '</p>'; 
} 

?> 

</body> 
</html>
If you are using PHP 4.0.6 or less replace $_POST with $HTTP_POST_VARS. I think that the problem may be related to register_globals - read:
viewtopic.php?t=511

If it's not this then using mysql_error() should give you a more useful error message.

Mac
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

SHouldn't that be:

$query = "SELECT FirstName, LastName, Phone, Email FROM ccc WHERE Fieldname LIKE " . $searchterm . '%'

Where does $searchtype come into play? Seems like your SQL would be different depending on the searchtype...??
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

fractalvibes wrote:Seems like your SQL would be different depending on the searchtype...??
Why would that be a problem? If you're doing a database search you may want to search on different fields for different things so a dynamic SQL query may be quite handy.

Mac
bchino
Forum Newbie
Posts: 2
Joined: Thu Jan 23, 2003 10:15 am
Location: Austin, TX

"LIKE" comparison on a numeric field

Post by bchino »

"SQL would be different depending on your search type":
It's good and it's not...
If you're hunting on a large table that utilizes indexes, the "like" comparison operator on a numeric field would not utilize the index.
It does kind of a a soft string conversion before performing the comparison.
Worst case scenario: you may see performance issues.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

I guess, as in all things programming related, it comes down to what you need a particular piece of code to do. To carry on from your point bchino, I personally would have stuff in arrays to determine which operator (LIKE, =) should be used for a particular field. That way you avoid using wildcards where they are unneccessary and hopefully make the performance of the search better.

Mac
Post Reply