PHP中的多维数组由mysql查询创建
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!
我在MySQL数据库中有一个包含200,000条记录的基表。 p>
该表的结构如下: p>
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
)
code> pre>
我将此表存储到一个数组中,其中键是npanxx值。 p>
我有一个平坦的csv文件,其中包含 以下结构: p>
npanxx(或仅限npa),ER,RA,UN
code> pre>
再多一点 信息npanxx是一个6位数字,npa将始终是一个3位数字(来自npanxx)。 p>
现在这个平面文件,在第一列可以有npanxx(6位数字) )或npa(3位)。 p>
我需要做的是读取csv文件的每一行(我已经可以做)并将其存储到具有以下内容的数组中 代码: p>
if(($ handle = fopen($ fileName,“r”))!== FALSE){
while(($ row = fgetcsv($ handle) ,1000,“,”))!== FALSE){
code> pre>
现在这里有一个棘手的部分。 我需要做的是遍历每个$ row(csv文件中的行)并找到我在MySQL表中创建的原始$ npanxxmaster数组中的匹配项。 我遇到的问题是平面文件可以在其第一列中包含npanxx或npa。 我需要做的就是将它们与$ npanxxmaster数组中匹配的npanxx或npa匹配,其中任何$ row的npanxx都是预先设定npa的那个,而且只有npa。 p>
例如: p>
如果在flat csv文件中给出以下$ rows: p>
201,.002,.002,。 002
201200,.001,.001,。001
code> pre>
$ npanxxmaster数组中npa为201的所有条目都将获得.002,.002, .002除了201200,它将得到.001,.001,.001 p>
我根本无法做到这一点。 如果需要,我可以提供更多我尝试过的代码或更多解释,因为我假设我已经剔除了这个解释。 p>
提前感谢所有人的帮助! p>
div>
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]
}
}