Member Avatar for narasimha9

display city_name using two tables. I have written two queries but unable to display the results. First Table name is tbl_city and it has id,state_id,city_id,city_name. Other table name is tbl_states and it has id,state_id,state_name

$state_id=$_REQUEST['state_id'];
$query="select c.city_name FROM tbl_city AS c,tbl_states AS s where c.state_id='$state_id' AND  s.state_id='$state_id'";(two tables using alias)
$query="select tbl_city.city_name FROM tbl_city INNER JOIN tbl_states ON tbl_states.state_id='$state_id' AND tbl_city.state_id='$state_id'";(using Inner join)
  • 3 Contributors
  • 2 Replies
  • 254 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

— your attempts are close; the most likely problems are the join condition/syntax, input handling, or that you don't actually need a join if you only want city names. is right that a single query is enough, and correctly flagged the stray "AND" after ON.

Quick checklist and practical fixes:

  • Confirm the exact schema and column names. If tbl_states uses id as the PK while tbl_city uses state_id as the FK, join on those exact columns. A mismatch of id vs state_id is a common cause of empty results.
  • If you only need city names for a state, you can query tbl_city alone (no join). Only join when you need fields from tbl_states.
  • Validate and bind the incoming value instead of interpolating $_REQUEST directly. Use $_GET/$_POST as appropriate and cast or validate the value to prevent SQL injection and type mismatches.
  • Debugging tips: echo or log the final SQL, run it in phpMyAdmin, check MySQL errors (mysqli_error or PDO exceptions), and var_dump the incoming state_id to ensure it contains what you expect.

Safer example (PDO, parameterized):

$stmt = $pdo->prepare(
  "SELECT c.city_name, s.state_name
   FROM tbl_city AS c
   JOIN tbl_states AS s ON c.state_id = s.state_id
   WHERE s.state_id = :state_id"
);
$stmt->execute([':state_id' => (int)$state_id]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Also ensure state_id is indexed in both tables for performance, and when iterating results use the correct array key (city_name). Following the schema-check + prepared-statement + basic debug steps will normally reveal the issue quickly.

Recommended Answers

All 2 Replies

Member Avatar for urtrivedi

Only you need one query as following

$state_id=$_REQUEST['state_id'];

$query="select tbl_city.city_name,tbl_states.state_name  FROM tbl_city INNER JOIN tbl_states ON  AND tbl_city.state_id=tbl_states.state_id where tbl_states.state_id='$state_id' "
Member Avatar for Reverend Jim

I think the ON AND in the above post should just be ON.

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.