Getting starting with PDO (switching from mysql_query)

I can’t seem to get this working… And I can’t figure out why. Anyone?

The following code works just fine.
[php]
$list_of_sites = $db_connection->prepare(‘SELECT * from site_list WHERE approved=1 ORDER BY votes_up DESC, votes_down ASC’);
$list_of_sites->execute();
while ($row = $list_of_sites->fetch(PDO::FETCH_ASSOC)){
echo $row[‘site_url’];
}
[/php]

I’m attempting to paginate the results via the following code (which does not work)
[php]
$list_of_sites = $db_connection->prepare(‘SELECT * from site_list WHERE approved=1 ORDER BY votes_up DESC, votes_down ASC LIMIT ?,?’);
$list_of_sites->bindParam(1,$page_num);
$list_of_sites->bindParam(2,$paginate);
$list_of_sites->execute();
[/php]

This does not work either:
[php]
$list_of_sites = $db_connection->prepare(‘SELECT * from site_list WHERE approved=1 ORDER BY votes_up DESC, votes_down ASC LIMIT :page_num,:paginate’);
$list_of_sites->bindParam(’:page_num’,$page_num);
$list_of_sites->bindParam(’:paginate’,$paginate);
$list_of_sites->execute();
[/php]

yet this works just fine:
[php]
$list_of_sites = $db_connection->prepare(‘SELECT * from site_list WHERE approved=1 ORDER BY votes_up DESC, votes_down ASC LIMIT 1,10’);
$list_of_sites->execute();
[/php]

This code is used for output in all the above examples. What am I doing wrong?
[php]
while ($row = $list_of_sites->fetch(PDO::FETCH_ASSOC)){
echo $row[‘site_url’];
}
[/php]

The values of $page_num and $paginate have been checked with the echo statment and are verified to be 1 & 10.

Think about using something like this instead of bind Param

[php] $list_of_sites->execute(array(
‘:page_num’’ => $page_num,
‘:paginate’ => $paginate
));[/php]

I rarely use bindParam. I would also include your where statement in the execute array

Just to add, a little off topic I agree but for better practice note this:

[php]while ($row = $list_of_sites->fetch(PDO::FETCH_ASSOC)){
echo $row[‘site_url’];
}[/php]

You’re pulling absolutely everything out of that table and only using the site_url column. There’s absolutely no need for this. Although only very small, it takes bandwidth. Developers should be doing their utmost to save bandwidth, so I suggest only ever pulling out what you need:

[php]
$list_of_sites = $db_connection->prepare(‘SELECT ‘site_url’ from site_list WHERE approved=1 ORDER BY votes_up DESC, votes_down ASC’);
$list_of_sites->execute();
while ($row = $list_of_sites->fetch(PDO::FETCH_ASSOC)){
echo $row[‘site_url’];
}
[/php]

It’s only a small amount of bandwidth, but over the whole website and all the queries you’ll be pulling, these minor details will have a major effect on the efficiency and performance of the website and ultimately the users experience.

scottlpool2003,

Appreciate the info, but the code is stripped for the forums since it’s just repeated echo statements (I do use all the data.)

Mrwilson1,
I can’t get your code working either. The problem must lye somewhere else.
(I use the same type of code for my login and that works just fine). I’ll post again if I figure this out.

Question, there is also the option of mysqli, correct? How similar is this too mysql_ ? Would a search replace on the code result in working code? (I’m thinking that would be too easy.)

Put an if statement in to see if you are getting a connection. It sounds to me like you may not be hitting the database to start with

[php]if($connection){
echo “its all good”;
}else {
echo “no connection”;
}
[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service