telsiin Posted May 2, 2007 Share Posted May 2, 2007 Hello to everyone I am sure that this question has come up in the past Ok so I have two table one is a refenece table,the second table actually hold the data I need what need to do is make a query for the second table base on the result of the first. the problem is I need to compare column data in the first table to the column/field NAME in the second table example Table one in Database test DROP TABLE IF EXISTS `fruitvendor` ; CREATE TABLE IF NOT EXISTS `fruitvendor` ( `Fruit` varchar(20) collate latin1_general_ ci NOT NULL, `State` varchar(2) collate latin1_general_ ci NOT NULL, `vendor1` char(2) collate latin1_general_ ci NOT NULL, `vendor2` char(2) collate latin1_general_ ci NOT NULL, `vendor3` char(2) collate latin1_general_ ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_ general_ci; INSERT INTO `fruitvendor` VALUES ('Apple', 'NY', 'y', 'n', 'n'), ('Pear', 'NJ', 'n', 'y', 'y'), ('Orange', 'FL', 'y', 'y', 'n'), ('Cherry', 'NJ', 'y', 'n', 'y'), ('Strawberry' , 'FL', 'n', 'y', 'n'), ('Peach ', 'GA', 'n', 'n', 'y'), ('Watermelon' , 'CA', 'y', 'y', 'n'), ('Grape', 'CA', 'n', 'y', 'y'), ('Pineapple' , 'FL', 'y', 'n', 'y'), ('Blueberry' , 'FL', 'y', 'y', 'y'); Table two DROP TABLE IF EXISTS `storevendor` ; CREATE TABLE IF NOT EXISTS `storevendor` ( `Storeid` int(3) NOT NULL, `totalpcs` int(5) NOT NULL, `Apple` int(3) NOT NULL, `Pear` int(3) NOT NULL, `Orange` int(3) NOT NULL, `Cherry` int(3) NOT NULL default '0', `Strawberry` int(3) NOT NULL, `Peach` int(3) NOT NULL, `Watermelon` int(3) NOT NULL, `Grape` int(3) NOT NULL, `Pineapple` int(3) NOT NULL, `Blueberry` int(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_ general_ci; INSERT INTO `storevendor` VALUES (123, 2011, 20, 15, 60, 0, 15, 45, 41, 900, 15, 900), (124, 251, 15, 16, 25, 0, 52, 15, 34, 15, 15, 64), (125, 480, 56, 63, 52, 0, 54, 56, 25, 34, 45, 95), (126, 275, 25, 45, 15, 0, 75, 25, 50, 15, 15, 20); My Script The problem is that the following rows for column (Items Left)are not completed in the following loops <?php $hostname_test = "localhost"; $database_test = "test"; $username_test = "XXXXX";//please change to your username $password_test = "XXXXX"; // please change to your password $test = mysql_pconnect( $hostname_ test, $username_test, $password_test) or trigger_error( mysql_error( ),E_USER_ ERROR); // 'y' would equel that the vendor has that fruit in stock $maxRows_Recordset1 = 10; $pageNum_Recordset1 = 0; if (isset($_GET[ 'pageNum_ Recordset1' ])) { $pageNum_Recordset1 = $_GET['pageNum_ Recordset1' ]; } $startRow_Recordset 1 = $pageNum_Recordset1 * $maxRows_Recordset1 ; mysql_select_ db($database_ test, $test); $query_Recordset1 = "SELECT fruitvendor. Fruit, fruitvendor. `State`, fruitvendor. vendor1 FROM fruitvendor WHERE fruitvendor. vendor1=' y'"; $query_limit_ Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset 1, $maxRows_Recordset1 ); $Recordset1 = mysql_query( $query_limit_ Recordset1, $test) or die (mysql_error( )); $row_Recordset1 = mysql_fetch_ assoc($Recordset 1); if (isset($_GET[ 'totalRows_ Recordset1' ])) { $totalRows_Recordse t1 = $_GET['totalRows_ Recordset1' ]; } else { $all_Recordset1 = mysql_query( $query_Recordset 1); $totalRows_Recordse t1 = mysql_num_rows( $all_Recordset1) ; } $totalPages_ Recordset1 = ceil($totalRows_ Recordset1/ $maxRows_Recordset1 )-1; // 'n' would equel that the vendor does NOT have that fruit in stock $maxRows_Recordset2 = 10; $pageNum_Recordset2 = 0; if (isset($_GET[ 'pageNum_ Recordset2' ])) { $pageNum_Recordset2 = $_GET['pageNum_ Recordset2' ]; } $startRow_Recordset 2 = $pageNum_Recordset2 * $maxRows_Recordset2 ; mysql_select_ db($database_ test, $test); $query_Recordset2 = "SELECT fruitvendor. Fruit, fruitvendor. `State`, fruitvendor. vendor1 FROM fruitvendor WHERE fruitvendor. vendor1=' n'"; $query_limit_ Recordset2 = sprintf("%s LIMIT %d, %d", $query_Recordset2, $startRow_Recordset 2, $maxRows_Recordset2 ); $Recordset2 = mysql_query( $query_limit_ Recordset2, $test) or die (mysql_error( )); $row_Recordset2 = mysql_fetch_ assoc($Recordset 2); if (isset($_GET[ 'totalRows_ Recordset2' ])) { $totalRows_Recordse t2 = $_GET['totalRows_ Recordset2' ]; } else { $all_Recordset2 = mysql_query( $query_Recordset 2); $totalRows_Recordse t2 = mysql_num_rows( $all_Recordset2) ; } $totalPages_ Recordset2 = ceil($totalRows_ Recordset2/ $maxRows_Recordset2 )-1; // use the result from query Recordset2 mysql_select_ db($database_ test, $test); $query_Recordset3 = "SELECT {$row_Recordset2[ 'Fruit']} FROM storevendor WHERE storevendor. Storeid=123" ; $Recordset3 = mysql_query( $query_Recordset 3, $test) or die (mysql_error( )); $row_Recordset3 = mysql_fetch_ assoc($Recordset 3); $totalRows_Recordse t3 = mysql_num_rows( $Recordset3) ; // use the result from query Recordset1 mysql_select_ db($database_ test, $test); $query_Recordset4 = "SELECT {$row_Recordset1[ 'Fruit']} FROM storevendor WHERE storevendor. Storeid=123" ; $Recordset4 = mysql_query( $query_Recordset 4, $test) or die (mysql_error( )); $row_Recordset4 = mysql_fetch_ assoc($Recordset 4); $totalRows_Recordse t4 = mysql_num_rows( $Recordset4) ; ?> <table border="1" cellpadding= "1" cellspacing= "2"> <tr> <td width="129" bgcolor="#3366CC" ><strong> Fruit</strong> </td> <td width="132" bgcolor="#3366CC" ><strong> State</strong> </td> <td width="147" bgcolor="#3366CC" ><strong> vendor1</ strong></ td> <td width="85" bgcolor="#3366CC" ><strong> Items left </strong></td> <td width="86" bgcolor="#3366CC" ><strong> Item bought </strong></td> <td width="104" bgcolor="#3366CC" ><strong> Sub Total </strong></td> </tr> <?php do { ?> <tr> <td><strong> <?php echo $row_Recordset1[ 'Fruit']; ?></strong>< /td> <td><strong> <?php echo $row_Recordset1[ 'State']; ?></strong>< /td> <td><strong> <?php echo $row_Recordset1[ 'vendor1' ]; ? ></strong></ td> <td><?php echo $row_Recordset4{ $row_Recordset1[ 'Fruit']} ; ?></td> <td> </ td> <td> </ td> </tr> <?php } while (@$row_Recordset1 = mysql_fetch_ assoc ($Recordset1) ); ?> </table> <p> </p> <table border="1" cellpadding= "1" cellspacing= "2"> <tr> <td width="129" bgcolor="#FF0000" ><strong> Fruit</strong> </td> <td width="132" bgcolor="#FF0000" ><strong> State</strong> </td> <td width="147" bgcolor="#FF0000" ><strong> vendor1</ strong></ td> <td width="85" bgcolor="#FF0000" ><strong> Items left </strong></td> <td width="86" bgcolor="#FF0000" ><strong> Item bought </strong></td> <td width="104" bgcolor="#FF0000" ><strong> Sub Total </strong></td> </tr> <?php do { ?> <tr> <td><strong> <?php echo $row_Recordset2[ 'Fruit']; ? ></strong></ td> <td><strong> <?php echo $row_Recordset2[ 'State']; ? ></strong></ td> <td><strong> <?php echo $row_Recordset2[ 'vendor1' ]; ? ></strong></ td> <td><?php echo $row_Recordset3{ $row_Recordset2[ 'Fruit']} ; ? ></td> <td> </ td> <td> </ td> </tr> <?php } while (@$row_Recordset2 = mysql_fetch_ assoc ($Recordset2) ); ?> </table> <?php mysql_free_result( $Recordset1) ; mysql_free_result( $Recordset2) ; mysql_free_result( $Recordset3) ; mysql_free_result( $Recordset4) ; ?> Quote Link to comment Share on other sites More sharing options...
trq Posted May 2, 2007 Share Posted May 2, 2007 You should avoid multiple queries where possible. The data is related, so use a JOIN statement. Quote Link to comment Share on other sites More sharing options...
solarisuser Posted May 2, 2007 Share Posted May 2, 2007 It might take a little bit of time to get comfortable with INNER JOIN, but look into it. tizag has some good tutorials 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.