 |
|
 |
Main Menu |
|
 |
Forums |
|
 |
Programming Contest |
|
 |
Documentation
|
|
 |
Partner
Sites |
|
 |
Sponsors |
|
 |
|
|
Register •
FAQ
• Search • Login
|
|
Page 1 of 1
|
[ 3 posts ] |
|
Multiple table and field search code - problem
Author |
Message |
ld0121
Junior Member
Joined: Tue Jun 10, 2003 1:38 pm Posts: 2
|
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 |
|
 |
ld0121
Junior Member
Joined: Tue Jun 10, 2003 1:38 pm Posts: 2
|
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 |
|
 |
barand
Junior Member
Joined: Sat May 10, 2003 1:39 pm Posts: 37
|
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 |
|
 |
|
|
Page 1 of 1
|
[ 3 posts ] |
|
Who is online |
Users browsing this forum: No registered users and 0 guests |
|
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
|
|
|
|