sql - Select 2 time records from one table -


i have 2 table below

postlist: post_id(pk,identity)  likelist: like_id(pk,identity), post_id(fk)

every entry of post in likelist, liked.

this task post on facebook

i passing 2 value .cs, when datalist binding whichever post_id , current user id(mid)

  1. count every post like.
  2. if current user alredy liked, change button text unlike

i create query like,

select mid ,(select count(post_id) likelist post_id=@sp_post_id) like_count likelist (post_id=@sp_post_id , mid=@sp_mid)  group mid

i got post_id count in below case if past post_id=27 , user/mid=2 not found record.

what want: check post likes count , check if user post or not. (how check counts , current user or not?)

table data:

postlist

post_id fid   mid   post_img         post_msg                      post_time  1       1     1     tulips.jpg       post user1.         2015-05-26 3       3     2     lighthouse.jpg   post user2.         2015-05-26 5       1002  3     road1.jpg        post user3.         2015-05-26 6       3     2     map1.jpg         2 post user2.       2015-05-27 7       1     1                      text post user1  2015-05-27 26      1     1     globe.jpg                                      2015-05-28 27      1003  5     gujarat.jpg      post user5, again.  2015-05-29 30      3     2     location2.jpg    post user2               2015-05-29

likelist

like_id  post_id    mid like_status like_time 1        27         1   1           2015-05-29 2        30         1   1           2015-05-29 3        6          1   1           2015-05-29 4        30         2   1           2015-05-29 9        6          2   1           2015-05-29

what happen: when user online: mid=1

post_id:30 likes 2 times (online user too)

when user online: mid=2

post_id:27 likes 1 time (online user not it)

select      post_id,      count(*) likecount,     max(case mid when @sp_mid 1 else 0 end) userliked -- 0 no; 1 yes likelist  1 = 1     , post_id = @sp_post_id -- can comment line viewing post     , like_status = 1 group post_id

Comments

Post a Comment

Popular posts from this blog

java - Null response to php query in android, even though php works properly -

Image
table i'm trying retrieve above data using 2 php files below. want pass email parameter , return bets user = email. work fine when tested advanced rest client, problem not php. however, when try through android 2 java classes below, null pointer exception (logcat below) , json object returned null. i'm not sure error is, think i'm not passing parameter php , that's why it's producing null response. logcat 05-31 15:10:50.227 26074-26074/com.example.albert.betterapp e/androidruntime﹕ fatal exception: main process: com.example.albert.betterapp, pid: 26074 java.lang.runtimeexception: unable start activity componentinfo{com.example.albert.betterapp/com.example.albert.betterapp.displayallbets}: java.lang.nullpointerexception @ android.app.activitythread.performlaunchactivity(activitythread.java:2292) @ android.app.activitythread.handlelaunchactivity(activitythread.java:2350) @ android.app.activitythread.acce...
Read more

node.js - Using Node without global install -

i'm working on few node & electron (atom shell) projects , i'm curious how them work without user having install node globally. i've seen few applications including node directory in project contains node.exe binary. how go setting node project use binary? edit: thought should add, i've searched everywhere answer this, unrelated results.
Read more

php - CakePHP HttpSockets send array of paramms -

i problem when make request (i can request). $link = 'http://uri/path/?param1=1&sid=2&sid=3&sid=4' $http = $httpsocket = new httpsocket(); $res = $httpsocket->get($link); if check request pr($http); i see : [line] => /url/path/?param1=1&sid%5b0%5d=2&sid%5b1%5d=3&sid%5b2%5d=4 http/1.1 and i'm getting empty response because server doesn't know parameters sid%5b0%5d=2 when try send parameters array : $data = array('param1' => '1', 'sid' => array('2','3', '4')); i see same changes %5b0%5d - additional indexes. how fix it? unfortunately , can not change server side, if send request in browser, i'll normal response in json format. when make request, can parameters this: $param1 = $_get['param1']; $sid= $_get['sid']; tip: use diferente names parameters. have multiples sid. use sid1, sid2, sid3... update: can parameters this:...
Read more