Getting starting with PDO (switching from mysql_query)

[Sensimillia]

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.

[Mrwilson1]

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

[scottlpool2003]

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.

[Sensimillia]

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.)

[Mrwilson1]

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]