Trying to return true or false from SQL

Question

Here is my code:

$result_username = mysqli_query($dbconnection, Data::checkForUsername($username));

It then goes here:

   public static function checkForUsername($username) {
            global $database;

            $query = "
                SELECT COUNT(*) 
                FROM users 
                WHERE username='{$username}';";

            $result = $database -> query($query);

            return $result;
        }

Then $result does this:

if (mysqli_result($result_username, 0) > 0) {

However, it then gives me back a Resource_Id?? I can not figure out why?

I simply want to check if the username exists in at least 1 row.

It's great that you're using `mysqli` but it's essential that you use [SQL placeholders](http://bobby-tables.com/php) to avoid SQL injection bugs. Putting `$username` directly in a query is a very bad idea. — tadman, Feb 16 '13 at 03:06
You could probably fix this problem by adding a `UNIQUE` index on the `username` column, which would prevent having to check for duplicates in the first place. — tadman, Feb 16 '13 at 03:06
Maybe this can help - http://stackoverflow.com/a/10194487/1073631 — sgeddes, Feb 16 '13 at 03:09
@tadman I actually have cleaned the code, its just not shown here. — TheLettuceMaster, Feb 16 '13 at 03:19

peterm · Accepted Answer · 2013-02-16T03:29:46.350

3

You need to fetch your data after you execute your query

$row = $result->fetch_array(MYSQLI_NUM);
return $row[0];

UPDATE: Now, using prepared statement your function can look like this:

public static function checkForUsername($username) {
    global $database;

    $result = 0;
    $query = "SELECT COUNT(*) FROM users WHERE username=?";
    /* create a prepared statement */
    if ($stmt = $database->prepare($query)) {
        /* bind parameters for markers */
        $stmt->bind_param("s", $username);
        /* execute query */
        $stmt->execute();
        /* bind result variable */
        $stmt->bind_result($result);
        /* fetch value */
        $stmt->fetch();
        /* close statement */
        $stmt->close();
    }
    return $result;
}

edited Feb 16 '13 at 03:29

answered Feb 16 '13 at 03:10

peterm

91,357
15
148
157

Why $row[0] and not just $row? – TheLettuceMaster Feb 16 '13 at 03:23
1

Because [`fetch_array()`](http://php.net/manual/en/mysqli-result.fetch-array.php) returns an array of strings that corresponds to the fetched row. – peterm Feb 16 '13 at 03:36

score 1 · Answer 2 · answered Feb 16 '13 at 03:11

1

You can try

return $result->num_rows

Then

if (checkForUsername('Redeyes') != 0) {
}

answered Feb 16 '13 at 03:11

redeyes

61
1
7

2

Since the OP is using an aggregate function `COUNT()` the query will always return one row – peterm Feb 16 '13 at 03:12

Trying to return true or false from SQL

2 Answers2