Php should i use pdo

SQL statements are often dynamically built. A user provides some input and this input is built into the statement. We must be cautious every time we deal with an input from a user. It has some serious security implications.

The recommended way to dynamically build SQL statements is to use parameter binding. PDO contains bindParam and bindValue method to create parameterized queries. In the example, we use bindValue to create a parameterized query. We use question mark placeholder. The select statement fetches a specific row from the table.

We bind the value with bindValue to a question mark placeholder. In the example, we create a new table. In any case, any problem with a PDO query would raise an error that should help you to find the problem. Actually, I had thought to do like that but I thought wrong that as I had no data fields in the table I could not do it If you want to update the "last record" than you definitely doing it wrong. To update a record you must know for certain which particular record you want to update, not just a relative position.

If you can explain why do you need this update I will tell you ow to do it right. How to formulate the query to modify only the last record of the table? Of course, there are no ID fields in this table, only a primary key. At least for mysql, there is no way to tell "the last" record, unless you have a field designated for this purpose. Besides, "the last" is too clumsy a description, you must know the exact key for the row you need to update. Other wise a race condition may ruin your operation.

Hello, by doing some research I found your site which unfortunately did not answer to my problem. Can you help me please? The request is executed but the table is not modified!

Can you tell me how to implement this please? Well, the answer is written on this very page, although I admit it is not that explicit as should be:.

So, for every query you run, if at least one variable is going to be used, you have to substitute it with a placeholder, then prepare your query, and then execute it, passing variables separately. It meand that every single variable should be substituted with a placeholder and then go into execute:. However, only by luck did I try to qualify my todos table with its database mytodo and see that when I.

I was adding to this table, and on my sql gui I could see they were adding, and same with the sql terminal, yet the fetchAll array was empty when I neglected the mytodo. If I didn't have luck, what search terms could I have entered on search engines to tell me that I need to do that prepending of the database name to my column? I'm following these beginner PHP videos on laracast, and quite a few times copying the exact thing in the video doesn't seem to work I'm on PHP 7.

Couldn't search for the right term for this problem I had and I'm scared once I progress to bigger errors, I wouldn't be able to find my way out! I don't know if it's your case, but only prepare is not enough.

You must always execute a prepared query to get the results. As of searching the answer, I would probably search for the basic PDO select example and follow it attentively.

However, if it works only if you add mytodo. Searching would be straightforward, something like my query works only if I add database to the table name. Hi, any compliment is superfluous.

I'm not sure if it's possible to reply to a reply here for us regular peons, but thank you for your quick and very thorough reply and I apologize for starting a new comment thread if it's possible to reply to my own.

That was an extremely helpful response. To be honest, I actually always saw the backticks used elsewhere and had no idea the purpose It wasn't until reading this fantastic article that I learned what those backticks are for.

I'll go update my code to use the backticks, just in case. And yeah, sorry, "user input" wasn't a very descriptive term to use, but I can say that yes, everything is hardcoded. I definitely would not be using any reserved words in table names, but I like the idea of just following good standards, and doing that early on enough in development which is the case here and sticking to them throughout any coding I do. So, off I go to fix it all up! My table names are all constants, stored in a constants php file so that if I ever change things like schema name or something like that, I have one central place to update.

So for example, I have a constant defined like so:. My syntax above might be a little off.. So I assume that unless they somehow gain access to that file to update it in which case I probably have bigger worries the above code is safe? Thanks for your time and advice, and this article The only proper PDO tutorial is incredibly well done. However, from the correct SQL syntax point of view there could be a problem.

Your table name could be an SQL reserved word or an incorrect syntax contain a space or a hyphen for example so it's much better to enclose it in backticks, just in case. So the only difference I would make is. In this case you must choose them from a whitelist - so effectively it will make them hardcoded and thus immune to injection.

I would also avoid the "user input" term as being quite vague and uncertain. A "hardcoded value" is much better to me, as you can always be certain, whether it's written in your code or not.

If not - then ot hould be added to the query through a placeholder. Hello, thank you for this excellent articles that filled a lot of my shortcomings. I am stuck in my development because I can not use the min and max functions in a select with a limit. Visibly, 'limit' is incompatible with these 2 functions but there may be a trick Do you have a way of doing that works please? Thank you for your help. SQL operators work from left to right, so min and max are taken from the entire table ordered by timestamp, and then only one row returned gets limited to 10 rows.

Also note that the way you are taking a single value from the query is rather peculiar, given PDO has a method for that already:. Hello, certainly off topic but I start Already, very good article on PDO, I learned a lot. Of course I can not find any documentation. Would you be so kind as to indicate a URL or can I find this information?

The function is written to the best of my C knowledge which is very limited , so it could be wrong. Feel free to ask any questions, I'll be happy to help.

Please accept my gratitude for your kindness and willingness to take questions and sacrifice your time for me and a host of others. What role is the slash performing there? A backslash here denotes a namespace - a relatively new concept in PHP. Basically all PHP built-in classes are in the root namespace, so just a single backslash means - the root namespace. I am a novice. I want to build a scoring application for a 'cricket' sports match which will involve a lot of real time inserting recalculation and updating and outputting.

Because of all the good things I've heard about PDO and because I will need to be connected to Mysql for about six hours straight, My question is, Do you think that this is the best or a good way to approach such a project. Software wise? Please note that unless you are using websockets, there won't be a constant connection to a database. By default, PHP processes are atomic, a request initiated from a browser makes a php script run, return the requested data, and die.

So it connects to a database every time. A very good and simple model. Great stuff, but I've posted a question at stackexchange for "PHP 7. In short, I used your advice above to write code more than a year ago, that has worked great with PHP 7. Have tried many things so far. Wondering if you will have better ideas. Glad you got your case resolved.

I had no idea of this issue and had nothing to offer but now I see it was resolved. Use when there is user input, or data being passed from one page to another PHP? Don't need to use when getting data within the same code page without any input except for the code itself?

Would appreciate our opinion. Thank you for the good question. There is indeed too much misunderstanding in the matter. So let's make it straight:. Notice that there is not a single condition you mentioned in your comment like "user input", "data within the same code page" and such. Simply because all these terms are essentially vague and uncertain.

There is no certain definition for "user input". Different people take it differently, with catastrophic results. The moment you start separating the seep from the goat, the "clean" data from "unclean", the moment you are making your first step to disaster. Remember, every application evolves over time. And something that has been hardcoded for ten years, one day becomes dynamically changed and dangerous. Why take chances? Why base your decision empty musings instead of following one simple rule - any variable that goes into query should be substituted with a placeholder, regardless of the source.

I have already tried googling anywhere but has no result, when i remember i have bookmarked your page, then I open it. I cannot upload my photo with this. There could be other issues as well. But it makes very little sense in posting a code and asking what is wrong with it. Humans are not intended to run a code mentally, it's a job for a computer.

So you have to run your code, make sure that all possible error reporting is on and start debugging. To tell you truth, debugging is the main occupations for any programmer. So it is not writing a program that takes most time but finding out why doesn't it work. I've used to your code given, but I do beg your pardon, I cannot see the data I need, I just see white screen whit table head data. Then you can display all the user data that is already fetched.

You need to know the column names in order to display them though. I have made it work without an api, and with a simple php api. However I fail to be able to do this with PDO. It is not as simple as one would think. You are probably mistaking PDO with something else. It belongs to that "remote DB site" only.

How this site is called is of no PDO's concern. The only server PDO talks to is a database server. This resource you have come up with is extremely helpful, especially the parts where you explain where prepared statements can be used. In the transaction section when you make mention of the rollback function, is it not meant to be in camel case as shown in the documentation?

Have you considered writing articles on design patterns and or generic articles regarding writing smarter code in general? Although it could make sense to use a camel case to separate different words in the method name, "rollback" is the established term by itself in a database word and could be count as a whole word, thus making it unnecessary to use a camel case in it.

General error: Cannot execute queries while other unbuffered queries are active. It seems as though there are multiple ways to fix this, however I'm really curious what YOU would recommend. There are so many differing opinions online, however yours is the only one I actually trust, so I thought I would ask you directly.

Most of time you should never see such an error at all. The only plausible case could be caused by a stored procedure, and should be treated as explained in the corresponding chapter. If value is 0, this code always do default action. How to select only one column a check it when this column is 0? That was it! Now everything works! I as able to insert into select fields in the table, not all fields.

Thanks so much for your help! Here's my info and using php tags:. Can you see what I'm doing wrong and help me? There is nothing wrong with your insert. Its HTML form which is at least wrong. You are closing an HTML tag too early should be like. Although there are ways to do that, I would advise against.

There is no point in stuffing as much queries in one statement as possible. Prepared statements are exactly for that purpose.

If your updates will run unexpectedly slow, then it's your database settings to blame. To fix that, wrap your updates in a single transaction. Hello I wish to resume coding after more than 2 years without practice. My strong recommendation wouild be to try a well-established framework, Symfony preferably. It will give you the best understanding of MVC. Bonjour Je souhaiterai reprendre? Thanks for your reply. I followed your guide for beginners and implemented the logging-errors advice.

So far, the best approach How can i use procedures with FOREACH, how can i replace the while with foreach, implementing the empty result prevention, or is more efficiente use while instead? It is not very clear what you are asking, but in general there is no difference between using foreach and while.

In both cases you can just iterate over the statement. In case there is no result, then there simply will be no output:. In case you want to know if there is any result beforehand, just fetch the resulting dataset using fetchAll :. Hi, Very good tutorial, thanks for sharing. My production database server went down and when the app tried to connect to it, the error report threw back the database connection details.

Not nice. How would you recommend to handle this error? This is a very important question! Your PHP sees to be misconfigured, leaking every error message outside, which shouldn't be. Actually, every error message is a breach in the security, so the solution should be a general one. On a live server PHP must be configured to log errors instead of displaying them, so it would never happen again to leak your database credentials outside.

Feel free to drop a line if something is still unclear or you disagree with the proposed solution. Yes, that's perfectly safe. PDO witll either correctly format a parameter for you if emulation mode is turned on or even send it completely separated from the query, and so there would be no way for it to interfere.

For the every class that needs a database connection, make PDO a constructor parameter. The connection has to be made only once! No connects in every function.

No connects in every class constructor. Otherwise, multiple connections will be created, which will eventually kill your database server. Thus, a sole PDO instance has to be created and then used through whole script execution.

I am using oop php. When you assign an object to a variable, there remains just a single object, whereas the second variable being just a reference to it. So when you are sending a PDO instance into a class' constructor, then it's actually a reference, that points to the same PDO instance. So the initial PDO object is not gets duplicated but remains essentially a single instance. So you can tell that the former statement doesn't violate the rule from the latter but rater gets along with it.

Why are you escaping the field names. Yeah, that's an awkward moment. On the one hand, you don't have to escape a whitelisted value as nobody in their right mind would use a backtick in the field name. On the other hand, however, if you take the formatting routine as an abstract one, unaware of the data source, it would be natural to do the full processing, both quoting and escaping - just like any good quoting function should do.

I think it would be better to move the full quoting routine into a distinct function that will be doing the correct formatting regardless. I suppose it will arise less questions. What do you think? Hi and thanks for this great information. I have learnt a lot about PDO and - at least as useful - about a consistent approach to error handling! I also like your style of writing. Thanks mucho! Thank you for your kind words and especially for mentioning the error handling stuff! I am nor sort of crusade in changing the current state of error handling in PHP.

Feel free to check back if you have any problem though - any real life use case can make my articles batter for everyone. It appears that adding a code block does not work.

I just tried submitting another comment with code, but it did not appear as I expected. I submitted a comment a few days ago about escaping operands for the LIKE operator.

Do you think it may have been sent to a spam folder? I was talking with another programmer and hes was complaining about SQL query not working He has not the use to test his queries on the database before coding them on PHP. And he said :. Although it is often useful to have a query from PDO with all the data interpolated, I would rather use it for the profiling. Whereas when a query is not working, all you need as an error message from a database, that will tell you precisely, what is the problem.

Note that you should be using prepared statements to avoid silly syntax errors caused by the data - it goes without saying. That said, there are several code snippets and packages that can get you raw SQL from a prepared query, just google for "pdo interpolated query" or "PDO raw query". I have a simple loop that runs a small number of update statements if there's only one in the loop it works fine more than one and only the first works, with no errors reported I'm confused!!

Dunno why did you cut off the part after WHERE but it's impossible to tell anything without seeing the actual code, sorry. Unfortunately, there is some confusion.

PDO::quote creates a data literal, not an identifier. For example, for MySQL the returned value will be enclosed in quotes, not backticks, which will make it unsuitable to be used as a table name.

How frustrating. I just tested with PostgreSQL, but it uses single quotes so it worked there. One wonders why in so many years, such a basic and necessary feature is not there found a thread from with people complaining about this, sayng it's years overdue.

I see Zend Engine has it, but that means a large dependency for a single quoting function. Seems I'll have to roll up my sleeves. Thanks for the heads up, it could have gone unnoticed otherwise! Nice rundown, much better than overly complex tutorials. However, identifier quoting does exist in PDO didn't back then? Dear Colonel, I greatly appreciate this website and the common sense you share on Stack Overflow.

May I ask you, please, if you have time, to take a look at a conundrum I have posted on SO? I understand if that's not possible for you. Best wishes for the future. I see it has been happily resolved already. Glad you got it working and thank you for your kind words. Oh I typed in my second thought before I saw that you had responded to my question.

Looks like we came to the same conclusion,. Hello, I am running into a problem with pagination. It's looking like 'then you can either use rowCount or simply call count on the array returned by fetchAll " are my only solutions.

I should have added it to the article. And especially thank you for asking. It's always better to ask than devise something of your own. Yes, there is a way to ask a database to count on its side in this case as well. Just wrap your query into another query, like. The idea is to wrap your query into another, and use it to count results.

For this purpose we are selecting just a constant value "1" instead of the actual fields in your main query, just to make a database to do less job. I have to say that I did not understand the code example you use to explain how to use 'like' with pdo. The latter can be left by any unsuspecting fellow and should be taken with a pinch of salt. I have enjoyed reading your content, especially the part about the PDO wrapper. Your site has become a bit of a reference on the topic.

Helps people not to reinvent the wheel every day. A bit off topic perhaps - it's just something I am working on these days. An ambitious idea of having a "caching system" underlying the app, i. Not an easy task to develop something like that. Great work. Yes this question a bit off topic, but from my experience I would rather warn you against such a generic all-embracing cache solution.

In my opinion, your regular queries should be fast by themselves, without the need of any cache. And it's only a few exceptional cases should be handled using a cache, but such cases should be carefully handpicked, considered using a regular optimization, and only then optimized using a cache, with a carefully considered invalidation.

Hi, I again stuck at some point, Now I am trying to edit product details which I already have added. I also have added image field when I click on edit button and then when I add only some feature after clicking on update button I find Picture which I did not touch have gone Now after analysing I have understood the Problem but I am unable to resolve the issue if you can help me in this please.

Please could you help me in this? I also removed function NOW and mysql is taking time itself. I was so happy and wanted to let you know, when came here I found your reply. I really appreciate you for a quick response, you are doing such a great job by helping students. Great work! I will try to bound foreign keys as well as suggested.

Thanks a lot and Regards,. All you need is to brace yourself and write a consistent code. Either in your editor or here in comments. This is absolutely nutty from both ends. Of course most developers don't really need the extra advanced features MySQLi offers, but it certainly could be extremely useful for some, as previously mentioned. It's especially curious that novices are scared to try something "new" and switch to PDO, while a lot of advanced users recite the good ole "ease of switching from database driver" argument as PDO's advantage.

Anyone who believes the myth that you can easily switch among databases in PDO seamlessly has obviously never attempted to do so. First of all let's make one thing clear, the syntax is very similar — almost identical, and I will present that in examples.

Though PDO does have several driver-specific features, it doesn't have all nor keep up with the latest. This is precisely why I don't think MySQLi and PDO aren't necessarily competitors, but rather two powerful libraries with completely different focuses for now. Obviously PDO should be more widely used, however. But as said before, the difference is pretty much negligible as is anyway.

Luckily PDO removed the need to use a dedicated bind function — though I'm not sure why the same isn't done for execute. Non-prepared MySQLi and PDO really aren't so bad, and it's only really the unfortunate implementation of prepared statements that caused them to seem verbose. In reality, the best route is to use a wrapper, query builder or ORM. I have a fairly rudimentary MySQLi wrapper you might like.

The following is a modified example that works with a mysql database. Tested FreeBSD v 6. Merge the prepare and execute in one function like a sprintf. And like sprintf, I choose to use unnamed args? Warning: PDO::query [function. When using persistent connections, pay attention not to leave the database connection in some kind of locked state. This can happen when you start a transaction by hand i. To use that database again, it may then be necessary to disable the persistence attribute to get a new database connection or restart the web server.