PDO Examples

Comments:

• Tim Dawson, 24.07.23 01:16

  Further to my previous reply to your e-mail, I now find that the following, with double quotes, as you sent it, works:

  $query1 = "UPDATE $table SET $col = $col+1 WHERE bk_ref = :bk_ref";

  (which is what you sent me) Whereas what I had written with single quotes and periods, does not:

  $query1 = 'UPDATE ' . $table . ' SET ' . $col . ' = ' . $col+1 . ' WHERE bk_ref = :bk_ref';

  I get an error message:

  Warning: A non-numeric value encountered in D:\Websites\mullbed2pdo\tests\skews_combined.php on line 211

  Update query is: 1 WHERE bk_ref = :bk_ref with bk_ref: 10063

  Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 WHERE bk_ref = '10063''

  I have used single quotes and periods with mysqli for many years. I won't claim it was good coding, but it worked. But this is the first time I've ever used variables for the column names. Thank you for your help.

• Tim Dawson, 23.07.23 16:47

  I have a web site where visitors can look at accommodation reviews. A selection of reviews is randomly selected from a database table each time. When a review is shown it is recorded in another table as a 'hit'. That works perfectly in PHP/MySQL (PDO) with:

  $query = "UPDATE " . $table . " SET hits = hits+1 WHERE bk_ref = :bk_ref";

  where there's just one column to update. But it runs into trouble if I also try to select the column with a variable as in:

  $query1 = "UPDATE " . $table . " SET " . $hits . " = " . ($hits+1) . " WHERE bk_ref = :bk_ref";

  I've tried various combinations of bracketing (round, square and curly) but without success. Is it possible, and if so how can I achieve my aim, please?

  Reply:

  Hello Tim.

  Sorry, I don't get what's the problem here.

  You just replace the column name with a variable (properly filtering it prior). That's all. So it should be just $col = $col+1. Like this

  if (!in_array($col, ['hits','visitors'])) {
      throw new InvalidArgumentException("$col is invalid column name");
  }
  if (!in_array($table, ['whatever','visitors'])) {
      throw new InvalidArgumentException("$table is invalid table name");
  }
  $query = "UPDATE $table SET $col = $col+1 WHERE bk_ref = :bk_ref";

  remember that if you don't filter out the table and column name it will be a straight up SQL injection.

• Marcel, 16.02.23 18:32

  I used to program with php/mysql many years ago. Not anymore sind mysqli and PDO. How do I get error-messages in PDO as it used to be with: $result=mysql_query("") or die(mysql_error(); Thanks a lot Greetsings from Switzerland

  Reply:

  Hello Marcel!

  Nothing could be simpler! With PDO you don't have to write any code, it already acts as though you wrote or die(mysql_error(). When an error occurs, you will see it. As simple as that.

• Jason Muniz, 06.02.22 23:26

  I'm trying to crm all this so i can teach it to someone for 60 an hour in the next 3 days wish me luck becuase i hve to learn php, mysql and pdo as well as the post and get methods to teach this guy....i have never programmed before well i made a website once using html5 css3 and javascript but that was once this is all new ans confusing to me but i'm glad i found this tutorial it's teaching me a lot that i didn't know

• Alan Netherclift, 21.07.21 06:40

  Please feel free to delete this comment, but I cant seem to see any of the examples on the page /pdo_examples (this page) and also the /articles page. I can only see the heading <h1 followed by the link to the comments section of the page. This happened a couple of weeks ago. Luckily I have bookmarked all of the pages with the individual examples on (select, insert, update etc) as I use this site as reference quite often. I have checked this in several browsers on several machines in different localities, so I'm pretty sure it's not me. Thanks for the help you provide to me and the community in general. You have completely changed the way I code PHP and MySQL. Cheers.

• Ray Jenson, 20.05.21 23:57

  Could you perhaps give an example of a PDO DELETE other than the single example in the main tutorial article? I've been finding a lot of SQL-injectable examples, and I think it might be best if you simply make a short article covering this just for completeness... and because I just had to chide my best friend for a DELETE that used the $needle directly instead of protecting it with PDO ... which is insanely injectable, and repeated many places around the web.

  Thanks in advance if you choose to do this!

  Reply:

  Hello Ray!

  You are absolutely right. I've no idea what I was thinking about when didn't include a page for DELETE. Now I added one, thanks to your suggestion!

  Thank you!

• Koketso, 21.03.21 23:21

  Hi Are we able to call procedures or routines with PDO?

  Reply:

  Hello, yes, it is covered in the main article, https://phpdelusions.net/pdo#call

• candra, 25.08.20 16:18

  Hello, Can you explain how to use NOT IN query and UNION query in PDO please? I need help. Thank You.

• John, 20.05.20 13:09

  Hi. How can we count the number of columns of a table with sql pdo?

• Elizabeth, 12.04.20 04:27

  Hi, my php programs which worked fine on my local server are now no longer passing through "Post" data from forms on the production server. Do i need to alter the PHP config file, any help would be most appreciated.

• Elizabeth, 14.11.19 02:47

  Hiya, is there safe way to use FILTER_VALIDATE_URL to validate URLs in PHP? thanks again for your help

  Reply:

  Hello Elizabeth

  It depends on what do you call "safe". Generally, I would consider FILTER_VALIDATE_URL safe enough.

• Stefan Radovanovic, 01.08.19 20:22

  Hi ther im unable to find any good tutorial on php PDO. Im heaving trouble with inserting into database. I tryd namespaces and other method and always error : "no parameters were bound". If you have time to check my code, or give me a link of good course. im trying to create register and login. thanks in advance!

• Richard, 11.01.18 16:41

  Excellent tutorial, thanks. Could you recommend a book covering PDO and web programming in more detail?