Android Question mysql query help for my php file

[tufanv]

Hello,

I want to use left join to join 2 databases. I am trying to create a statement for hours for my php file but couldnt finish it =) ( maybe there is a better way than left join )

DB1 = tbllists / coloumns: (soru,onay,public,date,listid,publicid)
DB2 = tblcomments / columns: (oyid,listid,username,date,publicid)

What i want to do is get the list of "listid " of DB1: where onay=1 , public=1 and date is bigger or equal to today's date MINUS 15 days compared to rows of DB2 :
Check each row where listid we got from db1 is also exists in DB2, where username column is equal to username in app (will be taken by the app from a textfield via a php file , lets say it is $usn ) and remove those ids form the main listid list. ( shortly I want to get the listids of db1 where my user is not commented on that list id by checking db2, -if the user comments on one of the listid , the comment is added to db2-)

I am not sure how to do this in my php file and print it as json so i can retrieve them from my app. What i tried was
select * from tbllists left join tblcomments on tbllists.publicid=tblcomments.publicid where tbllists.public=1 and tbllists.tarih is bigger than today-15 ( dont know how to do it) and tbllists.onay=1

but could not add anything to compare with db2. I know it is complicated but any help would be perfect !

ty

 

[sorex]

the check if the id exists in both db1 & db2 can be done with an inner join instead of left join.
only the rows where both ids exists will show up.


for the date you can use something like where datediff(tbllists.tarih,now())>-15

 

[tufanv]

the check if the id exists in both db1 & db2 can be done with an inner join instead of left join.
only the rows where both ids exists will show up.


for the date you can use something like where datediff(tbllists.tarih,now())>-15

Hello,

thanks for the answer. The problem is i dont want to check if both ids exists in both tables. I want to check the ids in first table with written conditions then for each id found in table1 i want to check rows of table 2'.

Ltets say table 1 gave me listid s of : 1234 , 4321, 6094, 0912

I want to go to db2 and check if ids above has a row which's username coloumn is $usn

 

[sorex]

then you can use something like

inner join db2 on db1.listid=db2.listid and db2.username like '$usn'

you can add multiple checks in join declarations

 

[tufanv]

then you can use something like

inner join db2 on db1.listid=db2.listid and db2.username like '$usn'

you can add multiple checks in join declarations

yes this will help ! Thank you very much for the date and inner join help !

 

[tufanv]

I now tried the statement and i could successfully get the listid s where user commented what i want to get is where user NOT commented. Is there any way to exclude the found listids from the all listids found in db1 within a sql statement ? or do i have to exclude them manually with a different way ?

then you can use something like

inner join db2 on db1.listid=db2.listid and db2.username like '$usn'

you can add multiple checks in join declarations

 

[sorex]

you can merge 2 queries and use username not in (select username from db2 where db2.listid=listid and the other checks...)

 

[tufanv]

I just used
inner join db2 on db1.listid=db2.listid and db2.username<>$usn

and it works great now! Thanks Sorex

you can merge 2 queries and use username not in (select username from db2 where db2.listid=listid and the other checks...)

 

[sorex]

that's also a possibility for this case. the other one might be faster on large records sets since you limit the list already before you start the join.