SEARCH PHP MYSQL QUERY

Question

I am trying to make a search program. I have three tables: postivewords, negativewords and recommendationwords. These tables consist only of word_id then the word. How I do it in a query? This is what I have so far. Please correct me if I am wrong.

if(isset($_POST['searchword']))    
{        
    $word = $_POST['search'];            
    $search1= mysql_fetch_array(mysql_query("SELECT * FROM positivethesaurus where word like '%$word%'"));
    $search2= mysql_fetch_array(mysql_query("SELECT * FROM negativethesaurus where word like '%$word%'"));    
    $search3= mysql_fetch_array(mysql_query("SELECT * FROM recommendationthesaurus where word like '%$word%'"));
}

You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Feb 27 '13 at 12:13

idipous · Answer 1 · 2013-02-27T12:47:35.107

1

select * from negativethesaurus, positivethesaurus,recomendationthesaurus where negativethesaurus.word like '%"word%' or positivethesaurus.word like '%word%' or recomendationthesaurus.word like '%word%';

This may be not the fastest way (you would use indexes and freetext) but it will be down on only one query.

Oh yes, that been said this query is vulnerable to sql injection attacks.

edited Feb 27 '13 at 12:47

answered Feb 27 '13 at 12:17

idipous

2,868
3
30
45

#1052 - Column 'word' in where clause is ambiguous – troubledone Feb 27 '13 at 12:36
echo "
"; echo "Search word here: "; echo ""; echo "
"; if(isset($_POST['searchword'])) { $word = $_POST['search']; $result = mysql_query("SELECT * FROM positivethesaurus where word like '%$word%'"); while($row = mysql_fetch_array($result)) { echo $row['word']."
"; } } – troubledone Feb 27 '13 at 12:38

Roger · Answer 2 · 2013-02-28T07:28:50.817

0

$query = SELECT positivethesaurus .*,negativethesaurus.*,recommendationthesaurus.*   FROM positivethesaurus,negativethesaurus,recommendationthesaurus where positivethesaurus.word like '%$word%' OR negativethesaurus.word like '%$word%' OR recommendationthesaurus.word like '%$word%'";

UPDATES if you want to check if results not found then do it like this

<?php
 $query = mysql_query("Your Query Here");
 $rowCount = mysql_num_rows($query);
 if($rowCount>0) {
   // DO YOur STUFF IF RESULTS FOUND
 } else {
  echo "No Results Found";
 }
?>

Please not that mysql_* function should not be used

Hope this works. Not Tested though. Let me know if you find any problem

edited Feb 28 '13 at 07:28

answered Feb 27 '13 at 12:18

Roger

1,693
1
18
34

i wanna do a validation for this one.. where if the word does not exist?? it will echo word not found? how do i do this in a while loop? echo "
"; echo "Search word here: "; echo ""; echo "
"; if(isset($_POST['searchword'])) { $word = $_POST['search']; $result = mysql_query("SELECT * FROM positivethesaurus where word like '%$word%'"); while($row = mysql_fetch_array($result)) { echo $row['word']."
"; } } – troubledone Feb 27 '13 at 12:39

score 0 · Answer 3 · answered Feb 27 '13 at 12:18

0

also do mysql_escape_string to avoid sql injection

  $word = $_POST['search'];
  $word = mysql_escape_string($word);

answered Feb 27 '13 at 12:18

Dino Babu

5,814
3
24
33

what does the mysql escape do again? :/ – troubledone Mar 02 '13 at 17:12

kapil · Answer 4 · 2013-02-27T12:33:50.090

-1

1) Select the respected column instead of select * from 2) avoid sql injection

edited Feb 27 '13 at 12:33

answered Feb 27 '13 at 12:20

kapil

162
5

1

Sql injection is not something you want to use. It's something you want to avoid being used against you. – idipous Feb 27 '13 at 12:23

SEARCH PHP MYSQL QUERY

4 Answers4