Generating a list from available products in database.

[elmas156]

Hello everyone,

 

I've been working on this for about 2 days now, and I just can't seem to figure it out so I need some help from someone here.  I have a database with three items (will be more, but for now there's three).  The database keeps a record of the days that each item is reserved.  I want to be able to select and generate a list of items that are available on a specific day.  Here is what I have so far:

 

<?php

$result = mysql_query("SELECT `prodid` FROM reservations WHERE `resdate` = '$resdate'") or die (mysql_error());

while ($row = mysql_fetch_row($result)) {

$resprodid = $row[0];

$result2 = mysql_query("SELECT `prodid`,`prodname` FROM products");
$row2 = mysql_fetch_row($result2);

$prodid = $row2[0];
$prodname= $row2[1];

if ($prodid != $resprodid) {

	echo $prodid;									

}

}

?>

 

The result that I'm getting are not correct. I either get only one unit listed, or none.  Any help would be appreciated.  Thanks!

[The Little Guy]

What does $resdate look like? is it a date, time, datetime?

 

I would also recommend doing a join.

<?php
$result = mysql_query("SELECT r.prodid, p.prodname FROM reservations as r left join products as p using(prodid) WHERE date(resdate) = date('$resdate')");
while ($row = mysql_fetch_assoc($result)) {
$prodid = $row["prodid"];
$prodname= $row["prodname"];
echo "$prodid<br />";
}
?>

[sunfighter]

Why do you even have two table here? Why not just add column `resdate` to the products table?

[elmas156]

$resdate is actually a string that I created by using "explode" to adjust the format of the date() function.  The end result always look like "02/17/2012."  I've never used a "join," any way you could break it down and help me understand exactly what's going on in your code?  Thanks very much!