Output MySQL query in HTML table cell with PHP

Question

I'm working in the IT department of my company and it is my responsibility to send daily reports of the calls registered in the Call Center department. I'm new with MySQL, PHP or HTML and I'm already facing my first problems.

My objective is to run some queries in PHP and then output them in a HTML table cell. Let me be more specific. I have access to a telephone record database and I need to count the number of total calls and lost calls of every branch daily. Right now I just run 2 queries in MySQL and then copy-paste them in a .xls table.

I have the right queries, I get the right result in mysql, even in PHP I get the result posted on the webpage (kind of), but I don't know how to create a table, or how to input the result of a query into a specific cell of the table.

Here's what I've got so far:

<?php

$conn=@mysql_connect('host', 'username', 'password', 'asteriskcdrdb.cdr');
echo ('total calls ROPCW: ');
$result=mysql_query('select count(*) from asteriskcdrdb.cdr where calldate like "2016-04-11%" and dst="020" and disposition="ANSWERED" and duration > "10" ');
echo mysql_result($result, 0);

mysql_close($conn);
?>

This code will post on my page the count of total calls made on 4.11.2016 like this: total calls ROPCW: 369

My objective is to create a table like this:

So the result of that query I mentioned above would go on the ROPCW->Total cell (where is 305) on the left side, on the right side are the monthly reports so far.

1. Don't use mysql_, use mysqli_ or PDO. 2. Don't use '@' to suppress errors, it will only lead to anger. Anger leads to the dark side. 3. Ultimately you need to use a foreach loop and build the html table. — Matt
– Matt, Commented Apr 15, 2016 at 13:57
w3.org/TR/html4/struct/tables.html -- HTML specifications for tables — aynber
– aynber, Commented Apr 15, 2016 at 13:57
I know that "@" will only hide the errors, it's just a temporary fix. I will use myslqi. — Munteanu Radu
– Munteanu Radu, Commented Apr 15, 2016 at 14:00

tanovellino · Accepted Answer · 2016-04-15 14:23:27Z

0

You can work out one big query or use a view to gather the data you need from a single source. Other suggestion would be, looping through all your branches and gather all the data you need, to build an associative array indexed similar to this:

$records[branch] = [date => [total => x, lost => y], mtd => [total => X, lost => Y]];

Then just do a:

foreach ($records as $branch => $record)
{
     echo string to build table row
}

answered Apr 15, 2016 at 14:23

tanovellino

733 bronze badges

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

Comments

tanovellino · Accepted Answer · 2016-04-15 14:10:55Z

-2

I suggest use mysql_fetch_object or mysql_fetch_assoc functions instead of mysql_result. Basically because those functions fetch an entire row instead of one single cell value. For your example query works fine because your aggregate function returns only one cell and row. But for your desired output won't work. Keep in mind that you can inject HTML code within PHP and vice versa. Just do something like:

The idea would be run a select * from yourTable; Then,

$result = mysql_query($sql);
echo "<table>\n";
echo "<tr><th>Branch</th></tr>\n";
while ($callRecord = mysql_fetch_object($result)) {
    echo "<tr><td>{$callRecord->branch}</td></tr>\n";
}
echo "</tr>\n";
echo "</table>\n";

That should output a list of all tour branches. this is a guideline. Hope this helps!!

You can go to www.php.net and choose between mysql_fetch functions which one will work better for you.

answered Apr 15, 2016 at 14:10

tanovellino

733 bronze badges

2 Comments

RiggsFolly Over a year ago

I suggest you do not suggest the use of any mysql_ database extension functions as they have been deprecated for years now and have been removed from PHP7 completely. Instead encourage newby developers to use either mysqli_ or PDO

tanovellino Over a year ago

Good point Riggss!!!. You can use my answer as a guideline and as encouragement to do further investigation towards the use of either mysqli_ or PDO as Riggss highlighted. Again, if anyone is already using mysql_result, make it work with the tools and libraries you are familiar with and once the concept is understood consider migrating to the new extensions

Collectives™ on Stack Overflow

Output MySQL query in HTML table cell with PHP

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related