无法从php中添加id到mysql中的字段

Question

I am trying to add ids like array into mysql in a field called all_ids which has all the ids associated to the user.And when a new id is added it should update the array in mysql fiels and if the id already exists it should not do anything and echo "match found".I am using php GET to supply values of arraylist from android it is decoded to json.But I am unable to add all users in array to mysql.

I really appreciate any help .
Thanks in Advance.

GET code:

www.example.com\example?$id=[123,234,567,8910,11324,1]&sno=12 

PHP code:

<?php


$sno=$_GET['sno'];

$id=$_GET['id'];


if (isset($id))
{

    $query=mysql_query("SELECT  all_ids FROM  `user` where sno='$sno';");

    $gamerow = mysql_fetch_array($query);

    $playerlist = explode(",", $gamerow['all_ids']);

    if( empty( $playerlist ) )
    {
        //empty array
    }else{
        $obj = json_decode($id);

        foreach ($obj as $value) {

            if (in_array($value, $playerlist))
            {
                echo "Match found";
                mysql_query("UPDATE  user  SET all_ids=CONCAT_WS(',',all_ids, '$value') WHERE sno='$sno';");

            }
            else
            {
    mysql_query("INSERT INTO user( sno, all_ids ) VALUES ( $sno, $value);");

                echo "Match not found";

            }
        }
    }

}

?>

Answer 1

Your design is incorrect. You should have a separate table that holds the ids for the user. This table should have two columns, one for the id, and one for the user. You then insert to or delete from that other table in order to handle the user's ids.

And don't use mysql extension! Use mysqli or PDO. Mysql extension has been deprecated for a long time now, and has been advised against for at least three years for many reasons. I am giving you the example with mysqli. Remember to research how to connect to a database using mysqli instead of mysql. (It's not really very different.)

Getting the ids for a user (assuming that $sno and $id are integers):

$stmt = mysqli_prepare("SELECT id FROM `ids_table` where sno = ?");
mysqli_stmt_bind_param($stmt, "i", $sno);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id);

$playerlist = array();
while (mysqli_stmt_fetch($stmt)) {
    $playerlist[] = $id;
}

Adding id to a user:

$stmt = mysqli_prepare("INSERT INTO ids_table (id, sno) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "ii", $id, $sno);
mysqli_stmt_execute($stmt);

Deleting id from a user:

$stmt = mysqli_prepare("DELETE FROM ids_table WHERE id = ? AND sno = ?");
mysqli_stmt_bind_param($stmt, "ii", $id, $sno);
mysqli_stmt_execute($stmt);