PDO Examples
In this chapter you will find examples for the various PDO use cases.
Related articles:
- SELECT query with PDO
- INSERT query using PDO
- UPDATE query using PDO
- How to connect to MySQL using PDO
- Authenticating a user using PDO and password_verify()
- How to check if email exists in the database?
- Select the number of rows using PDO
- How to create a WHERE clause for PDO dynamically
- DELETE query using PDO
- How to create a prepared statement for UPDATE query
- Getting a nested array when multiple rows are linked to a single entry
- How to execute 1000s INSERT/UPDATE queries with PDO?
- Adding a field name in the ORDER BY clause based on the user's choice
- INSERT helper function for PDO Mysql
- PDO Examples
- PDO Examples
Comments:
Further to my previous reply to your e-mail, I now find that the following, with double quotes, as you sent it, works:
(which is what you sent me) Whereas what I had written with single quotes and periods, does not:
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.
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:
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:
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 thisremember that if you don't filter out the table and column name it will be a straight up SQL injection.
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.
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
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.
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!
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
Hello, Can you explain how to use NOT IN query and UNION query in PDO please? I need help. Thank You.
Hi. How can we count the number of columns of a table with sql pdo?
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.
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.
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!
Excellent tutorial, thanks. Could you recommend a book covering PDO and web programming in more detail?