How do you write a simple PDO query?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do you write a simple PDO query?

Post by simonmlewis »

Code: Select all

$query = $pdo->query("DELETE FROM homepage WHERE id = '$id'");
Just been updating a ton of code to be PDO, and thought there was this shorter way of doing it, if only Admin are running it so doesn't need the injection protection.
It doesn't work.

Is it because query is for queries, and execute is for UPDATE/INSERT ?

What is the correct format for a UPDATE/INSERT simple query?

EDIT: I think it is because my older Mysql query escaped the apostrophes.
This is correct as far as I can tell, but the update won't run, because of "simon's" type text.
Is there a means to make it work? I'm sure I am missing something silly.

Code: Select all

$query = $pdo->exec("UPDATE homepage SET content = '$hometoptext' WHERE id = '$id'");
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do you write a simple PDO query?

Post by requinix »

A shorter way? Not really. Not sure where there's room for shortening...?
simonmlewis wrote: if only Admin are running it so doesn't need the injection protection.
No. No no no. Stop thinking that. It doesn't matter whether you believe you can trust your user or not, that line of thought is wrong and it is always wrong.

If you don't want to believe that your admin is untrustworthy, consider that maybe they're not as technically literate as you and they might do something bad accidentally. Or maybe consider that your application isn't as safe as you think it is and maybe some malicious user will trick your good users into doing something they shouldn't.
simonmlewis wrote:Is it because query is for queries, and execute is for UPDATE/INSERT ?
query() lets you get access to resultsets from a query, execute() does not. However both types of query will work with both methods.

If you're having problem with code that doesn't work then you have to post that code.
simonmlewis wrote:What is the correct format for a UPDATE/INSERT simple query?
I don't know what you think a "simple query" is.
simonmlewis wrote:I think it is because my older Mysql query escaped the apostrophes.
That would be an older PHP, not an older MySQL. And yes, that did happen in the past, but they got rid of it because it was bad.
simonmlewis wrote:This is correct as far as I can tell, but the update won't run, because of "simon's" type text.
Is there a means to make it work? I'm sure I am missing something silly.
Yes, you're missing something.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you write a simple PDO query?

Post by simonmlewis »

Ok. So the rule here is, no matter where an UPDATE/INSERT is added, always use the :variable method, to keep it safe and secure.
When I updated the code to use that, it worked. It's because of those ', that is failed. As soon as I used the :method, it worked.
I have added several simple one-line queries, but I think they ought to all be :method so that it's safe.

I see your point about always using it. Not just to avoid hacks, but in case someone puts something 'stupid' in the box by mistake. Or even if I did a silly 'Paste', and update instantly.... which pasted code in, rather than text.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you write a simple PDO query?

Post by simonmlewis »

This will not work. It won't insert. It won't show errors on screen (ie, too many in the array for those in the query), and if I ask it to show the last ID added, it just shows a zero. Nothing added.

Code: Select all

$query = ("INSERT INTO homepage (url, image, section, freetext, priority, content, homepromocolor, homepromotextcolor, stockbanner, dateadded) VALUES (:searchurl, :saved_filename, :section, :freetext, :priority, :content, :homepromocolor, :homepromotextcolor, :stockbanner, :todaydate)");
        
      $result = $pdo->prepare($query);
      
$result->execute(array(':searchurl' => $searchurl, ':saved_filename' => $saved_filename, ':section' => $section, ':freetext' => $freetext, ':priority' => $priority, ':content' => $content, ':homepromocolor' => $homepromocolor, ':homepromotextcolor' => $homepromotextcolor, ':stockbanner' => $stockbanner, ':todaydate' => $todaydate));
If I revert back to this:

Code: Select all

  mysql_query("INSERT INTO homepage (url, image, section, freetext, priority, content, homepromocolor, homepromotextcolor, stockbanner) VALUES ('$searchurl', '$saved_filename', '$section', '$freetext', '$priority', '$content', '$homepromocolor', '$homepromotextcolor', '$stockbanner')");
It works.
So what is missing or wrong in that PDO query?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How do you write a simple PDO query?

Post by Christopher »

Read the manual for the difference between PDO::exec() and PDOStatement::execute(). They are totally different. Both require that you to prepare a statement before using. Neither return results.

PDO::exec()
http://php.net/manual/en/pdo.exec.php

PDOStatement::execute()
http://php.net/manual/en/pdostatement.execute.php
(#10850)
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you write a simple PDO query?

Post by simonmlewis »

What have I done wrong then?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: How do you write a simple PDO query?

Post by thinsoldier »

http://php.net/manual/en/pdo.query.php

If you haven't actually done a PDO tutorial before diving into updating your project code, stop. Go do some tutorials.
Warning: I have no idea what I'm talking about.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you write a simple PDO query?

Post by simonmlewis »

I have built an entire site using PDO. But need to convert this code, and it's not working.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do you write a simple PDO query?

Post by requinix »

Could it have anything to do with the dateadded you introduced?

Fact is, if the statement executes then either (a) a row will be inserted or (b) there will be an error or warning indicating why it did not insert. One of those is definitely happening.
Post Reply