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 Link to comment https://forums.phpfreaks.com/topic/39899-mysql_queryselect/ 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; Link to comment https://forums.phpfreaks.com/topic/39899-mysql_queryselect/#findComment-192904 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.