How To Retrieve Records With An Array Of Id's

Yii 1.1 General Discussion
1

I have a form with an activecheckboxlist

From that form the data is stored in an array.

I need to be able to view the form selections in the view template rather than just id’s

I haven’t been able to figure out how to take the stored array, which could have one to multiple selections and get the tittles from the corresponding table.

So I have a value like this stored in the db

["4","8","10"]

Those numbers are id’s for a table, lets say the table is called Colors with the structure of

id | name

I need to list in the view the ‘name’ for the ids that match the id’s in the array.

This has been driving me crazy.

2

You should create a many many relations with a bridge table.

3

Yeah, it’s not generally a good idea to have a serialised list of foreign keys. You’re making extra work for yourself and losing some of the benefits that the database engine can bring.

If you really needed to, I imagine you could call


$records = YourModel::model()->findAllByPk($unserializedArray);

I’d very much recommend rethinking the design though.

4

I tried your code and didn’t notice anything wrong. To help debug, you could try adding some “print_r” and “echo” code for a better sense of what’s happending

Also, you mentioned that your array is imploded, which to my understanding returns a string with the values of the array in the form [value][separator][value]…, is it still an array when the ‘foreach’ starts? Are you finding that $transactions is empty?

print_r ($paymentr); // <----- ADDED FOR DEBUG

foreach ($paymentr as $v) {

$query = “SELECT id, refid, affid FROM transactions WHERE id = ‘$v’”;

echo $query . "<br/>"; // <----- ADDED FOR DEBUG, (XHTML <br/> closed tag)

$result = mysql_query($query) or die("Query Failed: ".mysql_errno()." - ".mysql_error()."<BR>\n$query<BR>\n");

$trans = mysql_fetch_array($result, MYSQL_ASSOC);

$transactions .= ‘<br>User ID:’.$trans[‘id’].’ – ‘.$trans[‘refid’].’ – ‘.$trans[‘affid’].’’;

}

Or, you could do this, which hits the db less:

foreach ($paymentr as $v) { $in_clause .= “’”.$v."’,"; }

$in_clause = trim($in_clause, ",");

$query = "SELECT id, refid, affid FROM transactions WHERE id IN ($in_clause)";

$result = mysql_query($query) or die("Query Failed: ".mysql_errno()." - ".mysql_error()."<BR>\n$query<BR>\n");

while ( $trans = mysql_fetch_array ( $result, MYSQL_ASSOC ))

{

$transactions .= ‘<br>User ID:’.$trans[‘id’].’ – ‘.$trans[‘refid’].’ – ‘.$trans[‘affid’].’’;

}

5

Do you have a recommend way of saving these checkbox values?

6

I heard that giix may help you.

7

Here’s the solution incase anyone else has the same situation

I don’t see a better way to do this, storing the id’s in the db is more efficient if you may need to possibly update the form at a later day.

But if you can think of a better way, please share.

I’m going to see if I can work this into the relations function in the model, but this will do for now.


<?php 

    $records = Teaser::model()->findAllByPk($model->promotions);


    foreach ($records as $obj)

    {

        echo $obj->title .'<br />';

    }


 ?>