work_it_work Posted May 10, 2008 Share Posted May 10, 2008 I have one database with 3 tables. each table contains different informations. I want to display informations from 2 tables in the same time... is this possible? mysql : table1: rows: id, name, email, AID table2: rows: id, phone, address table3: rows: id, date, mobile i want to display from table1 id, name, email, and from table2 phone and address all the details in one query i have created the request but only for one table, here it is: $result = @mysql_query('SELECT id, name, email FROM table1'); if (!$result) { exit('<p>Error performing query: ' . mysql_error() . '</p>'); } while ($row = mysql_fetch_array($result)) { $id = $row['id']; $name = $row['name']; $email = $row['email']; echo '<p>' . "$id " . "$name " . "$email " . ' <a href="' . $_SERVER['PHP_SELF'] . '?deletename=' . $id . '">' . 'Delete this Name</a></p>'; Quote Link to comment Share on other sites More sharing options...
peranha Posted May 10, 2008 Share Posted May 10, 2008 you would use join statements // create query $query = "SELECT F.facilityname, M.miscid, M.building, M.camloc, M.cammount, M.notes, M.serial, M.dateinst, C.model, C.colorbw, C.type, C.lens FROM facility_cameras FC INNER JOIN facilities F INNER JOIN cameras C INNER JOIN misc M ON FC.facilityid = F.facilityid AND FC.cameraid=C.cameraid AND FC.miscid = M.miscid ORDER BY F.facilityname, M.building, M.camloc;"; facility_cameras is a table facilities is a table cameras is a table misc is a table Quote Link to comment Share on other sites More sharing options...
hamza Posted May 10, 2008 Share Posted May 10, 2008 Simply use joins in that situation We have different types of joins in mysql go to the website and read it by your self. Quote Link to comment Share on other sites More sharing options...
work_it_work Posted May 10, 2008 Author Share Posted May 10, 2008 Thanks for help, but i'm still confused because i didn't use join tables before... i'll post the entire php here, maybe someone can show me how it will look like using join tables. mysql : table1: rows: id, name, email, AID table2: rows: id, phone, address table3: rows: id, date, mobile i want to display from table1 id, name, email, and from table2 phone and address all the details in one query i have created the request but only for one table, here it is: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>The Internet name Database</title> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> </head> <body> <?php if (isset($_GET['addname'])): // User wants to add his name ?> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> <label>Type your Name here:<br /> <textarea name="name" rows="1" cols="1"> </textarea></label><br /> <label>Type your EMail here:<br /> <textarea name="email" rows="1" cols="1"> </textarea></label><br /> <label>Type your phone here:<br /> <textarea name="phone" rows="1" cols="1"> </textarea></label><br /> <input type="submit" value="SUBMIT" /> <label>Type your address here:<br /> <textarea name="address" rows="1" cols="1"> </textarea></label><br /> <input type="submit" value="SUBMIT" /> </form> <?php else: // Default page display // Connect to the database server $dbcnx = @mysql_connect('localhost', 'root', 'pass'); if (!$dbcnx) { exit('<p>Unable to connect to the ' . 'database server at this time.</p>'); } // Select the Names database if (!@mysql_select_db('db')) { exit('<p>Unable to locate the name ' . 'database at this time.</p>'); } // If a name has been submitted, // add it to the database. if (isset($_POST['name'])) { $name = $_POST['name']; $email = $_POST['email']; $sql = "INSERT INTO table1 SET name='$name', email='$email', date=CURDATE()"; if (@mysql_query($sql)) { echo '<p>Name added</p>'; } else { echo '<p>Error adding submitted name: ' . mysql_error() . '</p>'; } } if (isset($_POST['phone'],$_POST['address'])) { $phone = $_POST['phone']; $address = $_POST['address']; $sql = "INSERT INTO table2 SET phone='$phone', address='$address'"; if (@mysql_query($sql)) { echo '<p>proceed to next step</p>'; } else { echo '<p>Error adding submitted phone: ' . mysql_error() . '</p>'; } } // If a name has been deleted, // remove it from the database. if (isset($_GET['deletename'])) { $id = $_GET['deletename']; $sql = "DELETE FROM take1 WHERE id=$id"; if (@mysql_query($sql)) { echo '<p>The name has been deleted.</p>'; } else { echo '<p>Error deleting name: ' . mysql_error() . '</p>'; } } echo '<p> Here are all the names in our database: </p>'; $result = @mysql_query('SELECT id, name, email FROM table1'); if (!$result) { exit('<p>Error performing query: ' . mysql_error() . '</p>'); } while ($row = mysql_fetch_array($result)) { $id = $row['id']; $name = $row['name']; $email = $row['email']; echo '<p>' . "$id " . "$name " . "$email " . ' <a href="' . $_SERVER['PHP_SELF'] . '?deletename=' . $id . '">' . 'Delete this Name[/url]</p>'; echo '<p><a href="' . $_SERVER['PHP_SELF'] . '?addname=1">Add a Name</a></p>'; endif; ?> </body> </html> Please help me to figure how to modify this script. I want to display on the line """" echo '<p>' . "$id " . "$name " . "$email " . """" phone and address form the second table. thanks! Quote Link to comment Share on other sites More sharing options...
bilis_money Posted May 10, 2008 Share Posted May 10, 2008 @work_it_work reminder before you can use JOIN you need to assign reference ID for your tables first. to learn join please visit the link below. http://dev.mysql.com/doc/refman/5.0/en/join.html Quote Link to comment Share on other sites More sharing options...
work_it_work Posted May 10, 2008 Author Share Posted May 10, 2008 thanks bilis_money, it seems i have to study a lot ... Quote Link to comment Share on other sites More sharing options...
work_it_work Posted May 10, 2008 Author Share Posted May 10, 2008 well, i found the way to do it, i'll post here the solution table1 has AID column, so i use it as link between the 2 tables: $result = @mysql_query('SELECT id, name, email, AID FROM table1'); if (!$result) { exit('<p>Error performing query: ' . mysql_error() . '</p>'); } while ($row = mysql_fetch_array($result)) { $id = $row['id']; $name = $row['name]; $email = $row['email]; $aid = $row['AID']; $details = mysql_query( "SELECT phone, address FROM table2 WHERE id=$aid"); $dude = mysql_fetch_array($details); $phone = $dude['phone']; $address = $dude['address']; echo '<p>' . "$id " . "$name " . "$email " . "$phone " . "$address " . ' <a href="' . $_SERVER['PHP_SELF'] . '?deletename=' . $id . '">' . 'Delete this name</a></p>'; so, i guess i used "a join" hope this tutorial will help others to figure out how it works. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.