Page 1 of 1
$row value lost when using LEFT JOIN
Posted: Mon Apr 07, 2008 4:12 am
by deejay
Hi
I'm doing a database search using LEFT JOIN but lose the values that are present in both tables. heres the code I'm using to test this
Code: Select all
$selectquery = "SELECT * FROM listings AS li LEFT JOIN weeks AS w ON w.llid = li.llid ORDER BY RAND() LIMIT 1";
//echo "$selectquery";
$result = mysql_query($selectquery)
or die ("Query failed");
while ($row = mysql_fetch_array($result))
{
$variable1=$row["rid"];
echo $variable1;
echo '<br>';
$llid=$row["llid"];
echo $llid;
$picthree=$row["img3"];
echo $picthree;
echo '<br>';
$picfour=$row["img4"];
echo $picfour;
echo '<br>';
$addtwo=$row["addtwo"];
echo $addtwo;
echo '<br>';
}
the only row values I don't get back are llid & rid - which appear in both table listings and weeks.
Thanks in advance for any help.
Re: $row value lost when using LEFT JOIN
Posted: Mon Apr 07, 2008 9:54 am
by onion2k
Try being more specific about what data your query should return. Instead of '*' list the fields with their table names eg "li.llid, w.rid".
Re: $row value lost when using LEFT JOIN
Posted: Mon Apr 07, 2008 3:06 pm
by deejay
i tried
Code: Select all
$selectquery = "SELECT li.llid, w.rid, li.img3, li.img4, li.addtwo FROM listings AS li LEFT JOIN weeks AS w ON w.llid = li.llid ORDER BY RAND() LIMIT 1";
is that what you meant,
but it gave me the same result. I think i need to use another way to JOIN other than left as I understand it returns columns on right as NULL.
maybe search both tables and use a WHERE statement, the only thing I can't really get my head around is that sometimes the weeks table will be completly empty. will have to give it some thought.
Thanks anyway.
Re: $row value lost when using LEFT JOIN
Posted: Mon Apr 07, 2008 7:52 pm
by califdon
deejay wrote:i tried
Code: Select all
$selectquery = "SELECT li.llid, w.rid, li.img3, li.img4, li.addtwo FROM listings AS li LEFT JOIN weeks AS w ON w.llid = li.llid ORDER BY RAND() LIMIT 1";
is that what you meant,
but it gave me the same result. I think i need to use another way to JOIN other than left as I understand it returns columns on right as NULL.
maybe search both tables and use a WHERE statement, the only thing I can't really get my head around is that sometimes the weeks table will be completly empty. will have to give it some thought.
Thanks anyway.
No, a LEFT JOIN means "return ALL rows from the first table I name in the "FROM" clause, and ONLY THOSE ROWS from the second table if they match the "ON" criteria. I don't immediately see why you have missing data. Are you really sure that the "missing" data are not just data from 'weeks' in cases where the id's don't match?
Re: $row value lost when using LEFT JOIN
Posted: Mon Apr 07, 2008 7:53 pm
by califdon
deejay wrote:i tried
Code: Select all
$selectquery = "SELECT li.llid, w.rid, li.img3, li.img4, li.addtwo FROM listings AS li LEFT JOIN weeks AS w ON w.llid = li.llid ORDER BY RAND() LIMIT 1";
is that what you meant,
but it gave me the same result. I think i need to use another way to JOIN other than left as I understand it returns columns on right as NULL.
maybe search both tables and use a WHERE statement, the only thing I can't really get my head around is that sometimes the weeks table will be completly empty. will have to give it some thought.
Thanks anyway.
No, a LEFT JOIN means "return ALL rows from the first table I name in the "FROM" clause, and ONLY THOSE ROWS from the second table if they match the "ON" criteria.
Oh, wait a minute--did you mean to say "w.llid = li.llid"? Are the fieldnames the same in both tables? Also, is it correct that you only want to return one field (rid) from weeks? And a random row if there are more than one match? You probably have a reason to do that, but it struck me as slightly unusual, so I thought I'd ask, to make sure that's what you meant.
Re: $row value lost when using LEFT JOIN
Posted: Tue Apr 08, 2008 8:35 am
by deejay
califdon wrote:
No, a LEFT JOIN means "return ALL rows from the first table I name in the "FROM" clause, and ONLY THOSE ROWS from the second table if they match the "ON" criteria.
thanks for your reply.
If this is the case all my problems stem from my understanding of how LEFT JOIN works, I thought that when using ON that would provide the link point.
What I wanted is random search for a property (listings table) and then to search in 'weeks' to see if there are any dates available.
i managed to write this hack
Code: Select all
$selectquery = "SELECT * FROM listings ORDER BY RAND() LIMIT 1";
$result = mysql_query($selectquery)
or die ("Query failed");
while ($row = mysql_fetch_array($result))
{
$variable1=$row["rid"];
$datequery = "SELECT * FROM weeks WHERE rid=$variable1 LIMIT 1";
$dateRst = mysql_query($datequery);
// some times this table will be empty - so have included the following if statement rather that die
if ($dateRst) {
$dateRow = mysql_fetch_array($dateRst);
}
where I have put the weeks search inside the row while.
Thanks