View unanswered posts | View active topics It is currently Tue Nov 20, 2018 2:13 am



Reply to topic  [ 3 posts ] 
 Multiple table and field search code - problem 
Author Message
Junior Member

Joined: Tue Jun 10, 2003 1:38 pm
Posts: 2
Post 
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);




Im realy new to this and get very confused very easily, any help would be great.

Cheers
Lee


Tue Jun 10, 2003 1:40 pm
Report this post
Profile
Junior Member

Joined: Tue Jun 10, 2003 1:38 pm
Posts: 2
Post 
Hi thanks for the reply, I put in the echos and found that it wasnt seeing the whole query so I have changed the query around a bit. The problem I have now is when I Search a word ie. "zone" 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 "zone". The amount pf results returned equals the number of fields in the 2 tables, I dont understand why its doing this when the echo sugests that its just searching for zone....

select * from gzdata, gzdata1 where gzdata.name like '%zone%' OR gzdata1.name like '%zone%' 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 "

Please enter a search...</p>";
exit;
}

// check for a search parameter
if (!isset($var))
{
echo "

We dont seem to have a search parameter!</p>";
exit;
}

//connect to your database
mysql_connect("localhost","XXX","XXXXX"); //(host, username, password)

//specify database
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 "

SQL Query Output:</p>";
echo "$query";
print ("
" . 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 "

Sorry, your search: "" . $trimmed . "" returned zero results</p>";

// google
echo "

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

You searched for: "" . $var . ""</p>";

// begin to show results set
echo "Results
";
$count = 1 + $s ;

{
echo "

Results Output:</p>";
echo "$query";
echo " $result";
echo " $count";
print ("
" . 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
$info
" ;
echo "<a href=\"" . $row["url"] . "\">$url</a>

";
$count++ ;
}

$currPage = (($s/$limit) + 1);

//break before paging
echo "
";

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

Showing results $b to $a of $numrows</p>";

?>


Wed Jun 11, 2003 3:16 pm
Report this post
Profile
Junior Member

Joined: Sat May 10, 2003 1:39 pm
Posts: 37
Post 
Quote:
select * from gzdata, gzdata1 ...


When you select from 2 or more table without specifying a join condition, you get the records in one table joined with all records in the other in all combinations. So if 100 recs in each of 2 tables you get 10000 rows.

_________________
Barand


Sun Jun 15, 2003 4:47 am
Report this post
Profile
Display posts from previous:  Sort by  
Reply to topic   [ 3 posts ] 

Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
© Copyright 2003-2008 www.php-editors.com. The ultimate PHP Editor and PHP IDE site.