Two Conditions on MySQL WHERE

Question

My error is: 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 ') AND (tblforecast.Issued like '%01-07%' ))order by tblforecast.Issued DESC' at line 2

My code is:

$news = "SELECT tblforecast.Fore_ID, tblforecast.Issued, 
          tblforecast.Synopsis,tblforecast.Forecaster, tblusers.FirstName,
          tblusers.LastName FROM tblforecast 
             INNER JOIN tblusers ON tblusers.UserNumber = tblforecast.forecaster 
          WHERE ((tblforecast.Valid =$type) 
              AND (tblforecast.Issued like '%$filter%' ))
           order by tblforecast.Issued DESC";

If I only have one condition in my WHERE statement, it works.

$type is either 24 or 12. $filter is "mm-dd" in numeric form.

after this is executed, it will proceed to the displaying of the news which is:

$loadnews = mysql_query($news);

                if (!($loadnews))
                    echo mysql_error();

                while($waiting = mysql_fetch_array($loadnews))
                {
                    $NiD = $waiting['Fore_ID'];
                    $thedate = $waiting['Issued'];
                    $synop = $waiting['Synopsis'];
                    $forecaster = $waiting['FirstName']." ".$waiting['LastName'];

                    $dDate = strtotime($thedate);
                    $newDate = date('F j, Y',$dDate);
                    echo "<div class='well well-large'>
                            <p><b>$newDate</b>
                            <br><small><i>$forecaster</i></small>
                            <br>$synop</p>
                            <br><small><a href='pdetails.php?NiD=$NiD'>Read More</a></small></p>
                            </div>";                    
                }

In response to the requests for the content of $news, this is what it holds after the assignment statement:

SELECT tblforecast.Fore_ID, tblforecast.Issued, tblforecast.Synopsis,tblforecast.Forecaster, tblusers.FirstName, tblusers.LastName FROM tblforecast INNER JOIN tblusers ON tblusers.UserNumber = tblforecast.forecaster WHERE ((tblforecast.Valid =) AND (tblforecast.Issued like '%01-07%' ))order by tblforecast.Issued DESC

Are you using `&&` or `AND`, because the error message doesn't match the code you've posted — Mark Baker, Feb 03 '14 at 08:14
Your syntax is completely correct. So the problem is in your substitution variable, `$filter` — Alma Do, Feb 03 '14 at 08:14
Seems like there's no syntax problem, your problem must be logical. If you can provide sql fiddle we can more likely be able to help — FreshPro, Feb 03 '14 at 08:15
its either `$filter` or `$type` containing some un-escaped sequence — TheVillageIdiot, Feb 03 '14 at 08:15
did you try to print out the value in the variable echo $news.. — shanavascet, Feb 03 '14 at 08:16
@shanavascet yes. it is working already. I just added a new feature which is the $filter (it's from another module which i decided to include in this) — Clary, Feb 03 '14 at 08:17
Is the `$type` value quoted or not? Is it a string or a numeric value? — Mark Baker, Feb 03 '14 at 08:17
@MarkBaker agreed. I'm guessing he means for it to be a string, while this way it's checking it as a field — Andrew Brown, Feb 03 '14 at 08:20
How bout that ? $news="SELECT tblforecast.Fore_ID, tblforecast.Issued, tblforecast.Synopsis, tblforecast.Forecaster, tblusers.FirstName, tblusers.LastName FROM tblforecast INNER JOIN tblusers ON tblusers.UserNumber = tblforecast.forecaster WHERE tblforecast.Valid = " . $type . "AND tblforecast.Issued LIKE '%" . $filter% . "' ORDER BY tblforecast.Issued DESC"; — Alexander, Feb 03 '14 at 08:21
Clary, @shanavascet is right, print out (and show us) what's in `$news` before trying to execute it. — paxdiablo, Feb 03 '14 at 08:22
if $type is a number that is a value in the DB, you must enclose it in single quotes, otherwise it is looking for a field with that name — Andrew Brown, Feb 03 '14 at 08:22
@AndrewBrown never do that with an INTEGER, works fine. If you'll use single quotes, it will search for a VARCHAR — Alexander, Feb 03 '14 at 08:25
Clary, you've updated the question but _not_ with the content of `$news` - it's important to see what's actually going into there. — paxdiablo, Feb 03 '14 at 08:27
@Clary http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Alexander, Feb 03 '14 at 08:27
my mistake, it doesn't search for a field, but you are perfectly fine using quotes around an integer, and searching integer fields — Andrew Brown, Feb 03 '14 at 08:28
Oh boy... please [read the red box](http://www.php.net/mysql_connect): `mysql_*` extension is _deprecated_, so don't use it. Use `PDO` or `mysqli_*` instead. Both support _prepared statements_, which will help you to fix that nasty injection vulnerability you have — Elias Van Ootegem, Feb 03 '14 at 08:29
@Clary: what is `$type`, does it contain any quotes and what happens if you add a space to `=$type` => `= $type`? — Elias Van Ootegem, Feb 03 '14 at 08:32
Clary, in your code, immediately after you set `$news` (your first code snippet above), log it or print it out or otherwise save it so we can get a look at it. — paxdiablo, Feb 03 '14 at 08:33
@paxdiablo I guess this is what you meant, I echoed $news and displays: SELECT tblforecast.Fore_ID, tblforecast.Issued, tblforecast.Synopsis,tblforecast.Forecaster, tblusers.FirstName, tblusers.LastName FROM tblforecast INNER JOIN tblusers ON tblusers.UserNumber = tblforecast.forecaster WHERE ((tblforecast.Valid =) AND (tblforecast.Issued like '%01-07%' ))order by tblforecast.Issued DESC — Clary, Feb 03 '14 at 08:36
@paxdiablo apparently, $valid worked if I don't put it inside the isset function — Clary, Feb 03 '14 at 08:37

Scary Wombat · Answer 1 · 2014-02-03T08:40:00.420

1

try

AND (tblforecast.Issued like '%' + $filter + `%' ))  
                        // or whatever is right for PHP

but more importantly make sure that your variables are set before trying to use them in the sql.

edited Feb 03 '14 at 08:40

answered Feb 03 '14 at 08:20

Scary Wombat

44,617
6
35
64

2

Why? PHP is perfectly able to substitute variables within strings, is it not? In fact the error text shows that it works and, in addition, the error seems well before that point. – paxdiablo Feb 03 '14 at 08:24
1

agreed. remember he is putting this all in a string, not writing directly executing SQL so what the OP had was fine. – Andrew Brown Feb 03 '14 at 08:25
1

Be nice to see what `$news` is ? – Scary Wombat Feb 03 '14 at 08:27

paxdiablo · Answer 2 · 2014-02-03T08:40:53.307

0

Since the output of your $news variable contains:

WHERE ((tblforecast.Valid =) AND ...
                          ^^
                          ||
                      important bit

it's a simple matter of your $type variable not being set.

As an aside, the first thing you should always do when you get an error like this, is to actually print out the string you're using for the query. Ideally, MySQL should actually give you some of the context before the error point so it would be easier to figure out.

edited Feb 03 '14 at 08:40

answered Feb 03 '14 at 08:16

paxdiablo

854,327
234
1,573
1,953

1

Yay. I'm hesitating to do something, but question as an answer from 313k rep. user makes me lost – Alma Do Feb 03 '14 at 08:17

score 0 · Answer 3 · answered Feb 03 '14 at 09:09

Clary,
If code mentioned is exact with that you using in your scripts , then I am not seeing any issue. I think that problem may be with variables used in a string.

I will Suggest you to use safe variable parsing .

$news = "SELECT tblforecast.Fore_ID, tblforecast.Issued, 
      tblforecast.Synopsis,tblforecast.Forecaster, tblusers.FirstName,
      tblusers.LastName FROM tblforecast 
         INNER JOIN tblusers ON tblusers.UserNumber = tblforecast.forecaster 
      WHERE ((tblforecast.Valid = '{$type}') 
          AND (tblforecast.Issued like '%{$filter}%' ))
       order by tblforecast.Issued DESC";

Even better would be to switch to MySQLi and use prepared statements/bind variables if possible — Mark Baker, Feb 03 '14 at 09:10

Two Conditions on MySQL WHERE

3 Answers3