Help with SELECT * FROM .......

kopsuz · #1 (**permalink**) 02-01-08, 10:47

Hello there,
now i am trying to create a php website for hotel registration. I have 2 tables - one is listings, where will be the apartments and other - kalendar, where will be information for availability. In table listings every record has unique rID, in other table kalendar i have records with dates, which are reserved already, because it's easy to add only these days and calendar script for checking availability is finished already.

table listings:

rID | type | name | city | address | rent | img1 | img2

table kalendar

ID | r_ID | date

rID is the same like r_ID. The people must choose check-in date; check-out date; city and name from search engine. This is my query, who don't work correctly, if you have some suggestions, please please answer me:

$selectquery = "SELECT * FROM listings WHERE NOT EXISTS (select * from kalendar WHERE listings.rid != r_id AND kalendar.date > '$check-in-date' AND kalendar.date < '$check-out-date' AND listings.city = '$city' AND listings.type = '$type') GROUP BY rid ";

values from the html fields:
$check-in-date
$check-out-date
$city
$type

The query returns all records from listings table.

I am waiting your answers. Thanks in advance.

sco08y · #2 (**permalink**) 02-01-08, 13:34

Quote:

Originally Posted by kopsuz

SELECT * FROM listings WHERE NOT EXISTS (select * from kalendar WHERE listings.rid != r_id AND kalendar.date > '$check-in-date' AND kalendar.date < '$check-out-date' AND listings.city = '$city' AND listings.type = '$type') GROUP BY rid

- select * is fine for testing queries, but in code you must always list the columns you want returned.

- I think you mean "ORDER BY" instead of "GROUP BY".

- you don't want to put columns from Listings in your subquery. I'd suggest a join instead.

- BETWEEN is a little easier to follow than doing two comparisons, IMHO.

I come up with:

Code:

SELECT  Listings.rID, type, name, city, address, rent, img1, img2
FROM Listings INNER JOIN Calendar ON Listings.rID = Calendar.rID
WHERE date BETWEEN '$check_in' AND '$check_out' 
    AND city = '$city'
    AND type = '$type'

Final point: the ID column in Calendar is probably unnecessary, assuming that there's no reason to have two appointments scheduled on the same day. You can use the r_ID and date as a primary key.

kopsuz · #3 (**permalink**) 02-01-08, 17:56

Hello again,
sco08y thanks for your reply, about select * maybe you are right, i am not very well with sql, i use GROUP BY, because if in table kalendar i have 20 days inserted, the query returns 20 rows with the same object, maybe DISTINCT is better, however this is not the problem. I put your code and still the search engine doesn't find right results. In table Calendar are RESERVED DAYS, i search FREE DAYS:

table listings:

rID | type | name | city | address | rent | img1 | img2
1 1 |studio |ap.1 | bansko | 23 sd str.| 30 | ......| .......
2 2 |1bed |ap.2 | basnko | 23 sd str.| 45 | ......| ......
3. 3 |1bed |ap.34| sliven | 566 ff str.| 35 | ......| .....

table kalendar:

ID | r_ID | date
1. 56 | 1 | 2008/02/20
2. 57 | 1 | 2008/02/21
3. 58 | 1 | 2008/02/22
4. 59 | 1 | 2008/02/23
5. 60 | 2 | 2008/02/22
6. 61 | 2 | 2008/02/23
7. 62 | 2 | 2008/02/24
8. 63 | 2 | 2008/02/25
9. 64 | 3 | 2008/03/01
10.65| 3 | 2008/03/02
..................................
..................................

the query must filter from the table listings by city and type, but from the second table kalendar must exclude days, which are inserted here. rid and r_id show which day is reserved for which apartment. i can't change the structure of table kalendar, the script is finished already.
when i use your code the query returns these apartments, which require by city and type and have a record in table kalendar with the same r_id, independant from the selected check-in date and check-out date. Maybe i can't explain the situation right, my english is not perfect too

.

my problem is while checking dates in the second table - kalendar to return the right results, filter by city and type and etc is not the problem.

sco08y · #4 (**permalink**) 02-01-08, 19:17

Quote:

Originally Posted by kopsuz

In table Calendar are RESERVED DAYS, i search FREE DAYS:

Right, I'm tracking. You do need the subquery.

Code:

SELECT Listings.rID, type, name, city, address, rent, img1, img2
FROM Listings WHERE city = '$city' AND type = '$type'
    AND NOT EXISTS (SELECT ID FROM Calendar WHERE Listings.rID = Calendar.rID AND date BETWEEN '$check_in' AND '$check_out')

This is pretty similar to your original query, but note that Listings.rID = Calendar.rID.

Quote:

rID is the same like r_ID.

Yeah, sorry if I don't use exactly your column and table names. I didn't even notice that you had two spellings for that column.

Quote:

i use GROUP BY, because if in table kalendar i have 20 days inserted, the query returns 20 rows with the same object, maybe DISTINCT is better,

That would be true if you were joining against the table, but you're using the EXISTS predicate. This query will only pull one record per record in Listings. If you have a primary key set on listings, there should only be one record per Listings.rID.

kopsuz · #5 (**permalink**) 02-02-08, 03:37

sco08y, now everything is OK, works perfect

thanks a lot!!!!
last question, for example:

table listings:

rID | type | name | city | address | rent | img1 | img2
1 1 |studio |ap.1 | bansko | 23 sd str.| 30 | ......| .......
2 2 |1bed |ap.2 | basnko | 23 sd str.| 45 | ......| ......
3. 3 |1bed |ap.34| sliven | 566 ff str.| 35 | ......| .....

table kalendar:

ID | r_ID | date
1. 56 | 1 | 2008/02/20
2. 57 | 1 | 2008/02/21
3. 58 | 1 | 2008/02/22
4. 59 | 1 | 2008/02/23
5. 60 | 2 | 2008/02/22
6. 61 | 2 | 2008/02/23
7. 62 | 2 | 2008/02/24
8. 63 | 2 | 2008/02/25
9. 64 | 3 | 2008/03/01
10.65| 3 | 2008/03/02

when we search: type - studio; city - bansko; check-in-date: 2008/02/20; check-out-date: 2008/02/23 the query returns no results, this is perfect

when we search: type - studio; city - bansko; check-in-date: 2008/02/23; check-out-date: 2008/03/23 the query returns no results, i think that here it's better to return the first studio, because only for one day you exclude apartment which is free for a month.
Is it possible to do this query? I mean if ALL dates between $check-in and $check-out are reserved, the query should return no results, if NOT ALL dates are reserved, the query should return the first studio. I want to know is this variant possible too!
Thanks again!

sco08y · #6 (**permalink**) 02-03-08, 07:35

Quote:

Originally Posted by kopsuz

when we search: type - studio; city - bansko; check-in-date: 2008/02/23; check-out-date: 2008/03/23 the query returns no results, i think that here it's better to return the first studio, because only for one day you exclude apartment which is free for a month.
Is it possible to do this query? I mean if ALL dates between $check-in and $check-out are reserved, the query should return no results, if NOT ALL dates are reserved, the query should return the first studio. I want to know is this variant possible too!
Thanks again!

Instead of running a query that said NOT EXISTS, you could do (SELECT COUNT(r_ID) FROM ...) < 2 to make sure that only a few days were reserved.

But that's a terrible, terrible idea. If I'm looking for a hotel that available from Monday through Friday, I don't want to arrive there on Monday and find that I can't check in until Tuesday!

kopsuz · #7 (**permalink**) 02-04-08, 05:21

Yes, i know that the idea is terrible, but in the beginning in the website will not have a lot of properties and the search results will be less. I think to add a column, which will show available nights for this period.
Because for an example, one room is reserved 20.02.2008 i 21.02.2008, you search room from 21.02.2008 till 15.03.2008 and the query returns 0 results.
Ony for one day this room could not be used. You can see available nights and decide.
I make almost the same query like your and working well. This is the code:

$selectquery = "SELECT * FROM listings LEFT OUTER JOIN kalender ON listings.rid = calendar.r_id WHERE (ISNULL(date) OR (SELECT COUNT(*) FROM calendar WHERE listings.rid = calendar.r_id AND calendar.date>='$check-in' AND calendar.date<='$check-out')<(SELECT DATEDIFF('$check-in','$check-out'))) AND listings.city = '$city' AND listings.type = '$type' GROUP BY rid";

$result = mysql_query($selectquery)
or die ("Query failed");

while ($row = mysql_fetch_array($result))
{
$bgcolor="#FFFFcc";
if (is_int($i/2)) {
$bgcolor="#FFFFFF";
}
$variable1=$row["rid"];
echo "<TR BGCOLOR=\"$bgcolor\"><TD WIDTH=195 align=center>";
echo $row["rid"];
echo "</TD><TD WIDTH=84 align=center>";
echo "<img src=\"/images/".$row['img3']."\">";
echo "</TD><TD WIDTH=84 align=center>";
echo $row["name"];
echo "</TD><TD WIDTH=84 align=center>";
echo $row["bed"];
echo "</TD><TD WIDTH=87 align=center>";
echo $row["bath"];
echo "</TD><TD WIDTH=56 align=center>€";
echo $row["rent"];
echo "</TD><TD WIDTH=58 align=center>€";
echo $row["deposit"];
echo "</TD><TD WIDTH=57 align=center>";
echo "<a href=\"/members/details.php?rid=".$row["rid"]."\">Details</a>";
echo "</TD><TD WIDTH=57 align=center>";
echo "<a href=\"/cal/index.php?r_id=".$row["rid"]."\" target=_blank>Check</a>";
echo "</TD><TD WIDTH=57 align=center>";
echo $row["HERE"]; // HOW CAN I PUT FREE DAYS HERE ????????????
echo "</TD></TR>";

$i++;
}
if (!$variable1)
{
echo "<TR><TD WIDTH=100% BGCOLOR=\"#FFFFFF\" colspan=\"10\">Sorry No Rentals Matched Your Search. For more results <a href=\"/index.php\">Click here</a> to try again.</TD></TR>";
}

Please tell me how can i use this query to calculate free nights? From $check-in to $check-out are "all dates". We must substract from "all dates" the records for the definite room and to see in table here:

echo $row["HERE"]; // HOW CAN I PUT FREE DAYS HERE ????????????

Internet Services

Web Development

Digital Marketing

Consumer Tech