Marios Nikolaou Marios Nikolaou - 1 year ago 153
MySQL Question

Retrieve distinct values from table

I have to print customer name once and all the products for each customer.My code is below.

<div id="Allproducts">
<?php
$AllprodsRes = $conn -> query("select * from sepproducts");
if($AllprodsRes ->num_rows > 0){
$result = $AllprodsRes -> fetch_array();
?>
<label for="name"><?php echo $result['name'] . " " . $result['surname']; ?></label>
<?php } ?>
<?php do{ ?>

<p><?php echo $result['product_name'] . " " //$result['count']; ?></p>
<?php }while($result = $AllprodsRes -> fetch_array()); ?>
</div>


view sepproducts

CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `sepproducts` AS
select
`customers`.`name` AS `name`,
`customers`.`surname` AS `surname`,
`custproducts`.`product_name` AS `product_name`,
count(0) AS `count`
from
(`custproducts`
join `customers` ON ((`custproducts`.`custid` = `customers`.`custid`)))
group by `custproducts`.`product_name`


Any help is welcome and appreciated.
Thanks in advance.

Answer Source

What you can use is something like the following (assuming you use MySQLi):

<?php
$con = new mysqli('localhost', 'username', 'password', 'db');
$query = $con->query('SELECT * FROM...');

$currentCustomer = null;
while ($result = $query->fetch_array()) {
    $name = $result['name'] . ' ' . $result['surname'];

    // Check to see if we're working with a new customer.
    if ($currentCustomer != $name) {
        echo $name . '<br />';
        $currentCustomer = $name;
    }

    echo $result['product_name'] . '<br />';
    echo $result['product_type'] . '<br />';

    // ETC.
}
?>

Or if you only have one customer to worry about, use the following:

<?php
$con = new mysqli('localhost', 'username', 'password', 'db');
$query = $con->query('SELECT * FROM...');

if ($query->num_rows > 0) {
    $result = $query->fetch_array();

    echo $result['name'] . ' ' . $result['surname'] . '<br />';

    do {
        echo $result['product_name'] . '<br />';
        echo $result['product_type'] . '<br />';

        // ETC.
    } while ($result = $query->fetch_array());
}
?>

In effect, it checks if records have been found and if so, writes one result to our array $result. We then output the customer's name OUTSIDE of the loop (so this only occurs once), then use a do...while() loop to continue through the rest of the result array.

I hope this helps!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download