multi-dimensional array problem

[Levinax]

ok, so im trying to input the result of a query into a multidimensional array. i know that the query returns the results i want, but im having problems inputting it into the array correctly.

theoretically, what should come out would be an array equal to the following:
[code]
$myarray = array ("location" => array ("CHEVROLET", "OLYMPIA", "TACOMA"),
                 "store" => array ("CHEVROLET", "NISSAN", "SUBARU", "MITSUBISHI", "CHRYSLER"),
                 "dept" => array ("BODY SHOP", "FINANCE", "OFFICE", "SALES", "SERVICE", "SHOP"));
[/code]

currently, i am inputing the code into an array in the following fashion:
[code]$qryres = pg_query($db, $lsd);
$myarray = array();
$lsdres = pg_fetch_array($qryres);

while ($lsdres = pg_fetch_array($qryres)) {

     $myarray[] = $lsdres;

}
[/code]

this inputs all the information, but not the correct way. the result of doing it this way returns the follwing:
[code]
Array (
[0] => Array ( [0] => OLYMPIA [location] => OLYMPIA )
[1] => Array ( [0] => TACOMA [location] => TACOMA )
[2] => Array ( [0] => CHEVROLET [location] => CHEVROLET )

[3] => Array ( [0] => CHRYSLER [location] => CHRYSLER )
[4] => Array ( [0] => MITSUBISHI [location] => MITSUBISHI )
[5] => Array ( [0] => NISSAN [location] => NISSAN )
[6] => Array ( [0] => SUBARU [location] => SUBARU )

[7] => Array ( [0] => BODY SHOP [location] => BODY SHOP )
[8] => Array ( [0] => FINANCE [location] => FINANCE )
[9] => Array ( [0] => OFFICE [location] => OFFICE )
[10] => Array ( [0] => PARTS [location] => PARTS )
[11] => Array ( [0] => RECEPTION [location] => RECEPTION )
[12] => Array ( [0] => SALES [location] => SALES )
[13] => Array ( [0] => SERVICE [location] => SERVICE )
[14] => Array ( [0] => SHOP [location] => ))
[/code]

if anyone could point me in the right direction, i would greatly appreciate it.

[Barand]

if you run this code, what do you get?

[code]$qryres = pg_query($db, $lsd);
$myarray = array();
$lsdres = pg_fetch_array($qryres);

echo '<pre>', print_r($lsdres , true), '</pre>';[/code]

In other words, what does the array $lsdres look like?


BTW, that pg_fetch_array() before the while loop will prevent the first record from being included in $myarray.

[Levinax]

when i use that code, it outputs the following...

[code]Array
(
    [0] => CHEVROLET
    [location] => CHEVROLET
)

[/code]

[Barand]

Where do store and dept come from?

[Levinax]

hopefully from the database, they are table names.

the SQL looks like this:

[code]$lsd = "SELECT DISTINCT location FROM info
UNION ALL
SELECT DISTINCT store FROM info
UNION ALL
SELECT DISTINCT dept FROM info";
[/code]

this is the only way i could get the query to return the proper results... now getting it to return the proper results in the right format will be the tricky part...

[kenrbnsn]

Use mysql_fetch_assoc() instead of mysql_fetch_array(). That should help.

Ken

[Barand]

try

[code]$lsd = "SELECT DISTINCT 'location' as keyval, location as val FROM info
UNION ALL
SELECT DISTINCT 'store' as keyval, store as val FROM info
UNION ALL
SELECT DISTINCT 'dept' as keyval, dept as val FROM info";

$qryres = pg_query($db, $lsd);
$myarray = array();

while ($lsdres = pg_fetch_array($qryres)) {

       $myarray[$lsdres['keyval'][] = $lsdres['val'];
}

echo '<pre>', print_r($myarray , true), '</pre>';[/code]

[Levinax]

when i use that code, the query fails...

[code]
[Tue Mar 28 10:17:51 2006] [error] PHP Warning:  pg_query(): Query failed: ERROR:  Unable to identify an ordering operator '&lt;' for type 'unknown'
        Use an explicit ordering operator or modify the query
. in /home/test/public_html/test/test4.php on line 258
[Tue Mar 28 10:17:51 2006] [error] PHP Warning:  pg_fetch_array(): supplied argument is not a valid PostgreSQL result resource in /home/test/public_html/test/test4.php on line 261
[Tue Mar 28 10:17:51 2006] [error] PHP Warning:  pg_num_rows(): supplied argument is not a valid PostgreSQL result resource in /home/test/public_html/test/test4.php on line 273
[/code]

[Barand]

Is there some esoteric syntax in postgresql for including a string literal in a query?