MYSQL data to an array

Hello I wonder if anyone can help me.

I currently have a database which holds information about a financial services companies clients. I want to pull a list of all the clients who are married into a drop down box so you can look at their client records together.

So i need to sort out from the database all the client records that have a number in 'client_rel_id'. This corresponds to the 'client_id' of their husband or wife and vice versa.

The problem with my existing code:

<select name="couple_id">
<?
$editlistq = "SELECT * FROM client WHERE client_rel_id =! '' ORDER BY client_surname ASC";
//echo $editlistq;
$editlistre = mysql_query($editlistq);
$editlistrow = mysql_fetch_array($editlistre);

do{

$client_id = $editlistrow[0];
$client_name = $editlistrow[1];
$client_surname = $editlistrow[2];
?>
<option value="<?=$user_name?> <?=$user_surname?>">
<?=$user_name?> <?=$user_surname?>
</option>
<?
}while($editlistrow = mysql_fetch_array($editlistre));
?>
<option selected="selected">
<?=$client_adviser?>
</option>
</select>


Whats above is wrong and stolen from another bit of my code. But basically I can pull out the information as couples and present it in the drop down box.

BUT records are replicated.

ie: client 1, 2 and 5 are single
client 3 and 4 are married

using my code above i get the following output:


1
2
3 & 4
4 & 3
5

I need to get rid of the replication (ie '4 & 3').

I have been told this should be done using a multidimensional array to loop through the records setting client_ids to (-1) if they appear previously as a client_rel_id.

Could someone give me some rough code or a link to a tutorial which will show me how i can make an array from my mysql database?


Thanks to anyone that can help!!!! :)

AR

the code i posted above is wrong because im in a rush! gota leave work now.

hope someone can help from my 'sketchy' outline of the problem! :o

nobody got any ideas? :[

Firstly, no offense, but your code is pretty nasty so I've rewritten what you've already quoted.

echo "
<select name='couple_id'>";

$editlistq = "SELECT * FROM client WHERE client_rel_id =! '' ORDER BY client_surname ASC";
//echo $editlistq;
$editlistre = mysql_query($editlistq);
$editlistrow = mysql_fetch_array($editlistre);

while($editlistrow = mysql_fetch_array($editlistre)){

echo"
<option value='" . $editlistrow['user_name'] . " " . $editlistrow['user_surname'] . "'>" . $editlistrow['user_name'] . " " . $editlistrow['user_surname'] . "</option>";

}

echo"
<option selected='selected'>" . $client_adviser . "</option>

</select>";

mysql_fetch_array() will output both mysql_fetch_rows() and mysql_fetch_assoc(). Now, mysql_fetch_assoc() is a much better way of accessing the bits of the array that you want, as it outputs them in a meaningful way. The keys in the array, are the column names from the database. Obviously I don't know what those are because you haven't posted those details, so I've just stuck with what you'd put the variables as (educated guess), just substitute in the appropriate keys.

As for duplicate entries, we'll need more details of the database before anyone is likely to be able to help.

thanks for helping snetty.

Basically i want to display the name and surname of all my clients in a list but if the client record has a value in client_rel_id

i want to display the client name and partner name

For example there are 6 clients:

id - 1 | name - Andrew Charles | client_rel_id - NULL
id - 2 | name - Rebecca Sno | client_rel_id - NULL
id - 3 | name - Marcus Rooney | client_rel_id - 4
id - 4 | name - Emma Rooney | client_rel_id - 3
id - 5 | name - Richard Crozier | client_rel_id - NULL
id - 6 | name - Chloe Ferguson | client_rel_id - NULL


The problem with my code at the moment is it will read and display every record. Which means if i have an IF statement which:

When displaying id - 3 also gets the client_name from client_rel_id (client_id) 4

This record will be displayed twice. Do you understand me? :o

Ive got this so:

<select name="client_id">
<?
mysql_connect("localhost", "root", "ytrewq") or die(mysql_error());
mysql_select_db("testing") or die(mysql_error());
$data = mysql_query("SELECT * FROM client WHERE client_rel_id != ''")
or die(mysql_error());

while($info = mysql_fetch_array( $data )) {

?>
<?

<option value="<?=$info['client_id']?>"> <?=$info['client_surname']?>, <?=$info['client_name']?> & <?=$partner_surname?>, <?=partner_name?></option>
<?
}
?>

</select>

This passes all the database information in table: client - into an array called info.

It then loops every client_name & client_surname into a drop down box.



But i still cant figure out how to pull up the partners name and surname ( the first clients client_rel_id is the same figure as the partners client_id. And then set the partners client_id to '-1' so that the record wont be replicated later on.

OK this now works how i want it to:

<select name="client_id">
<?
mysql_connect("localhost", "root", "ytrewq") or die(mysql_error());
mysql_select_db("testing") or die(mysql_error());
$data = mysql_query("SELECT * FROM client WHERE client_rel_id != '' ORDER BY client_surname ASC")
or die(mysql_error());

while($info = mysql_fetch_array( $data )) {

?>
<?
$partner_rel_id = $info['client_rel_id'];
$editlistq = "SELECT * FROM client WHERE client_id = '$partner_rel_id'";
//echo $editlistq;
$editlistre = mysql_query($editlistq);
$editlistrow = mysql_fetch_array($editlistre);

$partner_id = $editlistrow[0];
$partner_name = $editlistrow[1];
$partner_surname = $editlistrow[2];

?>
<option value="<?=$info['client_id']?>"> <?=$info['client_surname']?>, <?=$info['client_name']?> and <?=$partner_surname?>, <?=$partner_name?></option>


<?
}
?>

</select>


However - it replicates EVERY SINGLE COUPLE so does anyone know how i can set the client_id that appears as the first partners client_rel_id to -1 or something that the loop will skip when it comes to read it???

there's a GROUP function in mysql that I think you can use to do this.. take a look on the mysql website

change the query to something like:

SELECT c.firstname, c.surname, r.firstname, r.surname FROM client c JOIN client r ON (c.client_rel_id=r.id AND c.id < r.id)

and it will give you the rows you want to loop over, saving having to do queries inside the loop.