aztec Posted February 24, 2007 Share Posted February 24, 2007 I am testing out a new database and PhP to get information out of the 6 tables that make up the database. At the moment each table consists of 3 columns for testing purposes, id (auto inc), name and spouce_id (int). The names of the tables are gen_8, gen_8s, gen_9, gen_9s, gen_10 and gen_10s. The following code works but to me it looks very "messy" with 6 select calls for each page of information. <!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>Weston 1 Test 1</title> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> </head> <body> <?php // Connect to the database server $dbcnx = @mysql_connect('localhost', 'root', ''); if (!$dbcnx) { exit('<p>Unable to connect to the ' . 'database server at this time.</p>'); } // Select the weston_1 database if (!@mysql_select_db('weston_1')) { exit('<p>Unable to locate the weston_1 ' . 'database at this time.</p>'); } ?> <p>Here are all the names requested from the database:</p> <blockquote> <?php // Request the names of the people requested $result = @mysql_query("SELECT name FROM gen_8 WHERE id = '1'"); $result2 = @mysql_query("SELECT name FROM gen_8s WHERE spouce_id = '1'"); $result3 = @mysql_query("SELECT name FROM gen_9 WHERE id = '1'"); $result4 = @mysql_query("SELECT name FROM gen_9s WHERE spouce_id = '1'"); $result5 = @mysql_query("SELECT name FROM gen_10 WHERE id = '1'"); $result6 = @mysql_query("SELECT name FROM gen_10s WHERE spouce_id = '1'"); if (!$result) { exit('<p>Error performing query: ' . mysql_error() . '</p>'); } // Display the text of each joke in a paragraph while ($row = mysql_fetch_array($result)) {echo '<p>' . $row['name'] . '</p>';} while ($row = mysql_fetch_array($result2)) {echo '<p>' . $row['name'] . '</p>';} while ($row = mysql_fetch_array($result3)) {echo '<p>' . $row['name'] . '</p>';} while ($row = mysql_fetch_array($result4)) {echo '<p>' . $row['name'] . '</p>';} while ($row = mysql_fetch_array($result5)) {echo '<p>' . $row['name'] . '</p>';} ?> </blockquote> </body> </html> Is it possible to reduce the number of calls to the database? I am sure that it is better to try to get the code correct at this stage rather than later with 20+ columns in each table. Any help or direction would be gladly taken on board Kind Regards Quote Link to comment Share on other sites More sharing options...
sayedsohail Posted February 24, 2007 Share Posted February 24, 2007 HI, $result = @mysql_query("SELECT gen_8.name as name1, gen_9.name as name2 FROM gen_8,gen_9 WHERE gen_8.id = '1'"); Hope this solves your problem. please look at mysql.com website for a complete sql statement (http://dev.mysql.com/doc/refman/5.0/en/select.html) SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name; 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.