PHP - MySql Join -

Question

I was trying to get some details from MySql database, but i was working so long and tried so many ways that i am completely lost now :s

What i need to GET is two details which depend on information from 3 tables. I need to get $title and $siteurl (from table_sites) where current user did not click it before, AND siteurl owner still have remaining credits...

Here is my database:

USER:
id
username
password
credits
active
clicks

SITES:
id
userid
title
siteurl
clicks 
active 
weight

CLICKS:
id
siteid
byuserid

i tried with this MySql query:

include('db_con.php');
mysql_connect("$dbhost", "$dbusername", "$dbpassword")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$qrym = mysql_query("SELECT * FROM `users` WHERE username='$username' LIMIT 1") or die (mysql_error()); 
  while($mem = mysql_fetch_object($qrym)){
    $uid = $row->id;
  }

$qrys = mysql_query("SELECT * FROM sites, clicks WHERE clicks.byuserid != '$uid' and sites.userid != '$uid' and sites.active = '1' ORDER BY sites.weight DESC, sites.id DESC LIMIT 1") or die (mysql_error()); 
if(mysql_num_rows($qrys)!=0){
  while($row = mysql_fetch_object($qrys)){
    $title = $row->title;
    $siteurl = $row->siteurl;
    echo "$title $siteurl";
  }
} else {
echo "No more sites";
}

No errors, but whatever i try result is No more sites! How to JOIN this query correctly?

Answer 1

When fetching only a single row, as your first query does, there is absolutely NO need for a while() loop to retrieve the data.

That, and observe the comments in the code block:

$qrym = mysql_query("SELECT * FROM `users` WHERE username='$username' LIMIT 1") or die (mysql_error());
while($mem = mysql_fetch_object($qrym)){
       ^^^--- you fetch into $mem
    $uid = $row->id;
            ^^^--- but try to retrieve from $row?
}

Try this instead:

$sql = "SELECT ...";
$qrym = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($qrym);
$uid = $row['uid'];

Answer 2

You probably want a query like this:

SELECT [the columns you need]
FROM sites
LEFT JOIN clicks ON clicks.siteid = sites.id 
  AND clicks.byuserid = [current user id]
WHERE sites.active = 1 AND clicks.id IS NULL
ORDER BY sites.weight DESC, sites.id DESC 
LIMIT 1

As gpojd noted above, you must must MUST sanitize your inputs before using them in a query. Fix your first query's code:

$qrym = mysql_query("SELECT * FROM `users` 
  WHERE username='" . mysql_real_escape_string($username) . "' LIMIT 1");

Answer 3

Maybe do

while($row = mysql_fetch_object($qrym)){
    $uid = $row->id;

instead of

while($mem = mysql_fetch_object($qrym)){
    $uid = $row->id;