CODEWITHSUNDEEP
phpmysqlpdo
luqita
luqita

Reputation: 4077

MySQL: error when query returns no results?

I have a query that looks like this:

SELECT number
            FROM table1
            INNER JOIN table2
            WHERE name = 'stack_overflow' AND table1.id = table2.id
            AND user_id = 5

This returns a number. It does the right thing, but when inside name I pass a name that does not exist in db, PHP gives me an error. This is how I am executing it:

    $stmt = $this->db->prepare($sql);
    $stmt->execute();

    $x = $stmt->fetchColumn();

I always get the correct $x value when the name exists in the table, however when it doesn't, I get the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 'name_that_is_not_in_table'

No idea....

Upvotes: 0

Views: 580

Answers (2)

DRapp
DRapp

Reputation: 48139

Sounds like you need to check how many rows are returned first... if none, notify the user. If it DOES have a returned row count, then get the name column as you are expecting.

Additionally, you should clarify the alias.column in your queries as anyone new doesn't have to guess which table a given column comes from... Such as your user_id and name columns. (and "name" might be a reserved word and cause the choke... so you might want to wrap it in tick marks 

`name`

Upvotes: 0

xdazz
xdazz

Reputation: 160863

Try to pass name in this way:

SELECT number
            FROM table1
            INNER JOIN table2
            WHERE name = ? AND table1.id = table2.id
            AND user_id = 5

$stmt = $this->db->prepare($sql);
$stmt->execute(array($name));
$x = $stmt->fetchColumn();

Upvotes: 1

Related Questions