CODEWITHSUNDEEP
phpmysqlmysqli
Jayke
Jayke

Reputation: 9

Query Returns Success But no Rows Updated

I'm currently having an issue where my query returns a success however there is no actual update that occurs when I check my SQL database. Oddly enough, when I copy the same exact query into phpMyAdmin, a response is successfully returned and the query works just fine, the rows are updated. (Note: I'm well aware of the high risk of SQL injection, however mysqli_escape_string isn't working for some reason so I'll worry about that when I go into the production stage.)

script.php

$fave = json_decode($_POST['af']);
$unfave = json_decode($_POST['uf']);
$fave = "'".implode("','", $fave)."'";
$unfave = "'".implode("','", $unfave)."'";
if ($fave !== "''"){
    $fq     = "UPDATE post SET fave='1' WHERE 'an_id' IN ($fave) AND bid='$bizusr' AND fave='0'";
    $r_fq   = mysqli_query($GLOBALS["___mysqli_ston"], $fq);
    $ar_fq  = mysqli_affected_rows($GLOBALS["___mysqli_ston"]);   
} else {
    $r_fq = 1;
    $ar_fq = 0;
}
if ($unfave !== "''"){
    $ufq    = "UPDATE post SET fave='0' WHERE 'an_id' IN ($unfave) AND bid='$bizusr' AND fave='1'";
    $r_ufq  = mysqli_query($GLOBALS["___mysqli_ston"], $ufq);
    $ar_ufq = mysqli_affected_rows($GLOBALS["___mysqli_ston"]);   
} else {
    $r_ufq = 1;
    $ar_ufq = 0;
}
if ($r_fq && $r_ufq){
    $output = json_encode(array('type'=>'error', 'text' => "Favourites have been updated successfully. You've added $ar_fq favorites and removed $ar_ufq favorites." ));
    die($output);
}
if (!$r_fq && $r_ufq){
    $output = json_encode(array('type'=>'error', 'text' => "We've successfully favorited $ar_fq links, however there was an issue in unfavoriting some links, try refreshing." ));
    die($output);
}
if ($r_fq && !$r_ufq){
    $output = json_encode(array('type'=>'error', 'text' => "We've successfully unfavorited $ar_ufq links, however there was an issue in favoriting some links, try refreshing." ));
    die($output);
}
if (!$r_fq && !$r_ufq){
    $output = json_encode(array('type'=>'error', 'text' => "There was an error in updating your favorited links." ));
    die($output);
}
//        $un = mysqli_prepare($GLOBALS["___mysqli_ston"], "UPDATE analytics SET fave='0' WHERE an_id IN (?) AND bid= ? AND fave='1'");
//        $fa = mysqli_prepare($GLOBALS["___mysqli_ston"], "UPDATE analytics SET fave='1' WHERE an_id IN (?) AND bid= ? AND fave='0'");
//        mysqli_stmt_bind_param($un, 'ss', $unfave, $blockject);
//        $a = mysqli_stmt_execute($un);
//        mysqli_stmt_close($un);
//        mysqli_stmt_bind_param($fa, 'ss', $fave, $blockject);
//        $b = mysqli_stmt_execute($fa);
//        mysqli_stmt_close($fa);

The variables $fave and $unfave would return values like so: 'abcd123','dcba321','hello123', which would make the query look like so:

UPDATE post SET fave='0' WHERE 'an_id' IN ('abcd123','dcba321','hello123') AND bid='$bizusr' AND fave='1';

Now, entering the query into phpMyAdmin works just fine, but when doing it through php, the response returns a success however no rows are actually updated, so I'm not sure what is going on as my php error.log is as clean as a whistle.

Also, if you're wondering what my require_once connection.php file looks like which connects me to the database, it is the following:

$link = ($GLOBALS["___mysqli_ston"] = mysqli_connect(DB_HOST,  DB_USER,  DB_PASSWORD));
if(!$link) {
    die('Failed to connect to server: ' . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)));
}

//Select database
$db = ((bool)mysqli_query($GLOBALS["___mysqli_ston"], "USE " . constant('DB_DATABASE')));
if(!$db) {
    die("Unable to select database");
}

Upvotes: 0

Views: 304

Answers (1)

Jayke
Jayke

Reputation: 9

Silly me, I'm not sure why it was returning a successful query, however wrapping the column id an_id in single quotes was the issue

Upvotes: 0

Related Questions