• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Looping through mysql queries?

#1
[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!

#2
[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

#3
[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

#4
[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-gui...sults.html).

Dirk

#5
[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,

#6
[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 :-)


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.