[SOLVED] searching a table in mysql

[rondog]

I've searched through these forums about searching and didnt really find anything. All the questions that were asked were unanswered. I searched google and came up with this: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

 

except I am not sure if thats what I should be using nor do I understand how to write a query using that. Ill just be searching 1 field in 1 table. Can somone help me with an example query string ? Also I set the field as TEXT since I will just be adding keywords separated by commas into them. Should it be something else?

[haaglin]

try using 'LIKE':

 

SELECT * FROM `table` WHERE field LIKE `%searchtext%`

[MmmVomit]

Can you give us more information?

 

What are the columns in your table?

Can you give an example of what might be stored in those columns?

What precisely do you want to search for?

[rondog]

Thank you haaglin.

 

Can you give us more information?

 

What are the columns in your table?

Can you give an example of what might be stored in those columns?

What precisely do you want to search for?

 

The column name I want to search is called 'keywords'

 

Its essentially a bunch of video that I want to add keywords too like (red truck, fire engine, water hose) etc. If the user searches 'fire engine' I want whatever results with fire or engine in them to pop up.

[MmmVomit]

You want to use the LIKE operator.

 

<?php

$sql = "SELECT * FROM table
WHERE video_description LIKE '%$user_search_param%';";

?>

 

If you want the search criteria to be broken apart so that "fire engine" searches for anything containing either "fire" or "engine", you'll have to do a little more parsing of the search parameters.

 

I'd suggest using explode to split all the search parameters out into an array, then use a foreach loop to append all the LIKE clauses.

[rondog]

cool I am going to look more into this LIKE param and see what more I can get out of it. Thanks all!

[rondog]

I have another question actually. When you say explode whatever they input into an array, how would I write it so that its adds LIKE '%$arr[0]%' OR LIKE '%$arr[1]%' OR LIKE '%$arr[2]%' depending on how many words they input.

 

I figured out the explode thing and calling it from the array I just dont know how to write the loop that will do the LIKE clause X amount of times thanks!

<?php
  $searchtxt = $_GET['search'];
  echo("<span class=\"regtxt\">Search results for <b>$searchtxt</b>.</span><br />");
  $arr = explode(' ', $searchtxt);
  $searchquery = mysql_query("SELECT * FROM videos WHERE keywords LIKE '%$searchtxt%'") or die(mysql_error());
?>

[MmmVomit]

Look up a foreach loop in the PHP manual.  On each iteration of the loop append another LIKE expression to the sql query.

[rondog]

I kind of figured it out.

<?php
  $searchtxt = $_GET['search'];
  echo("<span class=\"regtxt\">Search results for <b>$searchtxt</b>.</span><br />");
  $arr = explode(' ', $searchtxt);
  $arrlength = count($arr);
  $sql = "SELECT * FROM videos WHERE LIKE '%$arr[0]%' ";
  for($i = 1;$i<$arrlength;$i++) {
  	$sql .= "OR '%$arr[$i]%' ";
  }
  $searchquery = mysql_query($sql) or die(mysql_error());
?>

 

except I am getting this error:

 

Search results for another wall.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '%another%' OR '%wall%'' at line 1

[rondog]

am I doing that select statement wrong or something?

[rondog]

really nobody knows?

[MmmVomit]

WHERE videos LIKE '%$arr[0]%' OR videos LIKE '%$arr[1]%' OR ...

 

I would do it like this.

 

<?php
  $searchtxt = $_GET['search'];
  echo("<span class=\"regtxt\">Search results for <b>$searchtxt</b>.</span><br />");
  $arr = explode(' ', $searchtxt);
  //$arrlength = count($arr); NOT NEEDED

  $sql = "SELECT * FROM videos WHERE 1=1";
  foreach($arr as $v)
  {
    $sql .= " OR videos LIKE '%$v%'";
  }
  $searchquery = mysql_query($sql) or die(mysql_error());
?>

 

You didn't look up foreach loops like I suggested ;-)  I don't think we're in C anymore, Toto.

[rondog]

doesnt my method do the same thing? Only thing I notice is you dont do as many calls as I do. So I guess yours would run faster correct? Is that the only difference ?

[MmmVomit]

You can absolutely use a for loop, but foreach loops are designed specifically for traversing arrays, so why not use them?  Your for loop was absolutely correct, and it's just a matter of coding style.  I would guess the difference in processing time is negligible

 

The problem with your SQL syntax was that you were thinking like a human, not a computer.  Computers can't infer what column you want to operate on.  You need to tell it each and every time.

 

A computer won't understand this.

 

if($a == $b || == $c)

 

You have to write it like this.

 

if($a == $b || $a == $c)

 

Similarly, you can't do this in SQL.

 

some_column LIKE '%a%' OR LIKE '%b%'

 

You have to do this.

 

some_column LIKE '%a%' OR some_column LIKE '%b%'

 

[rondog]

ahh ok I gotcha. That makes sense. And as far as the foreach loops go. Ive been doing AS for a long time and its always been for loops. No foreach in AS. Very handy to know thanks.

  <?php
  $searchtxt = $_GET['search'];
  if($searchtxt == "") {
  echo("<span class=\"regtxt\">You have to search for something.</span><br />");
  } else {
  echo("<span class=\"regtxt\">Search results for <b>$searchtxt</b>.</span><br />");
  $arr = explode(' ', $searchtxt);
  $sql = "SELECT * FROM videos WHERE keywords LIKE '%$searchtxt%'";
  foreach($arr as $v) {
	$sql .= " OR keywords LIKE '%$v%'";
  }
  $searchquery = mysql_query($sql) or die(mysql_error());
  while($row = mysql_fetch_array($searchquery)) {
  	$item = $row["vidname"];
	echo("$item<br />");
}
  }
  ?>

 

I didnt quite understand your 1=1 part in your code as it just output every single video in my DB so I just modified it so it searches the entire string as one and then each individual word.

[MmmVomit]

'WHERE 1=1' just makes the loop easier to deal with.  Each time the loop runs you append something like this.

 

 OR a LIKE 'b'

 

Without the 'WHERE 1=1' your sql query would end up looking like this.

 

SELECT *
FROM sometable
WHERE OR a LIKE 'b'

 

You would end up getting a syntax error.  There has to be a valid expression before an OR in the WHERE clause.  '1=1' is a valid expression that basically does nothing.  It's a place holder to make the syntax of the sql query work.  It makes your sql query come out like this, instead.

 

SELECT *
FROM sometable
WHERE 1=1 OR a LIKE 'b'

 

No syntax errors, and you don't have to bother appending element 0, then looping through elements 1..n.  You can just loop through elements 0..n blindly.

 

EDIT:  I like your solution better where you use 'keywords LIKE '%$searchtext%' instead of '1=1'

[rondog]

Sorry to keep bugging you. You have been a tremendous help to me, but I have a small issue that is probably just the way my select is set up. For testing purposes, 3 videos have a keyword "wall"

 

If I search for 'wall' they all come up. If I search for 'walls' I get nothing? I think it has something to do with the wildcard.

<?php
$searchtxt = $_GET['search'];
if($searchtxt == "") {
echo("<span class=\"regtxt\">You have to search for something.</span><br />");
} else {
$arr = explode(' ', $searchtxt);
$sql = "SELECT * FROM videos WHERE keywords LIKE '%$searchtxt%'";
foreach($arr as $v) {
	$sql .= " OR keywords LIKE '%$v%'";
}
$searchquery = mysql_query($sql) or die(mysql_error());
$numresults = mysql_num_rows($searchquery);
if($numresults > 1) {		  
	echo("<span class=\"searchtxtbig\">Found <b>$numresults</b> results for <b>$searchtxt</b>.</span><br /><br /><br />");
} else {
	echo("<span class=\"searchtxtbig\">Found <b>$numresults</b> result for <b>$searchtxt</b>.</span><br /><br /><br />");
}
while($row = mysql_fetch_array($searchquery)) {
	$cam = $row["camera"];
	$camrpl = str_replace("cam_", "", "$cam");
	$tape = $row["tape"];
	$taperpl = str_replace("tape_","","$tape");
	$vid = $row["vidname"];
	$vidrpl = str_replace(".flv","","$vid");
	$descr = $row["keywords"];
	echo("<a href=\"video.php?vid=$cam/$tape/$vidrpl&cam=$camrpl&tape=$taperpl\"><span class=\"regtxt\">$vidrpl</a><br />$descr</span><br /><br />");
}
}
?>

[MmmVomit]

There's nothing wrong with the query.  To account for searches like that you would have to do a lot of processing.  I say leave it like it is and leave it up to the user to account for plural vs. singular words.

[rondog]

ok sounds good..even google returns different results with wall and walls. I just need more data in my DB =] Thanks sooooo much you helped me a lot!