PHP中的多维数组由mysql查询创建

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!

Answer 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]
    }
}