PHP + MySQL: Possible to get array keys as a value?

PHP
1

This is perhaps a really dumb question, but I’ve been unable to find an answer after quite a bit of searching.

Is there a way to query MySQL (I’m using the ezSQL class) and have it return the PHP array with the keys set as a specific value?

For example, if I had a table called “Customers” with the columns “CustomerID, Name, State, ZIP”, how could I query MySQL so that it will return the array with the ‘CustomerID’ value set as the key:

Array(
    [CustomerID] => Array (
        [Name] => Value
        [State] => Value
        [ZIP] => Value
        )
    )

Instead of this:

Array(
        [0] => Array(
        [CustomerID] => Value
        [Name] => Value
        [State] => Value
        [ZIP] => Value
        )
    )

Any thoughts, suggestions, comments are most definitely appreciated - My genuine thanks in advance to all who reply.

2

You can build such an array from the result set

$array = array();
while ($row = mysql_fetch_assoc($result)) {
  foreach ($row as $key => $value) {
    $array[$key][] = $value;
  }
}
3

Thank you, Dan - I had considered going that route, but wasn’t sure if there was a method for doing it right in the MySQL query.

In any case, your solution works perfectly, and I appreciate your time helping me out. :slight_smile:

4

This is how I understood you wanted to re-organize the array (this assumes the first value is always customer id in your mysql query):


$array = array();
while($row = mysql_fetch_assoc($result))
{
	$array[] = $row;
}

$organizedArray = array();
foreach ($array as $innerArray)
{
	foreach ($innerArray as $key => $value)
	{
		if ($key === 'customer_id')
		{
			$customerId = $value;
			$organizedArray[$customerId] = array();
		}
		else
		{
			$organizedArray[$customerId][$key] = $value;
		}
	}
}

prints (every array key is customer id and customer info under it):


Array
(
    [1] => Array
        (
            [name] => test
            [state] => teststate
            [zip] => 12314
        )

    [2] => Array
        (
            [name] => test2
            [state] => test2state
            [zip] => 4444
        )

    [3] => Array
        (
            [name] => test3
            [state] => test3state
            [zip] => 3333
        )

)
5

You can do this with PDO
First instantiate the $pdo object, read the manual for how to do that here:
http://us3.php.net/manual/en/pdo.construct.php

Then:

$sql = ‘select CustomerID, Name, State, Zip from customer where CustomerID = :id’;
$sth = $pdo->prepare($sql);
$sth->bindParam( ‘:id’, $id );
$sth->execute();
$sth->fetchAll(PDO::FETCH_GROUP);

The trick here is the PDO::FETCH_GROUP

Your array will have the value of the first column as array key and the rest of columns are value of that array.

By the way, you don’t have to select just one customer, this was just an example. You can just as easily select all customers or customers whose State = ‘NY’, in which case you get array where array keys are customerID and values are arrays with the rest of customer data.

Basically, you will have array that looks exactly how you wanted it to look. Just dump your ezSql and switch to PDO.
I’ve been using DB class from pear, then MDB2 from pear for years, then finally dumped them in favor of PDO and it’s been great, I’ll never use anyther DB class again.

6

Some really excellent replies here - I really appreciate all of you taking the time to share your expertise!

I’m going to play around with both of the 2 new suggestions a bit later today and will post back with which one seems to work out best.

Huge thanks to TeNDoLLA and Sharedlog.com! :smiley:

7

As shareddog said if you are using some database class, my vote would also go to PDO.

8

This is my code:

$result = mysql_query("select * from 2mcom where father_name='Siyaram'");
    while (false != ($data = mysql_fetch_array($result, MYSQL_ASSOC)))
      foreach ($data as $key => $value)
      echo "$key: $value <br />";

I want $value to be in input box , i have tried the following code but not working…:

$result = mysql_query("select * from 2mcom where father_name='Siyaram'");
while (false != ($data = mysql_fetch_array($result, MYSQL_ASSOC))){
  foreach ($data as $key => $value){
  //echo "$key: $value <br />";
   echo "$key: ";
  
    ?>
<input type="text" name="" value="<?php echo '$value'; ?> " />
<?php echo "<br>"; }}
9

variables in single quotes are not parsed.