PHP SQL Query string concatenation with MySQLi prepared statement

Question

What is the correct syntax for concatenating strings and variables in PHP mysqli prepared statements?

The code I have right now is this:

        if ($test_stmt2=$mysqli->prepare("UPDATE friends SET friendslist=friendslist+','+? WHERE friendsuserid=? ")) { //Insert userid and frienduserid into friends table
               $test_stmt2->bind_param('si', $friendid, $userid); //Bind parameters (friend user id, your own user id)
               $test_stmt2->execute(); //Execute the prepared query
               echo "success";
        }

Basically, I want to get the string in the friendslist column, add a comma to it, then add another variable string ($friendid) to that cell in the friends table.

Your Common Sense · Accepted Answer · 2013-08-09 21:25:37Z

2

It seems you need SQL concatentation.

However, you shouldn't use it for this purpose. Your database structure is a deadly sin against Holy Normal Form.

Please, do not store comma-separated values in a database cell. Create a distinct table to store corresponding values.

userid | friendid
     1 |        2
     1 |        3
     1 |        5
     5 |        1
     5 |        2
     2 |       10

this way you will need a regular insert query like this:

INSERT INTO friendslist SET friendid = ?, userid = ?

It will save you A LOT of hair in the near future

edited Aug 9, 2013 at 21:25

answered Aug 9, 2013 at 21:13

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

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

4 Comments

user2415992 Over a year ago

yes, i agree that my design is pretty flawed. I want to do it the way you proposed, but how will i know how many columns of friendids to create if people choose different amounts of friends? will i need to constantly expand my table?

user2415992 Over a year ago

so a different table for every user? then a row for every user's friend?

Your Common Sense Over a year ago

just added the table.

Tomas Creemers Over a year ago

+1. Just for completeness: concatenation in MySQL is easiest using the CONCAT() function.

Collectives™ on Stack Overflow

PHP SQL Query string concatenation with MySQLi prepared statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related