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?

I would address your SQL injection bugs before anything else. — gpojd
– gpojd, Commented Jul 25, 2011 at 13:49
@gpojd It is a JOIN, just because it doesn't use the keyword JOIN doesn't mean that it's not. — Rudi Visser
– Rudi Visser, Commented Jul 25, 2011 at 13:53
I completely overlooked the join in the second query. I edited my response a while ago to remove that part of the comment. — gpojd
– gpojd, Commented Jul 25, 2011 at 13:57

marc · Accepted Answer · 2011-07-25 14:08:50Z

2

Maybe do

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

instead of

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

answered Jul 25, 2011 at 14:08

marc

6,2431 gold badge31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TehShrike Over a year ago

Way to catch bugs that he probably hadn't even noticed yet :-)

TehShrike · Accepted Answer · 2011-07-25 14:12:54Z

1

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");

answered Jul 25, 2011 at 14:12

TehShrike

10.1k2 gold badges36 silver badges28 bronze badges

Comments

Marc B · Accepted Answer · 2011-07-25 14:15:54Z

0

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'];

answered Jul 25, 2011 at 14:15

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

Ado Over a year ago

I am actually going to fetch few more details there latter. Thank you Marc.

Collectives™ on Stack Overflow

PHP - MySql Join -

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related