CODEWITHSUNDEEP
phpmysqlmysqlisql-update
Dan
Dan

Reputation: 2344

Issues using UPDATE statement in MySQL

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

enter image description here

Upvotes: 2

Views: 54

Answers (2)

Vaughn Cox
Vaughn Cox

Reputation: 21

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());
}

Upvotes: 1

Alex
Alex

Reputation: 3079

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.

Upvotes: 2

Related Questions