• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
mysql query help needed

#1
[eluser]LuckyFella73[/eluser]
Hi codeigniters,

I'm struggling with building a query and I bet
it's a 10 seconds job for all the sql pros out there Smile

The scenario:
I have a table called "maintable" where there is a column named "image_id"
and a second table called "images" where I have the columns "id","image" and "logo".

Each "image_id" entry from "maintable" is related to the "id" of the "images" table.

I need a query to get all rows (and all columns) of the "maintable" and the related infos
("image" and "logo") in one rush maybe someone can help me with this?
I had a simular thing a couple of year ago but I can't get it to work for
this case... In the end I would like to have this in my view file:

Code:
<?php
// simplified of course ...
foreach ($query->result() as $row)
{
    echo($row->title); // comes from table "maintable"
    //
    echo($row->date); // comes from table "maintable"
    //
    echo($row->image); // comes from table "images"
    //
    echo($row->logo); // comes from table "images"
    // etc.
}
?>

Would be very nice to get a hint!

Maybe it's better to write down the table structures:
table "maintable" (simplified):
| id | title | date | image_id | display |
------------------------------------------------------------
| 1 | test | 2008 | 3 | yes |
| 2 | test2 | 2009 | 7 | no |



table "images":
| id | image | logo |
-----------------------------------------
| ...
| 3 | image_a.gif | logo_a.jpg |
| ...
| 7 | image_b.gif | logo_b.jpg |
...and so on

#2
[eluser]TheFuzzy0ne[/eluser]
Code:
$this->db->select('title, date, image, logo');
$this->db->from('maintable');
$this->db->join('images', 'images.id = image_id', LEFT');
$res = $this->db->get();

That should do it, I haven't tested it though. (2 minutes 8 seconds) Tongue

#3
[eluser]LuckyFella73[/eluser]
I bet the only reason you needed 2 minutes 8 seconds was
you were replying to 27 other posts simultaneously ;-)

Thank you soo much !!


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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