$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.
$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.
$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?
$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.
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.
$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.