Help with Logic & Sort Script to View Records

HurricaneDigital · May 21, 2010

Ok so now that all my add/remove/update functions are working, I'm trying to improve the way the recorded data is displayed. I am basing this web app off an excel template that we currently use. I need the rows to be sorted by "Call Today" option first, then by "Date Last Called". Eventually I want to add color coding to it as well but that's not necessary yet.

I think I need to us an IF statement, but not sure how to tie it in with the displayed results for each row of data. I think this is something along the lines of what I need, but I know it's probably much more complicated:

if (($date - $date_last_called)>=$call_frequency), echo Yes, echo No

Is this something I'm going to be able to code as a novice or is it gonna get really complicated?

Appreciate any help!

<?php
$username="xxxxxxxx";
$password="xxxxxxxx";
$database="xxxxxxxx";
$con=mysql_connect(localhost,$username,$password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db($database, $con);

$query="SELECT * FROM call_tracker ORDER BY date_last_called ASC ";
$result=mysql_query($query);
$call="Yes";


echo "<table border='1'>
<tr>
<th>Store</th>
<th>Work Order</th>
<th>Customer Name</th>
<th>Contact Number</th>
<th>Date Last Called</th>
<th>Call Frequency</th>
<th>Call Today</th>
<th>Notes</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['location'] . "</td>";
  echo "<td>" . $row['work_order'] . "</td>";
  echo "<td>" . $row['customer_name'] . "</td>";
  echo "<td>" . $row['contact_number'] . "</td>";
  echo "<td>" . $row['date_last_called'] . "</td>";
  echo "<td>" . $row['call_frequency'] . "</td>";
  echo "<td>" . $call . "</td>";
  echo "<td>" . $row['notes'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
?> 

<html>
<head>
<title>Customer Call Tracker </title>
</head>
<body>

<br>

<form>
<input type="button" onclick="window.location.href='form.php'" value="Add Record"></input>
</form>

<form action="del.php" method="post">
<input type="text" name="work_order" />
<input type="submit" value="Picked Up" />
</form>

<form action="modify.php" method="get">
<input type="text" name="work_order" />
<input type="submit" value="Modify" />
</form>

<form action="call.php" method="get">
<input type="text" name="work_order" />
<input type="submit" value="Call" />
</form>

</body>
</html>

$call=Yes is just a place holder for now. I do have a field in the database for call_today.

leehanken · May 22, 2010

I think the simplest way is to load the rows of data into an array, then sort this with usort (using a user-defined comparison function that does the logic you describe), then finally output the sorted array.

Cagecrawler · May 22, 2010

You can use the following query:

$query = 'SELECT *, (NOW() - date_last_called) > call_frequency) AS call_today FROM call_tracker ORDER BY call_today DESC, date_last_called ASC;'

The section I've added ((NOW() - date_last_called) > call_frequency)) works out the time since the last call and compares it to the call frequency to see if a call is needed. It returns a boolean (actually tinyint(1) in MySQL) which means we can use it in the ORDER BY clause.

If taken a few assumptions with your database. I've assumed that date_last_called is stored as a datetime and call_frequency is stored as a int representing the number of seconds between calls. If it is something different you'll need to either adjust the code or the database to fit.

jcbones · May 22, 2010

Used Cagecrawler's db query, and added in some row styling. Each row is a different color, and call today rows are green. This is just to point you in the right direction.

<?php
$username="xxxxxxxx";
$password="xxxxxxxx";
$database="xxxxxxxx";
$con=mysql_connect(localhost,$username,$password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db($database, $con);

$query = 'SELECT *, (NOW() - date_last_called) > call_frequency) AS call_today FROM call_tracker ORDER BY call_today DESC, date_last_called ASC;'
$result=mysql_query($query);
$i = 0;

echo "<table border='1'>\n
<tr>\n
<th>Store</th>\n
<th>Work Order</th>\n
<th>Customer Name</th>\n
<th>Contact Number</th>\n
<th>Date Last Called</th>\n
<th>Call Frequency</th>\n
<th>Call Today</th>\n
<th>Notes</th>\n
</tr>\n";

while($row = mysql_fetch_array($result))
  {
  $style = ((++$i % 2) = 0) ? 'style="background-color:#BFC8FF"' : 'style="background-color:#AAB2E2"';
  $style= ($row['call_today'] == 1) ? 'style="background-color:#007F0E;color:#FFFFFF;"' : $style;
  $call = ($row['call_today'] == 1) ? 'Yes' : 'No';
  echo "<tr $style>\n"
	. "<td>" . $row['location'] . "</td>\n"
	. "<td>" . $row['work_order'] . "</td>\n"
	. "<td>" . $row['customer_name'] . "</td>\n"
	. "<td>" . $row['contact_number'] . "</td>\n"
	. "<td>" . $row['date_last_called'] . "</td>\n"
	. "<td>" . $row['call_frequency'] . "</td>\n"
	. "<td>" . $call . "</td>\n"
	. "<td>" . $row['notes'] . "</td>\n"
	. "</tr>\n";
  }
echo "</table>\n";
?>

<html>
<head>
<title>Customer Call Tracker </title>
</head>
<body>

<br>

<form>
<input type="button" onclick="window.location.href='form.php'" value="Add Record"></input>
</form>

<form action="del.php" method="post">
<input type="text" name="work_order" />
<input type="submit" value="Picked Up" />
</form>

<form action="modify.php" method="get">
<input type="text" name="work_order" />
<input type="submit" value="Modify" />
</form>

<form action="call.php" method="get">
<input type="text" name="work_order" />
<input type="submit" value="Call" />
</form>

</body>
</html>

HurricaneDigital · May 24, 2010

Ok, first of all, thanks so much for the help!

I've tried the combination of cagecrawler and jcbones code but I'm getting an error message when I run the page:

Parse error: syntax error, unexpected '=' in /home/xxxxxx/public_html/rma/view.php on line 31

I updated the field type for date_last_called to DATETIME in phpMyAdmin and copied the posted code into the view.php file. I'm don't think the existing data is causing the problem, but just in case I deleted the test data, however the error message wasnt affected Dreamweaver highlighted the row as well saying code error.

while($row=mysql_fetch_array($result))  
{  
$style=((++$i % 2) = 0) ? 'style="background-color:#BFC8FF"' : 'style="background-color:#AAB2E2"';
$style=($row['call_today'] == 1) ? 'style="background-color:#007F0E;color:#FFFFFF"' : $style;
$call=($row['call_today'] == 1) ? 'Yes' : 'No';

The first $style line is the one giving the error (line 31).

HurricaneDigital · May 25, 2010

That worked perfectly! There was just one little problem that was caused by a typo. the first $style line need double "=".

while($row=mysql_fetch_array($result) or die(mysql_error()))  
{  
$style=((++$i % 2) == 0) ? 'style="background-color:#BFC8FF"' : 'style="background-color:#AAB2E2"';
$style=($row['call_today'] == 1) ? 'style="background-color:#007F0E;color:#FFFFFF"' : $style;
$call=($row['call_today'] == 1) ? 'Yes' : 'No';

I also modified the SQL string to use CURDATE() instead of NOW() and switched the field's data type to DATE from DATETIME, since I need to use days not seconds.

$query="SELECT *, ((CURDATE() - date_last_called) > call_frequency) AS call_today FROM call_tracker ORDER BY call_today DESC, date_last_called ASC";

Sign In

Help with Logic & Sort Script to View Records

Recommended Posts

HurricaneDigital

Link to comment

Share on other sites

leehanken

Link to comment

Share on other sites

Cagecrawler

Link to comment

Share on other sites

jcbones

Link to comment

Share on other sites

HurricaneDigital

Link to comment

Share on other sites

HurricaneDigital

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information