Asked  7 Months ago    Answers:  5   Viewed   28 times

When running a long query from PHP, [how] can I kill the query if the user presses stop in their browser?

Take into consideration that I cannot call any other PHP functions because PHP is blocked while waiting for MySQL.

Also I cannot make any more requests to the server (via Ajax) because of session locking.

So one solution could be:

  • ignore user abort
  • run the long query in the back ground and have PHP check every 100ms if it has finished
  • get the pid from the query
  • if the user aborts, kill the pid
  • else return the result when finished

The 2 thing that I dont know how to do in that is:

  • run a non blocking (background) query
  • get the pid of a query

 Answers

88

For those who are interested, here is what I used:

<?php
// Connection to query on
$query_con = mysqli_connect($host, $user, $password, $name, $port);

// Connection to kill on
$kill_con = mysqli_connect($host, $user, $password, $name, $port);

// Start the query
$query_con->query($slow_query, MYSQLI_ASYNC);

// Get the PID
$thread_id = $query_con->thread_id;

// Ignore user abort so we can kill the query
ignore_user_abort(true);

do  {
    // Poll MySQL
    $links = $errors = $reject = array($mysqli->mysqli);
    $poll = mysqli_poll($links, $errors, $reject, 0, 500000);

    // Check if the connection is aborted and the query was killed
    if (connection_aborted() && mysqli_kill($kill_con, $thread_id)) {
        die();
    }
} while (!$poll);

// Not aborted, so do stuff with the result
$result = $link->reap_async_query();
if (is_object($result)) {
    // Select
    while ($row = $result->fetch_object()) {
        var_dump($row);
    }
} else {
    // Insert/update/delete
    var_dump($result);
}
Wednesday, March 31, 2021
 
TecHunter
answered 7 Months ago
26

The following query also returns the current connection identifier

SELECT CONNECTION_ID();

After receiving this connection identifier we can execute our query on this identifier.

Saturday, May 29, 2021
 
HamidR
answered 5 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 4 Months ago
27
Select * from Students where roll_no like '__2011%' and (subject1 ='maths' or subject2='maths' or subject3='maths' or subject4='maths' or subject5='maths' or subject6='maths');
Saturday, August 7, 2021
 
Sanguine
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share