commands out of sync

[alexandre]

Fatal error: Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now in C:\xampp\htdocs\container\donation-clash\donation-clash.php:93 Stack trace: #0 C:\xampp\htdocs\container\donation-clash\donation-clash.php(93): mysqli->prepare('UPDATE accounts...') #1 {main} thrown in C:\xampp\htdocs\container\donation-clash\donation-clash.php on line 93

 

here is the part of the code. 

$stmt = $con->prepare("UPDATE accounts SET userbalance = ?, totaldonationdc = ?, userlevel = ?, userexperience = ?, totaleventjoined = ? WHERE id = ?");

//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
$stmt->bind_param('iiidii', $userbalance, $totaldonationdc, $userlevel, $userexperience, $totaleventjoined, $_SESSION['id']);
 $stmt->execute();

i would like to know if there is a syntax error in this code or something. or if i can simply do this , anyways any help will be appreciated.

[Barand]

Ah! The delights of mysqli.

https://dev.mysql.com/doc/refman/8.0/en/commands-out-of-sync.html

Do yourself  favour and change to PDO.

[alexandre]

is there particular reason why pdo is so much better ? and as i see your opinion about mysqli i wonder if there are not some things or bugs making pdo better.

[Barand]

PDO is more streamlined

• mysqli has result objects and statement objects depending on whether you used query() or prepare(). The methods for handling the results of these two objects are completely different. This mean two confusing sets of methods to learn.
• On the other hand, PDO produces objects with exactly the same methods to process the results of query() or prepare(), and usage of prepare() is itself much simpler.
• Some mysqli methods are only available depending on your implementation of MySql.

PDO works with multiple varieties of DBMS (mysql, postgres etc). The SQL dialects may be different but your php code  remains the same if you change.

Having used the old mysql_* for years I originally changed to mysqli (how different could it be?) After a long while I decided I had to learn PDO in order to support others who were using it. I really wished I'd switched to it originally instead of mysqli.

[alexandre]

i understand i will dive into pdo then. but for this command out of sync i am clueless since i didnt use mysqli_use_result or store_result maybe i should have in between my queries but now i will change to pdo . thank you for your answers.

[mac_gyver]

41 minutes ago, alexandre said:

command out of sync

the most common cause is executing a query that returns a result set but not fetching all the data from that result set. this ties up the connection so you cannot run another query. therefore, if a query returns a result set, simply fetch all the data from the query in to an appropriately named  php variable, freeing up the connection for use by other queries, then test/use that variable throughout the rest of the code.

[alexandre]

in fact i am pretty sure that i am fetching everything at least everything before where the script breaks. if i used a store_result() before but removed it do the data could still be stored in memory or it goes when you remove the store_result()?

[mac_gyver]

or you could just convert to the much simpler, better designed, and more modern pdo extension. 

in your previous thread, i posted an example showing what your SELECT query would look like using the pdo extension. here's what the UPDATE query in this thread would look like -

 

$stmt = $pdo->prepare("UPDATE accounts SET userbalance = ?, totaldonationdc = ?, userlevel = ?, userexperience = ?, totaleventjoined = ? WHERE id = ?");
$stmt->execute([ $userbalance, $totaldonationdc, $userlevel, $userexperience, $totaleventjoined, $_SESSION['id'] ]);

after you have made a connection using the pdo extension, converting existing msyqli based code to pdo mainly involves removing statements, copy/pasting the list of variables being bound into an array in the ->execute([...]) call, changing fetch statements (see pdo's fetch(), fetchAll() and sometimes fetchColumn()), and using differently spelled methods for things like last insert id and affected rows.

[alexandre]

amazing thank you very much for the example of update there nothing better than to see to learn for me so i appreciate that.

i will now convert to pdo. 

i would have a last question . with pdo will i still be able to make a ranking like that with a query or the way of  doing so is changing a bit? 

 $stmt = 'SELECT DISTINCT participationid, usernames, donationamount, totaldonated, sum(donationamount) as totaldonated  FROM donationclashdetails GROUP BY participationid ORDER BY totaldonated DESC';
     $result = mysqli_query($con, $stmt);
     if ((mysqli_num_rows($result) > 0) && mysqli_num_rows($result) < 1000) {
         while ($ROW = mysqli_fetch_assoc($result)) {
             echo $ROW['participationid']. $ROW['usernames']. $ROW['donationamount']. $ROW['totaldonated'] . ' - ' . '<br>';
					 }}

 

Edited September 8, 2022 by alexandre

[mac_gyver]

the database extension just provides the means by which your php program interfaces with the database server. it doesn't know or care what the query is doing or what the data means.