Explode an array and return as X times n p

[SimonIoa]

Hello, i have a problem. I want to do two things

First, explode an array to retrieve the ids from a row on the table
e.g. 1,5,23 and then use a variable to call a Mysql statement.

Here is my code

public function Show($times)
{
    $q=mysqli_query($this->db,"SELECT to_uid_fk FROM table WHERE status='1' ") or die(mysqli_error($this->db));
    $row1=mysqli_fetch_array($q,MYSQLI_ASSOC);
    if(strlen($row1['to_uid_fk'])>1)
    {
        $s = explode(",", $row1['to_uid_fk']);
        foreach($s as $a)
        {
            $query=mysqli_query($this->db,"SELECT row1,row2,to_uid_fk,times FROM table WHERE to_uid_fk='$a' ORDER BY row1 DESC ")or die(mysqli_error($this->db));
            while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
            {
            $times=$row['times'];   
            $data[]=$row;

            }
            return $data;
        }
    }
}

and then i want to call this function on another file to run a php script as much times as the row times is set. The row times on the table stores an integer number of course.

here is the code

$Show=$db->Show($times); //dont mind that i know its wrong
if($Show)
{
    $count=0;

     for( $i=0; $i<$times; $i++ )
     {
            foreach($Show as $data)
            { 
                echo $data['row1'].$data['row2'].$data['row3'];
            }
      $count += $times;
}
?>

Argh. List in a field. This is very difficult. Normalization suggests data is atomic, that is, do not store a list of data in a single field (column).

The second query call doesn't make much sense to me. If you have say 5 fk ids in a row, you will run the second query 5 times.

What exactly are you trying to do. It looks rather convoluted.

[SimonIoa]

ok i got it about running the second query, i had my thought about it. Give me a few minuts to try something Tahnks

[SimonIoa]

ok how will i get the variable $a to call the second query? $a means each element of the array right? array(1,4,8)
$a=1, $a=4, $a=8

yes you told me before about not storing a list of data in a single field. I used it in many occasions. You are probably right but many people use it.

The point is that you are extracting a single value from a list in a single field and then searching the same field for that value you just extracted. Where clause will only work if the value you extracted is the only value in the field. Otherwise you.ll need FIND IN SET. This is one of the reasons why this data structure is not good. But your funeral. I think you are going to have more issues with it than it.s worth.

[SimonIoa]

thanks @diafol can you give me how to insert ids in multiple columns?

Sorry that would take more time than I have at this point. Carry on with FIND IN SET for now. If this is to be used as some sort of PM system, then this setup certainly needs to change. As each recipient may do different things with a particular message. What are you using this for?

[Taywin]

I am confused with your current script...

$q=mysqli_query($this->db,"SELECT to_uid_fk FROM table WHERE status='1' ") or die(mysqli_error($this->db));

Why do you want to save all IDs with status 1 in one record? Would it be easier to INSERT each ID and status 1 as a record in a table? Are you trying to optimize the data storage space in the table?

If you save each ID as a record, it will be easier to iterate through your 2nd script. However, I have no idea why you have to keep them in one record, so I can't comment further...

[SimonIoa]

How do i store multiple values? The only thing i can imagine is creating multiple columns

It's difficult to know what to suggest as you haven't answered the question with regard to what this table is for. Is it like a PM scenario?
I'll go on the premise that is indeed what you're trying to do (Simple PM system - not "conversation" type):

USERS
id | username ... (etc)...

MESSAGES
id | sender_id | title | body | msg_status | sent_at

MSG_RECIPIENTS
message_id
recipient_id
msg_status

There are a number of ways you could do it, this is just one. messages.sender_id and msg_recipients.recipient_id are FKs of users.id. This way all recipients have direct control over what they do with the message - read/unread/deleted etc.

[SimonIoa]

ok i got it. I used it also. No its not a im scenario. I use this though for im's