PHP: How can variables be assigned values from a MySQL Query?

Question

I have a MySQL database, 'fruits', with 10 rows like this:

id | variable | value  
1  | apples  | 5  
2  | oranges  | 6

How can I efficiently use a query to assign PHP variables values, so I can use them elsewhere:
$apples = 5;
$oranges = 6;

Or to put it another way, assign values to PHP variables based on the 'variables' column in the database.

The only way I can make it work is this:

$con = mysqli_connect($servername, $username, $password, $dbname);

$sql="SELECT variable, value FROM fruits where variable = 'oranges'";
$result=mysqli_query($con,$sql);
$row=mysqli_fetch_array($result,MYSQLI_ASSOC);
$oranges = $row["value"];

I could do this 10 times but it seems terrible, there must be a better way, presumably involving some sort of loop. Not a professional at this (clearly) so complete code so I can see how it all works would be much appreciated.

Thanks for your help!

You might be looking for variable variables, although in almost every scenario, it's better to use an array instead. Have an array, such as $fruits[$row['variable']] = $row['value'];, then use echo $fruits['oranges']; (be sure to define the array before entering the loop, to avoid Undefined variable... notices). — Qirel
– Qirel, Commented Sep 19, 2016 at 22:30
extract array_column($row, 'value', 'variable'); but as @Qirel says, better to work with an array instead — Mark Baker
– Mark Baker, Commented Sep 19, 2016 at 22:32
do you know the order of the values in db like apple then orange then ... — Ahmed Gamal
– Ahmed Gamal, Commented Sep 19, 2016 at 22:35

Ruben Nunez · Accepted Answer · 2016-09-19 23:24:46Z

3

Here an example of a Variable-Variable:

$sql="SELECT * FROM fruits";
$result = mysqli_query($conn,$sql);

if ($result->num_rows > 0) {

    while ($row = $result->fetch_assoc()) {
        $variable = $row["variable"];
        $$variable = $row["value"];

        echo("Name: " . ${variable} . " Value: " . $$variable . "<br>");
    }
}

Best regards

answered Sep 19, 2016 at 23:24

Ruben Nunez

1754 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Iqbal · Accepted Answer · 2016-09-20 03:17:27Z

1

Can use this too.

$con = mysqli_connect($servername, $username, $password, $dbname);

$sql="SELECT variable, value FROM fruits";
$result=mysqli_query($con,$sql);
while($row=mysqli_fetch_array($result,MYSQLI_ASSOC)){
    ${row["variable"]}= $row["value"];
}

answered Sep 20, 2016 at 3:17

Iqbal

731 silver badge9 bronze badges

Comments

jakub wrona · Accepted Answer · 2016-09-19 22:36:48Z

-1

I can't see a single reason to do so. An array is a variable itself. It's super easy to access any single element with: $rows[2]['variable'] or $rows[1]['value'].

But if you really need some mess you could combine the extract() and a foreach loop.

answered Sep 19, 2016 at 22:36

jakub wrona

2,25219 silver badges19 bronze badges

Collectives™ on Stack Overflow

PHP: How can variables be assigned values from a MySQL Query?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related