Menu
Community /Pin to ProfileBookmark
@NogDogOct 02.2010 — #At a guess, you may want to do something like:
However, I suspect you could clean that up by just executing a single query using a JOIN on the two tables, which would be much more efficient then running a separate query for each row in the first table. Maybe something like:
The $data array would then look something like:
@NogDogOct 02.2010 — #
[FONT="Courier New"][B]$control[67]['controlname'][0][/B][/FONT]
To go through all the results, you could use nested foreach loops:
@NogDogOct 05.2010 — #The empty brackets mean to use the next available integer index for that array dimension (0, 1, 2, 3,...). So since each ID will have only one exemption type, I just overwrite that 2nd dimension element, but then each new row for that ID will have a different control name, so it's adding a new, auto-indexed 3rd dimension array element for each. @NogDogOct 07.2010 — #Because my understanding of your data design and what you are trying to do is that the exemption type is the parent, so you only needed listed once in the resulting array. If you add the "[]", then you'll add an additional enumerated dimension to that part of the resulting array, so instead of this...
By not using "[]" and instead overwriting it if it exists each time, you avoid the extra and unneeded array dimension under extension type ("unneeded" if my understanding is correct, mind you).
[RESOLVED] How to populate multidimensional array using SQL?
Have struggled to come up with a solution so would be thankful if you could give me a helping hand!
Want to dynamically create one array based on information in my sql db.
Don’t think I’m far from the goal…..
Mysql setup:
Table tblexemptiontype (fields id, exemptiontype)
Table tblcontroltype (typeid, controlname)
Example rows in tblexemptiontype,
Id exemptiontype
67 ACCESS CONTROL
64 AUDIT AND ACCOUNTABILITY
Example rows in tblcontroltype,
67 AC1
67 AC2
64 AA1
64 AA2
Etc.etc……
Tblexemptiontype. Id= tblcontroltype. Typeid is where the two tables are ‘linked’
PHP code:
$sql = “SELECT id FROM tblexemptiontype ORDER BY exemptiontype”;
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
$control[$row[0]] = array();
$sql2 = “SELECT typeid, controlname FROM tblcontrolname WHERE typeid = $row[0] ORDER BY controlname”;
$rs1 = mysql_query($sql2);
while($rows1 = mysql_fetch_array($rs1)) {
$str = $str.”‘”.$rows1[0].”‘,”.$rows1[1].”‘,”;
$control[$row[0]] = ($str);
}
$str = “”;
}
If I echo $control[67][1]; then I should get ‘AC1’ and $control[64][3]; should give me ’AA2’ etc.etc….
With this code as I have added a string to $control I only get one character.
My guess is that something in this section should be different,
$str = $str.”‘”.$rows1[0].”‘,”.$rows1[1].”‘,”;
$control[$row[0]] = ($str);
}
$str = “”;
Please help!!!!
?
Sign in
to post a comment9 Comments(s) ↴
0
@NogDogOct 02.2010 — #At a guess, you may want to do something like:[code=php]
$control[$row[0]][] = $str;
[/code]However, I suspect you could clean that up by just executing a single query using a JOIN on the two tables, which would be much more efficient then running a separate query for each row in the first table. Maybe something like:
[code=php]
<?php
$sql = "
SELECT ex.id, ex.exemptiontype, ct.controlname
FROM tblexemptiontype AS ex
INNER JOIN tblcontroltype AS ct ON ct.typeid = ex.id
";
$result = mysql_query($sql);
$data = array();
while ($row = mysql_fetch_assoc($result)) {
$data[$row['id']]['exemptiontype'] = $row['exemptiontype'];
$data[$row['id']]['controlname'][] = $row['controlname'];
}
[/code]The $data array would then look something like:
<i>
</i>array(
64 => array(
'exemptiontype' => 'AUDIT AND ACCOUNTABILITY',
'controlname' => array(
0 => 'AA1',
1 => 'AA2'
)
),
67 => array(
'exemptiontype' => 'ACCESS CONTROL',
'controlname' => array(
0 => 'AC1',
1 => 'AC2'
)
)
)
0
@NogDogOct 02.2010 — #Hi again,
Have tried your join suggestion and the SQL question work (I think) but not sure about the last part.
If I try "echo $control[67][1];" I get "Notice: Undefined offset: 1 in C:wampwwwpublicfunctions.php on line 68".
And "print_r ($control);" gives me this "
Array ( [67] => Array ( [exemptiontype] => ACCESS CONTROL [controlname] => Array ( [0] => AC-01 ACCESS CONTROL POLICY AND PROCEDURES [1] => AC-02 ACCOUNT MANAGEMENT [2] => AC-03 ACCESS ENFORCEMENT [3] => AC-04 INFORMATION FLOW ENFORCEMENT [4] => AC-05 SEPARATION OF DUTIES [5] => AC-06 LEAST PRIVILEGE [6] =>etc.etc.etc........
How would I reference ex. ID=67 and "AC-01 ACCESS CONTROL POLICY AND PROCEDURES" in $control?
...
}[/QUOTE]
[FONT="Courier New"][B]$control[67]['controlname'][0][/B][/FONT]
To go through all the results, you could use nested foreach loops:
[code=php]
foreach($control as $id => $data) {
printf("<h3>%d: %s</h3>n", $id, $data['exemptiontype']);
echo "<ul>n";
foreach($data['controlname'] as $name) {
echo "<li>$name</li>n";
}
echo "</ul>n";
}
[/code]0
@NogDogOct 05.2010 — #0
@NogDogOct 07.2010 — #Because my understanding of your data design and what you are trying to do is that the exemption type is the parent, so you only needed listed once in the resulting array. If you add the "[]", then you'll add an additional enumerated dimension to that part of the resulting array, so instead of this...<i>
</i>array(
64 => array(
'exemptiontype' => 'AUDIT AND ACCOUNTABILITY',
'controlname' => array(
0 => 'AA1',
1 => 'AA2'
)
),
67 => array(
'exemptiontype' => 'ACCESS CONTROL',
'controlname' => array(
0 => 'AC1',
1 => 'AC2'
)
)
)
<i>
</i>array(
64 => array(
'exemptiontype' => array(
0 => 'AUDIT AND ACCOUNTABILITY',
1 => 'AUDIT AND ACCOUNTABILITY'
),
'controlname' => array(
0 => 'AA1',
1 => 'AA2'
)
),
67 => array(
'exemptiontype' => array(
0 => 'ACCESS CONTROL',
1 => 'ACCESS CONTROL'
),
'controlname' => array(
0 => 'AC1',
1 => 'AC2'
)
)
)
By not using "[]" and instead overwriting it if it exists each time, you avoid the extra and unneeded array dimension under extension type ("unneeded" if my understanding is correct, mind you).