PHP/MySQL - WHERE Clause - Won't Update

[Gingechilla]

Hi,

 

Im having a problem and I can't seem to figure it out or find anything on the net.

 

If I use the following code the script successfully updates every row in the table:

mysqli_query($con,"UPDATE Ads SET Ads_LocalArea='Stroud'");

However if I try updating the table using the WHERE clause in any of the combinations below nothing happens.

mysqli_query($con,"UPDATE Ads SET Ads_LocalArea='Stroud' WHERE Ads_ID=$DBROWID");

----------------------------------------------------------------------

 

My Script:

mysqli_query($con,"INSERT INTO Ads (Ads_ID, Ads_AID, Ads_Title)
VALUES ('', '$Polished_AdRef', '$Polished_AdTitle')");

$DBROWID = mysqli_insert_id($con);

mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID=$DBROWID");

// TRIED THESE TOO
// mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID='$DBROWID'");
// mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID='5'");

Does any one know where I am going wrong?

[Ch0cu3r]

The code you posted looks fine, it should insert a new record into the Ads table and then update the Ads_AID field value to stroud for the newly inserted record. I do have question why you are doing this?

 

Because you have not add error checking to your queries you wont be able to understand why the code is failing.

 

When running queries you should check it executed without returning errors. The mysqli_query function returns TRUE on success (note this does not indicate the query altered the data in the database), and FALSE if MySQL returned an error (indicates there is a problem with the query). When mysqli_query returns FALSE you can get the error message using mysqli_error.

 

When running queries which modify the data held in a table you need to use a function called mysqli_affected_rows to check that it actually did anything. This functions returns the number of rows affected by the query. This only applies to INSERT, UPDATE, REPLACE and DELETE queries.

 

Your code should look something like this

$qry_inserted_Ad = mysqli_query($con,"INSERT INTO Ads (Ads_ID, Ads_AID, Ads_Title) VALUES ('', '$Polished_AdRef', '$Polished_AdTitle')");

// checked query has executed (this DOES NOT indicate the query modified the data in the table)
if($qry_inserted_Ad)
{
    // check that the query did modify the table - ie: added a new row
    if(mysqli_affected_rows($con) > 0))
    {
        // get the newly inserted records id - mysqli_insert_id() returns zero on failure
        $new_Ad_id = mysqli_insert_id($con);
    }
}
// insert query return FALSE most likely due to a error in the query
else
{
    // trigger an error, returning the mysql error
    trigger_error('Adding new ad to the Ads table has failed: ' . mysqli_error($con), E_USER_WARNING);
}

// check that we have retrived the new rows id
if(isset($new_Ad_id) && $new_Ad_id != 0)
{
    $qry_updated_Ad = mysqli_query($con,"UPDATE Ads SET Ads_AID='Stroud' WHERE Ads_ID=$new_Ad_id");
    // checked query has executed (this DOES NOT indicate the query modified the data in the table)
    if($qry_updated_Ad)
    {
        // check that the query did modify the table - ie: updated a row
        if(mysqli_affected_rows($con) > 0))
        {
            echo "Succesfully updated Ad #$new_Ad_id!";
        }
        else
        {
            echo "Did not update Ad #$new_Ad_id!";
        }
    }
    // insert query return FALSE most likely due to a error in the query
    else
    {
        // trigger an error, returning the mysql error
        trigger_error("Updating ad #$new_Ad_id in Ads table failed: " . mysqli_error($con), E_USER_WARNING);
    }
}
else
{
    echo "Unable to retrieve the inserted Ad id!";
}

[Gingechilla]

Hi,

 

Thank you for your help. I will add that in to my code.

 

For some reason and I'm not sure why... I changed the connection user to root and it worked. I then granted all possible permissions to the user I was previously using but nothing. So I went back and added a brand new user with the some privileges and it worked. Really not sure what went wrong but it's working now. (XAMMP).

 

Thank you.

[Ch0cu3r]

 

 

 I then granted all possible permissions to the user I was previously using but nothing.

May you did not call  FLUSH_PRIVILEGES;  after modifying the permissions? If can revert back to your old mysql user you are having issues with and run my code it should tell you what was wrong.

 

Before doing so make sure you have error reporting enabled, add the following at top of your script (after the opening <?php  tag)

ini_set('display_errors', 1);
error_reporting(E_ALL);