Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -2 down vote favorite

How can I assign query result into array elements?

This is my code:

include('db.php');

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die("Database connection error");
mysql_select_db($dbname);
$query = "select * from test where value=20";
$result = mysql_query($query);
$vegetable_list = array('$rice', '$wheat', '$potato', '$pulses');
$i = 1;
while($row_result = mysql_fetch_row($result))
{
    ??????? = $row_result[$i];
    $i++;
}

How can I assign the query result into the array? Let's say:

$rice = $row_result[1];
$wheat = $row_result[2];
$potato = $row_result[3];

How I can assign the values automatically?

share|improve this question
3  
Wondering if this has something to do with a farm game? – Salman A Oct 5 '10 at 6:43

5 Answers

up vote 3 down vote accepted

Let try:

$vegetable_list= array('rice','wheat','potato','pulses'); //no $ symbol
while($row_result=mysql_fetch_row($result))
{
    foreach($vegetable_list as $k => $v)
         ${$v} = $row_result[$k + 1]; //I think mysql_fetch_row should indexing from 0 -> n (not from 1)
}
share|improve this answer
could u pls give me an example..because instead of retrieve result we have to assign the value of row result into the array elements.pls..help.... – riad Oct 5 '10 at 5:51
IT works.That's great. Exactly that's i m looking for. Thanks a lot Bang Dao..thanks--riad – riad Oct 7 '10 at 10:04
up vote 3 down vote

Ok not sure but seems like you have a 150column x 20rows table that you want to convert into a two dimensional array. It is as simple as this:

$data = array( );

while( $row = mysql_fetch_assoc( $result ) )
{
    // at this point, $row contains a single row as an associative array
    // keys of this array consist of column names
    // all you need to do is append $row to $data
    $data[ ] = $row;
}

// $data is a two dimensional array
// $data[ 0 ] contains 1st row
// $data[ 1 ] contains 2nd row
// ...
// $data[ 0 ][ 'rice'  ] contains rice column value for 1st row
// $data[ 0 ][ 'wheat' ] contains wheat column value for 1st row
// ...
// $data[ 1 ][ 'rice'  ] contains rice column value for 2nd row
// $data[ 1 ][ 'wheat' ] contains wheat column value for 2nd row
// ...
// and so on

var_dump( $data );
share|improve this answer
up vote 1 down vote

Edited the code.

Here:

include('db.php');
$conn=mysql_connect($dbhost,$dbuser,$dbpass) or die("Database connection error");
mysql_select_db($dbname);

$query="select * from test where value=20";
$result=mysql_query($query);
if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
$vegetable_list= array( array () );
$rcols = mysql_query("SHOW COLUMNS FROM test");
if (!$rcols) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
if (mysql_num_rows($result) > 0) {

    $i = 0;
    $j = 0;
    if (mysql_num_rows($result) == 0) {
        echo "No rows found.";
    } else {
        while ($row = mysql_fetch_assoc($result)) {
            while ($cols = mysql_fetch_assoc($rcols);) {
                $vegetable_list[$i][$j] = $row[$cols['Field']];
            $i++;
            }
            $j++;
        }
    }
} else {
    //some error message
}
share|improve this answer
It's not working.I need to assign the query result value into the array elements.It's showing the parser error.on for each loop..pls give me an solution. – riad Oct 5 '10 at 5:49
PROBLEM NOT SOLVED YET..dear ruel..could you please help me to solve the problem..i shall be very much glad to you.. – riad Oct 5 '10 at 5:55
Edited my answer, please check if its working, otherwise please post the error message. – Ruel Oct 5 '10 at 5:57
Thanks,But it's not a feasible solution for me to write the column names.Because i write here only 4 elements .But on my real project it contents more than 150 columns.That's why i am asking for the auto value insert.Is their any other solution??Again thx for effort..pls reply if any.. – riad Oct 5 '10 at 6:01
I'm not referring to $rice, etc. What are the fields on your test column? If your wanted value are all in column/field named name, then you should write: $row['name']. – Ruel Oct 5 '10 at 6:08
show 6 more comments
up vote 1 down vote

use switch case :

$i=1;

while($row_result=mysql_fetch_assoc($result))
{
     switch($i) {
         case 1 : $rice = $row_result[$i]; break;
         case 2 : $wheat = $row_result[$i]; break;
     }
     $i++;
}

or you can do this : 

$i=0;
$vegetable = array("rice", "wheat", "potato");
while($row_result=mysql_fetch_assoc($result))
{
     $idx = 0;
     foreach($row_result as $result){
          ${$vegetable[$i]}[$idx] = $result;
          $idx++;
     }
     $i++;
}

then try to 

var_dump($rice);

you should get array of your specific column.

share|improve this answer
Non of the solution works..thx – riad Oct 5 '10 at 6:20
sorry it sould be mysql_fetch_assoc. i just copy paste your code. it should be work now. – Jeg Bagus Oct 5 '10 at 6:27
Sorry brother.It's also not working.But modifying your last code it get value only for the first element.pls see my code below: $i=1; $j=0; $vegetable=array("rice","wheat"); while($row_result=mysql_fetch_row($result)) { $$vegetable[$j]=$row_result[$i]; $i++; $j++; } echo "Rice:".$rice; echo "<br/>"; echo "Wheat:".$wheat; – riad Oct 5 '10 at 6:35
I think we are near to the solution.Could you pls help me to solve to retrieve all the values.. – riad Oct 5 '10 at 6:36
i little bit confuse, can u show me your database schema? i can't reproduce your error while i don't get enough explanation. – Jeg Bagus Oct 5 '10 at 6:42
show 9 more comments
up vote 0 down vote

Extract?

http://www.php.net/manual/en/function.extract.php

share|improve this answer
that's not the solution... – riad Oct 5 '10 at 6:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.