On Monday 10 December 2001 00:36, you wrote:
> I have a MySQL query that I want to draw an associative array from -
> but I want the 'key' to the array to be the identifying element of
> the row, and I'm stuck on how to get what I want.
>
> For instance:
>
> Each row in the result set contains an id number, a keyword and a
> count related to that keyword.
>
> so in a 'while' loop I can get an associative array like $row["id"],
> $row["word"], etc. with no problem.
>
> However - later (outside of the 'while' loop) I want to be able,
> given the id number, to get the keyword and the count. Like this
> pseudo code:
>
> $id_list(1150, 1160, 1170);
> for each $item in $id_list {
> print ($item["word"] : $item["count"]);
> }
>
> The idea is that I want a series of associative arrays that might be
> otherwise set like...
>
> $1150["word"] = "Blue";
> $1150["count"] = 12;
> $1160["word"] = "Red";
> ...etc....
>
> ...except drawn from a MySQL query.
>
> Any ideas how to achieve this?
Here's what I use (I'm assuming you're PHP). Note on parameters:
$dbh is the database handle. If you use the built-in mysql functions then
subsitute with a valid mysql connection id.
$query is the query
$key_field is the name of the field of your "id number"
$fields is an array of the names of the fields to include in the result, if
empty then include all fields.
$stripslash is a boolean to specify whether or not to stripslashes from the
results.
If you need more explanation let me know.
============================================================
function get_db_array($dbh, $query, $key_field="", $fields="",
$stripslash="1") {
$result = "";
$sth = $dbh->prepare($query);
if ($sth) {
$sth->execute();
while ($row = $sth->fetchrow_hash()) {
$key_field_val = $row[$key_field];
if ($fields) {
reset($fields);
while (list($key, $field) = each($fields)) {
if (!is_array($field) AND isset($row[$field])) {
switch ($stripslash) {
case 1 : $result[$key_field_val][$field] =
stripslashes($row[$field]); break;
case 0 : $result[$key_field_val][$field] = $row[$field]; break;
}}
else {
switch ($stripslash) {
case 1 : $result[$key_field_val][$key] =
stripslashes($row[$key]); break;
case 0 : $result[$key_field_val][$key] = $row[$key]; break;
}
}
}}
else { #show_msg("Displaying row"); #get_array_elems($row);
$i = 0;
while (list($key, $field) = each($row)) {
$i++; #echo "Key:$key field:$field<br>";
if (!($i % 2)) {
if ($stripslash) {
#$result[$key_field_val][$key] = stripslashes($field); echo
"Assigned result[$key_field_val][$key]::$result[$key_field_val][$key]<br>"; }
$tmp[$key] = stripslashes($field); }#echo "Assigned
result[$key_field_val][$key]::$result[$key_field_val][$key]<br>"; }
else {
#$result[$key_field_val][$key] = $field; echo "Assigned
result[$key_field_val][$key]::$result[$key_field_val][$key]<br>";
$tmp[$key] = $field; #echo "Assigned
result[$key_field_val][$key]::$result[$key_field_val][$key]<br>";
}
}
} #get_array_elems($result);
$result[] = $tmp;
}
}
return($result);
}
}
============================================================
--
Jason Wong -> Gremlins Associates -> www.gremlins.com.hk
/*
Peers's Law:
The solution to a problem changes the nature of the problem.
*/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
