select columns base on a row from prevoius query

[telsiin]

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) ;
?>

[trq]

You should avoid multiple queries where possible. The data is related, so use a JOIN statement.

[solarisuser]

It might take a little bit of time to get comfortable with INNER JOIN, but look into it.

 

tizag has some good tutorials