problem in displaying a question with concat, foreach and explode in php

Question

I have a table in mysql. The name of this table is questiontbl. The questions are stored in this table. A question is displayed randomly to the user. This question should not be repeated. So the user ID is stored in a column called uid. I have some problems:

First, the question is displayed multiple times instead of once.

Second, the uid field update does not work correctly. For example, if the user ID is 40 and must be added to the field once, it will be added three times.

Please review my code and help me solve its problems.

my table:

id	questiontitle	uid
5	questionA	19,40,15,17,
6	questionB	19,

Note: The user ID should not be stored repeatedly in the uid column.

My code:

<?php
$uid2=$_SESSION['id'];
$get1 = "SELECT * FROM questiontbl ORDER BY RAND() LIMIT 1";
$get2=mysqli_query($conn,$get1);
$get3=mysqli_fetch_assoc($get2);
$questionid=$get3['id'];
$now=$get3['uid'];
$now2=explode(',',$now);
foreach ($now2 as $data) 
{
    if (strpos($data,$uid2) !== FALSE )
    {
        header("Location: startnew.php");
    }
    else
    {                               
        $final_show=$get3['questiontitle'];
        echo $final_show;                               
        $update_idq = "UPDATE questiontbl SET uid=concat(uid,'$uid2,') WHERE id='$questionid' LIMIT1";
        mysqli_query($conn, $update_idq);
    }           
}
?>

You should read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Nigel Ren, Apr 13 '21 at 13:11
It might be interesting to see if you can use `find_in_set()` to limit the select to only find questions that this user hasn't already answered. — Nigel Ren, Apr 13 '21 at 13:26

Nick · Accepted Answer · 2021-04-13T13:28:03.870

You are getting repetition because you are iterating over all the values in the uid column to see if you have a match with the session id value, and each time the values don't match you are updating your table. Instead, after explodeing the value, use in_array to see if the session id value exists in the uid column:

$uid2=$_SESSION['id'];
$get1 = "SELECT * FROM questiontbl ORDER BY RAND() LIMIT 1";
$get2=mysqli_query($conn,$get1);
$get3=mysqli_fetch_assoc($get2);
$questionid=$get3['id'];
$now=$get3['uid'];
$now2=explode(',',$now);
if (in_array($uid2, $now2)) {
    header("Location: startnew.php");
}
else {                               
    $final_show=$get3['questiontitle'];
    echo $final_show;                               
    $update_idq = "UPDATE questiontbl SET uid=concat(uid,",$uid2") WHERE id='$questionid' LIMIT 1";
    mysqli_query($conn, $update_idq);
}

Note also that variable substitution only occurs in double quoted strings (and you need the comma before the id value) so you need to change

SET uid=concat(uid,'$uid2,')

to

SET uid=concat(uid,",$uid2")

Finally you have a typo, LIMIT1 should be LIMIT 1 although it ought to be unnecessary for that query.

problem in displaying a question with concat, foreach and explode in php

1 Answers1