php search script

[andy_b_1502]

I would like to edit this script's SQL query to SELECT FROM multiple tables within my database. Can this be done first of all and what would i need to change?

 

here's the script:

 

<?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("host","username","password"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("database name") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select * from the_table where 1st_field like \"%$trimmed%\"  
  order by 1st_field"; // EDIT HERE and specify your table and field names for the SQL query

$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";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row["1st_field"];

  echo "$count.) $title" ;
  $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>&nbsp ";
  }

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

 

All i want it to do is search a field in 3 different tables in my database called "postcode" and return search results. Ive seen the JOIN feature but don't know how to use it, any help i would appreciate. Thank you.

[andy_b_1502]

Just to add, when i alter the code to look like this:

 
$query = "select * from freelistings where postcode like \"%$trimmed%\"  
  order by postcode";

 

i get a result in my browser:

You searched for: "ws122sh"

 

Results1.)

 

Showing results 1 to 1 of 1

 

 

which is great, but that's only searching the one table and not the other two? when i change the code to include the others like this:

$query = "select * from freelistings,basicpackage,premiumuser where postcode like \"%$trimmed%\"  
  order by postcode";

 

i get this error message:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /hermes/bosweb25a/b109/ipg.removalspacecom/new - - removalspace.com/search.php on line 35

 

Results

Sorry, your search: "ws122sh" returned zero results

 

Click here to try the search on google

 

Couldn't execute query

 

which is not picking up the example postcode at all?

[Psycho]

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

That means your query is failing. You should always have error handling to see the errors.

 

Yes this can be done. Based upon what I think you want to do, you probably want to do a UNION to get one result set from multiple tables. But, there are some specific things you will need to do to make that work. Primarily, the results from each table must "match" - they must return the same number of fields and each field must be of the same general type. So, if the first field returned from the first table is a text field, then the first field returned from the other tables must be a text field as well.

 

You shouldn't be using "SELECT *" anyway, but in this situation you will definitely need to explicitly list of the fields to return for each table and ensure they match up.

 

Although, if these tables contain the exact same fields you shouldn't be using separate tables to begin with.

[andy_b_1502]

Thank you, i do have a field called postcode in all of my tables although they are not all on the same number order, does this matter? they all match otherwise.

 

What should i be using instead of SELECT *

 

And how do i type UNION to make it work

 

$query = "select * from freelistings UNION basicpackage UNION premiumuser where postcode like \"%$trimmed%\"  
  order by postcode";

 

[Psycho]

Thank you, i do have a field called postcode in all of my tables although they are not all on the same number order, does this matter? they all match otherwise.

What do you mean they are not in the same order? In the order they are listed with the other fields in the table? It won't matter as long as you select the same number of fields from the tables and the field types match. As I already stated above.

 

What should i be using instead of SELECT *

You should be SELECTing the actual fields you want to query. Using '*' is a waste since it returns all the fields from the associated tables. If you only need certain fields, then you shoudl be listing them in your query

SELECT field1, field2, field3 FROM sometable

 

And how do i type UNION to make it work

You have a computer and you have access to the internet. Did you even do one search to look for information on using UNION? There are many pages available out there that will do a superior job of explaining how to use UNION than I can do within the confines of a forum post. It's not that I don't want to be helpful, but you should at least put forth some effort to solving your problem instead of expecting others to do it for you.

[andy_b_1502]

Sorry i thought it was a coding help forum, i'll do what you say. Thanks for your help so far.

[Psycho]

Sorry i thought it was a coding help forum, i'll do what you say. Thanks for your help so far.

Yes, it is. It is to get help on code you have written. If you had provided enough information for me to work with I might have been able to try and throw something together. But, I don't know what fields you need, the field types, etc. etc. As I stated above, there are some specific requirements for using UNION. It probably would have taken several posts back and forth for me to get the information I need to create the query for you - and you wouldn't have really learned much in the process. However, if you were to read a tutorial on how to use UNION and figured out how to implement it yourself you would learn a great deal.

 

So, give it a try. If you have problems then post what you have and the problems you are encountering.

[andy_b_1502]

Okay well here's the problem:

 

Used UNION as suggested. I was just testing it out so i inserted two example postcodes in two seperate tables in my database. "WS12 2SH", "WS12 5SH".

 

I searched a completly random letter "q" in my searchbar. The results were:

 

"You searched for: "Q"

 

Results for Q WS12 5SH WS122SH

 

Showing results 1 to 2 of 2

 

Click here to go to your local area"

 

As you can see, its picking up whatever is in the table fields, regardless of whats being searched? firstly why is this happeneing, secondly what do you need to know to help me remedy this. please note* i will learn by mistakes, a proccess many learn by, i could just trial-and-error for weeks maybe months like how i've done before but why? when there are these forums with more knowledgable folk that are considered experts on php that are willing to share their craft. Just my opinion.

[Buddski]

firstly why is this happeneing

The short answer is your code is wrong. And since you went away and modified your code, presumably after reading about UNION, it could be any number of possibilities.

We can help, but unless there is a specific detailed question, or a question based on supplied code, there is little for us to go on.

 

If you post your newest code variant we might be able to shed some light on the situation.

[andy_b_1502]

Thanks:

 

<?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("server","username","password"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("removalspacelogin") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select postcode from freelistings UNION select postcode from basicpackage UNION select postcode from premiumuser where postcode like \"%$trimmed%\"  
  order by postcode"; // EDIT HERE and specify your table and field names for the SQL query

$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 for $q";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row['postcode'];

  echo " $title" ;
  $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>&nbsp ";
  }

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

<form method="post" action="localarea.php">
Click here to go to your local area
<input type="submit" name="Submit" value="go" />
</form>

 

 

[Psycho]

There is no need to post your entire script - only the part that is relevant to the issue at hand. You should be able to determine what that is based upon what you have written and what your issue is. Posting the entire thing makes it difficult for those of us having to pick through the entire thing trying to figure out what's going on.

 

After hunting down the query in your code, the problem is obvious. By the way, I would highly suggest writing your queries in a structured format for easy reading - it makes debugging these types of errors much easier.

 

Here is your query in a formatted fashion:

(SELECT postcode FROM freelistings)
  UNION
(SELECT postcode FROM basicpackage)
  UNION
(SELECT postcode FROM premiumuser
WHERE postcode LIKE '%$trimmed%')
ORDER BY postcode

 

Look at the WHERE clause and see if you can tell how that WHERE clause would be applied. Do you see the problem? Hint: each SELECT statement is interpreted individually.

 

please note* i will learn by mistakes, a proccess many learn by, i could just trial-and-error for weeks maybe months like how i've done before but why? when there are these forums with more knowledgable folk that are considered experts on php that are willing to share their craft. Just my opinion.

Well, I would like to point out that I have NEVER actually used a UNION in a query. However, I found the solution to your problem in less than a minute by Googling "Mysql Union". I skipped the first result, which was the MySQL manual as it is not the most strait-forward documentation, and instead clicked the second link which was a tutorial for MySQL UNION. Although it didn't show an example with a WHERE clause the tutorial was illustrative enough that I could see how they would be implemented. A good programmer is not one who has memorized every function and how it works. A good programmer is one that can (among other things) easily find the functions and how to implement them.

 

EDIT: Actually, I just looked at the MySQL manual for UNION and the very first example query showed exactly how you would utilize UNION with WHERE clauses.

 

So, you came here asking a question on how to accomplish something and I gave you the answer - use UNION. You then expected me to write code for you instead of you taking a few minutes out of your day to learn how to use UNION. Again, I have never used UNION myself - I only knew the general concept of what it can accomplish - which is what I provided to you in my first response. So, we both had the exact same information but I was able to find the solution by doing a little work. The only reason I did respond to your last post was that you at least made an effort. That is all I expect from someone asking for my FREE help - that you show some effort.

[andy_b_1502]

Sorry for posting the whole code and thank you very much. From the help you've given me so far i've looked up on the WHERE clause.

 

So the WHERE is used to exctract only those records, in this case "postcode" like the searched postcode for the user.

 

Therefore the WHERE clause should be under each SELECT like this so its matching the search with the MySQL fields in the tables:

 

$query = 
"select postcode from freelistings
WHERE postcode like \"%$trimmed%\"  
  order by postcode 
UNION 
select postcode from basicpackage 
WHERE postcode like \"%$trimmed%\"  
  order by postcode
UNION 
select postcode from premiumuser 
WHERE postcode like \"%$trimmed%\"  
  order by postcode";

 

I know that the UNION is combining them together, the WHERE is taking only the postcode value and the ORDER BY sorts the data.

 

This script is failing so ive not sussed it yet, ive turned on error repporting and placed it at the top of the script with:

 

ini_set(‘display_errors’,1);

 

but all i get is:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /hermes/bosweb25a/b109/ipg.removalspacecom/new - - removalspace.com/search.php on line 47

 

Results

Sorry, your search: "ws122sh" returned zero results

 

Click here to try the search on google

 

Couldn't execute query

error_reporting(E_ALL|E_STRICT);

 

that postcode is definatley in one of the tables but it's not picking it up?

[Psycho]

Error reporting will only show you PHP errors - not Database errors. Echo out the generated query and the mysql error message.

 

  $result = mysql_query($query) or die("<b>Query:</b> $query<br><b>Error</b>: " . mysql_error());

 

OK, looking at the query you built though - it doesn't look right. Here is the 2nd example from the MySQL Manual for UNION

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

 

You see how they enclosed the sub-queries in parens. I don't think that is necessarily required in all situations. But, you have the added problem that you have a field with the exact same name in all the tables. So, it would be best to make sure you "separate" those sub-queries as much as you can. Lastly, you don't want the sub-queries ordered individually - you want them ordered together, correct?

 

So, I would follow the example above and use

$query = "(SELECT postcode FROM freelistings
             WHERE postcode like '%$trimmed%')
          UNION 
          (SELECT postcode FROM basicpackage 
             WHERE postcode like '%$trimmed%')
          UNION 
          (SELECT postcode FROM premiumuser 
             WHERE postcode like '%$trimmed%')
          ORDER BY postcode";

[andy_b_1502]

I just tried to change the field name "postcode" on each table and tried the new code:

 

$query = "(SELECT fpostcode FROM freelistings
             WHERE fpostcode like '%$trimmed%')         
		UNION           
	  (SELECT bpostcode FROM basicpackage              
	  	 WHERE bpostcode like '%$trimmed%')          
		UNION           
	  (SELECT ppostcode FROM premiumuser              
	  	 WHERE ppostcode like '%$trimmed%')         
	  ORDER BY postcode";

 

but unfortunatley i still get error message:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /hermes/bosweb25a/b109/ipg.removalspacecom/new - - removalspace.com/search.php on line 44

 

Results

Sorry, your search: "ws122sh" returned zero results

 

Click here to try the search on google

 

Couldn't execute query

 

 

[Psycho]

Did you implement the line of code I provided above so you could see the actual database error? The PHP errors are not going to help you. You need to see the actual query that was submitted to the DB server and the DB error. It could be something as simple as your $trimmed value is not what you expect.

 

Regarding your last query - I would expect it to fail. Since you changed the field names the ORDER BY clause is referencing a field that does not exist. You would need to alias the field for each sub-query to be the same. But, that wasn't even needed - you could have left the field names alone. That was obvious according to the example from the manual I posted above.

 

Anyway, I just created three tables (freelistings, basicpackage, and premiumuser) each with a field called postcode. I then ran the query I provided to you previously and it worked perfectly with the results I was expecting and in the proper order. Like I said, you need to add error handling for the query. If the query fails echo the "constructed" query and the DB error.

[andy_b_1502]

Sorry no, i didnt put the error reporting in. Once i did that it worked, could you please explain why putting in error reporting made it work correctly, i dont understand why that would help?

 

Thank you very much for your help guys!

[Psycho]

The error reporting wouldn't make it work. Most likely you happened to change something else in the process and fixed the actual error at the same time.