PHP/MySQL - Error when using SELECT but no matching data

Question

I want to get the ID of an image that is boostAmount-boostStart>total. Currently if an image exists that is appropriate it works. However, if there is nothing appropriate to show I get this error.

Invalid query: 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 'ORDER BY RAND() LIMIT 1' at line 1

$photoToGuess = mysql_query("SELECT photoID,id,total,boostStart,boostAmount,auth,photoUploadDate FROM photos WHERE (boostAmount !=0) AND ((boostAmount-boostStart)>total) AND (auth=2 OR auth=5 OR auth=7) ORDER BY RAND() LIMIT 1") or die("Invalid query: " . mysql_error());
$getphotoToGuess = mysql_fetch_array($photoToGuess);

    //Yes
    if(mysql_num_rows($photoToGuess) > 0)
    {
    //do something
    }

Your Query Syntax is correct....you need to debug your code and update your full stack trace... — Piyush Gupta, Jun 22 '16 at 07:07
Even when I remove ORDER BY RAND() LIMIT 1 it shows the same error. Which seems really weird to me. — user2203466, Jun 22 '16 at 07:13
change your != to <> use this as a reference http://stackoverflow.com/questions/11421741/mysql-where-how-to-write-or-not-equals — Moses Liao GZ, Jun 22 '16 at 07:17
changing your != to <> does not help :( but i appreciate this was wrong — user2203466, Jun 22 '16 at 07:20

score 2 · Answer 1 · answered Jun 22 '16 at 07:31

Try this, I removed die condition you can achieve die condition using if statement...It is working fine chech once.

$photoToGuess = mysql_query("SELECT photoID,id,total,boostStart,boostAmount,auth,photoUploadDate FROM photos WHERE (boostAmount !=0) AND ((boostAmount-boostStart)>total) AND (auth=2 OR auth=5 OR auth=7) ORDER BY RAND() LIMIT 1");
$getphotoToGuess = mysql_fetch_array($photoToGuess);

//Yes
    if(mysql_num_rows($photoToGuess) > 0)
    {
    //do something
    }

You can refer this http://php.net/manual/en/function.mysql-query.php

score 1 · Answer 2 · edited Jun 22 '16 at 07:45

1

Your if condition is wrong why are you using $photoToGuess in your if conditioner code here

if(mysql_num_rows($getphotoToGuess ) > 0){
    // do something
}

edited Jun 22 '16 at 07:45

GROVER.

4,071
2
19
66

answered Jun 22 '16 at 06:55

riya

116
15

This IF condition works if there is a result to show. The error is when there is no matching result in the database. – user2203466 Jun 22 '16 at 06:58
(boostAmount !=0) AND ((boostAmount-boostStart)>total) AND (auth=2 OR auth=5 OR auth=7) what is exactly you want to checked using this statment there is something wrong with your AND statment checked once again and clearly u miss the one closing bracket in above mention query – riya Jun 22 '16 at 07:03
you r query is not working because in your where condition it will get wrong value which is either 1 or 0 so your condition is working like this "from where photos 0 ORDER BY RAND() LIMIT 1" – riya Jun 22 '16 at 07:05
@user2203466 start removing conditions one-by-one and check which condition is giving problem. – Alive to die - Anant Jun 22 '16 at 07:08
((boostAmount-boostStart)>total) Gives the issue. – user2203466 Jun 22 '16 at 07:17
For reference (boostAmount-boostStart>total) IF figures are 200 - 0 > 200 (finds no results, gives error) 199 - 0 > 200 (works) – user2203466 Jun 22 '16 at 07:37
used >= instead of only '>' – riya Jun 22 '16 at 07:39

PHP/MySQL - Error when using SELECT but no matching data

2 Answers2