$stmt->execute() - what next?

PHP
1

Hello again, sorry for all those posts but I’m particularly slow this morning. :rolleyes:

I’m not sure how this may work.

I need to fetch the data from a given query, then use that fetched data to put on session variables.

I’m with something like this right now:


$stmt = $this->_dbh->prepare("
                                            SELECT id_utilizador, nivel_utilizador
                                            FROM utilizador
                                            WHERE nome_utilizador = ? AND password_utilizador = ? AND estado_utilizador = ?"
                                        );
            $stmt->bindParam(1,  $utilizadorVo->getNome(), PDO::PARAM_STR, 255);
            $stmt->bindParam(2,  $utilizadorVo->getPassword(), PDO::PARAM_STR, 255);
            $stmt->bindParam(3,  $utilizadorVo->getEstado(), PDO::PARAM_INT, 1);

            $stmt->execute();

But this will:
a) prepare the statement;
b) bind parameters;
c) execute;

The execute, according to the documentation, executes the prepared statement, and returns either TRUE or FALSE on success.

1)
This TRUE or FALSE on success means:
a) Return TRUE if the query is executed (even if no records were found) ?
or
b) Return TRUE if the query is executed and returns records ?

2)
What’s the point of doing a select and then do nothing with the returned results? I believe that after this, we need to fetch the data somehow no?
But on the PHP website the examples stop on that (I believe inconsequent) execute();

What am I missing? (besides some sleep hours?)

Márcio

2

Well, for a login process you might want to collect all the returned rows to see if there are more than one.

Usually with login systems:
0 records -> user does not exist or password is wrong
1 record -> user is found, great!
> 1 records -> something is wrong! There are more users than there should be with the same username

Of course, with correct validation when inserting the users this should never happen :slight_smile:

3

So, if you agree, we can stick without fetching an array until a reason comes by.
But once I feel the need to differentiate a “0 users” case with the “more then 1 user” case - I will surely recall it here.

Thanks for your feedback.
:slight_smile:
Márcio

4

Actually, since we want only one record (for login proposes on this case), we don’t need to fetchAll and return an array.

I believe this will do better:

$result = $stmt->fetch(PDO::FETCH_OBJ);

So that when we want to retrieve the values fetched we can simply do:
$result->my_database_column_name_that_I_want_to_retrieve;

Instead of:
$result[0]->my_database_column_name_that_I_want_to_retrieve;

I mean, there’s no point for searching an array key when we will always search the same key, right?

Please correct if I’m wrong. If not, this stays for others and for future checks by myself. :slight_smile:

5

The problem of multiple users should never arise, as uniqueness of the username should be checked when the user is created or an existing user is updated (and ideally it should be enforced by the database).

Maybe my example wasn’t such a good example :slight_smile:

I suppose that SQL injection attacks could lead to more than 0 or 1 results being returned, but you already have that covered as you’re using PDO and prepared statements.

6

Well… I’m not sure if I will need to differentiate between having no records, or having more then 1 record for the login. Both should not allow the user to login, and of course, that third option should never arrive in the first place, I believe.

So I’m having something like this, that limits the records to 1, and is based on this query that I found useless to return an array.


SELECT id_utilizador, nivel_utilizador FROM utilizador WHERE nome_utilizador = ? AND password_utilizador = ? AND estado_utilizador = 1 LIMIT 1

Immerse, do you found a reason for us to actually know if there is more then one user or, like me, do you believe that THAT problem should be deal earlier on the process?

Thanks a lot,
Márcio

7

fetch() :slight_smile:

8 
$stmt->fetchAll();

Is this the one you are looking for?

9

This at the end perhaps?

$fetchedData = $stmt->fetchAll(PDO::FETCH_OBJ);

Only the 1) prevails I believe.

Thanks again,
Márcio

10

Yes. Thank you.

About the first question, I believe I can test it. :slight_smile:

But ok, what are the point then, of doing a select of some results, and then do nothing with them, like on the examples showed here:
http://www.php.net/manual/en/pdostatement.execute.php

?

Thanks again, :blush:
Marcio