Jump to content


Photo

Join tables help required


  • Please log in to reply
2 replies to this topic

#1 bigfatwallet

bigfatwallet
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 09 November 2005 - 11:30 AM

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

#2 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 09 November 2005 - 12:51 PM

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()...

#3 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 09 November 2005 - 04:31 PM

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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users