Help with left join

[ameyemad]

Hello, I was hoping someone could give me a solution to this problem. I have 2 tables for this surf script. Table1 has the sites listed, Table2 holds the surf logs. Table2 is wiped of old records every 24 hours so it doesn't get too big. This is the code I am using:

 

$newsfr_pts=@mysql_fetch_array(mysql_query("select newsfr_sites.* from newsfr_sites left join newsfr_logs on newsfr_logs.pageid=newsfr_sites.id where newsfr_sites.hits<newsfr_sites.maxhits and newsfr_sites.username!='bob' and newsfr_sites.active='y' and newsfr_sites.approved='y' and  and newsfr_logs.username='bob' and (newsfr_logs.pageid is null or newsfr_logs.nextvisit<'$time') order by newsfr_sites.lastrun limit 1"));

 

So Table1 "newsfr_sites" has:

id, url, maxhits, hits, lastviewed, owner, approved, active, lastrun

 

and Table2 "newsfr_logs" has:

username, pageid, nextvisit

 

If you need more info please ask. Thank you!

[trq]

If you need more info please ask.

 

You forgot to describe your problem.

[ameyemad]

Oops sorry, first time here. It is not coming up with any matches, nor is mysql_error() giving any error messages.

[trq]

Firstly, you shouldn't feed one function directly into another like that, it makes things harder to debug.

 

You had two and's in your query. Formatting it a little better would help with readability.

 

$sql = "
SELECT newsfr_sites.* FROM newsfr_sites
LEFT JOIN newsfr_logs ON newsfr_logs.pageid = newsfr_sites.id
WHERE newsfr_sites.hits < newsfr_sites.maxhits
    AND newsfr_sites.username!='bob'
    AND newsfr_sites.active='y'
    AND newsfr_sites.approved='y'
    AND newsfr_logs.username='bob'
    AND (newsfr_logs.pageid is null OR newsfr_logs.nextvisit < '$time')
ORDER BY newsfr_sites.lastrun limit 1;
";

if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    $newsfr_pts = mysql_fetch_array($result);
  } else {
    echo "No results found";
  }
} else {
  echo "Query failed<br />$sql<br />" . mysql_error();
}

 

Does this help?

[ameyemad]

Thank you thorpe! That is fantastic! Thank you very much for your help!

[ameyemad]

Looks like this has not been solved at all

 

The code was working, and I *thought* everything was great, but that was until about an hour later. Looks as though this code will work, but it will not find a match for

pageid is null and username='bob'

. If all pages have been surfed, no matter what member surfs them, the code will not find a match.

 

So, for example (as I don't think I'm explaining myself clearly). The code that thorpe offered sort of works. But, let's say I'm a member and the only one surfing, it will let me surf every available page not a problem, but then will not allow any other member to surf as it can't find that segment of code I pasted above.

 

I hope you all understand.

[fenway]

If you left join, then any non-null condition that you want to check needs to be in the on clause, not the where clause.

[ameyemad]

If you left join, then any non-null condition that you want to check needs to be in the on clause, not the where clause.

Thanks for your reply, but do you have an example of how you could better the code from thorpe above please?

[ameyemad]

I'm sorry I have tried different ways that you have suggested, but no luck.

[fenway]

SELECT newsfr_sites.* FROM newsfr_sites
LEFT JOIN newsfr_logs ON ( newsfr_logs.pageid = newsfr_sites.id AND newsfr_logs.username='bob' AND newsfr_logs.nextvisit < '$time')
WHERE newsfr_sites.hits < newsfr_sites.maxhits
    AND newsfr_sites.username!='bob'
    AND newsfr_sites.active='y'
    AND newsfr_sites.approved='y'
    AND (1=1 OR newsfr_logs.pageid is null )
ORDER BY newsfr_sites.lastrun limit 1

[ameyemad]

That code works as well, but allows members to surf continuously even after they have surfed all the pages available.

[fenway]

That code works as well, but allows members to surf continuously even after they have surfed all the pages available.

What condition does that contradict?