SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    london
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re-keying an array

    Hi,

    I have an array in the following format :
    Code:
    Array
    (
        [0] => Array
            (
                [itineries] => Array
                    (
                        [ship_id] => 1
                    )
    
                [0] => Array
                    (
                        [GROUP_CONCAT(DISTINCT destination_ids)] => 9
                    )
    
            )
    
        [1] => Array
            (
                [itineries] => Array
                    (
                        [ship_id] => 6
                    )
    
                [0] => Array
                    (
                        [GROUP_CONCAT(DISTINCT destination_ids)] => 9,2,21
                    )
    
            )
    
        [2] => Array
            (
                [itineries] => Array
                    (
                        [ship_id] => 7
                    )
    
                [0] => Array
                    (
                        [GROUP_CONCAT(DISTINCT destination_ids)] => 9,12,4
                    )
    
            )
    
        [3] => Array
            (
                [itineries] => Array
                    (
                        [ship_id] => 8
                    )
    
                [0] => Array
                    (
                        [GROUP_CONCAT(DISTINCT destination_ids)] =>
    15,23,26,15,19,26
                    )
    
            )
    Yes, I'm querying a badly designed database in which destination_ids
    are stored as comma separated fields in a single field. I didnt design
    it and changing it is unfortunately not an option, at least for now.
    The itineraries table (the source of the array) is the only point in
    the database where the destination and ship_id are linked so I need to
    figure out a way of converting the above array so that i have a list
    of ships per destination id, instead of the other way round. So, for each individual destination_id, i need a list of ship_ids.

    the query i am using is $query = 'SELECT ship_id,
    GROUP_CONCAT(DISTINCT destination_ids) FROM itineries GROUP BY
    ship_id;';

    if i change this query to group by distinct destination ids, i still
    end up with some destination id entries with more that one value
    (comma separated).

    can anyone suggest how i can manipulate the above array using array
    functions to get the resultset i need? In english it would look something like:

    /* FOR EACH ENTRY IN THE $data ARRAY:

    EXPLODE THE DESTINATION IDS INTO A NEW ARRAY
    STORE AN ARRAY WITH JUST DESTINATION IDS
    GO THROUGH $DATA ARRAY AGAIN, THIS TIME FINDING INSTANCES OF EACH DESTINATION VALUE IN THE NEW DESTINATION IDS ARRAY
    WHERE THE DESTINATION ID IS FOUND, ADD/MODIFY A ROW TO THE RESULTS ARRAY - KEY DESTINATION ID, VALUE = SHIP IDS.

    */

    many thanks,

    lukemack.

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I feel that you are trying to shoehorn PHP into doing something that Mysql should be doing for you.

    Which version of mysql is that sql statement coming from?

    Maybe a subselect can help you get the data out correctly in the first place.

  3. #3
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    london
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The mysql version is 4.1.20. I think this could be upgraded to 5 if there is a feature that can do what I need.

    I'd be grateful if you could expand on the subselect idea?

    thanks,

    <L>

  4. #4
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    london
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sample data attached

    i've attached some sample data. i need to get extract ship ids per destination.
    Attached Files Attached Files

  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Luke,

    Create a function that returns you destination ID's as an array.
    Use the explode function :

    PHP Code:
    $ID_array explode","$Returned_Data_Field 
    Get you comma separated field and slice it up into an array.

    Need more?
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  6. #6
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    london
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tried many permutations using explode() within for loops etc and can't seem to get the result set i want so more would be good, if you have the time(!).

  7. #7
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Luke,

    The explode function does not require a loop.
    It generates an array from the comma separated field.

    Loop through the output array.
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  8. #8
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So for example,
    destination_id 9 is linked to ship_id 7,
    destination_id 12 is linked to ship_id 7,
    destination_id 4 is linked to ship_id 7

    ?

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Sorry, I did post this earlier but lost the line ... your zip is still awaiting approval ...

    Mysql supports subqueries starting from 4.1.

    http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

    Doesn't your query involve 2 tables?

  10. #10
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    function GetShipsByDest_ID$Dest_ID ) {
        
    $Ships = array();
        
    $query "select ship_id, destination_id from itineraries "// Build query
        
    $results mysql_query$query ); // Execute query
        /* Code goes here to check for errors and returned result set */
     
        
    while ( $row mysql_fetch_assoc$results )) {; //Fetch row
            
    $ID_array explode"," $row['destination_ids'] ) // explode the array on comas
            
    foreach ( $ID_array as $Dest ) { // iterate through each destination ID
                
    if ( $Dest_ID == $Dest ) { $Ships $row['ship_id']; } //Check for match and add ships ID to array
            
    }
        }
        return 
    $Ships// Returns array of ships sailing to this destination

    Last edited by byron3@earthlink; Jun 2, 2007 at 15:51.
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  11. #11
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    london
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys!

    wonshikee - yes, one ship can have many destinations
    cups - there is only one table. thanks for the link!
    byron - to complicate things further, i can't use mysql_fetch_assoc as there is a data abstraction layer in the way. i will try your function in a test area, though.

    byron - your query comes back like this:

    Code:
    Array
    (
        [0] => Array
            (
                [itineries] => Array
                    (
                        [ship_id] => 129
                        [destination_ids] => 9
                    )
    
            )
    
        [1] => Array
            (
                [itineries] => Array
                    (
                        [ship_id] => 114
                        [destination_ids] => 9
                    )
    
            )
    
        [2] => Array
            (
                [itineries] => Array
                    (
                        [ship_id] => 186
                        [destination_ids] => 29,16
                    )
    
            )
    thanks again for the replies. <L>

  12. #12
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,190
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    If I understand your problem correctly, the code below should fix it. Please note that I only used the array keys you posted in the start. So you might need to update the keys to the proper key values for the code to work.

    The code will add all destinations into a 2d array, including the ship ids under each destination.

    PHP Code:
    $destination = array();

    foreach (
    $the_array as $key => $value) {
        
    $temp_destination explode(','$value[0]['destination_ids']);
        
        foreach (
    $temp_destination as $key2) {
            
    $destination[$key2][] = $value['itineries']['ship_id'];
            }
        } 

  13. #13
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    london
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    dude!

    i think that solves it!. i've modified it so that i can read it without it just being a confusing list of numbers but i think this is what i need:

    Code:
    	$destination = array();
    
    		foreach ($data as $key => $value) {
    		$temp_destination = explode(',', $value[0]['GROUP_CONCAT(DISTINCT destination_ids)']);
    		
    			foreach ($temp_destination as $key) {
    				$destination['dest_'.$key][] = 'ship_'.$value['itineries']['ship_id'];
    				}
    		}
    	print_r($destination);
    thanks to everyone who replied and a high 5 to TheRedDevil

  14. #14
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    london
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    more problems

    Hi,

    I now have a further problem which is that i need to replace the destination ID with the destination name from the destinations table. I was advised that this would be v difficult in MYSQL so have gone the PHP route. SO far I have:

    Code:
    function index() {
    	
    	$this->autoRender = false;
    	//$data = $this->Itinery->findAll(null,'ship_id,destination_ids',null,null,null,0);
    	$query = 'SELECT ship_id, GROUP_CONCAT(DISTINCT destination_ids)  FROM itineries GROUP BY ship_id;';
    	$query2 = 'SELECT  ship_name , GROUP_CONCAT(DISTINCT destination_ids )  FROM itineries,ships WHERE itineries.ship_id = ships.id GROUP BY ship_id;';
    	$data = $this->Itinery->query($query2);
    	//debug($data); exit;
    	$destNames = $this->Destination->findAll(null, 'id, destination_name');
    	$destination = array();
    
    		foreach ($data as $key => $value) {
    			
    			//pr($value);
    		$temp_destination = explode(',', $value[0]['GROUP_CONCAT(DISTINCT destination_ids )']);
    		
    			foreach ($temp_destination as $key) {
    				$destination[$key][] = $value['ships']['ship_name'];
    				}
    		}
    		
    		asort($destNames,SORT_NUMERIC);
    		ksort($destination,SORT_NUMERIC);
    		//debug($destination);
    
    	echo "DESTINATION NAMES FROM DESTINATIONS TABLE<br />";
    	debug($destNames);
    	
    	echo "RESULT OF QUERY FROM ITINERARIES TABLE<br />";
    	
    	debug($destination);
    	}
    	
    
    }
    I have tried looping through one array and assigning the value directly but got some weird results. The debug outputs are viewable here:

    http://dev.insidecruise.co.uk/map

    What I want is to replace the numeric key from the second lot of output with the destination name from the corresponding id in the first lot of output.

    hope that makes sense. anyone got any thoughts on how best to do this?

    thanks!

    <L>
    Last edited by lukemack; Jun 4, 2007 at 10:50. Reason: typos

  15. #15
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,190
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You can try this if you want to change the index key.

    PHP Code:
    foreach ($data as $key => $value) {
                
                
    //pr($value);
            
    $temp_destination explode(','$value[0]['GROUP_CONCAT(DISTINCT destination_ids )']);
            
                foreach (
    $temp_destination as $key2) {
                    
    $destination[$destNames[$key2]][] = $value['ships']['ship_name'];
                    }
            } 

  16. #16
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    london
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks TheRedDevil. I'm getting an error though:

    Warning: Illegal offset type in controllers/map_controller.php on line 61

    its complaining about this line - $destination[$destNames[$key2]][] = $value['ships']['ship_name'];

    any ideas?

  17. #17
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    function GetShipsByDest_ID$Iten_array$Dest_ID ) {
        
    $Ships = array();
        foreach ( 
    $Iten_array as $iten) {; // Loop for Itineraries array
            
    $ID_array explode"," ,  $iten['destination_ids'] ); // explode the destinations on comas
            
    foreach ( $ID_array as $Dest ) { // iterate through each destination ID
                
    if ( $Dest_ID == $Dest ) { $Ships $iten['ship_id']; } //Check for match and add ships ID to array
                
    }
            }
        }
        return 
    $Ships// Returns array of ships sailing to this destination

    One more function, this one should split your itineraries array up and return a ship id array, need to split that array let me know.

    Byron
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  18. #18
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,190
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You get illigal offset errors if you try to use a array key which does not exist.

    My guess is that either does not the $destNames[] for that $key2 exist, or that is something wrong with the $value['ships']['shipname'] array (i.e. the naming).

    Do a few var_dumps to check what the arrays return, and figure out when it fails.


Bookmarks

Posting Permissions

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