result multiple table data?

Question

m-hrt 0 Light Poster

17 Years Ago

i have 3 tables i want to show the data from them to a page.

table1 "trip"
table2 "seat"
table3 "user_information"

show all the data of table one which is working its showing but how can i show multiple table data any ways.

show all data of trip and seat

from user_information it shows

first_name
last_name

all the tables have id with the same name.

here is the code im using.

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

mysql_select_db("online_bus_project", $con);

$result = mysql_query("SELECT * FROM trip, seat, user_information ORDER BY `id` DESC LIMIT 1");  


echo "<table border='1'>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>From</th>
<th>To</th>
<th>Date</th>
<th>Fare</th>
<th>Seat</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['first_name'] . "</td>";
  echo "<td>" . $row['last_name'] . "</td>";
  echo "<td>" . $row['from'] . "</td>";
  echo "<td>" . $row['to'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";
  echo "<td>" . $row['fare'] . "</td>";
  echo "<td>" . $row['seat'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

database-design mysql php

4 Contributors
14 Replies
187 Views
2 Days Discussion Span
Latest Post 17 Years Ago Latest Post by m-hrt

Dani AI

Generated 6 Months Ago

Short summary and what most people miss: and were right to point you at joins, but a join that returns no rows usually means the columns you’re joining on aren’t the actual relationships (or you’ve got name collisions / reserved-word columns). Before changing code, verify the schema: which column is the trip primary key, which column (if any) on seat points to trip, and which column links a seat to a user. Many beginners assume every table’s id is the same thing — often it isn’t.

Quick checklist to find the problem

Confirm column names/keys with DESCRIBE or SHOW CREATE TABLE for each table and note the actual foreign-key columns.
Run a simple single-table SELECT from each table to inspect sample id values — if values don’t match, your join will be empty.
Avoid SELECT * when joining tables that share column names (like id) — pick and alias columns so PHP array keys don’t get overwritten.
If columns are named from or to, quote them or alias them because they clash with SQL keywords.

Example (adjust to your actual foreign-key names)

SELECT
  u.first_name, u.last_name, u.contact_no,
  t.`from` AS trip_from, t.`to` AS trip_to, t.date, t.fare,
  s.seat
FROM trip AS t
LEFT JOIN seat AS s ON s.trip_id = t.id
LEFT JOIN user_information AS u ON u.id = s.user_id
ORDER BY t.id DESC
LIMIT 1;

Use LEFT JOIN while debugging so you still see trip rows even if a seat or user is missing. Fetch associative rows and var_dump() one row to see exactly which keys exist — that will show whether contact_no is present or being overwritten.

A few practical notes: select explicit columns and give them unique aliases, check mysql_num_rows()/rowCount, and move off the old mysql_* extension to mysqli or PDO for better error handling and prepared statements.

All 14 Replies

somedude3488 228 Nearly a Posting Virtuoso

17 Years Ago

Look into mysql joins or using the where clause and table aliases.

Ex.

SELECT * FROM trip t,seat s,user_information u WHERE t.id = s.id AND t.id = u.id

somedude3488 228 Nearly a Posting Virtuoso

17 Years Ago

Do you have a column named id in your table. That was just an example.

somedude3488 228 Nearly a Posting Virtuoso

17 Years Ago

It should work then. Something else has to be wrong.

add this: or die(mysql_error()); to the end of the mysql_query function. That way you can see if there are any database problems.

m-hrt commented: thanx for the help +1

Poojasrivastava 1 Newbie Poster

17 Years Ago

try this..

select  t.id,t.from,t.to,t.date,t.fare,s.seat,u.first_name,u.last_name from trip t,seat s,user_information u where t.id=s.id

m-hrt commented: thanx for the help +1

Reply to this topic

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.

m-hrt 0 Light Poster · Answer 1 · 2009-05-17T17:26:23+00:00

m-hrt 0 Light Poster

17 Years Ago

any one?

m-hrt 0 Light Poster · Answer 2 · 2009-05-17T20:47:05+00:00

Look into mysql joins or using the where clause and table aliases.
Ex.
SELECT * FROM trip t,seat s,user_information u WHERE t.id = s.id AND t.id = u.id

that's not working??? its not showing any error but its also not showing the result all the tables are blank

check this?

$result = mysql_query("SELECT * FROM trip t,seat s,user_information u WHERE t.id = s.id AND t.id = u.id");  





echo "<table border='1'>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>From</th>
<th>To</th>
<th>Date</th>
<th>Fare</th>
<th>Seat</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['first_name'] . "</td>";
  echo "<td>" . $row['last_name'] . "</td>";
  echo "<td>" . $row['from'] . "</td>";
  echo "<td>" . $row['to'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";
  echo "<td>" . $row['fare'] . "</td>";
  echo "<td>" . $row['seat'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);

Dani50 0 Newbie Poster · Answer 3 · 2009-05-17T20:54:13+00:00

Have you got WHERE twice? It is on my screen twice.
Dani.

m-hrt 0 Light Poster · Answer 4 · 2009-05-17T21:21:58+00:00

m-hrt 0 Light Poster

17 Years Ago

yes id is in every table? any solutions to this?

m-hrt 0 Light Poster · Answer 5 · 2009-05-17T21:34:50+00:00

Have you got WHERE twice? It is on my screen twice.
Dani.

that's one time after that is 'AND'

can any one show me how to display that correctly? with one table its working fi9 any ideas.

m-hrt 0 Light Poster · Answer 6 · 2009-05-17T22:18:20+00:00

Table (trip) show all from this
* id
* from
* to
* date
* fare

Table (seat) show all from this also
* id
* seat

Table (user_information) show only first_name and last_name
* first_name
* last_name
* email
* address
* city
* province
* contact_no

somedude3488 228 Nearly a Posting Virtuoso · Answer 7 · 2009-05-17T23:03:22+00:00

somedude3488 228 Nearly a Posting Virtuoso

17 Years Ago

user_information has no id.

m-hrt 0 Light Poster · Answer 8 · 2009-05-18T02:33:43+00:00

yes it has id i did mistake on writing its named same as id kkeith29?

any solution?

m-hrt 0 Light Poster · Answer 9 · 2009-05-18T04:57:20+00:00

i have done it like this but its not showing contact number why????

because its a number that's why?

m-hrt 0 Light Poster · Answer 10 · 2009-05-19T16:27:41+00:00

thanx for the help all really appreciate.

thanx
Poojasrivastava rep added also for kkeith29 thanx

result multiple table data?

Dani AI

Recommended Answers Collapse Answers

All 14 Replies

Recommended Answers