[RESOLVED] Php MySQL query problem

ozode

I am new at PHP, having just began working with it last week. Now, I am working on my personal company website and trying to query a MySQL database that I have created. I think that the problem is with the connection to the database. Since this is the first time I have done this, I am sure this code is full of errors. My goal with this code is to run a query based on date input from the user, and output the results on the website. The date input variables are $checkindate and $checkoutdate and based on a third variable 'Availability' = "Vacant". Anyway, it is not working and I figured someone out there might be able to help. If anyone has any comments all responses are welcome.

<?php

$link = mysql_connect("mysql", "USERNAME", "PASSWORD");
mysql_select_db("asianbeachhouse booking");

if (!$link)
{
die('Could not connect: ' . mysql_error());
}
else
{
$Query = SELECT Count( reservation.RoomID ) AS CountOfRoomID,
reservation.RoomID
FROM reservation
WHERE ((((reservation.RDate) >= $checkindate)
AND ((reservation.RDate) <= $checkoutdate))
AND ((reservation.Availability) = "Vacant"))
GROUP BY reservation.RoomID;
$RAvail = mysqli_query($link,$Query);
$NumResAvail = mysqli_num_rows($RAvail);

echo "<b>$NumResAvail Rooms Available</b>";

<table border="1">
<tr>
<th>Count of RoomID</th>
<th>RoomID</th>

</tr>

<?php

while ($Row = mysqli_fetch_assoc($RAvail))
{
echo '<tr>';
echo '<td>' . $Row['CountOfRoomID'] . '</td>';
echo '<td>' . $Row['RoomID'] . '</td>';
echo '</tr>';
}
?>

</table>

<?php
mysqli_free_result($RAvail);
mysql_close($link);
}
?>

dagon

you should be getting some syntax errors on screen, if not error checking is off and thats a bad thing.

ozode

I am not getting any syntax errors, because I am writing this on yahoo's server php builder. As far as in MySQL, I have already ran this query and it tested out just fine. My problem is with the php programming I think, not the query itself.

Do you have some advice on a better editor that I can use to write this code. I have Dreamweaver 8, but php editing is not working on my local system yet.

NoEffinWay

Im no pro, but I think this line:

$Query = SELECT Count( reservation.RoomID ) AS CountOfRoomID,
reservation.RoomID
FROM reservation
WHERE ((((reservation.RDate) >= $checkindate)
AND ((reservation.RDate) <= $checkoutdate))
AND ((reservation.Availability) = "Vacant"))
GROUP BY reservation.RoomID;
$RAvail = mysqli_query($link,$Query);
$NumResAvail = mysqli_num_rows($RAvail);

Should look like:

$Query = mysql_query("SELECT Count( reservation.RoomID ) AS CountOfRoomID,
reservation.RoomID
FROM reservation
WHERE ((((reservation.RDate) >= $checkindate)
AND ((reservation.RDate) <= $checkoutdate))
AND ((reservation.Availability) = 'Vacant'))
GROUP BY reservation.RoomID;
$RAvail = mysqli_query($link,$Query);
$NumResAvail = mysqli_num_rows($RAvail)");

Someone please correct me if I am mistaken.

ozode

Ok, Thanks NoEffinWay, that did not completely solve the problem, but it certainly made things better. Now instead of getting a blank screen after I run the search I am at least getting something. So thanks, now let me update everyone. My code now is written as follows:

<?php

$link = mysql_connect("mysql", "USERNAME", "PASSWORD");
mysql_select_db("asianbeachhouse booking");

if (!$link)
{
die('Could not connect: ' . mysql_error());
}
else
{
$Query = mysql_query("SELECT Count( reservation.RoomID ) AS CountOfRoomID,
reservation.RoomID
FROM reservation
WHERE ((((reservation.RDate) >= $checkindate)
AND ((reservation.RDate) <= $checkoutdate))
AND ((reservation.Availability) = 'Vacant'))
GROUP BY reservation.RoomID;
$RAvail = mysqli_query($link,$Query);
$NumResAvail = mysqli_num_rows($RAvail)");

echo "<b>$NumResAvail Rooms Available</b>";

<table border="1">
<tr>
<th>Count of RoomID</th>
<th>RoomID</th>
</tr>

<?php

while ($Row = mysqli_fetch_assoc($RAvail))
{
echo '<tr>';
echo '<td>' . $Row['CountOfRoomID'] . '</td>';
echo '<td>' . $Row['RoomID'] . '</td>';
echo '</tr>';
}
?>
</table>
<?php
mysqli_free_result($RAvail);
mysql_close($link);
}
?>

You can see what the results is by logging onto my website at www.asianbeachhouse.com. My goal is to have a system that has condo information stored in a database and it allows the user to select the dates of their vacation and then search for available condos to rent in the area. Right now I am working on the search part of this website, with the following query. It searches the reservation table which holds the following fields. ID, RoomID, Reservation date as RDate, and Availability. My query is going to search this Table for available rooms on or between the check in and check out date. Compare the count of those rooms with the difference between the check in/check out date + 1 and this should produce a list of Room IDs that is available for the full time. Once I get this query working I will add in my final criteria which will be comparing the date diff function I created to the count of rooms as I stated above.

ozode

My script is working now! Thanks for everyone's help.

For those of your who have been following this thread this is the script that I have that is working now.

<?php
$con = mysql_connect("MySQL","USERNAME","PASSWORD");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("asianbeachhouse booking", $con);

$result = mysql_query("SELECT Count( reservation.RoomID ) AS CountOfRoomID, reservation.RoomID
FROM reservation
WHERE ((((reservation.RDate) >= '$checkindate')
AND ((reservation.RDate) <= '$checkoutdate'))
AND ((reservation.Availability) = 'Vacant'))
GROUP BY reservation.RoomID
");

echo "<table border='1'>
<tr>
<th>Count ID</th>
<th>Room ID</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['CountOfRoomID'] . "</td>";
echo "<td>" . $row['RoomID'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);
?>