CODEWITHSUNDEEP
phpmysqlassociative-array
champ
champ

Reputation: 817

How to populate a flat associative array while looping query results?

I want an array to be declared like $value=array('apple'=>'red', 'mango'=>'yellow').

I want this values to be fetched from the database -- both the apple and the red.

Suppose these colours are saved in table called 'colors' and fruits in the table 'fruits', with the color_id.

How to fetch them and put inside this array?

I tried to put the codes in side the bracket like array (code to fetch) but it didn't work.

table -> fruit(fruit_id, color_id, fruit_name) table -> color(color_id, color_name)

$result = mysql_query("select * from fruit_table");
while ($row = mysql_fetch_array($result)) {
    $row_color - mysql_fetch_array(mysql_query("select color_name from colors where color_id=$row['color_id']"));
    $val[] = "$row['fruit_name'] => $row_color[color_name]";     
} 
$data = implode(",", $val);
$value = array($data);

Upvotes: 0

Views: 474

Answers (3)

Jacob
Jacob

Reputation: 8344

There are two things you will have to do.

  1. Perform a query to get the required information from the database.
  2. Turn the result from the query into the format you want.

Here's some example code (assuming you have already made a successful connection to the database).

Assumptions I've made on your database schema are:

  • You use id as the primary key in both tables
  • name is the field that contains the color/fruit names

If they are correct it should work, otherwise minor adjustments are required to the query

$result = mysql_query('
    SELECT fruits.name as fruit, colors.name as color 
    FROM fruits 
    JOIN colors 
    ON fruits.color_id = color.id');

if (!$result) {
    die(mysql_error());
}

$value = array();

while ($row = mysql_fetch_assoc($result)) {
    $value[$row['fruit']] = $row['color'];
}

mysql_free_result($result);

Upvotes: 3

Akhilesh Sharma
Akhilesh Sharma

Reputation: 1628

Here is the code to make the following work. This is basically the concept of the key value pairs in the ASSOCIATIVE ARRAY

//Assuming the name of the fruits column is fruit_name and color column is color_name

$value = array();
$query = "SELECT * FROM colors as c LEFT JOIN fruits as f ON c.color_id=f.color_id";
$result = mysql_query($query);

if(mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_array($result))
{
$fruitname = $row['fruit_name'];
$colorname = $row['color_name'];
$value['$fruitname'] = $color_name;
}
}

At the end of the loop the value array will have the values as required.

Thanks J

Upvotes: 1

UltraInstinct
UltraInstinct

Reputation: 44454

This way?

$value = array();
while ($r = mysql_fetch_array($mySqlQuery)){
    $value[$r['fruit_field']] = $r['color_field'];
}

MySQL query isnt here; hope you already have it.

Upvotes: 1

Related Questions