Boostr



0 votes
12 views
mysqli_affected_rows is a strange behavior. It won't return 1 if fname is updated, probably caused by 2 times of mysqli_query. I can get 1 if another field is updated.

mysqli_query($mysqli, "UPDATE persons SET fname='$fname' WHERE id='$id'");
mysqli_query($mysqli, "UPDATE tools SET knife='$knife', other='$other' WHERE person_id='$id'");

if (mysqli_affected_rows($mysqli) > 1){
        echo json_encode(array(
        'id' => (int)$id,
        'fname' => $fname,
        'tools' => array(
            'knife' => $knife,
            'other' => $other
        )
    ));
}
So how to return updated row in my situation now?
asked Dec 30, 2017 in PHP by Amrendra | 12 views



1 Answer

0 votes
Best answer
Your correct in your assumption that as the second query has been run, this will give a different result, so the best thing to do is restructure your code slightly to...

mysqli_query($mysqli, "UPDATE persons SET fname='$fname' WHERE id='$id'");

if (mysqli_affected_rows($mysqli) >= 1){
    mysqli_query($mysqli, "UPDATE tools SET knife='$knife', other='$other' WHERE person_id='$id'");
    echo json_encode(array(
        'id' => (int)$id,
        'fname' => $fname,
        'tools' => array(
            'knife' => $knife,
            'other' => $other
        )
    ));
}
(Just updated it to say >=1 as this would - I assume be OK if just 1 row was updated)

Although I would also recommend looking into prepared statements and binding the values rather than directly putting values into SQL statements.

You should also check at each point if your query execution has failed... Something like

if ( !mysqli_query($mysqli, "UPDATE tools SET knife='$knife', other='$other' WHERE person_id='$id'"))  {
   die ( "Error-".mysqli_error($mysqli));
}
Or if you can update anything or nothing, then...

mysqli_query($mysqli, "UPDATE persons SET fname='$fname' WHERE id='$id'");
$rowsUpdated = mysqli_affected_rows($mysqli);

mysqli_query($mysqli, "UPDATE tools SET knife='$knife', other='$other' WHERE person_id='$id'");
$rowsUpdated += mysqli_affected_rows($mysqli);
if ($rowsUpdated >= 1){
    echo json_encode(array(
        'id' => (int)$id,
        'fname' => $fname,
        'tools' => array(
            'knife' => $knife,
            'other' => $other
        )
    ));
}
Which just adds up the totals rows updated across both SQL statements.
answered Dec 30, 2017 by Amrendra

Related Questions



Welcome to Knowledge Boostr, where you can ask questions and receive answers from other members of the community.
This is a collaboratively edited question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

http://boostr.in Q&A for professional and enthusiast programmers http://boostr.in Q&A for professional and enthusiast programmers http://boostr.in Q&A for professional and enthusiast programmers http://boostr.in Q&A for professional and enthusiast programmers


23,494 questions
40,169 answers
12 comments
23,610 users