Page 1 of 1
Multiple mysqli queries[solved]
Posted: Sun Nov 27, 2011 10:19 pm
by 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'");
Re: Multiple mysqli queries
Posted: Sun Nov 27, 2011 10:31 pm
by 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.
Re: Multiple mysqli queries
Posted: Sun Nov 27, 2011 11:58 pm
by 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.
Re: Multiple mysqli queries
Posted: Mon Nov 28, 2011 12:38 am
by MikeD
Xaleph wrote: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.
Re: Multiple mysqli queries
Posted: Mon Nov 28, 2011 1:26 am
by 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));
Re: Multiple mysqli queries
Posted: Mon Nov 28, 2011 1:52 am
by MikeD
Got it all fixed.
Using
Code: Select all
mysqli_next_result($db);
mysqli_next_result($db);
fixed it all up! Finally lol.
Re: Multiple mysqli queries[solved]
Posted: Mon Nov 28, 2011 4:59 pm
by 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.