2

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

Improve this question
3
  • Why not normalize the design? Commented Jul 24, 2018 at 23:55
  • I'm a novice in terms of Database design. Can you elaborate or point me to an example/article? Thank you Commented Jul 25, 2018 at 1:41
  • Incidentally, I think you have a table of adults , and a table of children Commented Jul 25, 2018 at 5:54

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
This is a problem, but it isn't really the problem - although, I guess that's covered in the final paragraph
@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.
@Strawberry (and Alex). I'll look at improving the database design. The quoting solution to this specific issue did solve my immediate problem
1

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.