Issues using UPDATE statement in MySQL

Question

I'm trying to update a column using PHP and MySQL.

Basically I have a database of adults and a database of children. When I child is added I want to add the ID(s) of the children to their respective adults.

When I child is added I grab the ID for that INSERT (I already have the ID of the adult).

If no children exist for the adult I simply add the ID of the child (this is always the case for the first run). The issue I have is when I add a second child for the same adult. The "Kids" column for the adult just doesn't update. I'm splitting the child ID's with a #.

It works perfectly the first time but just fails to update the table every other time.

The code I have is:

$sqlstatment = "INSERT INTO attendees (first, last, dob, access) VALUES ('$first','$last','$dob','$parent')";
$result = $link->query( $sqlstatment );

if ( $result ) {
            $kid_id = $link->insert_id;
            if ( $profile[ 'kids' ] === "" ) {
                $kids = $kid_id;
            } else {
                $kids = $profile[ 'kids' ]. "#" . $kid_id;
            }

            $updatesql = "UPDATE access SET kids = $kids WHERE id = $parent";
            $updaccess = $link->query( $updatesql );
            if ( $updaccess ) { 
                outputToConsole("Adult update success");
            } else {
                outputToConsole("Adult update failed");
            }
}

Table format

I'm a novice in terms of Database design. Can you elaborate or point me to an example/article? Thank you — Dan, Jul 25 '18 at 01:41
Incidentally, I think you have a *table* of adults , and a *table* of children — Strawberry, Jul 25 '18 at 05:54

Alex · Accepted Answer · 2018-07-24T23:50:07.097

The problem is that you're not quoting your values in your SQL queries. Consider the two resultant queries:

UPDATE access SET kids = 1 WHERE id = 4

UPDATE access SET kids = 1#6 WHERE id = 4

In the first query, 1 is treated like a number, so that's fine. In the second, 1#6 is intended to be a string, but without quotes (") around it, it's not treated like one. You'll get a syntax error of some sort (incidentally, you can use mysqli_error() to get an error message which will give you more info).

You need quotes around the values, like this:

UPDATE access SET kids = '1' WHERE id = '4'

UPDATE access SET kids = '1#6' WHERE id = '4'

This is EXTREMELY IMPORTANT as otherwise your application is open to SQL injection attacks, which, in this case, would allow someone to arbitrarily change your whole database query. Google "SQL Injection" and also look at the PHP function mysqli_real_escape_string for a go-to function to make sure you're protected against this kind of attack.

Another note: You should remember that the kids field only has space for 255 characters. As your attendee row IDs get larger, you may find your kids value in the update query is truncated, and inadvertently a given access row is assigned an erroneous attendees entry, and others are lost completely. To solve this, look into using a relational/many-to-many table, something like access_to_attendees.

Their query is already inside double quotes `"UPDATE access SET kids = $kids WHERE id = $parent"`. The quotes in your answer should be single quotes instead. — Funk Forty Niner, Jul 24 '18 at 23:47
This *is* a problem, but it isn't really *the* problem - although, I guess that's covered in the final paragraph — Strawberry, Jul 25 '18 at 00:53
@Strawberry I agree, it's a very funky way of storing an association... but I figured the bigger fish to fry here was the apparent lack of understanding when it comes to quoting string values. A bad design choice might make maintenance hard and impose needless limitations... but the quoting/SQLI issue is way more fundamental and potentially catastrophic, IMO. — Alex, Jul 25 '18 at 01:11
@Strawberry (and Alex). I'll look at improving the database design. The quoting solution to this specific issue did solve my immediate problem — Dan, Jul 25 '18 at 01:43

score 1 · Answer 2 · answered Jul 25 '18 at 00:17

My personal approach to dealing with issues like this is to leverage the existing class in it's entirety rather than mixing different methodologies. This will ensure issues (such as SQL injection as mentioned) and errors, class method(s) results are handled cleanly resulting in a much more fault tolerant code and a smoother debug process etc...

$query = "INSERT INTO attendees (first, last, dob, access) VALUES (:first,:last,:dob,:parent)";
try {
    $statement = $link->prepare($query);
    $statement->bindValue(":first",$first,PDO::PARAM_STR);
    $statement->bindValue(":last",$last,PDO::PARAM_STR);
    $statement->bindValue(":dob",$dob,PDO::PARAM_STR);
    $statement->bindValue(":parent",$parent,PDO::PARAM_STR);
    if ($statement->execute()) {
        $kid_id = $statement->insert_id();
        $kids = ($profile[ 'kids' ] === "")?  $kid_id : $profile['kids']."#".$kid_id; 
        $updatequery = "UPDATE access SET kids = :kids WHERE id = :parent";
        try { 
            $updatestatement = $link->prepare($updatequery);
            $updatestatement->bindValue(":kids",$kids,PDO::PARAM_STR);
            $updatestatement->bindValue(":parent",$parent,PDO::PARAM_STR);
            if ($updatestatement->execute())
                outputToConsole("Adult update success");
        } catch (PDOException $e) {
            outputToConsole("Adult update failed");
        }
    };
} catch (PDOException $e) {
    outputToConsole($e->getMessage());
}

Issues using UPDATE statement in MySQL

2 Answers2