mysql - Select date from another table -
i need help. please me out work.
i have these 2 tables, relation between them user_id. accomplished select follow , display images wall. however, have strange issue: photos sorted desc want, sorted user_id. means whoever follow first, image sorted first desc.
i tried every possible way photos sorted base on desc newest photo top, couldn't. here tables , i'll show every possible thing tried:
create table if not exists `photos` ( `id` int(11) not null auto_increment, `img` varchar(255) not null, `about` text not null, `date` varchar(222) not null, `user_id` varchar(255) not null, `likes` int(255) not null, `down` int(255) not null, primary key (`id`) ) ; create table if not exists `follow` ( `id` int(11) not null auto_increment, `followers` varchar(255) not null, `following` varchar(255) not null, primary key (`id`) } one
followers_photo.php, retrieve following id from:
$the_user = mysql_query("select * users username = '$getuser'"); $tuser=mysql_fetch_array($the_user); $tu_id = $tuser['id']; $followers = mysql_query("select distinct follow.* follow join photos on (follow.followers = $tu_id) order photos.date desc"); $f_s_count = mysql_num_rows($followers); - index.php display images here.
while($uim = mysql_fetch_assoc($followers)){ $i_id = $uim['following']; $followers_image = mysql_query("select distinct photos.* photos join follow on (photos.user_id = $i_id) group rand() order date desc");
the above working, mentioned sorts images based on date desc , user_id don't want. want stop sorting image based on user_id
two
followers_photo.php
$the_user = mysql_query("select * users username = '$getuser'"); $tuser=mysql_fetch_array($the_user); $isuser = mysql_num_rows($the_user); $tu_id = $tuser['id']; $tu_name =$tuser ['username']; ////users whos following me $followers = mysql_query(" select distinct follow.* photos join follow on follow.followers = photos.user_id follow.followers = $tu_id order photos.date desc"); // $f_s_count = mysql_num_rows($followers); index.php
while($uim = mysql_fetch_assoc($followers)){ $i_id = $uim['following']; $followers_image = mysql_query("select * photos user_id = '$i_id' order date desc "); the above same in first step. point me right way? sort photos people follow based on date desc, posts last comes first. guys, appreciate lot. , sorry bad english.
update
now solution vegatripy give , images duplicating , not order wanted. on left showing now. want sort right image. ideas? http://i.stack.imgur.com/cod6z.jpg
solved
$followers = mysql_query("select photos.img, follow.followers, follow.following, photos.date,photos.img,photos.likes,photos.down,photos.id,photos.about follow join photos on ( photos.user_id = follow.following ) follow.followers =31 order photos.date desc"); $f_s_count = mysql_num_rows($followers); while($fimage = mysql_fetch_assoc($followers)){ $i_id = $fimage['following']; $disimg = $fimage['img']; $disid = $fimage['id']; $distime = $fimage['date']; $i_like=$fimage['likes']; $i_unlike=$fimage['down'];
you're mixing join missing where clause in sentences (and that's why used distinct, because there's no join condition @ , had repeated rows)
$followers = mysql_query("select distinct follow.* follow join photos on (follow.followers = $tu_id) order photos.date desc");
which equivalent to:
$followers = mysql_query("select distinct follow.* follow, photos (follow.followers = $tu_id) order photos.date desc"); that join giving whole cartesian product (from follow join photos) column follow.followers = $tu_id, includes not truly joined rows between "follow" , "photos" tables . discard rows, you'll need where clause after join codition.
anyway, you're mixing things little bit. i've analyzed code several times , don't why joining "photos" "follow" tables, when you're selecting columns , using condition 1 of them.
i'm going set example of you're trying do. sql meaningful stuff. i'll let php you. these tables users, photo , follow
users: id username -- -------- 01 john 02 smith 03 rambo photo: (just relevant columns) id img date user_id -- ----- ----------- ------- 01 a.jpg 01-02-2013 03 (rambo) 02 b.jpg 30-07-2013 03 (rambo) 03 c.jpg 04-04-2012 02 (smith) 04 d.jog 04-04-2013 01 (john) follow: id followers following -- -- -- 01 01 02 (john following smith) 02 01 03 (john following rambo) 03 02 01 (smith following john) 04 03 01 (rambo following john) 05 02 03 (smith following rambo) with set up, want display images users of given username following, order date of image. so, given username "john". id "01". if need images people john following, then:
select photo.img follow join photo on (photo.user_id = follow.following) follow.followers = 01 order photo.date desc; we joining "photo.user_id" "follow.following", order date desc. make temp table like:
photo.id photo.img photo.date photo.user_id follow.id follow.followers follow.following -- ----- ---------- ------- -- -- -- 02 b.jpg 30-07-2013 03 (rambo) 02 01 03 (john following rambo) 04 d.jog 04-04-2013 01 (john) 04 03 01 (rambo following john) 04 d.jog 04-04-2013 01 (john) 03 02 01 (smith following john) 01 a.jpg 01-02-2013 03 (rambo) 02 01 03 (john following rambo) 03 c.jpg 04-04-2012 02 (smith) 01 01 02 (john following smith) then where clause select rows follower user_id want (01). that's:
photo.id photo.img photo.date photo.user_id follow.id follow.followers follow.following -- ----- ---------- ------- -- -- -- 02 b.jpg 30-07-2013 03 (rambo) 02 01 03 (john following rambo) 01 a.jpg 01-02-2013 03 (rambo) 02 01 03 (john following rambo) 03 c.jpg 04-04-2012 02 (smith) 01 01 02 (john following smith) so john see 2 photos rambo , 1 smith. ordered date.
hope you.
Comments
Post a Comment