Looping through mysql queries?

Looping through mysql queries? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Looping through mysql queries? (/showthread.php?tid=27548)

Looping through mysql queries? - El Forum - 02-14-2010

[eluser]Guest[/eluser]
Hi guys,

I'm letting users of my site store their own page of items - a bit like google personal homepage.

I've stored their choices as a "TEXT" in a database this:

Code:
TBL_USERS:

UserName    PersonalPage

BOB        1,4,6,3,2

MAT        5,11,4,33

AMY        6,3,1,8,4

TBL_ITEMS:

itemID        itemName

1        Item1

2        Item2

3        Item3

4        Item4

etc...

So when "BOB" goes to his personal page, he would the details of each item he has selected.

To load the item's details on their personal pages, all i can think of is looping through each item in the array of "PersonalPage" IDs. But that could mean 20 queries on one page.

Could someone give me an idea of the right way to do this.

Many thanks!

Looping through mysql queries? - El Forum - 02-14-2010

[eluser]Dirk Einecke[/eluser]
Hi,

something like this (not tested):

Code:
$arr_result = $this->db->query('SELECT DISTINCT PersonalPage FROM TBL_USERS WHERE UserName = \'BOB\' LIMIT 1')->result_array();

$arr_result = $this->db->query('SELECT itemName FROM TBL_ITEMS WHERE itemID IN ('. $arr_result[0]['PersonalPage'].')')->result_array();

print_r($arr_result);

Dirk

Looping through mysql queries? - El Forum - 02-14-2010

[eluser]Guest[/eluser]
Dirk!!

That works amazing! Thanks so much :-) :-)

You did in 2 lines what i would have tried to do with Loops and FORs and explodes :-)

It kind of makes me think how bad the rest of my code will be :down:
I guess i could always optimize in the future if the site takes off.

Could someone please explain how i can change this line:

Code:
$arr_result = $this->db->query('SELECT DISTINCT tryItems FROM users WHERE trypage = \'BOB\' LIMIT 1')->result_array();

To use the Query Bindings

Code:
$sql = 'DISTINCT tryItems FROM users WHERE trypage = ? LIMIT 1'; 

$this->db->query($sql, array($name));

I do not understand where this bit: "->result_array();" would fit into it?

Thanks again for the help! Smile

Looping through mysql queries? - El Forum - 02-14-2010

[eluser]Dirk Einecke[/eluser]
Hi,

don't forget the "SELECT" in your $sql string. "DISTINCT" is just to say that the mysql server should stop searching after he has found a record. With "result_array()" your are converting the result of the query into a php array (see: http://ellislab.com/codeigniter/user-guide/database/results.html).

Dirk

Looping through mysql queries? - El Forum - 02-14-2010

[eluser]Guest[/eluser]
Thanks again Dirk.

Oh -- i've just noticed, that for some reason it loses the ordering of my array.

So if i give it "7,6,5,4" -- it will reorder it to 4,5,6,5.

As the array is the order of the users page, is there anyway to make it keep the right order???

So an array of 6,1,9,5 Should stay that when it goes through the foreach ($query->result_array() as $row)

Thanks again for the help.

Regards,

Looping through mysql queries? - El Forum - 02-15-2010

[eluser]Guest[/eluser]
Ok, i've worked it out :coolsmile:

If you want to display the items in the order of the array - you need to use this:

ORDER BY FIELD(PersonalPage,' . $arr_result . ')

Example:

Code:
$arr_result = $this->db->query('SELECT DISTINCT PersonalPage FROM TBL_USERS WHERE UserName = \'BOB\' LIMIT 1')->result_array();

$arr_result = $this->db->query('SELECT itemName FROM TBL_ITEMS WHERE itemID IN ('. $arr_result[0]['PersonalPage'].') ORDER BY FIELD(PersonalPage,' . $arr_result . ')')->result_array();

print_r($arr_result);

Hope this helps anyone else that ever needs it.

Thanks again Dirk for the help :-)