php function to show value of one table column from another table column

[HalRau]

Hi,

I have two tables in the same database that contain the same transaction_id

In the one table the amount of the transcation is also stored.

 

I would like to display the amount in a listing of other values from the one table by matching the transaction_ id values in the two tables.

 

Here is the code I have tried.

<?php do { ?>
    <?php if ($totalRows_rsSubscribers > 0) { // Show if recordset not empty ?>
      <tr>
        <td><?php echo $row_rsSubscribers['Sub_lastname']; ?>, <?php echo $row_rsSubscribers['Sub_firstname']; ?> 
          <?php echo $row_rsSubscribers['Sub_address']; ?> 
          <?php echo $row_rsSubscribers['Sub_city']; ?> <?php echo $row_rsSubscribers['Sub_state']; ?>, <?php echo $row_rsSubscribers['Sub_zip']; ?></td>
        <td align="center"><input <?php if (!(strcmp($row_rsSubscribers['Sub_paidbycheck'],1))) {echo "checked=\"checked\"";} ?> name="Sub_paidbycheck" type="checkbox" value=""></td>
        <td><?php echo $row_rsSubscribers['Sub_payment_amount']; ?></td>
       
        <td><?php echo $row_rsSubscribers['Sub_tranxID']; ?></td>
Code starts here         <td><?php $name = $_GET["Sub_tranxID"];
$sql = "SELECT `amount` FROM dc_donations WHERE transaction_id='$name'";
$result = mysql_query($sql);
$row=mysql_fetch_array($result)
This is line 224 $value = $row[0]; ;?></td> Code ends here
        <td><?php echo $row_rsSubscribers['Sub_date']; ?></td>
        <td align="center"><input <?php if (!(strcmp($row_rsSubscribers['Sub_nopaper'],1))) {echo "checked=\"checked\"";} ?> name="Sub_nopaper" type="checkbox" value=""></td>
        <td> </td>
      </tr>
      <?php } // Show if recordset not empty ?>
    <?php } while ($row_rsSubscribers = mysql_fetch_assoc($rsSubscribers)); ?>
  <?php if ($totalRows_rsSubscribers == 0) { // Show if recordset empty ?>

Here is the error I am getting. Parse error: syntax error, unexpected T_VARIABLE in /...../admin-memberadmin-list.php on line 224

 

Hope someone has a solution

Thank you for your help.

 

[Barand]

you are missing the ; from end of 223

[HalRau]

Thank you Guru.

Adding ; at the end of line 223 removed the error but no value for amount is showing up in the table cell.

Tried changing line 220 to  <td><?php $name = $row_rsSubscribers['Sub_tranxID'];

but still blank.

Any ideas?

[Barand]

I don't see where you attempt to output the amount anywhere in that code .

 

And instead of a second query to get the amount you should be using a JOIN in the original query

Edited February 28, 2015 by Barand

[Barand]

You should be using mysqli or PDO functions and not the deprecated mysql function. You don't show your first query but piecing together what I can see it shou look something like this

$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);

$sql = "SELECT sub_lastname
        , sub_firstname
        , sub_address
        , sub_city
        , sub_state
        , sub_zip
        , sub_paidbycheck
        , sub_tranxID
        , sub_payment_amount
        , sub_nopaper
        , sub_date
        , d.amount as donation
    FROM subscriber s
        LEFT JOIN dc_donations d ON d.transaction_id = s.sub_tranxID
    ";
$smt = $db->prepare($sql);
$smt->execute();
$smt->bind_result($ln,$fn,$add,$cty,$state,$zip,$pbc,$stid,$amt,$nopaper,$date,$donation);
while ($smt->fetch() ) {
    $chk_pbc = $pbc==1 ? 'checked="checked"' : '';
    $chk_nop = $nopaper==1 ? 'checked="checked"' : '';
    echo "tr>
            <td>$ln,$fn,$add,$cty,$state,$zip<td>
            <td><input $chk_pbc name='Sub_paidbycheck' type='checkbox' value='1'></td>
            <td>$amt</td>
            <td>$stid</td>
            <td>$donation</td>
            <td>$date</td>
            <td><input $chk_nop name='Sub_nppaper' type='checkbox' value='1'></td>
            <td> </td>
      </tr>";
}
?>

[HalRau]

Thanks Guru,

With some modifications the code worked using JOIN

[HalRau]

Oops! Just found a problem using JOIN. The table LSFM_Subsribers contains records of both "PayPal subscribers" and "Paid by Check subscribers." In the case of Paid by Check subscribers there is no corresponding record in the dc_donations table.

SO, I figured out a way to store the donation amout in the Subscribers table as it is created. I no longer need the JOIN.

I really appreciate the time you spent on this and have saved the code so I can use it in the future should the need arise.