PHP Mysql subraction issues

General PHP Help
#1

Here’s the deal I’m using a pretty basic php pdo CRUD framework that I’m customizing for my workplace to use as a tracker. What has been requested of me is to set it up so that for example 10 minutes has passed the cell color would change from green to red. The big problem I’m running into is that it would appear that a part of my code is either not able to fetch or query the database and pull the required info. Considering everything else within the coding works as expected. I’m pretty sure I’ve narrowed down my issue. I just don’t know what I’m doing wrong. I’m using views created within phpmyadmin to aid in helping me keep coding cleaner. So the calculation for the “waiting” column is being done within the view itself.

Here’s part of the code I’m working with:
include ‘config/database.php’;
$page = isset($_GET[‘page’]) ? $_GET[‘page’] : 1;
$records_per_page = 5;
$from_record_num = ($records_per_page * $page) - $records_per_page;
$query = “SELECT ptfin, ptname, ptdob, pore, notes, labstat, rtstat,radstat,waiting FROM dashboard_view WHERE (labstat != ‘’ AND labstat IS NOT NULL) OR (radstat != ‘’ AND radstat IS NOT NULL) OR (rtstat != ‘’ AND rtstat IS NOT NULL) ORDER BY waiting DESC LIMIT :from_record_num, :records_per_page”;
$stmt = $con->prepare($query);
$stmt->bindParam(":from_record_num", $from_record_num, PDO::PARAM_INT);
$stmt->bindParam(":records_per_page", $records_per_page, PDO::PARAM_INT);
$stmt->execute();
$num = $stmt->rowCount();

$curDate = time();
$mysqlTimestamp = $row[‘waiting’]; //This is the piece of code that is giving me issues.
$dif = strtotime($curdate) - strtotime($mysqlTimestamp);

if($num>0) {

if($dif < 10000) {
$tdStyle=‘background-color:green;’;
} else {
$tdStyle=‘background-color:red;’;
}

echo “

”; 
echo "<tr>";
    echo "<th>FIN#</th>";
    echo "<th>Name</th>";
    echo "<th>PorE</th>";
    echo "<th>Notes</th>";
    echo "<th>Modalities</th>";
    echo "<th>Waiting</th>";
echo "</tr>";

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){

extract($row);
 
    echo "<tr>";
    echo "<td width=15>{$ptfin}</td>";
    echo "<td width=100>{$ptname}</td>";
    echo "<td width=5>{$pore}</td>";
    echo "<td width=200>{$notes}</td>";
    echo "<td width=170><img src=".$labstat." ><img src=".$rtstat." ><img src=".$radstat." ></td>";
    echo "<td style=\"$tdStyle\" width=50>{$waiting}</td>";
    //echo "<td width=5>{$dif}</td>"; code for testing visual output of timestamp mathmatics
#2

what should that mean? enable mysql throw exception.

#3

A few comments on the code…

  1. Stop using extract. It’s lazy and will cause problems on apps of a decent size with variables “magically” appearing out of nowhere.
  2. Stop using multiple echo’s when only one is needed. You can also use HEREDOC.
  3. Use single quotes in your elements and you wont have to do escaping gymnastics.
  4. The curly braces around the variables are not needed, but they will be when you stop using extract.
  5. You need to escape your DB output with htmlentities.
#4

I appreciate the comments. However, I’m a very amateur coder and I’m learning things on the fly. I don’t know all the lingo. I know enough to go out, find code that performs a task, and have enough knowledge to be able to modify that code to perform the tasks that I want it to perform.

#5

It’s all good. We were all beginners at one time. I did the same exact thing you described when I started.

#6

I found a means that works. Wasn’t what I was looking for but it does do the job.
So here’s what I found.
Someone mentioned javascripting would do what I want. So, I dug into looking at google and kept finding what appears to be a fairly common script.

var cells = document.getElementById(“2”).getElementsByTagName(“td”);
for (var i = 0; i < cells.length; i++) {
if (cells[i].innerHTML <“00:06:00”) {
cells[i].style.backgroundColor = “green”;
}
}

To get the effect I wanted I changed one of my rows to read:
“

{$waiting}”;_

So, in essence that cell is always red, but the script overlays green until the designated time has passed.

I realize this probably isn’t the best way to code. But for someone who is learning and in my mid 40’s it’s start.

This allowed me to get rid of all of this code:
$curDate = time();
$mysqlTimestamp = $row[‘waiting’]; //This is the piece of code that is giving me issues.
$dif = strtotime($curdate) - strtotime($mysqlTimestamp);
if($dif < 10000) {
$tdStyle=‘background-color:green;’;
} else {
$tdStyle=‘background-color:red;’;
}

echo “

”;

Also I noticed this is actually cutting out some of my code. But I think you get the idea.

#7

If you’re still working on it

This code runs way before you actually fetch any rows from the db. so $row does not exist, this should be easily seen if you have error reporting on.

$mysqlTimestamp = $row[‘waiting’]; //This is the piece of code that is giving me issues.
Sponsor our Newsletter | Privacy Policy | Terms of Service