I don’t think I’ve ever used bindValue myself, but I don’t do much real-world PHP so maybe there’s a time when it would be preferable.
There are only two main things that are sent to the database server for a prepared query - 1) the sql query statement to prepare, and 2) an execute command, that optionally may contain a positional list of data (the named place-holders that PDO supports only exist within the PDO driver and cause php to do a name to position replace operation on each execution.) See the definition in the MySql internals doc - https://dev.mysql.com/doc/internals/en/prepared-statements.html
Using explicit binding, bindParam/bindValue, is an unnecessary waste of typing, memory, and execution time, and putting them inside a loop only wastes more time, since these only affect what the PDO driver does internally, and don’t cause anything to occur between php and the database server.
Just avoid all of that and simply, directly supply an array of values to the ->execute([…]) call, regardless of calling ->execute once or inside of a loop. Note: this works correctly even if the array is empty for those cases where the sql statement may have been dynamically built and doesn’t contain any prepared query place-holders.
Next, the OP is using a multi-value insert query to achieve a large performance gain over looping over each row of data. The bottle-neck for an insert/update/delete query that you would tend to execute multiple times with different data is in the communications between php and the database server. You want to reduce the total number of communications and you want to do the most work on the database server per communication. After a LOAD DATA [LOCAL] INFILE … query, a prepared multi-value insert query is (I did a benchmark when the mysqli extension first came out) the fastest way of inserting a large amount of data.
1 Like
really! so am the only developer still using only php :
So which language is your top choice when it comes to development?
really? but i thought is recommended to use prepared statement with bindParam to avoid SQL injection? infact my old way of mysql CRUD was much simpler to use without prepared statement and bindParam to am seriously opened to sql injection which made me start using prepared statement and bindParam.
are you saying i should just do this
$data = array('mango', 'pawpaw', 'orange');
$add = $con->query ("INSERT INTO table (id, name, comment, date);
$add->execute ($data);
Wil really like to see how the code will look like, so curious now.
It is, I believe the point was to not use bindparam, but to provide the parameters as an array in your excute() call instead, as the solution is doing in post #14 .
No, that wouldn’t work as you are passing in an array without any parameters in the query to replace.
I don’t do very much real-world development work in anything at all (my current project uses a mix of VB and a business BASIC on an operating system you probably have never heard of), and I no longer have any live web-based projects. I’m semi-retired, and just here to learn about PHP, which the forum is very good for.
Okay thats very perfect, even the bindParam looks dirty and makes code lengthy, if it is same effect and protect SQL injection then is no need using it as @mabismad had pointed out
no doubt this forum is rich with loads of experience and things to learn, it has improved my development skill. but I want to perfect with PHP before moving deeply into JavaScript as everyone tends to be doing JavaScript this days but the language still rely on php for some server side function like adding to database.
For me, that would be too ambitious - as soon as I get through everything on PHP, someone comes along and brings out a new version* with more things to learn, like any other language. So I’d say the thing is to become “proficient” and then do your best to keep that way.
Keep reading up on this stuff - I felt quite smug because I started learning PHP from an old book which told me about all the old-style mysql library calls, but then I came on here and learned about PDO and didn’t fall into the trap of wasting too much time on the old (and at the time deprecated) functions. But then I’ve been happily using bindParam and the like since then, thinking it was a great way of doing it.
( * I know it’s probably not all that often, but with any language that’s under development it’s inevitable that things will change.)
1 Like
Modern development has moved away from using monolithic PHP CMSs and Frameworks to APIs and reactive front-end experiences. API development is typically done in Java, .NET, Python, and NodeJS not php. Most of the PHP you will come across these days is maintaining older legacy applications. Not the fun work of building brand new applications from the ground up.
That type of work is being done using much more modern, and robust languages and frameworks not php. Someone though does need to maintain all the php projects out there built on Wordpress, Drupal, Magento, Symfony, etc. I don’t consider that work fun at all but if that is your desire more power to you. Otherwise I wouldn’t recommend learning php.
It’s far more useful to learn JavaScript since it can be used both on the client and server for modern development. Thats my opinion about php development these days which is why I ran away from it about 4 years ago now and have only been working on modern tech. You can easily be caught in a situation where options are limited with only php knowledge. Where as with javascript and node the possibilities, opportunities are really endless. I do see many php jobs but they are always for maintaining outdated code probably using some legacy CMS and that work is such a drag.
NodeJS, React, Angular those are really the technologies that lead to most opportunities developing modern web applications. NoSQL is another but people and companies have not been as quick to switch from relational databases to NoSQL solutions compared to programming languages and frameworks in the last decade. SQL is still used much more than some might of thought when NoSQL was a BIG trend.
1 Like
Thanks alot, I will try as much to become proffecient with PHP and It will be very easy to adapt to new updates and codes when they are released because I already have a clear knowledge about the language.
I started with mysql, then moved to mysqli and prepared, now am doing PDO with bindParam, i learnt PDO and prepared on this forum too.
Thanks alot but I just want to learn PHP as to gain a background knowledge of how things work, moreover PHP is the father of server side programming, and I think wordpress will still be using PHP till the next 50years.
But then i will learn more languages
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.