[Resolved] How could I hide a row in a table.

[hazmatt]

I have a query that searches for any jobs that occured between July 1st, 2003 and 30 days ago.

I have successfully queried the database (MySQL) for any jobs after July 1st 2003. I read that MySQL ignores dates in the future so I used PHP to filter out the result set from MySQL to see if the dates occured at least 30 days ago. Currently I have PHP change the date to "Not Yet" if the job did not occur atleast 30 days ago. I would like to have PHP hide the row instead of show "Not Yet".

Is there a way I can do this? Sorry if this is confusing.

Hopefully the code will help.

Thank you for your time in advance.

<?php

// BUILD FORM FOR RE-SORTING \\
$sort_form_header = "<form action=\"./\" method=\"post\">
<input type=\"hidden\" name=\"page\" value=\"email\">
<input type=\"hidden\" name=\"sub\" value=\"ispy5\">";

$days_past = '30';

include($unix_root . "libs/niftywerks/build_list");
include($unix_root . "libs/niftywerks/html_select");

$where_list[] = "b.job_date >= '2003-07-01'";
$where_list[] = "b.step like 'R%'";
$where_list[] = "b.step <> 'RR'";
$where_list[] = "b.step <> 'RD'";
$where_list[] = "FIND_IN_SET('relief', j.kind)";

if($sort_by == '') {
	$sort_by = 'l.last_name';
}
if($sort_order == '') {
	$sort_order = 'ASC';
}

 // $sort_form_header 

$where_statement = build_list($where_list, ' && ');

if($where_statement != "") {

  $where_statement = "WHERE $where_statement"; 

 }

$new_qtx = "
      SELECT j.id, b.bid_id, MAX(b.job_date),
      j.hosp_id, l.last_name, l.first_name, j.email, i.fax, l.email
      FROM jobs_web as j
      LEFT JOIN jobs_bid as b ON (j.id=b.job_id)
      LEFT JOIN login as l on (b.login=l.login)
      LEFT JOIN info_hosp as i ON (j.hosp_id=i.id)
      $where_statement
      GROUP BY b.bid_id
      ORDER BY $sort_by $sort_order;";

$new_q = mysql_query($new_qtx, $vet_sqlid);
$num_jobs = mysql_num_rows($new_q);

$sort_sel = html_select($sort_by, 'selected');
$sort_ord_sel = html_select($sort_order, 'selected');

echo $sort_form_header;
?>

<select name="sort_by" size=1>
<option value="j.id" <?php echo $sort_sel['j.id'] ?>>Job ID
<option value="i.name" <?php echo $sort_sel['i.name'] ?>>Hospital
<option value="l.last_name" <?php echo $sort_sel['l.last_name'] ?>>Doctor
<option value="b.bid_id" <?php echo $sort_sel['b.bid_id'] ?>>Bid ID
<option value="b.job_date" <?php echo $sort_sel['b.job_date'] ?>>Bid Date
</select>

 <select name="sort_order" size=1>
<option value="ASC" <?php echo $sort_ord_sel['ASC'] ?>>Ascending
<option value="DESC" <?php echo $sort_ord_sel['DESC'] ?>>Descending
</select>
<input type="submit" value="Sort">
</form>

<form action="./" method="post">
<input type="hidden" name="page" value="email">
<input type="hidden" name="sub" value="queue_ispy">
<input type="hidden" name="days_past" value="<?php echo $days_past; ?>">

<script language="php">

echo "<b>$num_jobs jobs found </b>";

echo "<table border=1 cellpadding=4>
<tr bgcolor=\"#aaaaff\">
<td align=\"center\"><b>Job ID</b></td>
<td align=\"center\"><b>Bid ID</b></td>
<td align=\"center\"><b>Bid Date</b></td>
<td align=\"center\"><b>Hospital</b></td>
<td align=\"center\"><b>Vet</b></td>
<td align=\"center\"><b>Last ISPY</b></td>
<td align=\"center\"><b>Email Hosp</b></td>
<td align=\"center\"><b>Fax Hosp</b></td>
<td align=\"center\"><b>ISPY Doc</b></td>
            </tr>";

// we will toggle each row's bgcolor
$color_one = "ffffff";
$color_two = "aaffaa";
$the_color = $color_one;

for($cnt = 0; $cnt < $num_jobs; $cnt++) {

list($job_id, $tmp_id, $bid_date, $tmp_hosp, $vet_last, $vet_first, $tmp_email, $tmp_fax,  $vet_email) = mysql_fetch_row($new_q);

/* Check to make sure the bid date occured at least 30 days ago.  If so mark it */              

$last_date = date("Y-m-d", mktime(0,0,0, date(m), date(d)-30,date(Y)));

           if ($bid_date > $last_date) {
               $bid_date = "<font color=\"#ff0000\">Not Yet</font>";
           }

 $when_qtx = "
 SELECT type IN ('hosp_ispy', 'vet_ispy'), type_id, MAX(DATE_FORMAT(sent, '%b-%d-%Y'))
 FROM mail_queue
 WHERE type_id = '$tmp_id'
 GROUP by sent DESC;";

   list($ispied, $type_id, $ispy_sent) = mysql_fetch_row(mysql_query($when_qtx, $vet_sqlid));

if ($ispied != '1') {

   $last_ispy_sent = "<font color=\"#ff0000\">Never</font>";

   } else {

   $last_ispy_sent = $ispy_sent;

   }

  if ($tmp_email == '[e-mail]') {

 $tmp_email = "<font color=\"#ff0000\">No Email</font>";

 } else {

 $tmp_email = 'H Email';

 }

  if ($vet_email == '[e-mail]') {

 $vet_email = "<font color=\"#ff0000\">No Email</font>";

 } else {

 $vet_email = 'V Email';

 }

$hosp_qtx = "SELECT name FROM info_hosp WHERE id='$tmp_hosp';";

	list($tmp_hosp_name) = mysql_fetch_row(mysql_query($hosp_qtx, $vet_sqlid));

	if ($the_color == $color_two) {

		$the_color = $color_one;

	} else {

		$the_color = $color_two;

	}

	echo "
		<input type=\"hidden\" name=\"ids[]\" value=\"$tmp_id\">
        	<tr bgcolor=\"#$the_color\">
			<td align=\"center\">$job_id</td>
            <td align=\"center\"><a href=\"./?page=jobs&sub=bid_detail&bid_id=$tmp_id\">$tmp_id</a></td>
            <td align=\"center\">$bid_date</td>
			<td align=\"center\">$tmp_hosp_name</td>
            <td align=\"center\">$vet_last, $vet_first</td>
            <td align=\"center\">$last_ispy_sent</td>
            <td align=\"center\"><input type=\"checkbox\" name=\"send_email_$tmp_id\" value=\"1\">$tmp_email</td>
            <td align=\"center\"><input type=\"checkbox\" name=\"send_fax_$tmp_id\" value=\"1\">H Fax</td>
            <td align=\"center\"><input type=\"checkbox\" name=\"send_vemail_$tmp_id\" value=\"1\">$vet_email</td>
            </tr>";
}

echo "</table>";

</script>
	<input type="submit" value="Generate Emails">
</form>

[bastien]

couple of options here

1. change the sql to get exactly what you want then simply echo it out - not quite sure what your crtieria is...

2. a simple if statement (which you already have and use to change the value to Not Yet)

if (crtieria is met){
//echo record
}

[Pig]

Not sure what you mean about mysql not reading dates in the future... I have never had that problem. Can you explain further?

[hazmatt]

bastien, thanks for the reply.

Thank you, I don't know what I was thinking.

I just needed to echo the table if the criteria was met, instead of changing the date to "not yet".

I feel silly.

Oh well, thanks again.

[hazmatt]

Pig:

I was looking at the MySQL manual and they state:

"Here is an example that uses date functions. The following query selects all records with a date_col value from within the last 30 days:

mysql> SELECT something FROM tbl_name
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
(Note that the query will also select records with dates that lie in the future.) "

When I was running a query similar to the one above MySQL was returning dates which were in the future thereby making my result set incorrect. There fore I used PHP to do the final date verification, making sure that the dates occured atleast 30 days or more ago.

It is possible that I misunderstood what the MySQL documentation was stating and I just couldn't figure out a way to work around it. Either way thankfully PHP saved the day for me.

[Pig]

Gotcha. I should have realized you were talking about date functions. I always use unix timestamps unless I am dealing with dates WAY WAY in the future. If you are not already, you might want to check them out. They are superior to MySQL timestamps (YYYYMMDDHHIISS) except where the date range is extremely large.