25 replies to this topic

[Alexander]

@DEViANT: You are thinking too complex again I think. This should return a complete associative array of the result set without need of a loop, using PDOStatement::fetchAll():

    // create PDOstatement object    
    $sth = $db->query('SELECT * FROM users ORDER BY u_id ASC');
    // fetch all rows with it
    $array = $sth->fetchAll();
    
    print_r($array);

In fact it should be exponentially faster than mysql_query + foreach even being a class.

[DEViANT]

@DEViANT: You are thinking too complex again I think. This should return a complete associative array of the result set without need of a loop, using PDOStatement::fetchAll():

    // create PDOstatement object    
    $sth = $db->query('SELECT * FROM users ORDER BY u_id ASC');
    // fetch all rows with it
    $array = $sth->fetchAll();
    
    print_r($array);

In fact it should be exponentially faster than mysql_query + foreach even being a class.

I did try that yesterday, but I got the following error message :

Fatal error: Call to a member function fetchAll() on a non-object

[Alexander]

@DEViANT

PDO::query() returns a PDOStatement object, or FALSE on failure.

Can you try your example within try/catch blocks, or see if $db->query returns FALSE?

[DEViANT]

well this is rather embarrassing

My SQL Query sellects 'users' instead of 'user'.

I got it working the way I want it, this is the code I used :

	// create PDOstatement object    	

    $sth = $db->query('SELECT * FROM user ORDER BY u_id ASC');

    // fetch all rows with it

    $array = $sth->fetchAll(PDO::FETCH_ASSOC);

Brilliant tutorial, and I will most defenitely be using this from now on. Rep+!

[DEViANT]

PDOstatement::numRows; This can get number of rows affected in an executed query statement much like PDOstatement::exec() return value;

Stumbled upon this though... In your original post you make a refference to "numRows". I tried to use it and found it doesnt work. A quick check on php.net told me its "rowCount"

[Alexander]

I am glad you had gotten it to work. I had updated that in my tutorial, thank you for pointing that out DEViANT!

[Vswe]

PDOstatement::rowCount; This can get number of rows affected in an executed query statement much like PDOstatement::exec() return value;

Does that mean I can do something like this?

if ($sth->execute() == 0) {
   echo "No results found.";
}

EDIT: Oh, I missread, it wasn't execute it was exec. I solved it easily though.

$sth->execute()
if ($sth->rowCount() == 0) {
   echo "No results found.";
}

[Alexander]

Does that mean I can do something like this?

Only PDO->exec returns row count (i.e. direct execution) you will need to check it separately:

$sth->execute();
if (!$sth->rowCount()) {
       echo "No results found.";
}

[Vswe]

I already did that, thanks

[Graphene]

I was lacking enough knowledge to piece together all the bits and bytes on the web about using PDO for anything useful (transactions, sqlite), I have learned a lot by this. I will definitely be spamming you with questions when I run in to trouble!

[Vswe]

//PARAM_INT for int, PARAM_STR for string, PARAM_BOOL for bool

What would I use for enums? can't get it to work with either INT or STR.

[Alexander]

What would I use for enums? can't get it to work with either INT or STR.

How are you using it? According to MySQL however (MySQL :: MySQL 5.0 Reference Manual :: 10.4.4 The ENUM Type) :

An enumeration value must be a quoted string literal; it may not be an expression, even one that evaluates to a string value.

It may be entirely possible that it is not seen as a string yet, even with a PARAM_STR bound to it. I've never done that type before nor can I find any documentation specific to PDO.

Enums may be inefficient if they are modified down the road, maybe see this if it is possible to modify your database model: Why You Should Replace ENUM With Something Else | BrandonSavage.net or if required possibly sanitise with conventional methods and caution.

Alexander.