Multiple mysqli queries[solved]

[MikeD]

Trying to do 3 queries into 3 different tables for players to get rid of their weapons/armor.

I noticed that it would only run the 1st query (inserting into armory) but not the other two. So I googled and came up with this, same problem though, still only running the 1st query.

Code: Select all

$db->multi_query(" Many SQL queries ; "); 
while ($db->next_result()) {;} 
$db->query("INSERT INTO `armory` (`itemname`,`level`,`type`,`iclass`,`speedadd`,`poweradd`,`meleeadd`,`magicadd`,`archadd`,`intadd`,`luckadd`,`healthadd`,`enchantslots`,`itemcost`)VALUES('$wepname','$wlvl','$type','$class','$speed','$power','$mel','$mage','$arch','$int','$luck','$health','$slots','$cost')"); // now executed!
$db->query("DELETE FROM `playeritems` WHERE `charname`='$warrior' AND `citemid`='$wepid'");
$db->query("UPDATE `characters` SET `gold`=`gold`+'$gold' WHERE `charname`='$warrior'");

[Zyviel]

I think the mysqli query function will only run one query because its designed to only run one query. I think it is that way to protect against sql injection exploits. I think you can just do mysql without the i if you want to run multiple queries in the same string.

[Xaleph]

couple of things going wrong here.

1. You are not really running a query.
2. You use quotes around integers/doubles
3. Messy code.

1. The thing is, you use an abstraction on top of SQL or MySQLi in this case, so in order to perform "more advanced" queries, I recommend running SQL directly instead of the PDO / abstraction layer.

2. Integers are non-characters in a way that means that integers ( or any number format for that matter ) can be ( and SHOULD be ) used as direct input. So '1' does not equal 1.

3. By that, really, i mean all the backticks you use, the ` quote. It doesnt do any real good, however it can mess up your query. The reason the admins ( PHPmyAdmin etc ) use it for compatibility issues. So if you are new, you can name your table VARCHAR and it would still work, because the backtick tells the driver to parse it as a string, long story.. Short story: stop using them.

[MikeD]

couple of things going wrong here.

1. You are not really running a query.
2. You use quotes around integers/doubles
3. Messy code.

1. The thing is, you use an abstraction on top of SQL or MySQLi in this case, so in order to perform "more advanced" queries, I recommend running SQL directly instead of the PDO / abstraction layer.

2. Integers are non-characters in a way that means that integers ( or any number format for that matter ) can be ( and SHOULD be ) used as direct input. So '1' does not equal 1.

3. By that, really, i mean all the backticks you use, the ` quote. It doesnt do any real good, however it can mess up your query. The reason the admins ( PHPmyAdmin etc ) use it for compatibility issues. So if you are new, you can name your table VARCHAR and it would still work, because the backtick tells the driver to parse it as a string, long story.. Short story: stop using them.

1) I can't run SQL, everything I've coded so far is in mySQLi and it's much safer from what I hear. Don't want to have a lapse in security for this one thing.

2) I didn't know that, most things I've tried never worked without the single quotes. Will try that again, thanks.

3) I use backticks now because people advised me to do so. I used to do it without them, but ran into problems time to time, so i've learned to use them now.

[MikeD]

Well, got it working for the most part.

Everything works, the item goes to the armory, gets deleted from the playeritems and the character get's gold.

However, everytime I do it, I get this.

Commands out of sync; you can't run this command now

like I said everything still works though.

Code: Select all

$query  = "INSERT INTO `armory` (`itemname`,`level`,`type`,`iclass`,`speedadd`,`poweradd`,`meleeadd`,`magicadd`,`archadd`,`intadd`,`luckadd`,`healthadd`,`enchantslots`,`itemcost`)VALUES('$wepname','$wlvl','$type','$class','$speed','$power','$mel','$mage','$arch','$int','$luck','$health','$slots','$cost');";
$query .= "DELETE FROM `playeritems` WHERE `cid`=$cid AND `citemid`=$wepid;";
$query .= "UPDATE `characters` SET `gold`=`gold`+'$gold' WHERE `randcid`='$warrior'";
                                                                                      
mysqli_multi_query ($db, $query ) or die("MySQL Error: " . mysqli_error($db));

[MikeD]

Got it all fixed.

Using

Code: Select all

mysqli_next_result($db);
    mysqli_next_result($db);

fixed it all up! Finally lol.

[Xaleph]

Good for you, but coming back on the backticks, it`s a workaround. Honestly, there`s no real need for it to be using it. It skips the problem rather then solving it. It has something to do with your table names probably, but I really recommend to not use them. It really is like I said, it doesn`t really help, however, it can create unwanted ( and hard to debug ) problems.

On the direct SQL, it`s possible to do in MySQLi but you are right not to do so. Using the objects for it is the right thing to do. Also, truth be told, i never really looked at your code in a way that could help you, however, You cannot delete and update and insert in 1 query. It can be an advanced SELECT query or UPDATE or INSERT however, you cannot do both.