[RESOLVED] PHP PDO

[shamly]

Hi,

I have come across small problem with PDO and MySQL. This is the first time I am using PDO class n I am trying to execute 2 sql statements. Let me try to explain .. below is the code..

$sth = $dbh->prepare('SELECT * FROM users');

$sth->execute();

while ($row = $sth->fetch(PDO::FETCH_OBJ))
{
    $sth_user = $dbh->prepare('SELECT * FROM staff_details where staff_id = ?');

$sth_user->execute(array($row->id));

$user_row = $sth_user->fetch(PDO::FETCH_OBJ);

}

As you can see in the code I fetch the user details from the user table and then within a loop try to fetch the rest of the data by executing another sql statement. Now the problem is that the first SQL executes fine and I get the data. But the second SQL query does not get executed and I get the following error

Call to a member function execute() on a non-object in .... 

which points to the line

$sth_user->execute(array($row->id));

Can anyone help me please ?

Thanks

[Weedpacket]

Why would you want to do this in a loop anyway?

Select staff_details.* from staff_details, users where staff_id=user.id

[etully]

I've never used PDO so I'm probably not going to be able to help you but let me think out loud for a moment.

The error message seems to say that execute is not a method on $sth_user - or that $sth_user isn't even an object. But that can't be right because back in lines 1-2, you were able to call execute() on the $sth object.

But I do notice that you are preparing a simple SQL statement that should be accepting a single scalar variable (an integer).

SELECT * FROM staff_details where staff_id = ?

That SQL statement should take a number. But you are passing it an array when you call this:

execute(array($row->id))

So I am going to guess that you should call this instead:

execute($row->id)

If that works, then great... except that it contradicts the problem stated in the error message.

[etully]

Why would you want to do this in a loop anyway?

Why not? It looks normal to me. Get me a list of all the users. Loop through that list of users. For each user, get all the data from the staff_details table. Loop back to next user.

Oh, yes, it could have been done in one SQL statement instead of two, true, so it's inefficient. Is that what you meant?

[shamly]

Weedpacket : Yes i realized that I could join both queries in the example i gave. Its just that later on the data fetched will be used to access data from several other tables. One column in the table will have a User Type, which will point to different tables based on the value in that column.

For ex. If the user is an admin, then the rest of the details of that user will be in the admin table and if the user is a agent, then need to refer agent table.

I need to display all the users info in a table. This is the problem I am facing. With standard MySQL commands or AdoDB classes I have had no problem before. For this application I thought of using PDO classes for performance gains.

etully : I have already tried your suggestion and it dosen't work, even with basic SQL statement that does not have a single scalar variable.

Thanks

[NogDog]

etully;10923319 wrote:

...Oh, yes, it could have been done in one SQL statement instead of two, true, so it's inefficient. Is that what you meant?

Sure it is. If there are hundreds of users then you'll have hundreds of loop iterations executing hundreds of DB queries. I'd take a single query with hundreds of loops on the result set any day.

[NogDog]

Off-hand I cannot see why that second query does not work without throwing an exception. From the manual for [noparse]PDO::prepare()[/noparse]:

Return Values

If the database server successfully prepares the statement, [noparse]PDO::prepare()[/noparse] returns a PDOStatement object. If the database server cannot successfully prepare the statement, [noparse]PDO::prepare()[/noparse] emits PDOException.

So it would seem to me that either you should have a valid statement object or else have had an exception you needed to handle. 🙁

[shamly]

try catch don't seem to work either in catching any error messages .. still getting

<b>Fatal error</b>: Call to a member function execute() on a non-object in

🙁

[NogDog]

shamly;10923325 wrote:

try catch don't seem to work either in catching any error messages .. still getting

<b>Fatal error</b>: Call to a member function execute() on a non-object in

🙁

Maybe after the prepare do a var_dump($sth_user) and find out what was, in fact, returned by the prepare(). It also might be informative to do an error_reporting(E_ALL) before this section of code just to see if there is anything "interesting" PHP has to tell us.

[shamly]

Phew .. ok got the code working by making some temporary adjustments to the DB & code .. so that I dont have to execute a query within the loop. Not the best solution, but for now it works .. 🙂. I will look into this a bit more later on.

Thanks for helping ...