CODEWITHSUNDEEP
phphtmlmysqlcss
Tony W.
Tony W.

Reputation: 11

PHP format table in number format

I'm quite new to PHP, so forgive me for asking this simple question, but I can't get a simple table formatted properly.

I use PHP to get results from MYSQL and show that in a HTML page. The table has ID-labels left and on top, the body is numerical.

Here is the PHP code:

$sql = "CALL pivot('DATA', 'amount', 'scen_id', 'dim2_id', 'where ent_id=''abc1''')";
$res = mysqli_query($mysqli, $sql) or die('Query failed: ' . mysqli_error($mysqli));

$row = mysqli_fetch_assoc($res);

echo '<table>';

// printing table header
foreach($row as $name => $value) {
    echo "<th>$name</th>";
}
// printing table rows
while($row = mysqli_fetch_row($res))
{
    echo "<tr>";

    foreach($row as $cell) 
        echo "<td>$cell</td>\n";

    echo "</tr>\n";
}
echo '</table>';

This is what I get:

scen_id ProdA       ProdB       ProdC       Total
BUD     59903423    66278016    55422083    181603522
FORC    37195845    52753807    41339980    131289632
Total   157331578   185154736   164437401   506923715

While this is better than an empty page, the first line with data (containing ACT... etc) is missing. It does show up when I run the query in Mysql. I want everything to align right, and the numbers to be formatted with thousands separators. I understand that you can't use CSS for that.

I tried anything already, like this one:

echo "<td>".number_format($cell)."</td>\n";

but then the result is this:

scen_id ProdA       ProdB       ProdC       Total
        59,903,423  66,278,016  55,422,083  181,603,522
        37,195,845  52,753,807  41,339,980  131,289,632
        157,331,578 185,154,736 164,437,401 506,923,715

No more row labels.... And also still not aligned right. And still the first line is missing. I hope somebody can tell me how to get this to work. I suppose it's such a standard task that nobody asks this question. I could not find much on the web.

edit: Okay problem solved, thanks guys! For the sake of completeness: here is my working code;

$sql="CALL pivot('DATA', 'amount', 'scen_id', 'dim2_id', 'where ent_id=''abc1''')";
$res=mysqli_query($mysqli, $sql) or die('Query failed: ' . mysqli_error($mysqli));

echo '<table>';

$first = true;
while($row = mysqli_fetch_assoc($res)) {
   if ($first) {
       foreach(array_keys($row) as $name) {
            echo "<th>$name</th>";
       }
       $first = false;
   }
    echo "<tr>";
        foreach($row as $cell) 
        if (!is_numeric($cell)) 
            {echo "<td>$cell</td>\n";} 
        else 
            {echo "<td>".number_format($cell)."</td>\n";}

    echo "</tr>\n";
}

echo '</table>';

?>

and here the CSS code:

table tr td, table tr th {
    text-align: right;
    font-size: 10px;
    font-family: Verdana, 'Lucida Grande', Helvetica, Arial, Sans-Serif;
    padding: 0 4px;
}

Upvotes: 1

Views: 2334

Answers (3)

Colin Rosen
Colin Rosen

Reputation: 21

The reason the first row is missing is probably because you are calling mysqli_fetch_assoc($res); and after that mysqli_fetch_row($res) without resetting the iterator.

you can prevent this by putting mysqli_free_result($result); in between there.

to not include the row labels I'd recomend checking if the $cell variable is a number. You can do this with

if (!is_numeric($cell)) {
  continue; // Go to next item in array
}

// Your code

As for aligning text right. You can do that with css:

table tr td, table tr th {
    text-align: right;
}

Hope this helps.

Edit: As Marc B says. It is better to make one loop instead of two. That way you don't have to call mysqli_free_result($result); either.

Upvotes: 1

Marc B
Marc B

Reputation: 360662

You're wasting/throwing away the first row:

    $row = mysqli_fetch_assoc($res);  <---fetch first row

foreach($row as $name => $value) {  <--- spit out column names, throw away row

while($row = mysqli_fetch_row($res)) <---fetch/display REMAINING rows

A better/working solution would be something more like

$res = mysqli_query(...) or die(mysqli_error(...));
$first = true;
while($row = mysqli_fetch_assoc()) {
   if ($first) {
       foreach(array_keys($row) as $name) {
            ... display field names
       }
       $first = false;
   }
   ... display row data as usual...
}

Upvotes: 1

sikar
sikar

Reputation: 41

Make a twig extension that returns a list of the fields you want, that way you can use php to get the fields. After that use twig's attribute function

{{ attribute(object, fields) }} to call the getters on the object

http://twig.sensiolabs.org/doc/functions/attribute.html

{% set temp = entities|first %}
{% set fields = getObjectFields(temp) %}
<tr>
{% for property_title in fields %} 
    <td>{{ property_title }}</td>
{% endfor %}
</tr>
{% for item in entities %}
    <tr>
        {% for field in fields %}
            <td>{{ attribute(item, field) }}</td>
        {% endfor %}
    </tr>
{% endfor %}

This may work for you.

Upvotes: -1

Related Questions