query problem?

[sylunt1]

Hello...

Im having issues trying to get the desired output... What I want is this:
I have a license # that is assigned to multiple people - I want it to list the user names that are assigned. As it is now - it prints the license info and a user name, the license info again and the next user name and so on.

Any assistance is appreciated.

<?php

 $lic_id = $_POST['licid'];

$result = mysql_query ("SELECT DATE_FORMAT(license.exp_date, '%M %d, %Y') AS exp_date1, usersw.user_id, usersw.lic_id, 				users.user_id, users.user_name, license.lic_id, license.sw_id, license.lic_num, license.po_num, sw.sw_id, sw.sw_name, license.auth_num, DATE_FORMAT(license.purch_date, '%M %d, %Y') AS purch_date1, license.notes, license.quantity
			FROM users, usersw, license, sw 
			WHERE $lic_id = license.lic_id and license.lic_id = usersw.lic_id and users.user_id = usersw.user_id and license.sw_id = sw.sw_id and users.user_id = usersw.user_id
			ORDER BY license.lic_num, users.user_name") or die(mysql_error()); 

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

	$user_id = $row['user_id'];
	$user_name = $row['user_name'];
	$lic_id = $row['lic_id'];
	$lic_num = $row['lic_num'];
	$auth_num = $row['auth_num'];
	$purch_date = $row['purch_date1'];
	$exp_date = $row['exp_date1'];
            $sw_id = $row['sw_id'];
	$sw_name = $row['sw_name'];		
	$po_num = $row['po_num'];
	$notes = $row['notes'];
	$quantity = $row['quantity'];


print '<br /><h4>' . $lic_num . ' - ' . $sw_name . '</h4>'; 

print '<center><table border="0" width="90%" cellspacing="0" cellpadding="0">';
	echo '<tr><td width="40%">PO Number</td><td width="40%">' . $po_num . '</td></tr>
	<td width="40%">Authorization Number</td><td width="40%">' . $auth_num . '</td></tr>
	<td width="40%">Quantity</td><td width="40%">' . $quantity . '</td></tr>
	<td width="40%">Purchase Date</td><td width="40%">' . $purch_date . '</td></tr>
	<td width="40%">Expires</td><td width="40%">' . $exp_date . '</td></tr>	
	<td width="40%">Users</td><td width="40%">' . $user_name . '</td></tr>	
	<td width="10%">Notes</td><td width="70%">' . $notes . '</td></tr>			
	</table></center>';

}        	
?>

[bastien]

try this:

<?php

$lic_id = $_POST['licid'];

$sql = "SELECT 
          DATE_FORMAT(license.exp_date, '%M %d, %Y') AS exp_date1,
          usersw.user_id,
          usersw.lic_id,
          users.user_id, 
          users.user_name, 
          license.lic_id, 
          license.sw_id, 
          license.lic_num, 
          license.po_num, 
          sw.sw_id, 
          sw.sw_name, 
          license.auth_num, 
          DATE_FORMAT(license.purch_date, '%M %d, %Y') AS purch_date1, 
          license.notes, 
          license.quantity
        FROM 
          users, usersw, license, sw
        WHERE 
            $lic_id = license.lic_id
            and license.lic_id = usersw.lic_id
            and users.user_id = usersw.user_id 
            and license.sw_id = sw.sw_id 
            and users.user_id = usersw.user_id
        ORDER BY 
            license.lic_num, users.user_name";


$result = mysql_query ($sql) or die(mysql_error());

$old_lic = ''; //set a default value for a variable used store changes in lic_num
while($row = mysql_fetch_array($result))
        {

    $user_id    = $row['user_id'];
    $user_name  = $row['user_name'];
    $lic_id     = $row['lic_id'];
    $lic_num    = $row['lic_num'];
    $auth_num   = $row['auth_num'];
    $purch_date = $row['purch_date1'];
    $exp_date   = $row['exp_date1'];
    $sw_id      = $row['sw_id'];
    $sw_name    = $row['sw_name'];
    $po_num     = $row['po_num'];
    $notes      = $row['notes'];
    $quantity   = $row['quantity'];

//only echo the line if the $lic_num doesn't equal the $old_lic
if ($old_lic != $lic_num){  

  print '<br /><h4>' . $lic_num . ' - ' . $sw_name . '</h4>'; 
  $old_lic = $lic_num;
}

print '<center><table border="0" width="90%" cellspacing="0" cell';
print '<center><table border="0" width="90%" cellspacing="0" cellpadding="0">';
echo '<tr><td width="40%">PO Number</td><td width="40%">'        . $po_num     . '</td></tr>
      <td width="40%">Authorization Number</td><td width="40%">' . $auth_num   . '</td></tr>
      <td width="40%">Quantity</td><td width="40%">'             . $quantity   . '</td></tr>
      <td width="40%">Purchase Date</td><td width="40%">'        . $purch_date . '</td></tr>
      <td width="40%">Expires</td><td width="40%">'              . $exp_date   . '</td></tr>
      <td width="40%">Users</td><td width="40%">'                . $user_name  . '</td></tr>
      <td width="10%">Notes</td><td width="70%">'                . $notes      . '</td></tr>
      </table></center>';

}            

?>