Help with while loops...

[elmas156]

Hello everyone, I've already posted a question trying to resolve my problem,  but I've changed my code so much, I figured it may be best just to post a new thread with a better explanation of what I'm trying to do.  Anyway, here's what I've got:

 

I have a database with 2 tables: products and reservations.  I have 3 products for now, that can be reserved for any date.  I'm trying to create a page that will display all of the available products for a specific date.  The user selects the date they want to check, and then the code should check their selected date and compare it with the database to display everything that is available on that date.

 

The database looks like this:

 

Products Table:

 

prodid  prodname

01        item 1

02        item 2

03        item 3

 

Reservations Table:

 

prodid  resdate

01        02/22/2012

01        02/23/2012

03        02/22/2012

 

Here is the code that I have now:

 

<?php

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

while ($rowres = mysql_fetch_row($resultres)) {

$resprodid = $rowres[0];

$resultavail = mysql_query("SELECT `prodid` FROM products WHERE `prodid` != '$resprodid'") or die (mysql_error());

while ($rowavail = mysql_fetch_row($resultavail)) {

	$prodid = $rowavail[0];

	echo $prodid;
                echo $proname;

       }

}

?>

 

When I select 02/21/2012, I get no results, where I should get all three products as a result, because none of them are reserved on this date.

 

When I select 02/22/2012, I get a result of these products, in this order: 02, 03, 01, 02.  I should get a result of ONLY 02, because this is the only product that isn't reserved for this date.

 

When I select 02/23/2012, I get a result of 02 and 03, which in this case would be the correct return, because 01 is the only one reserved for this date.

 

Any idea what I'm doing wrong here and how to fix it?  Any help is GREATLY appreciated!

[mikosiko]

LittleGuy already gave you an answer/solution in this thread

http://www.phpfreaks.com/forums/index.php?topic=353934.0

 

did you try what he suggested?... try that... it should solve your problem or let you very..very close

[Psycho]

DO NOT RUN QUERIES IN LOOPS. It is terribly inefficient.

 

Anyway, LittleGuy's solution in the other thread was adequate. But, I think your problem is this:

$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!

 

If you have dates - store them as Dates. Otherwise, you will not be able to use the date functions within MySQL. You need to store the dates in one of the date/datetime formats supported by MySQL. Then just change the format of the date to YYYY-MM-DD when inserting the record. Also, use the field names when you retrieve the values instead of the index. Otherwise, if you add/remove a field your code will break

 

Then just use a query with a JOIN. Here is what I would do.

//Add any additional field you want from the products
//or reservations table in the SELECT clause
$query = "SELECT p.prodid, p.prodname
          FROM products AS p
          JOIN reservations AS r USING (`prodid`)
          WHERE `resdate` = '$resdate'";
$result = mysql_query($query) or die (mysql_error());

while ($row = mysql_fetch_assoc($resultavail))
{
    echo "{$row['prodid']} - {$row['prodname']}<br>\n";
}

 

If you don't know how to use JOINs go find some tutorials. Trying to teach that in a forum post is not realistic.