EXISTS always returns true simple query

Question

I use this code to check if an entry already exsists but it seems like the query always returns true. When i use the same query on shell it works properly. Have in mind that foodname is Primary Key.

$query = "SELECT EXISTS ( SELECT * FROM Food WHERE foodname = '$food_name')";
    $result = pg_query($conn,$query) or die("Query could not be executed");
    if($result)
    {
        echo 'food already exists: ';
        echo $food_name;
        printf("\n");
    }
    else
    {
        echo 'new food inserted';
        printf("\n");
        $query = "INSERT INTO food VALUES ('$food_name','$food_price','$food_date')";
        $result = pg_query($conn,$query) or die("Query could not be executed");
    }

QUESTION: I modified it just like 'download download' said and it works as it also works with Kettners answer but isn't EXISTS faster for checking if an entry already exists for the reason that it stops when it finds a pair? Is there any query using EXISTS that can work in this case? Thnx for the help.

ANSWER: After reading everything you guys said the following one is what i choosed to use,it works and also uses EXISTS.

$query = "SELECT 1 FROM food WHERE EXISTS ( SELECT * FROM Food WHERE foodname = '$food_name')";
        $result = pg_query($conn,$query) or die("Query could not be executed");
        $row = pg_fetch_row($result);
        if($row[0])
        {
            echo 'food already exists: ';
            echo $food_name;
            printf("\n");
        }
        else
        {
            echo 'new food inserted';
            printf("\n");
            $query = "INSERT INTO food VALUES ('$food_name','$food_price','$food_date')";
            $result = pg_query($conn,$query) or die("Query could not be executed");
        }

have a look at http://stackoverflow.com/questions/7471625/fastest-check-if-row-exists-in-postgresql — Chetan Ameta, Jan 19 '16 at 11:09
First check what output is coming in $result. if query is running successful or failure — Monty, Jan 19 '16 at 11:10
Have you tried SELECT EXISTS ( SELECT 1 FROM Food WHERE foodname = '$food_name');? — Ravi Hirani, Jan 19 '16 at 11:14

score 2 · Accepted Answer · answered Jan 19 '16 at 11:11

2

Just try this :

  $query="SELECT * FROM Food WHERE foodname = '$food_name'";
  $result = pg_query($conn,$query) or die("Query could not be executed");

  if(pg_num_rows($result )>=1){
     echo 'food already exists: ';
     echo $food_name;
     printf("\n");
  }
else{
        echo 'new food inserted';
        printf("\n");
        $query = "INSERT INTO food VALUES ('$food_name','$food_price','$food_date')";
        $result = pg_query($conn,$query) or die("Query could not be executed");
}

answered Jan 19 '16 at 11:11

user2460074

1,252
3
11
28

Thats what i tried too and it works but my questions about EXISTS and why $result gets resource id #number as input from the pg_query() still remains. – prof chaos Jan 19 '16 at 11:26
because this condition `if($result)` is `TRUE` – user2460074 Jan 19 '16 at 11:38

Monty · Answer 2 · 2016-01-19T11:29:31.543

1

Database : postgresql
Query : giving result on success t and on fail f
Type : boolean.

You have to change your if condition.

if($result == 't'){
   // Your code here
}else {
  // Your code here
}

Modified :

$result = pg_query($conn, "Your Query");
$rows = pg_num_rows($result);
if($rows != -1){
   // Success
}else {
   // Fail
}

edited Jan 19 '16 at 11:29

answered Jan 19 '16 at 10:52

Monty

1,110
7
15

pg_query returns boolean as result. So if($result) or if(!$result) both are correct. – Ravi Hirani Jan 19 '16 at 10:55
Strangelly ($result == 't') has different outcome from ($result) or ($result == true) no. I changed it to 't' and the problem now is that it goes inside else while $result = true. – prof chaos Jan 19 '16 at 11:04
@prof chaos: This is because TRUE and 'true' and 't' are synonyms in PostgreSQL, but not in PHP. Please check what $result actually contains after execution. – Thorsten Kettner Jan 19 '16 at 11:07
Just checked and in both cases( existed or not) $result contains resource idnumber so i guess thats the problem but i don't get while it contain resourseid it goes insside if and not inside else. I thought that if SELECT finds nothing it suppose to return false. Got no clue why this happens. – prof chaos Jan 19 '16 at 11:12
2

@prof chaos False is only returned on failure. You need to fetch the data to get a false. In pdo you would now do `$result->fetch()` which would return false if no rows were found (or the row(s) on success). – Charlotte Dunois Jan 19 '16 at 11:29
It doesn't work with if($rows != -1) because it also works for 0. it needs if($rows >=1) or if($rows != 0) – prof chaos Jan 19 '16 at 12:00

Thorsten Kettner · Answer 3 · 2016-01-19T12:10:14.703

1

pg_query returns a result resource. You can query this ressource with pg_fetch_row to get to the the data.

pg_query only returns FALSE when the statement fails. Your statement, however, never fails (as long as you can access the database and table properly). It runs and returns a value (TRUE or FALSE).

Hence if($result) always returns true and doesn't give you any information on the query's result.

This should work: Execute the query, fetch the first row, check its first (and only) column.

$query = "SELECT EXISTS (SELECT * FROM Food WHERE foodname = '$food_name')";
$result = pg_query($conn,$query) or die("Query could not be executed");
$row = pg_fetch_row($result);
if($row[0])
{
  echo 'food already exists: ';
  echo $food_name;
  printf("\n");
}
else
{
  echo 'new food inserted';
  printf("\n");
  $query = "INSERT INTO food VALUES ('$food_name','$food_price','$food_date')";
  $result = pg_query($conn,$query) or die("Query could not be executed");
}

edited Jan 19 '16 at 12:10

answered Jan 19 '16 at 11:27

Thorsten Kettner

89,309
7
49
73

Thnx for the informations. I tried this before too but again it always goes inside if. – prof chaos Jan 19 '16 at 11:40
I'm sorry i had already inserted the data into the query, going to delete and check cause it's a bit different that what i tried. – prof chaos Jan 19 '16 at 11:47
Yea it works but isn't it more "painfull" compared to EXISTS because EXISTS stops the search if it finds a pair? – prof chaos Jan 19 '16 at 11:52
Oh sorry, that was nonsense. I wanted to copy from your request, and it seems I copied the line from an answer by mistake. Of course you should use EXISTS instead for the very reason you state. Sorry, I'll correct my answer. It is just the one line holding the SQL. – Thorsten Kettner Jan 19 '16 at 12:09
Checked your answer but it doesn't work with your query. It works with $query = "SELECT * FROM food WHERE foodname = '$food_name"; though – prof chaos Jan 19 '16 at 12:21
1

Then pg_query seems to have a problem with booleans. (You could look into row[0] to see what it contains.) You can circumvent this by selecting something else, e.g. 1 for true, 0 for false: `SELECT CASE WHEN EXISTS (SELECT * FROM Food WHERE foodname = '$food_name') THEN 1 ELSE 0 END` and check with `if($row[0] == 1)`. – Thorsten Kettner Jan 19 '16 at 12:40
But i have to write "SELECT 1 EXISTS (SELECT * FROM Food WHERE foodname = '$food_name')"; in order to work. If i don't use 1 there it doesn't run. Thnx a lot for the help. – prof chaos Jan 19 '16 at 14:30
That's strange. `SELECT 1 EXISTS ...` looks like invalid syntax to me. – Thorsten Kettner Jan 19 '16 at 14:54

EXISTS always returns true simple query

3 Answers3