How to use PHP variables in MySQL queries with WHILE fetched rows?

Question

I have successfully done the php script that connect to my database and return desired data. But i need to extend this script to insert a variable that will show the price with some calculations (tax,no-tax,margin etc.).

This script show me only price value of the first database row 0.00 in all fetched rows and its not correct - for the first database row is ok because product have 0.0000 price, but the other rows are filled with correct values. It seems like the while loop dont like my $styledprice variable . I can't figure how to show correct field values in all lines. Any ideas much apppreciated? I'm a PHP beginner!

$pricequery = "SELECT rrp FROM my_products";
$b2bprice = mysql_query($pricequery);
$rrps = mysql_fetch_array($b2bprice); 
$price = $rrps['rrp'];
$styledprice = number_format($price, 2, '.', '');

$query = mysql_query("
SELECT 
CONCAT(' <td> ',p.id,' </td><td> ',p.manufacturer,' </td><td> ',p.reference,' </td><td> ',p.name,' </td><td> ',p.quantity,' <td> ','".$styledprice."',' </td> ') AS row
FROM my_products p
");

echo "
<table>
    <tr>
        <td><h5>ID</h5></td>
        <td><h5>Manufacturer</h5></td>
        <td><h5>PN</h5></td>
        <td><h5>Name</h5></td>
        <td><h5>Quantity</h5></td>
        <td><h5>Price</h5></td>
    </tr>";

while($row=mysql_fetch_array($query))
    {
        echo "<tr>".$row['row']."</tr>";
    }

 echo "
 </table>";

Yes i know about mysql_ functions that are deprecated.

it's not common to see html being mixed into the sql statement itself — Professor Abronsius
– Professor Abronsius, Commented Aug 12, 2016 at 8:17
I'm surprised to see such query being included with html tags. — Nana Partykar
– Nana Partykar, Commented Aug 12, 2016 at 8:18
Yes, don't use HTML in your SQL query. Just retrieve the column values with SQL and then add the HTML in PHP. You need to use the number_format() inside the while loop on the price retrieved from MySQL. — KIKO Software
– KIKO Software, Commented Aug 12, 2016 at 8:19
it works? really - how many results does it fetch / should it fetch? You could do that in one sql command rather than two — Professor Abronsius
– Professor Abronsius, Commented Aug 12, 2016 at 8:21

Cagy79 · Accepted Answer · 2016-08-12 08:29:51Z

2

Something like this should work better. It seperates the resultset from the database and the output via HTML.

// Get the Result Set
$result =  mysql_query("SELECT p.id, p.manufacturer, p.reference, p.name, p.quantity FROM my_products p");

// Convert the rows and columns from the Result Set to a PHP Array
$data = array(); // empty array
while ($row = mysql_fetch_assoc($result)) {
    $data[] = $row;
}

// Now you have access any row or column
echo "<table>";
foreach($data as $row){

    // prepare the data
    $formatttedQuantity = number_format($row['quantity'], 2, '.', '');

    // show each Table Row

    echo "<tr>";
    echo "<td>" . $row['id'] . "</td>";
    echo "<td>" . $row['manufacturer'] . "</td>";
    echo "<td>" . $row['reference'] . "</td>";
    echo "<td>" . $row['name'] . "</td>";
    echo "<td>" . $formatttedQuantity . "</td>";
    echo "</tr>";
}
echo "</table>";

edited Aug 12, 2016 at 8:29

answered Aug 12, 2016 at 8:25

Cagy79

1,6201 gold badge19 silver badges25 bronze badges

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

2 Comments

PipBoy2000 Over a year ago

Thanks for Your effort @Cagy79 ! I will check it within a minutes!

PipBoy2000 Over a year ago

Top notch! This is exactly what i need and with this one i can make a lot of flexible modifications! +1 and Answer

Professor Abronsius · Accepted Answer · 2016-08-12 08:29:03Z

1

It looks like you can do this all in one sql command like:

<?php

    $sql = "SELECT * FROM my_products";
    $result = mysql_query( $sql );

    echo "
    <table>
        <tr>
            <td><h5>ID</h5></td>
            <td><h5>Manufacturer</h5></td>
            <td><h5>PN</h5></td>
            <td><h5>Name</h5></td>
            <td><h5>Quantity</h5></td>
            <td><h5>Price</h5></td>
        </tr>";

    while( $row=mysql_fetch_array( $result ) ){

        $price=$row['rrp'];
        $styledprice = number_format( $price, 2, '.', '' );

        echo "
            <tr>
                <td>{$row['id']}</td>
                <td>{$row['manufacturer']}</td>
                <td>{$row['reference']}</td>
                <td>{$row['name']}</td>
                <td>{$row['quantity']}</td>
                <td>{$styledprice}</td>
            </tr>";
    }

     echo "
     </table>";

 ?>

answered Aug 12, 2016 at 8:29

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

1 Comment

PipBoy2000 Over a year ago

Thanks @RamRaider! Looks very simple! I will check it! :)

Collectives™ on Stack Overflow

How to use PHP variables in MySQL queries with WHILE fetched rows?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related