Help with order of code

[herghost]

Evening everyone

 

I wonder if you can help me with the below code

<table width="823" cellspacing="0" id="mytable" summary="User Transactions Summary"> 
<caption>Transaction Summary</caption> 


  <tr> 
    <th width="136" class="nobg" scope="col" abbr="Merchant Name">Merchant Name</th> 
    <th width="154" scope="col" abbr="Order Value">Order Value</th> 
    <th width="145" scope="col" abbr="Comm">Cash Back</th> 
<th width="278" scope="col" abbr="Status">Status</th> 
  </tr> 
<?php 
$result = mysql_query("SELECT * FROM transactions WHERE u_id = '$u_id' ORDER BY order_date DESC LIMIT 10 "); 
echo mysql_error() ;

while($row = mysql_fetch_assoc($result)){

$m_name = $row['m_name'];
$orderval = $row['order_val'];
$status = $row['order_status'];
$m_affid = $row['m_affid'];

$query = mysql_query(" SELECT percent FROM merchants WHERE m_affid = '$m_affid' LIMIT 1");
$num = mysql_num_rows($query);
$i=0;
while ($i < $num) {

$per=mysql_result($query,$i,"percent");

$i++;
}

$com1 = $orderval / 100;
$com2 = $com1 * $per;

$u_comm = round($com2,2);

?>
    

    

  <tr> 
    <th scope="row" abbr="<?php echo $m_name;?>" class="spec"><?php echo $m_name;?></th> 
    <td class="alt">&pound<?php echo $orderval ;?></td> 
    <td class="alt">&pound<?php echo $u_comm ;?></td> 
    <td class="alt"><?php echo $status ;?></td> 
    
  </tr> 
  <?php
}?>  

</table> 

 

Basically it is meant to simply show fields from a database in a table.

 

If there is only 1 entry for  a u_id it works fine, however if their is more than one then the $u_comm value shows the same for all results, which is for the last result/row found.

 

Hope this makes sense!

 

[jcbones]

Let me know if it doesn't work, please specify any problems fully.

<table width="823" cellspacing="0" id="mytable" summary="User Transactions Summary"> 
<caption>Transaction Summary</caption> 
  <tr> 
    <th width="136" class="nobg" scope="col" abbr="Merchant Name">Merchant Name</th> 
    <th width="154" scope="col" abbr="Order Value">Order Value</th> 
    <th width="145" scope="col" abbr="Comm">Cash Back</th> 
<th width="278" scope="col" abbr="Status">Status</th> 
  </tr> 
<?php 
$result = mysql_query("SELECT t.*, m.percent FROM transactions JOIN merchants ON t.m_affid = m.m_affid WHERE u_id = '$u_id' ORDER BY t.order_date DESC LIMIT 10 "); 
echo mysql_error() ;
while($row = mysql_fetch_assoc($result)){	

$m_name = $row['m_name'];
$orderval = $row['order_val'];
$status = $row['order_status'];
$per = $row['percent'];


$com1 = $orderval / 100;
$com2 = $com1 * $per;
$u_comm = round($com2,2);
?>
  <tr> 
    <th scope="row" abbr="<?php echo $m_name;?>" class="spec"><?php echo $m_name;?></th> 
    <td class="alt">&pound<?php echo $orderval ;?></td> 
    <td class="alt">&pound<?php echo $u_comm ;?></td> 
    <td class="alt"><?php echo $status ;?></td> 
    
  </tr> 
  <?php
}?>  

</table> 

[herghost]

Hi Thanks,

 

I am getting unknown table t error?

 

 

 

 

[Pikachu2000]

Looks like he simply overlooked the AS t alias.

"SELECT t.*, m.percent FROM transactions AS t JOIN . . .

[herghost]

Thanks, do I have to do something with m. as well as I am now getting Unknown column 'm.percent' in 'field list'?

 

Just looked up basic joins on google and they make little sense to me at the moment!

[Pikachu2000]

JOIN merchants AS m ON . . . 

 

[herghost]

$result = mysql_query("SELECT t.*, m.percent FROM transactions AS t JOIN merchants AS m ON merchants ON t.m_affid = m.m_affid WHERE u_id = '$u_id' ORDER BY t.order_date DESC LIMIT 10 "); 

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON t.m_affid = m.m_affid WHERE u_id = '23' ORDER BY t.order_date DESC LIMIT 10' at line 1

 

I think I have a lot to learn.........

[Pikachu2000]

You ended up with an extra 'merchants ON' in there. Here's the whole thing.

"SELECT t.*, m.percent FROM transactions AS t JOIN merchants AS m ON t.m_affid = m.m_affid WHERE u_id = '$u_id' ORDER BY t.order_date DESC LIMIT 10"

[herghost]

Thanks again, it now displays the data, however I still have same problem I started with! The % are the same for both

[jcbones]

Yep, I patched that in and forgot to alias the table names.

 

Give us a dump of your table data, and we can get it sorted out.