MySQL Can't Perform More Than 1 Query at a Time

Mysql can't perform more than 1 query at a time

$db->query('insert into movies values("lion king");');
$db->query('select * from movies;');

phpMyAdmin doesn't let me run more than one query at a time, and importing my SQL fails

Separate commands by ; like:

INSERT INTO b1b_postmeta (meta_key, meta_value) VALUES ('only_buy_now', 1);
INSERT INTO b1b_postmeta (meta_key, meta_value) VALUES ('auto_renew_item', 0);

As I stated in comment phpMyAdmin doesn't let me run more than one query at a time, and importing my SQL fails

Execution of two mysql queries gets extremely slow

    LEFT JOIN  planner P  ON P.weekid = W.id
      AND  P.vakid = ?
      AND  P.studieid = ?

and

    LEFT JOIN  planner P  ON P.studieid = K.studieid
      AND  P.vakid = K.vakid
      AND  P.weekid = ?

both need INDEX(weekid, valid, studieid) (in any order)

    LEFT JOIN  studie S  ON K.studieid = S.id
    LEFT JOIN  vakken V  ON K.vakid = V.id
    WHERE  S.leerjaar = ?
      AND  S.studie = ?

is not a "LEFT" join since you specify values for 2 columns. I suggest adding INDEX(leerjaar, studie, id)

    AND  (P.toets != '' OR  P.toets2 != '' )

In general, OR cannot be optimized. But please provide the EXPLAIN.

    ORDER BY  P.toetsdag

might benefit from INDEX(toetsdag)

Why do you need to run the query 8 times?

perform multiple sql queries when clicking a submit button on PHP Page

I think that this is likely to be your problem

while($rows=mysql_fetch_array($result)){

Should be

while($rows=mysql_fetch_assoc($result)){

To refer to them as

echo $rows['dispatcharea'];

EDIT:

You also need to split both of your queries up in to two separate queries, because you cannot run two queries in one mysqli_query() tag.

You will need to split them as shown below:

 // First update query
 $sql1="update loaddetails set loadid= (select max(loadid)+1 from loadcounterid) where loadid=0";

// Second update query
$sql2="update loadcounterid set loadid= (select max(loadid) from loaddetails) where loadid>0";

// Run both queries independently
$final_query1 = mysql_query($sql1);
$final_query2 = mysql_query($sql2);

// Check for query success
if ($final_query1 && $final_query2)
{
   // Success running the queries
}
else
{
   // Unsuccessful running the queries
}

node-mysql multiple statements in one query

I guess you are using node-mysql. (but should also work for node-mysql2)

The docs says:

Support for multiple statements is disabled for security reasons (it
allows for SQL injection attacks if values are not properly escaped).

Multiple statement queries

To use this feature you have to enable it for your connection:

var connection = mysql.createConnection({multipleStatements: true});

Once enabled, you can execute queries with multiple statements by separating each statement with a semi-colon ;. Result will be an array for each statement.

Example

connection.query('SELECT ?; SELECT ?', [1, 2], function(err, results) {
  if (err) throw err;

  // `results` is an array with one element for every statement in the query:
  console.log(results[0]); // [{1: 1}]
  console.log(results[1]); // [{2: 2}]
});

So if you have enabled the multipleStatements, your first code should work.

Multiple queries executed in java in single statement

I was wondering if it is possible to execute something like this using JDBC.

"SELECT FROM * TABLE;INSERT INTO TABLE;"

Yes it is possible. There are two ways, as far as I know. They are

1. By setting database connection property to allow multiple queries,
   separated by a semi-colon by default.
2. By calling a stored procedure that returns cursors implicit.

Following examples demonstrate the above two possibilities.

Example 1: ( To allow multiple queries ):

While sending a connection request, you need to append a connection property allowMultiQueries=true to the database url. This is additional connection property to those if already exists some, like autoReConnect=true, etc.. Acceptable values for allowMultiQueries property are true, false, yes, and no. Any other value is rejected at runtime with an SQLException.

String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true";  

Unless such instruction is passed, an SQLException is thrown.

You have to use execute( String sql ) or its other variants to fetch results of the query execution.

boolean hasMoreResultSets = stmt.execute( multiQuerySqlString );

To iterate through and process results you require following steps:

READING_QUERY_RESULTS: // label  
    while ( hasMoreResultSets || stmt.getUpdateCount() != -1 ) {  
        if ( hasMoreResultSets ) {  
            Resultset rs = stmt.getResultSet();
            // handle your rs here
        } // if has rs
        else { // if ddl/dml/...
            int queryResult = stmt.getUpdateCount();  
            if ( queryResult == -1 ) { // no more queries processed  
                break READING_QUERY_RESULTS;  
            } // no more queries processed  
            // handle success, failure, generated keys, etc here
        } // if ddl/dml/...

        // check to continue in the loop  
        hasMoreResultSets = stmt.getMoreResults();  
    } // while results

Example 2: Steps to follow:

1. Create a procedure with one or more select, and DML queries.
2. Call it from java using CallableStatement.
3. You can capture multiple ResultSets executed in procedure.
   
   DML results can't be captured but can issue another select
   to find how the rows are affected in the table.

Sample table and procedure:

mysql> create table tbl_mq( i int not null auto_increment, name varchar(10), primary key (i) );
Query OK, 0 rows affected (0.16 sec)

mysql> delimiter //
mysql> create procedure multi_query()
    -> begin
    ->  select count(*) as name_count from tbl_mq;
    ->  insert into tbl_mq( names ) values ( 'ravi' );
    ->  select last_insert_id();
    ->  select * from tbl_mq;
    -> end;
    -> //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call multi_query();
+------------+
| name_count |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

+---+------+
| i | name |
+---+------+
| 1 | ravi |
+---+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Call Procedure from Java:

CallableStatement cstmt = con.prepareCall( "call multi_query()" );  
boolean hasMoreResultSets = cstmt.execute();  
READING_QUERY_RESULTS:  
    while ( hasMoreResultSets ) {  
        Resultset rs = stmt.getResultSet();
        // handle your rs here
    } // while has more rs

NodeJS app fails to run queries on MySQL database more than 6 times

Solved it!

I wasn't using a "res" object in there. Added "res.status(201).json()", and it's all better now.

app.post('/deleteBtDb/', upload.none(), function (req, res, next) { 
    let sql = `DELETE from tblbacktrack order by btID ASC limit 1`
    let query = db.query(sql, (err, results) => {
        if(err) {
           throw err
           }
        })
res.status(201).json();
})

More efficient - multiple SQL queries or one query and process in php?

Generally, it's better to filter on the RDBMS side so you can reduce the amount of data you need to transfer.

Transferring data from the RDBMS server over the network to the PHP client is not free. Networks have a capacity, and you can generate so much traffic that it becomes a constraint on your application performance.

For example, recently I helped a user who was running queries many times per second, each generating 13MB of result set data. The queries execute quickly on the server, but they couldn't get the data to his app because he was simply exhausting his network bandwidth. This was a performance problem that didn't happen during his testing, because when he ran one query at a time, it was within the network capacity.

---

Related Topics