AND and OR in SqL statement is not working

Question

I am trying to retrieve some information from database but I guess I am doing something wrong because I am only able to retrieve half info not full. Please let me know where i am wrong

In Below code there are two SQL statements 1st) $sql and 2nd) $result. I have problem in $sql statement.

UPDATE- QUESTION HAS BEEN SOLVED.SEE Below for answer

<?php
include "head.html";
echo '</div>';
 $search_id =  $_GET["id"]; 

   $dbhost = 'localhost';
   $dbuser = 'lemonwork';
   $dbpass = 'lemonwork@2014';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'SELECT * FROM lemontable where STORE_ID=';
   $val=" AND (DESCRIPTION=%'Toy'% OR TITLE=%'TOY'% OR Category=%'TOY'%)";
   $sql=$sql.$search_id.$val;
   $result='SELECT  STORE_LOGO, Lemon_COUNT FROM lemoncount where STORE_ID=';
$result=$result.$search_id;
   mysql_select_db('lemondata');
   $retval = mysql_query( $sql, $conn );
   $retval1=mysql_query($result, $conn);
   $x=0;
   if ($result) {
  $row1 = mysql_fetch_assoc($retval1);

echo'<div style="text-align: center;">';
  echo'<img src="'.$row1['STORE_LOGO'].'"style="display: block; margin-left: auto;  margin-right: auto;">';
  echo 'Total Coupons: '.$row1['COUPON_COUNT'].'<br/>';
echo'</div>';
  }
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {   
echo "<table>";
  echo '<tr><th><a href="'.$row["LINK"].'"><img src="'.$row1["STORE_LOGO"].'"></th>';
echo "<td style='padding-left: 6px !important;'><a href='".$row['LINK']."'><h1>".$row["title"]. "</h1></a>";
echo "<br/><strong>By</strong> ".$row["STORE_NAME"]."<br/><strong>Expire on:</strong> ".$row["Dates"]."<br/><strong>Category:</strong> ".$row["Category"]."<br/><strong>work:</strong> ".$row["work"]."<br/><strong>DESCRIPTION:</strong> ".$row["DESCRIPTION"]."<b style='color:Blue'> Read More"."<hr></td></tr>";

}       
echo '</table>';
   mysql_close($conn);
?>

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 15 '17 at 15:25
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 15 '17 at 15:26
`DESCRIPTION=%'Toy'%` < `%` signs should be inside the apostrophes; but realistically you'd be better off with bound parameters in a `MATCH ... AGAINST` with a `FULLTEXT` index across all relevant columns. — CD001, May 15 '17 at 15:27
well for thing, you have a `echo "";` in your loop and then the `
` outside that loop. so that breaks your table's markup. — Funk Forty Niner, May 15 '17 at 15:27
`(DESCRIPTION=%'Toy'%` What's with the `%` symbols? Are you trying to use `LIKE`? — Mark Baker, May 15 '17 at 15:27
@JayBlanchard I know that and in next week I will change it. But for now Please help — manoj kumar, May 15 '17 at 15:28
yes @MarkBaker. I want to find "TOY" in description and title and if its there then it should pring. — manoj kumar, May 15 '17 at 15:29
For debugging, first start by printing out the contents of `$sql`. Does it contain what you think it should? Does this query work when you try running it in an SQL client. Then go from there. But as others have noted, you also have some significant security issues in your code, which should be addressed. — , May 15 '17 at 15:29
@dan1111 When i print $sql with echo $sql; It gives me this statment "select * from lemontable where Store_id=1192 AND (DESCRIPTION %'TOY'% OR TITLE=%'TOY'% " — manoj kumar, May 15 '17 at 15:31
Then use `LIKE` instead of `=`; and the `%` wildcard should be inside the quotes, not outside.... then rewrite it all to use bind vars with a prepared statement — Mark Baker, May 15 '17 at 15:38
@MarkBaker The problem has been solved. Thanks for your help. I cant vote your answers because I dont have enough points but still thanks — manoj kumar, May 15 '17 at 15:48

score 0 · Answer 1 · 2017-05-15T15:46:02.940

I think you want an underlying SQL query like this:

select * from lemontable
    where store_id=1192 and (
       description like '%TOY%' or
       title like '%TOY%'
    )

This will return any records within the given store where the title or the description contain the word toy.

However, rather than simply slightly changing your code to correct this syntax, you really ought to rewrite this in a more secure way, using parameterized queries. The code you have shown here would potentially allow malicious users to manipulate your web form to do things like delete or alter your data, steal private information, or bog down your site to take it off line. In short: not good.

There were a number of helpful links in the comments, but this one in particular gives an example that should serve as a good starting point.

score 0 · Answer 2 · answered Jul 28 '17 at 05:23

Please change your SQL Queries Like below

$sql = "SELECT * FROM lemontable where `STORE_ID` = ".$search_id;
$sql .= " AND ( `DESCRIPTION` LIKE '%Toy%' OR `TITLE` LIKE '%TOY%' OR `Category` LIKE '%TOY%' ) ";

$result = "SELECT  STORE_LOGO, Lemon_COUNT FROM lemoncount where `STORE_ID` = ".$search_id;

AND and OR in SqL statement is not working

2 Answers2