Member Avatar for dsgnews

Hi Friends, I need your help.
Whenever I do a query on localhost it retrieves a value, but when I try on live it shows an error? What could be the problem?

  • 4 Contributors
  • 7 Replies
  • 180 Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by izzi

Dani AI

Generated

A few likely causes and a short diagnostic path to follow. The #1064 syntax error on the live server but not locally most often means either (a) the live MySQL server is an older build that does not accept the same SQL features (notably subqueries), (b) the SQL that actually reaches the live server is different or truncated (copy/paste or string building bug), or (c) a small syntax/quoting problem in the production copy. ’s suggestion to run the subquery on its own is sound, and ’s point about differing connection/privilege settings is also relevant.

Recommended quick checks:

  1. Compare server versions: run SELECT VERSION(); on both local and live and confirm feature parity.
  2. Run the inner SELECT alone on the live server — this isolates syntax vs. logic.
  3. Log or echo the exact SQL string sent by the application (to catch truncation, missing quotes, or stray control characters).
  4. If subqueries aren’t supported on the live server, rewrite the logic with joins or NOT EXISTS.

An alternative that avoids subqueries and handles date overlap correctly (adjust the operators to match inclusive/exclusive booking rules) is the LEFT JOIN / IS NULL pattern:

SELECT r.*
FROM room AS r
LEFT JOIN booking AS b
  ON r.room_no = b.room_no
  AND b.arrival_date < '2005-03-19'
  AND b.departure_date > '2005-03-16'
WHERE r.room_type = 'single'
  AND b.room_no IS NULL
LIMIT 30;

Additional notes: avoid NOT IN when the subquery can return NULL — use NOT EXISTS or LEFT JOIN ... IS NULL. Ensure relevant indexes exist (e.g., booking(room_no, arrival_date, departure_date) and room(room_type)) for performance. If the error remains after these checks, capture the exact SQL as sent to the server and the full (untruncated) error message for precise diagnosis.

Recommended Answers

All 7 Replies

Member Avatar for Member #33065

What do you mean by live?

Member Avatar for dsgnews

live I mean server

Member Avatar for Member #33065

Ok, tell me if I am correct here. WHen you query the database from the local server, you get results, but when you query it from a remote server you get an error. Is this correct?

Member Avatar for dsgnews

The error is as follows:

SELECT *
FROM room
WHERE room_no NOT
IN (
SELECT R.room_no
FROM room R, Booking B
WHERE R.room_no = B.room_no AND R.room_type = 'single' AND
B.arrival_date = '2005-03-16' AND B.departure_date = '2005-03-19'
)

LIMIT 0 , 30

#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.room

Member Avatar for Member #33065

without knowing the schema, the only suggestion I can give you is to try the subquery on its own. If this doesn't work, then you have an issue with your subquery. Try the select subquery withtout the where clause. Basically, break the query down into manageable steps.

Andy

Member Avatar for jwshepherd

make sure your database connection string is for remote access and not limited to local.

Member Avatar for izzi

How to maximaize the execution of mysql, coz i use a very large database. everytime i do query always comes up with maximum execution of 60 seconds elapse.., cant stand of that. is ther some way ??

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.