Assistance With PHP MySQL Query

[mcubedma]

Hello-

 

I am relatively new to PHP and MySQL and would appreciate any assistance with a problem I am having.

 

This is the code I am using:

<?php
$con = mysql_connect("localhost","UID","PASSWORD");
if (!$con)
{die('Could not connect: ' . mysql_error());}
mysql_select_db("DBNAME", $con);

$query  = "SELECT fname, lname, car_id, make FROM names LEFT JOIN cars on (names.name_id = cars.name_id) ORDER BY lname";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "{$row['fname']} {$row['lname']} <br>";
echo "{$row['car_id']} <br>";
echo "{$row['make']} <br>";	
}
?>

 

Here is my output:

FirstName LastName1

1

Volkswagon

 

FirstName LastName2

2

Toyota

 

FirstName Lastname2

3

Mercury

 

FirstName LastName2

4

Lexus

 

I would like the output to be:

FirstName LastName1

1

Volkswagon

 

FirstName LastName2

2

Toyota

 

3

Mercury

 

FirstName LastName2

4

Lexus

 

I would like people who have multiple cars to only have the name print once and the cars print underneath.

 

Thank you very much for your time.

[JAY6390]

<?php
$con = mysql_connect("localhost","UID","PASSWORD");
if (!$con)
{die('Could not connect: ' . mysql_error());}
mysql_select_db("DBNAME", $con);

$query  = "SELECT fname, lname, car_id, make FROM names LEFT JOIN cars on (names.name_id = cars.name_id) ORDER BY lname";
$result = mysql_query($query);
$last = '';
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    $name = $row['fname'].' '.$row['lname'];
    if($last != $name) {
        $last = $name;
        echo "{$row['fname']} {$row['lname']} <br>";
    }
echo "{$row['fname']} {$row['lname']} <br>";
echo "{$row['car_id']} <br>";
echo "{$row['make']} <br>";	
}
?>

[mcubedma]

Jay6390-Thank you for replying, but when I run your code change I get the following results:

First Name Last Name1

First Name Last Name1

 

 

First Name Last Name2

First Name Last Name2

1

Toyota

First Name Last Name2

2

Mercury

First Name Last Name3

First Name Last Name3

[rajivgonsalves]

try this, hope its helpful

 

<?php
$con = mysql_connect("localhost","UID","PASSWORD");
if (!$con)
{die('Could not connect: ' . mysql_error());}
mysql_select_db("DBNAME", $con);

$query  = "SELECT fname, lname, car_id, make FROM names LEFT JOIN cars on (names.name_id = cars.name_id) ORDER BY lname";
$result = mysql_query($query);

$records = array();
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    if (!is_array($records[$row['fname'].' '.$row['lname']])) $records[$row['fname'].' '.$row['lname']] = array(); 
    $records[$row['fname'].' '.$row['lname']][] = array('id'=> $row['car_id'], 'make' => $row['make']);   
}

foreach ($records as $name => $cars) {
    echo $name . '<br />';
    foreach ($cars as $car) {
       echo $car['id'] . '<br />';
       echo $car['make'] . '<br />';
    }
}
?>

[mcubedma]

Yes, that worked.  Thank you, Rajiv.  I appreciate your help.