Multiple MySQL queries in one call
April 11th, 2002[Note – this post was written in 2002. Things have changed since then – check your docs]
What the hell good is a sql call that is limited to a single query? I find that PHP’s mysql_query() won’t take multiple statements. Makes for some unnecessarily wordy code. I found a good snippet in PHP.net’s online manual annotations, though…
mars@planet-d.net
28-Jan-2002 11:39
it seems php’s mysql_query does handle multiple queries at the same time,
but only returns a boolean value even if the last request is a select…
phpmyadmin which uses mysql_query a lot has this behaviour too.
so i wrote this function to solve this problem
function sendquery( $db_host, $db_base, $db_user, $db_pw, $query )
{
$mess = ‘Connexion with server ‘ . $db_host . ‘ failed’;
mysql_connect( $db_host, $db_user, $db_pw ) or die( errorhandle($mess));
mysql_select_db( $db_base ) or die( errorhandle($mess));
$array = explode( ‘;’, $query );
foreach( $array as $value )
{
if( !$result = mysql_query( $value ))
break;
}
return $result;
}
i hope this helps.
Excellent function.
Was I lucky to find this info!
Brilliant!
great!
simple and very useful!
Just thought I’d let you know that this isn’t a particularly useful function if the data that you are going to be dealing with has semicolons in it, or has the potential to.
For example, form input (especially in a CMS) may have semicolons in, and this function will split up the $query string on the semicolon, potentially in the middle of data.
The previous commenter is right, you should just make that take an array unless you want to implement your own SQL mini parser.
As sql solved this? i saw mysqli solutions but i dont want to use extensions in order to make multiple queries in a call. Going to put this to work, but is there any other alternative other then functions?
Yes there is an alternative – use MySQL 5 with stored procedures. This post is 4 years old and a lot of things have happened since then.
What if there is “;” inside the query, something like
SELECT title,text FROM article WHERE text LIKE ‘%;%’
?
it can be used for SQL Injection…
hello there,
i am trying to reproduce a data server that a company uses to present information to its customers via the web. No problem. What they are using right now is Microsoft Access, connecting via ODBC to a server on line. The cool thing that they can do is have several tables linked together. Like if a certain field is updated in one table, it updates the same info automatically in another table. So, i want to reproduce this in MySQL, but i can’t seem to find out how. Is there a way to pull this off on the server side ? or does that have to be done on the client side ?
thanks for any tips.
I’m not sure if MySQL has triggers yet, but that’s how I have implemented a similar replication scheme in PostgreSQL – put a trigger on the table that on insert/update/delete makes the same change in the other tables.
This is something here that looks like it will use mysql_query in php to execute multiple queries. I’ll admit that I haven’t tested it, but if it’s up on their site, I assume it works.
http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html
Try this ….
‘; DELETE FROM users;
as an input. Goodbye data 😛
try execute a db script with create function statement
while the above solution is useful to minimize amount of code required to make multiple queries, we do still need the support for patch queries, and having each query’s bool or resource return value. that’s improves performance and at some cases is so needed.
OR: include a .sql file with as many queries in it as you want.