How to execute two mysql queries as one in PHP/MYSQL?

Question 1

I have two queries, as following:

SELECT SQL_CALC_FOUND_ROWS Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;
SELECT FOUND_ROWS();

I want to execute both these queries in a single attempt.

$result = mysql_query($query);

But then tell me how I will handle each tables set separately. Actually in ASP.NET we uses dataset which handles two queries as

ds.Tables[0];
ds.Tables[1]; .. etc

How can I do the same using PHP/MYSQL?

Question 2

Update: Apparently possible by passing a flag to mysql_connect(). See Executing multiple SQL queries in one statement with PHP Nevertheless, any current reader should avoid using the mysql_-class of functions and prefer PDO.

You can't do that using the regular mysql-api in PHP. Just execute two queries. The second one will be so fast that it won't matter. This is a typical example of micro optimization. Don't worry about it.

For the record, it can be done using mysqli and the mysqli_multi_query-function.

Question 3

yes, generally being minimal when it comes to the number of querys is performant. But, your script is limited by network io(download speed), not the database. Your script will be asleep most of the time while the os downloads the data. Any queries you issue would be relatively infrequent because of that.

my advice is to just get it working.

Question 4

If you want to execute multiple statements atomically, you need to use a transaction. A JDBC connection defaults to 'auto-commit' mode, which means each statement is executed in its own transaction. So you first need to disable auto-commit mode, using Connection.setAutoCommit(false).

With auto-commit mode disabled, executed statements will be executed in the current transaction, if there is no current transaction, one will be started. This transaction can then either be committed using Connection.commit() or rolled back using Connection.rollback().

You will need to do something like:

try (Connection connection = DriverManager.getConnection(...)) {
    connection.setAutoCommit(false);
    try (Statement stmt = connection.createStatement()) {
        stmt.executeUpdate(<your first update>);
        stmt.executeUpdate(<your second update>);

        connection.commit();
    } catch (SQLException e) {
        connection.rollback();
        throw e;
    }
}

For more details, see the JDBC tutorial chapter Using Transactions.

And please learn about prepared statements. Concatenating values into a query string is bad, because it can lead to SQL injection or weird errors if you forget to escape values. See also the JDBC tutorial chapter Using Prepared Statements.

Question 5

Calling mysql_fetch_assoc() retrieves the next row (i.e., the next one you haven't already retrieved). Once you've retrieved all the rows, it returns false. So, once you've gotten through that first loop, you have retrieved all the rows, and all you'll get back is false every time!

If you need to reuse the same data twice, how about putting it all in an array?

$rows = array();
while($row = mysql_fetch_assoc($affiliateID)){ 
    $rows[] = $row;
}

Now you can iterate through $rows as many times as you like:

foreach($rows as $row) { ... }

Question 6

SELECT option_name,
       option_value
  FROM bdlocal_wrdp1.wp_options
 WHERE option_name IN ('siteurl', 'blogname', 'blogdescription')

And after this query execution you'll have 3 pairs of option_name + option_value values.

How to execute two mysql queries as one in PHP/MYSQL?

Answers

php,mysql,loops