PHP Select ALL not showing ALL results?

[sblake161189]

Hi Guys,

 

I am normally switched on when it comes to PHP mysql_query's but for some reason I cannot work this one out. Its probably something daft!

 

I have a SELECT * query's which then displays results in the table.

 

But for some reason it isnt showing every result (i.e. some properties are missing). I have worked out they are the properties where there is no customer, orderdetail or payment linked to it.

 

I would like it to show ALL results regardless of wether a customer, order or payment is attached it.

 

Here is my code:

 

<?php

// Find All Properties
$resulta = mysql_query("SELECT * FROM yea_properties ORDER BY PROPERTY_ID DESC");
if(mysql_num_rows($resulta)==0) {
echo "No properties in database.";
} else {

?>

<table width="990" border="0">
  <tr>
    <td><strong>Property ID</strong></td>
    <td><strong>Address</strong></td>
    <td><strong>Created</strong></td>
    <td><strong>Customer</strong></td>
    <td><strong>Order</strong></td>
  </tr>

<?php

while($property=mysql_fetch_array($resulta)){ 
$propertyID =  $property['PROPERTY_ID'];

// Find Details of Prop/Customer Link
$resultb = mysql_query("SELECT * FROM yea_link_prop_cust WHERE PROPID = '$propertyID'");
$link = mysql_fetch_array($resultb);
$userID = $link['USERID'];

// Find Details of Customer
$resultc = mysql_query("SELECT * FROM yea_customers WHERE USERID = '$userID'");
while($customer=mysql_fetch_array($resultc)){ 

// Find Details of Latest Order with Property
$resultd = mysql_query("SELECT * FROM yea_orderdetails WHERE DetailPropertyID = '$propertyID' ORDER BY DetailOrderID DESC Limit 0,1");
while($order=mysql_fetch_array($resultd)){ 
$orderID =  $order['DetailOrderID'];

// Find Details of Payment
$resulte = mysql_query("SELECT * FROM yea_payments WHERE orderID = '$orderID'");
$payment = mysql_fetch_array($resulte);
$paymentID = $payment['paymentID'];

?>

<tr>
    <td><a href="view-property.php?propertyID=<?php echo $propertyID; ?>">View Property #<?php echo $propertyID; ?></a></td>
    <td><?php echo $property['ADDRESS_1']; ?> <?php echo $property['ADDRESS_2']; ?>, <?php echo $property['ADDRESS_3']; ?>, <?php echo $property['TOWN']; ?>, <?php echo $property['POSTCODE1']; ?> <?php echo $property['POSTCODE2']; ?></td>
    <td><?php echo $property['CREATE_DATE']; ?></td>
    <td><a href="view-customer.php?userID=<?php echo $customer['USERID']; ?>">Customer #<?php echo $customer['USERID']; ?> (<?php echo $customer['FIRST_NAME']; ?> <?php echo $customer['LAST_NAME']; ?>)</a></td>
    <td><a href="view-order.php?orderID=<?php echo $orderID; ?>&paymentID=<?php echo $paymentID; ?>">View Order #<?php echo $orderID; ?></a></td>
  </tr>

<?php }}}} ?>

</table>

 

Any help would be much appreciated 

[trq]

Sorry, but that code needs to be scraped all together and a fresh start made. Running queries within loops is never a good idea.

 

There is a construct in sql called a JOIN, you should do some research into them.