matthewst Posted April 19, 2007 Share Posted April 19, 2007 <?php include('user_check.php'); include('db_con.php'); ?> <html> <head> <title>Reports</title> </head> <body bgcolor="white"> <font face="Verdana, Arial, Helvetica, sans-serif"> <center>Ad Number<br><br></center> <?php $tableid=68; echo "<center><strong>$tableid</strong><br><br></center>"; $query="SELECT * FROM ad_order WHERE cust_id=$tableid"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $i=0; while ($i < $num) {$id=mysql_result($result,$i,"id"); ////////////////////////////////////////////////////////////////// //////I need something like query=SELECT time FROM job_log WHERE id=$id //////echo $id $time associated with that id //////then go on with the loop ////////////////////////////////////////////////////////////////// echo "<center>$id<br></center>"; $i++;} ?> </body> </html> My page looks like this: Table ID id# id# id# id# What I need is this: Table ID id# time id# time id# time id# time Ben Stein voice "Anyone, anyone?" Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/ Share on other sites More sharing options...
soycharliente Posted April 19, 2007 Share Posted April 19, 2007 It's perfectly fine to do another query inside your while loop. Just write another one. <?php $qry1 = "SELECT * FROM table1"; $rslt1 = mysql_query($qry1); while($r = mysql_fetch_array($rslt1) { $var1 = $r["var1"]; $qry2 = "SELECT something FROM table2 WHERE var='$var1'"; $rslt1 = mysql_query($qry2); while($r = mysql_fetch_array($rslt1) { // more code } } ?> Hope that helps. Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-233516 Share on other sites More sharing options...
Psycho Posted April 19, 2007 Share Posted April 19, 2007 That is very poor programming. You should never do nested queries as it can cause serious overhead. Plus, you are defeating the whole purpose of having a relational database. You can get all the data you need with just one query: SELECT ad_order.*, job_log.time FROM ad_order, job_log WHERE ad_order.id = job_log.id AND cust_id=$tableid Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-233523 Share on other sites More sharing options...
per1os Posted April 19, 2007 Share Posted April 19, 2007 <?php $tableid=68; echo "<center><strong>$tableid</strong><br><br></center>"; $query="SELECT ad_order.id id, job_log.time time FROM ad_order LEFT JOIN (job_log) ON (job_log.id=ad_order.id) WHERE ad_order.cust_id=$tableid"; $result=mysql_query($query); while ($row = mysql_fetch_assoc($result)) { echo "<center>" . $row['id'] . " " . $row['time'] . "<br></center>"; } mysql_close(); // not really necessary but should be after you are done with MySQL ?> </body> </html> It is always good to consolidate querys when you can. Note I was just guessing on the ad_order.id and the job_log.id, you need to change those to what would make logical sense there. Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-233525 Share on other sites More sharing options...
boo_lolly Posted April 19, 2007 Share Posted April 19, 2007 in charlieholder's defense, i've always used nested while loops, but only because i never found the time to really study mysql for all of its diverse functions. i guess i should start now... also, the scripts i write are never used to an extendable degree, however, i will be working on projects in the future where i would expect hundreds of thousands of queries to be executed every day. this is good to know. thank's for the tip frost and mjdamato! Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-233534 Share on other sites More sharing options...
per1os Posted April 19, 2007 Share Posted April 19, 2007 There are better ways to do it than a nested query because think of this. Let's say that I have 100 Entries for the above, 100 id's k? In order to get that using the nested query's it would take 101 queries to retrieve that data. That is an absurd amount of queries and will tie up your MySQL and slow down your system a TON. Now getting 100 IDs and Time in 1 query, takes maybe .0001 seconds longer without the load on the server. Nested querys should never be an answer. Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-233536 Share on other sites More sharing options...
boo_lolly Posted April 19, 2007 Share Posted April 19, 2007 There are better ways to do it than a nested query because think of this. Let's say that I have 100 Entries for the above, 100 id's k? In order to get that using the nested query's it would take 101 queries to retrieve that data. That is an absurd amount of queries and will tie up your MySQL and slow down your system a TON. Now getting 100 IDs and Time in 1 query, takes maybe .0001 seconds longer without the load on the server. Nested querys should never be an answer. thanks for the explaination bud Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-233551 Share on other sites More sharing options...
matthewst Posted April 20, 2007 Author Share Posted April 20, 2007 all you guys are frickin awesome, thanks for the help got one more for ya what format of time stamp is this "1176903017" and how do i convert it to regular date and time this is the "insert datetime" code written by my predecessor $now=(mktime()-21600); here is my current page: <?php include('include/user_check.php'); include('include/db_con.php'); $id = $_SESSION['track_id']; ?> <html> <BODY BGCOLOR=#FFFFFF leftmargin="0" marginwidth="0" topmargin="0" marginheight="0"> <div align="center"> <TABLE WIDTH=758 BORDER=0 CELLPADDING=0 CELLSPACING=0> <? include('include/top.php'); ?> <TR height="516"> <TD valign="top" height="516"> <div align="center"> <center>Table ID<br></center> <?php echo "<center><strong>$rest_name</strong><br><br></center>"; echo "<center><strong>$table_id</strong><br><br></center>"; $query="SELECT ad_order.company company, ad_order.date_ordered date_ordered, job_log.time time FROM ad_order LEFT JOIN (job_log) ON (job_log.id=ad_order.id) WHERE ad_order.cust_id=$table_id"; $result=mysql_query($query); while ($row = mysql_fetch_assoc($result)) { echo "<center>" . $row['company'] . " " . $row['date_ordered'] . " " . $row['time'] . "<br></center>"; } mysql_close(); ?> </body> </html> "time" comes back as the timestamp i need converted Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-234236 Share on other sites More sharing options...
per1os Posted April 20, 2007 Share Posted April 20, 2007 www.php.net/date Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-234243 Share on other sites More sharing options...
matthewst Posted April 20, 2007 Author Share Posted April 20, 2007 I've got php.net bookmarked, but I'm still a noob. So alot of it is still hard to understand. Anyway I'm trying to do something like this: echo "<center>" . $row['company'] . " " . $row['date_ordered'] . " " . $row['date('m/d/y - h/m/s',time')] . "<br></center>"; Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-234261 Share on other sites More sharing options...
per1os Posted April 20, 2007 Share Posted April 20, 2007 date('m/d/y', $row['time']) Quote Link to comment https://forums.phpfreaks.com/topic/47801-use-results-from-one-query-in-another-query-on-same-page/#findComment-234273 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.