How do I check if a table is empty or already my query didn't match any results

Question

I want an if-statement that only runs, when there are no rows in the table or if existing rows dont match a specific parameter from my input. I tried this way:

$currentURL = $post["media_url"];
$sql = "SELECT * FROM images WHERE imageURL = '$currentURL'";
$result = $conn->query($sql);

if(!$result)
{ ... }

From my thinking this should execute the if-statement on the first time I want to add something to the database and if the $currentURL does not exist in existing data. But this does not seem to work the way I think it does. How would you do this? Maybe I'm handling the $result wrong, because if I test the sql-query inside phpmyadmin this shows the right result (no rows).

Does this answer your question? [How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL)](https://stackoverflow.com/questions/22252904/how-to-check-if-a-row-exists-in-mysql-i-e-check-if-an-email-exists-in-mysql) — Dharman, Jul 30 '20 at 08:50

score 1 · Answer 1 · answered Jul 30 '20 at 17:15

The correct way to do this would be to use prepared statement and fetch the results into an array. You can fetch all rows into an array using fetch_all()

$stmt = $conn->prepare("SELECT * FROM images WHERE imageURL = ?");
$stmt->bind_param('s', $post["media_url"]);
$stmt->execute();
// Get result and then fetch all rows from the result object
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Then check if you have any rows at all using a simple if statement
// Negate it using ! to check if the array is empty
if (!$result) {
    // no results found
}

mscho · Accepted Answer · 2020-07-30T06:51:50.417

I guess, that $conn is a PDO connection? In that case, the method $conn->query() returns an object of type PDOStatement. See https://www.php.net/manual/de/class.pdostatement.php

The method does NOT return the result set.

Instead you can use the PDOStatement object, to fetch the results:

$currentURL = $post["media_url"];
$sql = "SELECT * FROM images WHERE imageURL = '$currentURL'";
$result = $conn->query($sql)->fetchAll();

if(empty($result))
{ ... }

In case you are using mysqli, the object returned by query() is this: https://www.php.net/manual/en/class.mysqli-result.php

So the code would be:

$currentURL = $post["media_url"];
$sql = "SELECT * FROM images WHERE imageURL = '$currentURL'";
$result = $conn->query($sql)->fetch_all(MYSQLI_ASSOC);

if(empty($result))
{ ... }

Please also note: Your code is highly insecure! You should use prepared statements to prevent sql-injection:

$currentURL = $post["media_url"];
$sql = "SELECT * FROM images WHERE imageURL = :currentUrl";
$stmt = $conn->prepare($sql);
$stmt->execute(['currentUrl' => $currentURL]);
$result = $stmt->fetchAll();

if(empty($result))
{ ... }

thanks it worked. but when i try the secure version it throws an error: Uncaught Error: Call to a member function execute() on boolean in — Hendrik Niemax, Jul 30 '20 at 07:02
@HendrikNiemax My secure solution was regarding PDO. Please see answer of Dharman for a secure mysqli solution — mscho, Jul 31 '20 at 14:04

score -2 · Answer 3 · answered Jul 30 '20 at 06:48

sanitize input against sql injection (or better - use prepared statements and param binding)

$sql = "SELECT * FROM images WHERE imageURL = '".$conn->real_escape_string($currentURL)."'";

mysqli query returns true on success (even empty dataset is success), use num_rows instead:

if ( $result->num_rows === 0 ) { ... }

How do I check if a table is empty or already my query didn't match any results

3 Answers3