Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Nov 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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:

    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

  • #2
    New Coder
    Join Date
    Nov 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  • #3
    New Coder
    Join Date
    Nov 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    nobody got any ideas? :[

  • #4
    New Coder
    Join Date
    May 2005
    Posts
    94
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Firstly, no offense, but your code is pretty nasty so I've rewritten what you've already quoted.

    PHP Code:
    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.

  • #5
    New Coder
    Join Date
    Nov 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #6
    New Coder
    Join Date
    Nov 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ive got this so:

    PHP Code:
    <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.

  • #7
    New Coder
    Join Date
    Nov 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK this now works how i want it to:

    PHP Code:
    <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???

  • #8
    New Coder
    Join Date
    May 2005
    Posts
    94
    Thanks
    4
    Thanked 0 Times in 0 Posts
    there's a GROUP function in mysql that I think you can use to do this.. take a look on the mysql website

  • #9
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    change the query to something like:
    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •