note 34663 added to ref.mysql
| From: | LCValentine at aol dot com | Date: | Sun, 03 Aug 2003 05:55:27 +0000 |
| Subject: | note 34663 added to ref.mysql | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-53229@lists.php.net to get a copy of this message | ||
Kinda funny to see an AOLer here I'm sure, but everyone's talked a lot about making their
MySQL query results easier to handle. Well if this sounds like "easy to handle" then this
is for you.
I wrote a function (name mysql_fetch_assarr) because I wanted an associate array for all the
information requested (rather than several arrays) which was would also cut down on queries and
still be easily navigable.
Prototype:
mysql_fetch_assarr( string <i>database</i>, string <i>table</i>, [string
<i>"select" parameters</i>], [string <i>"select"
columns</i>] )
where "select" parameters are the actual query part minus the word 'where'. i.e.
name = 'john' and age > 10 and eyes like '%brown%'
and
"select" columns are the columns you want data from which usually appear after the word
'select'. i.e. name, age, eyes
When the last two variables are ommitted, the function will query the table within the database,
retrieving all info, and putting it into an associative array based against the Primary Key.
Assuming the 'name' column from my previous examples is the primary key:
echo my_array['john']['age']
yields the eye color of john. In the same way:
echo my_array['john']['eyes']
echo my_array[billy']['eyes']
echo my_array['billy']['age']
yields the eye colors of john and billy, and age respectively.
If you your "select" <i>columns</i> variable does not include primary key, it
will find and enter it for you.
function mysql_fetch_assarr( $db, $table, $params, $select ){
$query = mysql_db_query( $db, "describe ".$table );
$n = mysql_num_rows( $query );
for( $i = 0; $i < $n; $i++ ){
$data = mysql_fetch_array( $query );
$col[$i] = $data[0];
if( empty($pri_key) ){
if( $data['Key'] == 'PRI' ){
$pri_key = $col[$i];
$key = $i;
}
}
}
if( empty($select) ){
$select = '*';
}else{
$get = explode( ',' , $select);
$o = count( $get );
for( $i = 0; $i < $o; $i++){
trim( $get[$i] );
}
if( !in_array( $pri_key, $get ) ){
$select .= ", ".$pri_key;
array_push( $get, $pri_key );
$o++;
}
}
$query = mysql_db_query( $db, 'select '.$select.' from '.$table.' where
'.$params );
$o = mysql_num_rows( $query );
for( $i = 0; $i < $o; $i++){
$data = mysql_fetch_array( $query );
for( $j = 0; $j < $n; $j++){
if( $data[ $pri_key ] != $data[ $col[ $j ] ] ){
$assoc[ $data[ $pri_key ] ][ $col[$j] ] = $data[ $col[ $j ] ];
}
}
}
return $assoc;
}
----
Manual Page -- http://www.php.net/manual/en/ref.mysql.php
Edit Note -- http://master.php.net/manage/user-notes.php?action=edit+34663
Delete Note -- http://master.php.net/manage/user-notes.php?action=delete+34663&report=yes
Reject Note -- http://master.php.net/manage/user-notes.php?action=reject+34663&report=yes