0

I am saving many waypointIDs, bearings, and distances in three columns in a single row of my database. Each column stores it's respective item separated by a comma.

  • WaypointIds: 510,511,512
  • Bearings: 65,50,32
  • Distances: 74,19,14

I think I might have coded myself into a corner! I now need to pull them from the database and run through adding them to a table to output on screen.

I'm using the following to put the corresponding columns back into an array but am a little stuck with where to go after that, or if that is even the way to go about it.

$waypointArrays = explode (",", $waypointsString);
$bearingArrays = explode (",", $bearingsString);

$waypointsString & $bearingsStrings are the variables set by my database call.

I think I need to add them all together so I can iterate through each one in turn.

For example, waypointId 510, will have bearing 065, and distance 0.74.

I think I need an associative array but not sure how to add them all together so I can run through each waypoint ID and pull out the rest of the entry.

e.g. for each waypointId give the corresponding bearing and waypoint.

I have checks in place to ensure that as we add waypoints/bearings/distances that they don't fall out of step with each other so there will always be the same number of entries in each column.

Matthew Barraud
  • 467
  • 1
  • 5
  • 17
  • Possible duplicate of [Array\_merge versus +](https://stackoverflow.com/questions/7059721/array-merge-versus) Read that maybe, if still stuck show us what you've tried. Reading it thru again sounds like using a for loop is all you need? Same element count with each of the three arrays? – ficuscr Jul 10 '19 at 17:14
  • 2
    Your data structure is wrong from the start. You should put things together that belong together, and avoid that you have to look at the same index in different arrays to find what really belongs together. Look into database normalisation to fix your database design. – trincot Jul 10 '19 at 17:20
  • 1
    I would recommend fixing it now before the code base only gets more complicated and this causes more issues – Ice76 Jul 10 '19 at 17:23

3 Answers3

1

To answer your question, code below will work as long as waypointsIds are unique. That beign said, as other mentioned, fix your database layout. What you have here could really benefit from a separate table.

<?php
$waypointIds = [510, 511, 512];
$bearings = [65, 50, 32];
$distances = [74, 19, 14];

$output = [];
for ($i = 0; $i < count($waypointIds); $i++) {
    $output[$waypointIds[$i]] = [
        'bearing' => $bearings[$i],
        'distance' => $distances[$i]
    ];
}

print_r($output);
vicbyte
  • 3,690
  • 1
  • 11
  • 20
1

Don't continue with this design: your database is not normalised and therefore you are not getting any benefit from the power that a database can offer.

I don't think working around this problem by extracting the information in PHP using explode, ..etc is the right way, so I will clarify how your database should be normalised:

Currently you have a table like this (possibly with many more columns):

Main table: route

+----+---------+-------------+----------+-----------+
| id | Name    | WaypointIds | Bearings | Distances |
+----+---------+-------------+----------+-----------+
|  1 | myroute | 510,511,512 | 65,50,32 | 74,19,14  |
| .. |   ....  |    ....     |   ....   |   ....    |
+----+---------+-------------+----------+-----------+

The comma-separated lists violate the first normal norm:

A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.

You should resolve this by creating a separate table for each of these three columns, which will have one record for each atomic value

Main table: route

+----+---------+
| id | Name    |
+----+---------+
|  1 | myroute |
| .. |   ....  |
+----+---------+

new table route_waypoint

+----------+-------------+------------+----------+
| route_id | waypoint_id | bearing_id | distance |
+----------+-------------+------------+----------+
|     1    |    510      |    65      |    74    |
|     1    |    511      |    50      |    19    |
|     1    |    512      |    32      |    14    |
|     2    |    ...      |    ..      |    ..    |
|    ..    |    ...      |    ..      |    ..    |
+----------+-------------+------------+----------+

The first column is a foreign key referencing the id of the main table.

To select the data you need, you could have an SQL like this:

select     route.*, rw.waypoint_id, rw.bearing_id, rw.distance
from       route
inner join route_waypoints rw on rw.route_id = route.id
order by   route.id, rw.waypoint_id

Now PHP will receive the triplets (waypoint, bearing, distance) that belong together in the same record. You might need a nested loop while the route.id remains the same, but this is how it is done.

trincot
  • 317,000
  • 35
  • 244
  • 286
0
$waypoints = array(510, 511, 512);
$bearings = array(65, 50, 32);
$distances = array(74, 19, 14);

for ($i = 0; $i < count($waypoints); $i++) {
    $res[] = array(
        'waypoint' => $waypoints[$i],
        'bearing' => sprintf("%03d", $bearings[$i]),
        'distance' => $distances[$i]/100
    );
}

print_r($res);
LIGHT
  • 5,604
  • 10
  • 35
  • 78