[SOLVED] Is this correct mysqli coding?

[GeorgeWade]

My hosting provider says the following code is bad mysql coding. They say check with the developer. I am the developer although have not touched PHP in a few years. (I like PHP but work in an asp.net shop.) Please advise. (Userid/password/dbname are dummies as shown here - the code successfully does a SELECT and returns values but while it shows 1 row affected in the UPDATE the table never changes according to my CPanel/PHPAdmin screen). NOTE that the userid/password I currently use in the connect is the same used in CPanel to login and originally create the database. I have also tried it with a different userid given all permissions.

 

$link = mysqli_connect('localhost', 'userid','password','dbname');
$query = "SELECT c.gameID, c.date, c.time, c.chatStream from chatTbl c WHERE c.gameID = " . $inGameID ; //gameID is bigint
if ($result = mysqli_query($link, $query)) {
   $numChat_rows = mysqli_num_rows($result); 
}
else $numChat_rows = 0;
$row = mysqli_fetch_assoc($result);
$fndID          = $row["ID"];
$fndGameID  = $row["gameID"];
//AND $fndID and $fndGameID successfully get values which can be displayed.

 

BUT the following only appears to work while never actually changing the database table: (ID is bigint autonumbered and record 1 exists)

 

$MySQL = "UPDATE chatTbl set mydate = '" . $inDate . "' WHERE ID = 1";
if ($result = mysqli_query($link, $query)) {
   if (mysqli_affected_rows($link) > 0) {
      $numInserted_rows = 1;
   }
   else if (mysqli_affected_rows($link) == 0)
            $numInserted_rows = 0;
         else $numInserted_rows = -1;
}
else $numInserted_rows = 0;

 

I also get 1 record affected when I execute the following:

$numInserted_rows = mysqli_affected_rows($link);

 

 

[GeorgeWade]

Updated by requester: Version information.

MySQL v5.0.51a-community PHP v5.2.5

[fenway]

I'm not sure I understand.

[GeorgeWade]

Is the code shown for accessing a MySQL database / UPDATING the record, including any required code such as the connection correct?

 

I know the SELECT code is right because it returns data. But while the UPDATE code reports 1 record affected using the

mysqli_affected_rows($link)

code but the data itself does not get changed (as shown from a browse in PHPadmin).

 

So, I think the code is correct but my hosting admins tell me MySQL is setup/configured right and the code must be wrong. Since I am very rusty with PHP at this time I'm wanting someone who codes against MySQL regularly to either confirm the code is correct and complete to UPDATE a table or to point out where the code is wrong or missing something.

[PFMaBiSmAd]

See anything wrong in the following code -

 

$MySQL = "UPDATE chatTbl set mydate = '" . $inDate . "' WHERE ID = 1";
if ($result = mysqli_query($link, $query)) 

 

Hint: $MySQL and $query are not the same variable.

[GeorgeWade]

Thanks, PFMaBiSmAd. As often the case, something that should be obvious. In my comfort zone of asp.net/c# I always set the query to the MySQL variable whereas out of that comfort zone using PHP I copied examples ($query) and was completely blind as to the variable disconnect.

 

Not really sure why mysqli_affected_rows($link) inaccurately showed 1 row affected (which caused me to believe code might be right) but Microsoft messages are no more helpful. Thanks again.

 

Changed code and it worked of course.