Re-keying an array

[lukemack]

Hi,

I have an array in the following format :

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.

[Cups]

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.

[lukemack]

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>

[lukemack]

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

[byron3@earthlink]

Luke,

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

$ID_array = explode( ",", $Returned_Data_Field ) 

Get you comma separated field and slice it up into an array.

Need more?

[lukemack]

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(!).

[byron3@earthlink]

Luke,

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

Loop through the output array.

[wonshikee]

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

?

[Cups]

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?

[byron3@earthlink]

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
} 

[lukemack]

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:

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>

[TheRedDevil]

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.

$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'];
        }
    } 

[lukemack]

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:

	$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

[lukemack]

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:

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>

[TheRedDevil]

You can try this if you want to change the index key.

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'];
                }
        } 

[lukemack]

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?

[byron3@earthlink]

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

[TheRedDevil]

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.