Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

PDO: Database connections and abstraction

connection bound parameter

  • Please log in to reply
25 replies to this topic

#13 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 12 October 2010 - 09:55 PM

@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.
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#14 DEViANT

DEViANT

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 265 posts

Posted 13 October 2010 - 12:13 AM

@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


  • 0

:D You should rep+ me so that I can win :D

My Blog | Ask me!
Error : Satan did it

#15 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 13 October 2010 - 10:53 AM

@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?
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#16 DEViANT

DEViANT

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 265 posts

Posted 14 October 2010 - 06:03 AM

well this is rather embarrassing :o

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+!
  • 0

:D You should rep+ me so that I can win :D

My Blog | Ask me!
Error : Satan did it

#17 DEViANT

DEViANT

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 265 posts

Posted 14 October 2010 - 07:40 AM

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"
  • 0

:D You should rep+ me so that I can win :D

My Blog | Ask me!
Error : Satan did it

#18 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 14 October 2010 - 03:18 PM

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

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#19 Vswe

Vswe

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1989 posts
  • Programming Language:Java, C#, PHP, Python, JavaScript, PL/SQL, Visual Basic .NET, Lua, ActionScript

Posted 02 November 2010 - 03:42 PM

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.";
}

  • 0

#20 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 02 November 2010 - 04:05 PM

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.";
}

  • 1

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#21 Vswe

Vswe

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1989 posts
  • Programming Language:Java, C#, PHP, Python, JavaScript, PL/SQL, Visual Basic .NET, Lua, ActionScript

Posted 02 November 2010 - 04:06 PM

I already did that, thanks :D
  • 0

#22 Graphene

Graphene

    CC Regular

  • Member
  • PipPipPip
  • 34 posts
  • Learning:C, C++, Python, JavaScript

Posted 16 February 2011 - 06:08 PM

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! :)
  • 0

#23 Vswe

Vswe

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1989 posts
  • Programming Language:Java, C#, PHP, Python, JavaScript, PL/SQL, Visual Basic .NET, Lua, ActionScript

Posted 30 January 2012 - 11:24 AM

//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.
  • 0

#24 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 31 January 2012 - 10:45 PM

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.
  • 1

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.






Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download