Need Help query!!

[eluser]noobs[/eluser]
hello all i am beginner to used code igniter so have a lot problem to develop...

this it my problem :

i have 3 table they are properti,picture and user..
i want to view at front page properti with thumbnail from picture table..one of properti have a lot picture...to be thumbnail picture on front page i want get last upload picture on either properti table...all this time i can't view what i want.. this is my query

Code:

$this->db->select('*,user_id,user_name','picture_thumb');
$this->db->where('properti_is_public', 1);
$this->db->join('picture','properti_id = picture_properti_id','LEFT');
$this->db->order_by('picture_id',1);
$this->db->join('user','properti_user_id = user_id');
$this->db->limit($this->config->item('front_properti'));
$result = $this->db->get('properti');

any suggestion for my problem...

forgive me for my bad engglish...

[eluser]Azafar[/eluser]
hi noobs as i could understand by your post is you want to display a single picture of properti from a bunch of photos. isn't it? I can give u a suggestion if u dont have any restriction of last uploaded picture. The suggestion is this why dont u display a random picture rather than displaying a last uploaded picture. It will be beneficial for you too. cause you could be able to display multiple images for a single properti on the same place. simply tell me your table structure so that i could be able to send you the correct query.

[eluser]noobs[/eluser]
i see...bro...can you show me your query...with random images...single properti...thx b4..

used my current query this it result :

Preview result image file click here..

[eluser]JoostV[/eluser]
I haven't seen your data model, but have you tried this?

Code:

// MAX(picture_id) returns the most recent picture, assuming picture_id is auto_increment
$this->db->select('*, user_id, user_name, picture_thumb, MAX(picture_id) as picture_id', false);
// Group by properti_id to return only one record per properti
$this->db->group_by('properti_id');

BTW, this looks like a syntax error:

Code:

$this->db->order_by('picture_id',1);

[eluser]noobs[/eluser]
[quote author="JoostV" date="1269890557"]
BTW, this looks like a syntax error:

Code:

$this->db->order_by('picture_id',1);

[/quote]

yes i know bro...but i used that syntax no error found it...still work..

[eluser]noobs[/eluser]
[quote author="JoostV" date="1269890557"]I haven't seen your data model, but have you tried this?

Code:

// MAX(picture_id) returns the most recent picture, assuming picture_id is auto_increment
$this->db->select('*, user_id, user_name, picture_thumb, MAX(picture_id) as picture_id', false);
// Group by properti_id to return only one record per properti
$this->db->group_by('properti_id');

[/quote]

thank you very very very very much master....your query working..haha...

[eluser]JoostV[/eluser]
Your're welcome

[eluser]Azafar[/eluser]
hi noobs good to see that ur problem has been fixed. still i can send u the code u were asking. but b4 going a head let me clear something i am sis not bro :-P Ok let me give you that code now. I could not understand ur table structure but let me send u a supposed query


$this->db->select('*,user_id,user_name','picture_thumb');
$this->db->where('properti_is_public', 1);
$this->db->limit($this->config->item('front_properti'));
$result = $this->db->get('properti');

foreach($result as $res)
{
$photo = "";
this->db->order_by("picture_id","random");
$this->db->limit(1);
$qry = $this->db->get_where("picture",array('properti_id'=>$res->properti_id));
if($qry->num_rows() > 0)
{
$res = $qry->row();
$photo = $res->property_photo;
}
$result[$i]->photo = $photo;
}

You can create a separate function to get this random photo. I hope it will help you. If not i am here to help you. This is my first forum which i have been joined and trying to be active in it. Please forgive me for any mistake and will appriciate the guidance by any member of the forum.

[eluser]JoostV[/eluser]
@Azafar loop queries like that can consume a lot of CPU. If you have 1.000 properties the script will do 1.000 lookups for photos, while the entire lookup can be done with a single query. No offence meant

[eluser]Azafar[/eluser]
@JoostV Yes you are absolutely right thanks.... dont worry i dont mind. I read somewhere that knowledge is increased by distributing it. I have shared my own knowledge and you have shared your own. If you are right why should I mind. my basic purpose was to help noobs anyway. well thanks again...