2

I'm an SQL noob and learning how to use PDO. I'm doing a course which introduces basic user login functions. In an example of a login page, they check the username/password against a MySQL database. I edited their code slightly to be able to simultaneously check whether the user/pass combo exists and also grab the user's first name:

$sql = sprintf("SELECT firstname FROM users WHERE username='%s' AND password='%s'",
    mysql_real_escape_string($_POST["username"]),
    mysql_real_escape_string($_POST["password"]));

        // execute query
        $result = mysql_query($sql);

    if (mysql_num_rows($result) == 1) {
        $_SESSION["authenticated"] = true;

        // get contents of "firstname" field from row 0 (our only row) 
        $firstname = mysql_result($result,0,"firstname");
        if ($firstname != '')
            $_SESSION["user"] = $firstname;
    }

What I want to do is use SQLite instead and do the same thing. Searching around has only resulted in people saying you should use a SELECT COUNT(*) statement, but I don't want to have to use an extra query if it's possible. Since I'm SELECTing the firstname field, I should only get 1 row returned if the user exists and 0 if they don't. I want to be able to use that number to check if the login is correct.

So far I've got this:

$dsn = 'sqlite:../database/cs75.db';
$dbh = new PDO($dsn);

$sql = sprintf("SELECT firstname FROM users WHERE username='%s' AND password='%s'",
                    $_POST["username"],
                    $_POST["password"]);

// query the database and save the result in $result
$result = $dbh->query($sql);

// count number of rows
$rows = sqlite_num_rows($result);

if ($rows == 1) { ...

But this is returning Warning: sqlite_num_rows() expects parameter 1 to be resource, object given.

Is there a way I can do this efficiently like in MySQL, or do I have to use a second query?

EDIT:

I found this, not sure if it's the best way but it seems to work: How to get the number of rows grouped by column?

This code let me do it without the second query:

// query the database and save the result in $result
$result = $dbh->query($sql);

// count number of rows
$rows = $result->fetch(PDO::FETCH_NUM);

echo 'Found: ' . $rows[0];

$rows is an array so I can just count that to check if it's > 0.

Thanks to everyone who commented. I didn't know until now that there were 2 different approaches (procedural & object oriented) so that helped a lot.

Community
  • 1
  • 1
sebu
  • 206
  • 3
  • 12

2 Answers2

1

Normally, you can use PDOStatement::rowCount(), however, SQLite v3 does not appear to provide rowcounts for queries.

You would need to seperately query the count(*), or create your own counting-query-function.

The documentation comments have an example of this

Jacob S
  • 1,693
  • 1
  • 11
  • 12
-1

A bit late, but i tried this with SQLite3 successful:

$result = $db->query('SELECT * FROM table_xy');
$rows = $result->fetchAll();
echo count($rows);
user5250844
  • 1