How do you do multiple SQL statements in one mysql_query?
I've never tried this, but I think you can use mysqli::multi_query. One of the good things about mysql_query
rejecting multiple statements is that it immediately rules out some of the more common SQL injection attacks, such as adding '; DELETE FROM ... #
to a statement. You might therefore want to be careful with multiple statements.
PHP code to execute multiple queries with a single mysql_query() call
From the manual:
mysql_query()
sends a unique query (multiple queries are not
supported) to the currently active database on the server that's
associated with the specified link_identifier.
Move to mysqli
, which has support for multiple statements.
Executing multiple SQL queries in one statement with PHP
Pass 65536
to mysql_connect
as 5th parameter.
Example:
$conn = mysql_connect('localhost','username','password', true, 65536 /* here! */)
or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
INSERT INTO table1 (field1,field2) VALUES(1,2);
INSERT INTO table2 (field3,field4,field5) VALUES(3,4,5);
DELETE FROM table3 WHERE field6 = 6;
UPDATE table4 SET field7 = 7 WHERE field8 = 8;
INSERT INTO table5
SELECT t6.field11, t6.field12, t7.field13
FROM table6 t6
INNER JOIN table7 t7 ON t7.field9 = t6.field10;
-- etc
");
When you are working with mysql_fetch_* or mysql_num_rows, or mysql_affected_rows, only the first statement is valid.
For example, the following codes, the first statement is INSERT, you cannot execute mysql_num_rows and mysql_fetch_*.
It is okay to use mysql_affected_rows to return how many rows inserted.
$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
INSERT INTO table1 (field1,field2) VALUES(1,2);
SELECT * FROM table2;
");
Another example, the following codes, the first statement is SELECT, you cannot execute mysql_affected_rows. But you can execute mysql_fetch_assoc to get a key-value pair of row resulted from the first SELECT statement, or you can execute mysql_num_rows to get number of rows based on the first SELECT statement.
$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
SELECT * FROM table2;
INSERT INTO table1 (field1,field2) VALUES(1,2);
");
Can I fetch multiple different SQL statements in one times loop? in PHP
I just had an answer. It's working for me
Idea from @Drew while($continue)
Here is my code
$mssql_query = mssql_query(..mssql..);
$mysql_query = mysql_query(..mysql..);
$continue = true;
while( $continue )
{
$ms_rs = mssql_fetch_assoc($mssql_query);
$my_rs = mysql_fetch_assoc($mysql_query);
if($ms_rs){ ..some action.. }
if($my_rs ){ ..some action.. }
$continue = $ms_rs|| $my_rs;
}
Thank you for all the ideas.
PHP multiple MYSQL commands in one mysql_query() query
I think you need this?? http://us2.php.net/manual/en/mysqli.multi-query.php
multiple queries with mysql_query in a c++ project
According to the MySQL C API documentation:
MySQL 5.6 also supports the execution of a string containing multiple
statements separated by semicolon (“;”) characters. This capability is
enabled by special options that are specified either when you connect
to the server with mysql_real_connect() or after connecting by
calling` mysql_set_server_option().
And:
CLIENT_MULTI_STATEMENTS enables mysql_query() and mysql_real_query()
to execute statement strings containing multiple statements separated
by semicolons. This option also enables CLIENT_MULTI_RESULTS
implicitly, so a flags argument of CLIENT_MULTI_STATEMENTS to
mysql_real_connect() is equivalent to an argument of
CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS. That is,
CLIENT_MULTI_STATEMENTS is sufficient to enable multiple-statement
execution and all multiple-result processing.
So, you can supply several statements in a single mysql_query() call, separated by a semicolon, assuming you set up your mysql connection a bit differently, using mysql_real_connect.
You need to pass the following flag as the last argument: CLIENT_MULTI_STATEMENTS, whose documentation says:
Tell the server that the client may send multiple statements in a
single string (separated by “;”). If this flag is not set,
multiple-statement execution is disabled. See the note following this
table for more information about this flag.
See C API Support for Multiple Statement Execution and 22.8.7.53. mysql_real_connect() for mroe details.
php/mysql with multiple queries
Thanks to php.net I've come up with a solution : you have to use (mysqli_multi_query($link, $query))
to run multiple concatenated queries.
/* create sql connection*/
$link = mysqli_connect("server", "user", "password", "database");
$query = "SQL STATEMENTS;"; /* first query : Notice the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS"; /* last query : Notice the dot before = at the end ! */
/* Execute queries */
if (mysqli_multi_query($link, $query)) {
do {
/* store first result set */
if ($result = mysqli_store_result($link)) {
while ($row = mysqli_fetch_array($result))
/* print your results */
{
echo $row['column1'];
echo $row['column2'];
}
mysqli_free_result($result);
}
} while (mysqli_next_result($link));
}
EDIT - The solution above works if you really want to do one big query but it's also possible to execute as many queries as you wish and execute them separately.
$query1 = "Create temporary table A select c1 from t1";
$result1 = mysqli_query($link, $query1) or die(mysqli_error());
$query2 = "select c1 from A";
$result2 = mysqli_query($link, $query2) or die(mysqli_error());
while($row = mysqli_fetch_array($result2)) {
echo $row['c1'];
}
Execute multiple queries at once in Mysql using PHP
What you are looking for is TRANSACTIONS
assuming you are not using MyISAM
since it does not supports Transactions
.
The concept of transactions
is that either all the queries will execute or no query would execute at all.
In simple words all-or-nothing
is what Transactions
do
This is a basic example using mysqli
mysqli_query("START TRANSACTION");
$query1 = mysqli_query("INSERT INTO TABLE1(id) VALUES(2)");
$query2 = mysqli_query("INSERT INTO TABLE2(id) VALUES(3)");
if ($query1 and $query2) {
mysqli_query("COMMIT"); //Commits the current transaction
} else {
mysqli_query("ROLLBACK");//Even if any one of the query fails, the changes will be undone
}
NOTE: This was just a simple example.It would better if you implement using try
and catch
blocks handling then exceptions properly.
Take a look at PHP DOCS
Multiple $query = mysql_query in PHP
foreach ($fields as $field => $value) {
$value = mysql_real_escape_string($value);
$query = mysql_query("SELECT COUNT({$field}) AS n FROM Users WHERE {$field}='{$value}';");
$tuple = mysql_fetch_assoc($query);
if ($tuple['n'] > 0) {
$dup = true;
break;
}
This is what you are looking for. But be careful, it is vulnerable to SQL Injection.
Related Topics
PHP Datetime() Class, Change First Day of the Week to Monday
MySQL Select from Tables Based on Multiple Rows
How to Check If a Directory Exists? "Is_Dir", "File_Exists" or Both
Pull All Images from a Specified Directory and Then Display Them
Turn Off Deprecated Errors in PHP 5.3
How to Paginate a Merged Collection in Laravel 5
PHP Multidimensional Array Get Values
Best Way to Avoid Code Injection in PHP
Laravel Compact() and ->With()
Protection Against Xss Exploits
Simplexml: Working with Xml Containing Namespaces
Pdo Try-Catch Usage in Functions
How to Redirect Back to Form with Input - Laravel 5
How to Set an Arrays Internal Pointer to a Specific Position? PHP/Xml