MYSQLI NOT UPDATING NO ERRORS

[cutielou22]

It seems none of the mysqli is updating on this ONE page I have. It works fine on all other pages (even using the same tables) - just not this one. It is super weird. Could it be a page problem? Is that a thing?

I have tried many . . . many trial error options in more than 2 and 1/2 hours - absolutely none of them give any errors.

 

This is how most of them look or similar. There is only 3 different tables that I update on this page and none of them update or give errors. I am at a lost.

$stmt = $mysqli->prepare("UPDATE `solbattle` SET `pet_turn` = 'pet_one', `p2_turns`=p2_turns+1 WHERE (`player_one` = ? OR `player_two` = ?) AND `battle_status` = 'active' AND `winner` = '' AND `battleid` = ?");
$stmt->bind_param('sss', $userid, $userid, $battleid);
$stmt->execute();
$stmt->store_result();
$stmt->close();

 

[requinix]

Check whether you have your variables set up correctly.

[cutielou22]

Yes, my variables are correct.

The thing I really don't get is that the exact same snippet of code is working on another page, but not this page. What could cause that?

I even bothered checking the permissions of the page.

Edited November 24, 2018 by cutielou22

[requinix]

Build the query as a string

"UPDATE `solbattle` SET `pet_turn` = 'pet_one', `p2_turns`=p2_turns+1 WHERE (`player_one` = $userid OR `player_two` = $userid) AND `battle_status` = 'active' AND `winner` = '' AND `battleid` = $battleid"

echo/log the query somewhere, verify it is correct, and try to run it yourself.

[Barand]

Always put this line  in your code before your connections to mysql

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

At the moment you have no error checking code

[cutielou22]

@barand Tried that already. No errors.

@requinix I thought I tried this once, but tried again and got a error. Then looked up the error and it ended up me adding quotes to remove the error.  . . . . though database still not updating. Same results. I know this is an extremely odd problem.

[requinix]

26 minutes ago, cutielou22 said:

I thought I tried this once, but tried again and got a error. Then looked up the error and it ended up me adding quotes to remove the error.  . . . . though database still not updating. Same results. I know this is an extremely odd problem.

What quotes?

[cutielou22]

haha I realize you did not use quotes "" but used the single quote. Using the single quotes for the strings I got a error, but making it into quotes with singles quotes made the error go away. (Can't remember exactly how I did it to show you - google helped me.) It was still not updating the database though. Just got rid of the error.

[cutielou22]

I just fixed it! I knew it was going to be a tiny problem. Thank you guys for helping me.

How I Fixed It: I been putting the $battleid in my url so I can see it. I just assumed as long as it was showing it would be the right one. Then just a little bit ago I did another test and noticed that the $battleid should be higher than that. So I have the correct one showing on another page - so I checked to see if it matched or if this may be the error I am looking for! It was the error I was looking for. The problem was I was grabbing the wrong $battleid (which did not need to be updated so it wasn't - makes sense right?!). Man, I feel stupid and so relieved at the same time.

So ALWAYS do a double triple check for your SELECT mysql. x)

Thanks guys for your time though. I really do appreciate it.

Edited November 27, 2018 by cutielou22

[gizmola]

This might help with some of the confusing quoting you are doing.

When you specify a table or column name in mysql, you have to use backtics:

"UPDATE `solbattle` SET `pet_turn` =

But do you really?  MySQL supports this in case there is some ambiguity and you need to make sure that MySQL knows this is a table or column name and not a MySQL keyword.  However, with few exceptions, your table and column names won't conflict with MySQL.  In this example from your code, there is no need for using backtics and cluttering up your code.  I try to never use a bunch of characters and syntax if I don't have to, unless there's some big win to doing so.  This will work just as well:

$stmt = $mysqli->prepare(
  "UPDATE solbattle 
  SET pet_turn = 'pet_one', 
      p2_turns = p2_turns + 1 
  WHERE (player_one = ? OR player_two = ?) 
      AND battle_status = 'active' 
      AND winner = '' 
      AND battleid = ?"
);

$stmt->bind_param('sss', $userid, $userid, $battleid);
$stmt->execute();
$stmt->store_result();
$stmt->close();
 

 

Next comes, the question of single quotes.  When do you need them? 

Single quotes, delimit a string constant.  So that could be an interpolated PHP string variable (where email = '$email'), or constants like the ones you are using (pet_turn = 'pet_one'). 

You don't need nor want quotes if it's a numeric constant like "WHERE user_id = 1".

You also don't need nor want them if you are using prepared statements and bind parameters, as you are in your example.    It doesn't matter if the column is a string, numeric or date type.

With that said, your code may work but there is a subtle error you are making with your bind_param statement.  You are passing in 3 integers ($userid, $userid, $battleid)  but you are specifying that you are passing strings.   Your bind_param statement should be:

$stmt->bind_param('iii', $userid, $userid, $battleid);