Member Avatar for dsgnews

Hey folks, I need your help. I stuck with my Database project.
I have five tables on the database: These are

Room, Guest, Booking, Employee and Admin

Room (room_ no, room_ type, price)
Guest (guest_ no, Fname, Sname, address,…)
Booking (room_no, guest_no, Fname, Sname, arrival_date,
departure_date, emp_no, user_name, password)
Employee (emp_no, Fname, Sname, uaser_name, password)
Admin (admin_no, fname, Lname, user_name, password)

I used the fallowing query to search for available rooms:

Select *
From Room R
Where R.room_no
NOT IN (Select B.room_no
From R.room_no = B.room_no
And R.room_type = ‘single’
And B.arrival_date = ‘2005-03-16’
And Bdeparture_date = ‘2005-04-19’

Q1. Whenever I do a query on localhost it retrieves a value, but when I try on live it shows an error. Please, tell me what the problem in this query could be?

Q2. Does MySQL use different SQL query such as like PL SQL or is it the similar query?

Q3.Is their any simple query than this.

Thanks for all your help.

DSG

  • 4 Contributors
  • 4 Replies
  • 124 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by DanceInstructor

Dani AI

Generated

A few quick, practical points that will fix the kind of syntax error reported and make the availability test correct.

The usual causes here are a malformed subquery (missing FROM or WHERE clauses inside it), “smart” quotes or stray characters copied from a word processor, and case-sensitive table names on a Linux host (as noted). Confirm the server’s identifier case rules in the MySQL docs: Identifier case-sensitivity.

For availability you also need a proper overlap test, not equality of dates. Two ranges overlap when existing.arrival < desired_departure AND desired_arrival < existing.departure. A safe correlated-subquery using NOT EXISTS looks like this:

SELECT r.*
FROM Room r
WHERE r.room_type = 'single'
  AND NOT EXISTS (
    SELECT 1
    FROM Booking b
    WHERE b.room_no = r.room_no
      AND b.arrival_date < 'YYYY-MM-DD'
      AND 'YYYY-MM-DD' < b.departure_date
  );

A common alternative is a LEFT JOIN filtering for NULL bookings (same overlap logic):

SELECT r.*
FROM Room r
LEFT JOIN Booking b
  ON b.room_no = r.room_no
  AND b.arrival_date < 'YYYY-MM-DD'
  AND 'YYYY-MM-DD' < b.departure_date
WHERE r.room_type = 'single'
  AND b.room_no IS NULL;

Practical checklist (useful when calling SQL from PHP/ASP as asked): run the raw query on the server (mysql CLI or phpMyAdmin) to get the exact error line, check that table and column names match case exactly, use straight single quotes (not curly), verify arrival_date/departure_date are DATE or DATETIME types, and parameterize inputs to avoid injection. The MySQL subqueries docs are a helpful reference: Subqueries.

Recommended Answers

All 4 Replies

Member Avatar for pcschrottie

Whenever I do a query on localhost it retrieves a value, but when I try on live it shows an error

Windows doesn't care about capital letters. Linux/Unix does.


Michael

Member Avatar for Phaelax

well if it works on localhost, then the code is fine. There's probably a problem with the server you're trying to connect to. What error do you get?

Member Avatar for dsgnews

THIS WAS THE ERROR CODE:

#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near
'SELECT R.room_no
FROM room R, Booking B
WHERE R.room_no = B.roo

Member Avatar for DanceInstructor

Are you using a scripting language to send the query? Like php or asp? If so you will need to post the code you are using to submit the query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.