Solved

multiple statements one connection pdo

Posted on 2014-10-09
6
338 Views
Last Modified: 2014-10-10
Hi

How do you send multiple statements on one command in pdo

Ihave seent this

http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd

But the tone suggets you shouldnt do it

I have seen mysqli_multiquery

Is there any reason why you shouldnt send mltiple statements in one command in pdp to mysql? Just trying to exany my knowledge
0
Comment
Question by:andieje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 167 total points
ID: 40370213
One of the reasons can be that what if you send three statements related to each other insert in the first then update of the inserted record in the second line and the insert fails and the update is successful you can then end up with some dirty data and the tracking of that is quite a mess.

Also, the security threat, since like this some may alter your site and send in the drop table or database statement etc..

My opinion, more the issues then the benefits but if it suits you just take care of those things and go with it...
0
 

Author Comment

by:andieje
ID: 40370276
ok

1)scenario 1
Make connection
Make repeaed statements on on go
close connection

2)secenrio 2
make db connection
make multiple statements
disconnect

In my case the statements are just imple retrieval,

I dont understand your last sentence. My apologies. Please could you explain. Are you saying more issues sending multiple statements in one go than the benefits?

I can follow that advice.

But you only need to open the database connection once dont you for all your statements? Or do you have to open/close each time to free it up for other users as that seems to be taking time
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40370290
Basically,

you open connection for sql and close it but here the difference is that in that sql you have multiple SQL statements instead of just one in the first case...

What i meant is that there can be some possible issues in using it than benefits and that you should test it regarding the performances of the database if there is any change and you are ok with it you use if not try not to use it...

Anyway, the best way is that you try and review performance of both approach and then decide...

And no need to apology for not understanding, just ask;)
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 34

Assisted Solution

by:Beverley Portlock
Beverley Portlock earned 167 total points
ID: 40370298
Database connections only need to be opened once. You then use the connection to run whatever queries you wish and then close it when you are done.

As for the advantages of packing multiple queries into one statement and running it ... well I cannot see any. I understand that you are running SELECT queries and therefore it may not be much of an issue but how do you check for success of all queries and if you are ever in a situation where query #2 depends on query #1 what happens then?

I run one query at a time and then check its outcome before doing the next so these issues never arise for me. It seems to me that throwing a lot of queries at the database and then picking through the outcomes trying to determine what happened would require a lot of work and careful programming.

At the end of the day you have a choice and it is your decision which to use, but personally I cannot see any advantages of single-statement-multiqueries.
0
 

Author Comment

by:andieje
ID: 40373752
I've requested that this question be closed as follows:

Accepted answer: 0 points for andieje's comment #a40370276
Assisted answer: 250 points for samo4fun's comment #a40370213
Assisted answer: 250 points for samo4fun's comment #a40370290

for the following reason:

brilliant
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 total points
ID: 40370510
What @bportlock said.  Of course, there are transactions, but that seems to be beyond the scope of this question.  If you're unsure about how to use MySQL databases with PHP, take a little while with this article.  It shows all the basics.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question