Php: MySql append to existing row

Question

I'm trying to append to the user Jake's 'projects' by doing so

UPDATE login SET projects='CONCAT(projects, '$id,')' WHERE username='$name'

But it does nothing. Any ideas what a good MySql code is to append to a specific row's column?

Answer 1

Foreword. Please go over my answer in its entirety and not just the code.

• Consult "Footnotes" for a tested/working example.

---

Besides the typo for CONACT, you need to remove the quotes and the comma.

You are passing a literal string when using quotes for the function.

UPDATE login SET projects='CONACT(projects, '$id,')' WHERE username='$name'
                          ^                     ^  ^

• Checking for errors in your query, would have produced something similar to the following with your present usage: (Using "1" and "John" as an example).

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,')' WHERE username='John'' at line 2

Modify it to read as:

UPDATE login SET projects = CONCAT(projects, '$id') WHERE username='$name'

or $id without the quotes, if it is indeed an integer.

UPDATE login SET projects = CONCAT(projects, $id) WHERE username='$name'

Depending on how you're using your query through a connection, would resemble something like this and as a MySQLi method:

$query = mysqli_query($connection, 
                "UPDATE login 
                 SET projects = CONCAT(projects, '$id') 
                 WHERE username='$name'")

                 or die(mysqli_error($connection));

While making sure that both variables are indeed set and populating correctly.

If coming from an HTML form, make sure there are name attributes for them and that the method is correct.

I.e.: <input type="text" name="username">

If from a form: $name = $_POST['username'];

Plus, since we do not know which MySQL API you are using to connect with, or if you've successfully established a connection to your database, that is known to you only, so if you are, great.

If not, then that could mean there are errors somewhere but you're probably not checking for them.

Sidenote: When you tell us "It doesn't work", that doesn't help anyone.

We need to know the exact error message, if and when you will be checking for them.

Here are a few links you can look into and use the error checking method according to your MySQL API.

• http://php.net/manual/en/function.mysql-error.php - MySQL
• http://php.net/manual/en/mysqli.error.php - MySQLi
• http://php.net/manual/en/pdo.error-handling.php - PDO

Error reporting: http://php.net/manual/en/function.error-reporting.php

See also this link to choose your MySQL API:

• https://php.net/mysqlinfo.api.choosing

---

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

---

I believe that I have given you enough information. Try different methods; you won't break anything.

Also see these links on CONCAT

• http://www.mysqltutorial.org/sql-concat-in-mysql.aspx
• https://dev.mysql.com/doc/refman/5.0/en/string-functions.html

---

Footnotes: (tested code)

The following worked flawlessly, as suggested above and using mysqli_affected_rows().

<?php 
$DB_HOST = 'xxx'; // Replace
$DB_USER = 'xxx'; // with
$DB_PASS = 'xxx'; // your
$DB_NAME = 'xxx'; // own

$db = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($db->connect_errno > 0) {
  die('Connection failed [' . $db->connect_error . ']');
}

$id = 1; // wrap in quotes if it's a string, like "string_1".
$name = "John"; // an example only

$query = mysqli_query($db, 
                "UPDATE login 
                 SET projects = CONCAT(projects, '$id') 
                 WHERE username='$name'")

                 or die(mysqli_error($db));

if(mysqli_affected_rows($db)){
   echo "Successfully updated the database.";
}

else{
   echo "Sorry, check your logs.";
}

---

Sidenote:

• Your present code is open to SQL injection. Use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

Answer 2

Your sql query is full of typo-errors... Change your query to:

"UPDATE login SET projects=CONCAT(projects, $id) WHERE username='".$name."'";