Search db - PHP SQL query shows more than desired results

[defroster]

Hello,

 

I have a search engine on my website that uses the following code:

	//SQL FOR *SEARCH.PHP

 		$table = "videos";
	  $search = $_GET['q'];
	  // explode search words into an array
	  $arraySearch = explode(" ", $_GET['q']);
	  // table fields to search
	  $arrayFields = array(0 => "title", 1 => "titletext", 2 => "tags", 3 => "originaltitle", 4 => "url");
	  $countSearch = count($arraySearch);
	  $a = 0;
	  $b = 0;
	  $sql = "SELECT * FROM ".$table."  WHERE status ='1' AND (";
	  $countFields = count($arrayFields);
	  while ($a < $countFields)
	  {
	    while ($b < $countSearch)
	    {
	      $sql = $sql."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
	      $b++;
	      if ($b < $countSearch)
	      {
	        $sql = $sql." AND ";
	      }
	    }
	    $b = 0;
	    $a++;
	    if ($a < $countFields)
	    {
	      $sql = $sql.") OR (";
	    }
	  }
	  $sql = $sql.") ORDER BY (videos.up-videos.down) DESC
		LIMIT $start, $limit";

$result = mysql_query($sql);	

 

My problem is that the SQL Query includes even videos that have STATUS='0' .. I only want the STATUS='1' to be shown.. How come? Thanks for help

[mikesta707]

Since you are using OR, you need to keep all the OR statements inside 1 group parentheses. Try outputting the whole query as it is right now. once you do that you will probably be able to see where your error is in your code.

 

also, you may want to use a full text search rather than using LIKE. Its faster. read up on it here: http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

[defroster]

Thank you so much for help, but I am a bit confused. My query looks like this today:

 

SELECT * FROM videos WHERE status ='1' AND (title LIKE '%a%') 
OR (titletext LIKE '%a%')
OR (tags LIKE '%a%') OR (originaltitle LIKE '%a%') 
OR (url LIKE '%a%') ORDER BY (videos.up-videos.down) DESC LIMIT 0, 5

 

Where shall I put the status='1' for it to consider it?

 

Thanks a million /df

[cunoodle2]

The issue is that your query will pull the value if ANY of the following items are true....

 

1)  status ='1' AND (title LIKE '%a%')

2) (titletext LIKE '%a%')

3) (tags LIKE '%a%')

4) (originaltitle LIKE '%a%')

5) (url LIKE '%a%')

 

What exactly do you want your results to be?  Since you are using "OR" if ANY of those items are true they will be pulled.  What mikesta707 is saying is that if you move the parenthesis around you will be able to alter your out put.  For example...

 

SELECT * FROM videos WHERE status ='1' AND ((title LIKE '%a%') 
OR (titletext LIKE '%a%')
OR (tags LIKE '%a%') OR (originaltitle LIKE '%a%') 
OR (url LIKE '%a%')) ORDER BY (videos.up-videos.down) DESC LIMIT 0, 5

 

Note that I added in another set of parenthesis.  The query I wrote would be True only in 1 cases...

 

1) status ='1' AND  ANY of the following are true..  (title LIKE '%a%') OR titletext LIKE '%a%')

OR (tags LIKE '%a%') OR (originaltitle LIKE '%a%')

OR (url LIKE '%a%')

 

 

[cunoodle2]

I edited my post while you were viewing the page.  Please refresh to get the correct code.

 

[defroster]

Ahh. I get it now! Thanks a million for the explanation

[defroster]

That worked like a charm THANKS again