pass get variable to mysql query

[kaiman]

I am working on a room availability calendar that has links for each day. When you click on a link it passes the day month and year in the URL to another page like this:

 

echo "<td class=\"today\"> <a href=\"status.php?month=$month&day=$day_num&year=$year\">$day_num</a> </td>\n";

 

I can see that the correct information is being passed through the URL like this:

 

.../status.php?month=12&day=9&year=2010

 

Then the information is supposed to be passed to the MySQL query, but here is my question: How do I do this? I have a DB table set up, but the query is currently returning a blank page.

 

Here is my current query:

 

// connects to server and selects database.
include ("../includes/dbconnect.inc.php");

// table name
$table_name = "availability"; 

// query database for events

$result = mysql_query ("SELECT id FROM $table_name WHERE month=$month AND year=$year AND day=$day_num LIMIT 1") or die();

if (mysql_num_rows($result) > 0 ) {
	while($row = mysql_fetch_array($result)) {
		extract($row);
		echo "<h1>Current availability for ".$row['month'] . "/" . $row['day'] . "/" . $row['year'] . "</h1>";
		echo "	<ul>";
		echo "		<li>Earth Room: " . $row['earth_room'] . "</li>";
		echo "		<li>Air Room: " . $row['air_room'] . "</li>";
		echo "		<li>Fire Room: " . $row['fire_room'] . "</li>";
		echo "		<li>Water Room: " . $row['water_room'] . "</li>";
		echo "	</ul>";
	}
}
else {
		echo "	<ul>";
		echo "		<li>Currently no reservations.</li>";
		echo "	</ul>";	
}

 

Any help is appreciated.

 

Thanks,

 

kaiman

[shlumph]

How are you setting $month, $year, and $day_num? Also, change the code to

or die(mysql_error())

what does the error say?

[solon]

Add the following lines to your 'status.php' file:

 

$month = $_GET['month'];
$day_num = $_GET['day'];
$year = $_GET['year'];

 

this should work

[kaiman]

@ shlumph - okay that resolved the MySQL error - thanks!

 

@ solon - yep that is what I was looking for, that seems to be working, but the issue I am having now is that it doesn't seem to be echoing out the row info from the db. One days there is no reservations it prints out:

 

Currently no reservations.

 

As expected. However, on days where I have entered information into the DB for testing it prints out empty rows such as:

 

Current availability for 12/25/2010

    Earth Room:
    Air Room:
    Fire Room:
    Water Room:

 

Here is my DB structure:

 

CREATE TABLE `availability` (
`id` int(11) NOT NULL auto_increment,
`day` varchar(2) NOT NULL default '',
`month` varchar(2) NOT NULL default '',
`year` varchar(4) NOT NULL default '',
`earth_room` varchar(25) NOT NULL default 'Available',
`air_room` varchar(25) NOT NULL default 'Available',
`fire_room` varchar(25) NOT NULL default 'Available',
`water_room` varchar(25) NOT NULL default 'Available',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

And here is my working code:

 

// get url variables
$month = $_GET['month'];
$year = $_GET['year'];
$day_num = $_GET['day'];

// connects to server and selects database.
include ("../includes/dbconnect.inc.php");

// table name
$table_name = "availability"; 

// query database for events

$result = mysql_query ("SELECT id FROM $table_name WHERE month='$month' AND year='$year' AND day='$day_num' LIMIT 1") 
or die(mysql_error());

if (mysql_num_rows($result) > 0 ) {
	while($row = mysql_fetch_array($result)) {
		extract($row);
		echo "<h1>Availability for ".$_GET['month'] . "/" . $_GET['day'] . "/" . $_GET['year'] . "</h1>";
		echo "	<ul>";
		echo "		<li>Earth Room: " . $row['earth_room'] . "</li>";
		echo "		<li>Air Room: " . $row['air_room'] . "</li>";
		echo "		<li>Fire Room: " . $row['fire_room'] . "</li>";
		echo "		<li>Water Room: " . $row['water_room'] . "</li>";
		echo "	</ul>";
	}
}
else {
		echo "<h1>Availability for ".$_GET['month'] . "/" . $_GET['day'] . "/" . $_GET['year'] . "</h1>";
		echo "	<ul>";
		echo "		<li>Currently no reservations.</li>";
		echo "	</ul>";	
}	

 

Any ideas?

 

Thanks for the continued help,

 

kaiman

[PFMaBiSmAd]

Your query is only SELECTing id in the query, so the data in the other columns is not available. You would need to select the columns that you want.

[kaiman]

@ PFMaBiSmAd

 

Right you are! Thanks for looking over my shoulder while I stumble through this.

 

Regards,

 

kaiman