Jump to content

Archived

This topic is now archived and is closed to further replies.

bigfatwallet

Join tables help required

Recommended Posts

Can someone please help with the following:

 

Table url:

urlid

url

user

credits

 

Table urlhistory:

urlid

user

date

 

What I would like to do is return a random urlid to the browser based on the following:

 

1) credits > 0

2) user (in table url) is not the user($id)

3) The table urlhistory does not have a record for that urlid, that user and todays date.

 

 

I thought I would be able to join the tables,

(limit by 1 and order random)

but have never used a join in mysql before.

 

Also if there are no more remaining urlids to choose I would need to show default

text saying no more urls available.

 

Many thanks in advance

Share this post


Link to post
Share on other sites

This is an SQL question, and as such, will be moved to our SQL forum. I'm going to take a stab at answering it for you though, not sure if this is right, but will give you something to start with:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] u.urlid FROM url AS u, urlhistory AS h WHERE u.credits > 0 AND u.user != '$id' AND h.date != '$today' [!--sql2--][/div][!--sql3--]

You will need to provide $today with todays date with some php, I cant remeber the SQL to insert todays date, without the time, it might just be date()...

Share this post


Link to post
Share on other sites

need to join the tables together as well, and order by rand()

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] u.urlid FROM url AS u LEFT JOIN urlhistory AS h ON u.urlid=h.urlid WHERE u.credits > 0 AND u.user != '$id' AND h.urlid IS NULL AND LEFT(h.date,10) != CURDATE() AND h.user!='$id' ORDER BY RAND() LIMIT 1 [!--sql2--][/div][!--sql3--]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.