Hi all,
I have code that should allow me to search multiple tables and fields in my MySQL database....
So far I only has these 2 tables.....
Table 1 is gzdata name, info, url
Table 2 is gzdata1 name, info, url
but I get an error.....
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/xxxx/xxxx/xxxxx/search2.php on line 47
line 47 is
$numrows=mysql_num_rows($numresults);
Here is the full code...
<?php
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","XXXXXX","XXXXXXX"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("XXXXXX") or die("Unable to select database"); //select which database we're using
// Build SQL Query
$query = "select * from gzdata, gzdata1 where";
$query. " gzdata.name like '%$trimmed%'";
$query. " OR gzdata1.name like '%$trimmed%'";
$query. " ORDER by gzdata.name";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results, offer a google search as an alternative
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
// google
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
// begin to show results set
echo "Results<br>";
$count = 1 + $s ;
// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$url = $row["url"];
$name = $row["name"];
$info = $row["info"];
echo "$count.) $name<br>$info<br>" ;
echo "<a href=\"" . $row["url"] . "\">$url</a><p>";
$count++ ;
}
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 10</a> ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
Im realy new to this and get very confused very easily, any help would be great.
Cheers
Lee
Problem searching multiple tables and fields
Moderator: General Moderators
echo
try echoing out your query to the page then running it just against the database. If you are using phpMyAdmin then it will at least give you some idea where they might be a problem with your query.
phpScott
phpScott
Hi thanks for the reply, I put in the echos and found that it wasnt seeing the whole query so I have changed that. The problem I have now is when I Search a work ie. klm which has only 1 entry in each table so 2 results, I get about 10 multiple results of exactly the same thing and results that dont contain "klm", I dont understand why its doing this when the echo sugests that its just searching for klm....
select * from gzdata, gzdata1 where gzdata.name like '%klm%' OR gzdata1.name like '%klm%' ORDER by gzdata.name
Any other thoughts?
Cheers
Lee
Updated Code below:
<?php
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","XXX","XXXXX"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("XXXXX") or die("Unable to select database"); //select which database we're using
// Build SQL Query
$query = "select * from gzdata, gzdata1 where gzdata.name like '%$trimmed%' OR gzdata1.name like '%$trimmed%' ORDER by gzdata.name";
{
echo "<p>SQL Query Output:</p>";
echo "$query";
print ("<br>" . mysql_error());
}
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results, offer a google search as an alternative
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
// google
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
// begin to show results set
echo "Results<br>";
$count = 1 + $s ;
{
echo "<p>Results Output:</p>";
echo "$query";
echo " $result";
echo " $count";
print ("<br>" . mysql_error());
}
// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$url = $row["url"];
$name = $row["name"];
$info = $row["info"];
echo "$count.) $name<br>$info<br>" ;
echo "<a href=\"" . $row["url"] . "\">$url</a><p>";
$count++ ;
}
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 10</a>  ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
select * from gzdata, gzdata1 where gzdata.name like '%klm%' OR gzdata1.name like '%klm%' ORDER by gzdata.name
Any other thoughts?
Cheers
Lee
Updated Code below:
<?php
// Get the search variable from URL
$var = @$_GET['q'] ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($trimmed == "")
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>We dont seem to have a search parameter!</p>";
exit;
}
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","XXX","XXXXX"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("XXXXX") or die("Unable to select database"); //select which database we're using
// Build SQL Query
$query = "select * from gzdata, gzdata1 where gzdata.name like '%$trimmed%' OR gzdata1.name like '%$trimmed%' ORDER by gzdata.name";
{
echo "<p>SQL Query Output:</p>";
echo "$query";
print ("<br>" . mysql_error());
}
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results, offer a google search as an alternative
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";
echo "<p><a href=\"http://www.google.com/search?q="
. $trimmed . "\" target=\"_blank\" title=\"Look up
" . $trimmed . " on Google\">Click here</a> to try the
search on google</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
// begin to show results set
echo "Results<br>";
$count = 1 + $s ;
{
echo "<p>Results Output:</p>";
echo "$query";
echo " $result";
echo " $count";
print ("<br>" . mysql_error());
}
// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$url = $row["url"];
$name = $row["name"];
$info = $row["info"];
echo "$count.) $name<br>$info<br>" ;
echo "<a href=\"" . $row["url"] . "\">$url</a><p>";
$count++ ;
}
$currPage = (($s/$limit) + 1);
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><<
Prev 10</a>  ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
}
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>