Multidimensional arrays in PHP created by a mysql query

Question

I have a base table in a MySQL database that has 200,000 records.

The structure of this table is as follows:

    CREATE TABLE `npanxxmaster` (
  `npanxx` varchar(6) NOT NULL DEFAULT '',
  `npa` varchar(3) DEFAULT NULL,
  `ocn_lata` varchar(7) DEFAULT NULL,
  `lata` varchar(3) DEFAULT NULL,
  `st` varchar(2) DEFAULT NULL,
  `canada` varchar(10) DEFAULT NULL,
  `ext` varchar(10) DEFAULT NULL,
  `er` double DEFAULT NULL,
  `ra` double DEFAULT NULL,
  `un` double DEFAULT NULL
)

I'm storing this table into an array with the key being the npanxx value.

I have a flat csv file that has the following structure:

npanxx(or npa only), ER, RA , UN

For a little more information npanxx is a 6 digit number and npa will always be a 3 digit number (from the npanxx).

Now this flat file, in the first column can have either npanxx (6 digits) or npa (3 digits).

What I need to do is read in each line of the csv file (which I can already do) and I store that into an array with the following code:

if (($handle = fopen($fileName, "r")) !== FALSE) {
    while (($row = fgetcsv($handle, 1000, ",")) !== FALSE){

Now here comes the tricky part. What I need to do is go through each of the $row (lines in the csv file) and find a match in the original $npanxxmaster array that I made from the MySQL table. The problem I have is the flat file can contain in its first column either npanxx or npa. And what I need to do with those are match them with the matching npanxx or npa in the $npanxxmaster array with any $row that has an npanxx taking precident over one that only has an npa.

For example:

If given in the flat csv file the following $rows:

201,.002,.002,.002
201200,.001,.001,.001

All entries in the $npanxxmaster array with an npa of 201 would get the .002,.002,.002 OTHER THAN 201200 which would get .001,.001,.001

I have no been able to achieve this at all. I can provide more code of what I have tried or more explanation if needed because i'm assuming I butchered that explanation.

Thank you all in advance for all the help!

Create two arrays, one that uses npanxx as the key, another that uses npa as the key. When you're going through the CSV, check if the first field has 6 digits or 3 digits, and then look it up in the appropriate array. — Barmar
– Barmar, Commented Oct 25, 2017 at 16:46
At the end of this I need to load the updated $npanxxmaster array into the MySQL database. How would I combine these two arrays? — MJCS
– MJCS, Commented Oct 25, 2017 at 16:48
Make them arrays of references, so they both refer to the same rows. Then you can loop through either array to do all the updates. — Barmar
– Barmar, Commented Oct 25, 2017 at 16:50
I have been having problems making an array off of NPA because it will have many NPANXX's that map to one NPA which would make the key not unique — MJCS
– MJCS, Commented Oct 25, 2017 at 17:02

Barmar · Accepted Answer · 2017-10-25 19:22:28Z

1

When processing the query, make two arrays. One that maps NPANXX to rows, and another that maps NPA to an array of NPANXX.

$npanxx_array = array();
$npa_array = array();
while ($row = $stmt->fetch()) {
    $npanxx = $row['npanxx'];
    $npa = $row['npa'];
    $npanxx_array[$npanxx] = $row;
    if (!isset($npa_array[$npa])) {
        $npa_array[$npa] = array();
    }
    $npa_array[$npa][] = $npanxx;
}

Then when you're processing the CSV, you can look up the appropriate field.

while ($row = fgetcsv($handle)) {
    if (strlen($row[0]) == 6) {
        $npanxx_to_update = array($row[0]);
    } else {
        $npanxx_to_update = $npa_array[$row[0]];
    }
    foreach ($npaxx_to_update as $npanxx) {
        // update $npanxx_array[$npanxx]
    }
}

answered Oct 25, 2017 at 19:22

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MJCS Over a year ago

With this i'm only getting the last possible npanxx in the npa_array. For example in the else statement I print_r($npanxx_to_update) and it prints 201999 which is the last possible npanxx before it goes to the next npa 202. I think this is because there are multiple npanxx's that map to 1 npa and since we are using npa as the key in the npa_array it only takes the last record

MJCS Over a year ago

Never mind I found my problem. I am testing it now and will update with results. Thank you!

Collectives™ on Stack Overflow

Multidimensional arrays in PHP created by a mysql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related