PHP Forum

**rahulp959** · 06-07-2010, 12:23 PM

PHP Code:


			
<?php
/*Coded By Rahul A. Parkar, ZAU Webmaster 2010-Present) */
/*ZAU Roster Activity Checker*/

$con = mysql_connect("localhost","xxx","xxx") or die("Failed to connect: " . mysql_error());
mysql_select_db("fsprosho_postnuke", $con) or die("Failed to select database: " . mysql_error());

$con2 = mysql_connect("localhost","xxx","xxx") or die("Failed to connect: " . mysql_error());
mysql_select_db("fsprosho_postnuke", $con2) or die("Failed to select database: " . mysql_error());

$con3 = mysql_connect("localhost","xxx","xxx") or die("Failed to connect: " . mysql_error());
mysql_select_db("fsprosho_postnuke", $con2) or die("Failed to select database: " . mysql_error());

$result = mysql_query("SELECT * FROM controllers ORDER BY CID") or die("Failed to query: " . mysql_error());

echo "<h1>Controllers</h1>";

echo "<table>
<tr>
<th class=\"controllers\">CID</th>
<th class=\"controllers\">Name</th>
<th class=\"controllers\">CI</th>
<th class=\"controllers\">Rating</th>
<th class=\"controllers\">Hours</th>
<th class=\"controllers\">Active?</th>
</tr>";


while($row = mysql_fetch_array($result))
{
    
  $cid = $row['CID'];
  $month = date("Y-m-d",strtotime("+4 week"));
  $month2 = strtotime("-4 week");
  $month_q = mysql_query("SELECT SUM(duration) AS month FROM controller_log WHERE start >= '$month2' AND CID = '$cid'", $con2) or die("Failed to query: " . mysql_error());;
  $last_controlled = mysql_query("SELECT LAST(date) AS last_controlled FROM controller_log WHERE cid='$cid' AND duration >=3600", $con3) or die("Failed to query: " . mysql_error());;
  $row3 = mysql_fetch_object($last_controlled);
  mysql_free_result($last_controlled);
  $row4 = mysql_fetch_object($month_q);
  mysql_free_result($month_q);
   $month_test = date_diff($row3->last_controlled, $month);
  if ($month_test >= 28) { mysql_query("UPDATE `controllers` SET `active`=0 WHERE CID = '$cid'", $con2); }
  ?>
  <td><?php echo $row['CID']; ?> </td>
  <td><?php echo $row['name']; ?></td>
  <td><?php echo $row['ci']; ?></td>
  <td><?php echo $row['rating']; ?></td>
  <td align="center"><?php
$my_t=getdate($row4->month);
if ($row4->month > 3600) { echo "$my_t[hours]:$my_t[minutes]"; } elseif ($row4->month < 3600) { echo "<font color=\"#FF0000\">" . "$my_t[minutes] Minutes" . "</font>"; } ?></td>
  <td><?php if ($row['active'] == 1) { echo "<img src=\"/images/check.gif\" />"; } else { echo "<img src=\"/images/x.gif\" />";} ?> </td>
  </tr>
  <?php
}
?>
</table><?
mysql_close($con);
mysql_close($con2);
mysql_close($con3);
?>

It gives me this error

Code:

Failed to query: You have an error in your SQL syntax; check the manual  that corresponds to your MySQL server version for the right syntax to  use near '(date) AS last_controlled FROM controller_log WHERE  cid='800015' AND duration >=' at line 1

I have no idea why it won't work.. XD... Anybody else want to give it a shot?

**SnapCount80** · 06-07-2010, 02:37 PM

I'm not certain but my guess would be that when you are trying to retrieve the last date from the date column in the database, mySQL is reading that as the date() function and returning an error because the function is not formatted correctly.

PHP Code:


			
 $last_controlled = mysql_query("SELECT LAST(date) AS last_controlled  FROM controller_log WHERE cid='$cid' AND duration >=3600", $con3) or  die("Failed to query: " . mysql_error());;

You might try changing the column name in the database to something like "col_date" or something to avoid keyword issues. Don't forget to update the php code too if you do.

You might also look at the number of semicolons at the end of each line. It looks like there might be a few lines with extras.

Hope this helps.

**rahulp959** · 03:09 PM

Okay... So I tried your suggestion... its still got something weird about it... Meet the code + MySQL Errors as they stand now.

PHP Code:


			
<?php
/*Coded By Rahul A. Parkar, ZAU Webmaster 2010-Present) */
/*ZAU Roster Activity Checker*/

$con = mysql_connect("localhost","xxx","xxx") or die("Failed to connect: " . mysql_error());
mysql_select_db("fsprosho_postnuke", $con) or die("Failed to select database: " . mysql_error());

$con2 = mysql_connect("localhost","xxx","xxx") or die("Failed to connect: " . mysql_error());
mysql_select_db("fsprosho_postnuke", $con2) or die("Failed to select database: " . mysql_error());

$con3 = mysql_connect("localhost","xxx","xxx") or die("Failed to connect: " . mysql_error());
mysql_select_db("fsprosho_postnuke", $con3) or die("Failed to select database: " . mysql_error());

$result = mysql_query("SELECT * FROM controllers ORDER BY CID") or die("Failed to query: " . mysql_error());

echo "<h1>Controllers</h1>";

echo "<table>
<tr>
<th class=\"controllers\">CID</th>
<th class=\"controllers\">Name</th>
<th class=\"controllers\">CI</th>
<th class=\"controllers\">Rating</th>
<th class=\"controllers\">Hours</th>
<th class=\"controllers\">Active?</th>
</tr>";


while($row = mysql_fetch_array($result))
{
    
  $cid = $row['CID'];
  $month = date("Y-m-d",strtotime("+4 week"));
  $month2 = strtotime("-4 week");
  $month_q = mysql_query("SELECT SUM(duration) AS month FROM controller_log WHERE start >= '$month2' AND CID = '$cid'", $con2) or die("Failed to query: " . mysql_error());;
  $row4 = mysql_fetch_object($month_q);
  $last_controlled = mysql_query("SELECT LAST(`col_date`) AS last_controlled FROM `controller_log` WHERE `cid`='". $cid ."' AND `duration` >= '3600'", $con3) or die("Failed to query: " . mysql_error());;
  $row3 = mysql_fetch_object($last_controlled);
   $month_test = date_diff($row3->last_controlled, $month);
  if ($month_test >= 28) { mysql_query("UPDATE `controllers` SET `active`=0 WHERE CID = '$cid'", $con2); }
  ?>
  <td><?php echo $row['CID']; ?> </td>
  <td><?php echo $row['name']; ?></td>
  <td><?php echo $row['ci']; ?></td>
  <td><?php echo $row['rating']; ?></td>
  <td align="center"><?php
$my_t=getdate($row4->month);
if ($row4->month > 3600) { echo "$my_t[hours]:$my_t[minutes]"; } elseif ($row4->month < 3600) { echo "<font color=\"#FF0000\">" . "$my_t[minutes] Minutes" . "</font>"; } ?></td>
  <td><?php if ($row['active'] == 1) { echo "<img src=\"/images/check.gif\" />"; } else { echo "<img src=\"/images/x.gif\" />";} ?> </td>
  </tr>
  <?php
}
?>
</table><?
mysql_close($con);
mysql_close($con2);
mysql_close($con3);
?>

Code:

Failed to query: You have an error in your SQL syntax; check the manual  that corresponds to your MySQL server version for the right syntax to  use near '(`col_date`) AS last_controlled FROM `controller_log` WHERE  `cid`=800015 AND `du' at line 1

**metho** · 06-07-2010, 06:43 PM

LAST() is not a MySQL function. LAST() is an Access/MSSQL agg function.

**tripy** · 06-10-2010, 03:04 AM

Quote:

Originally Posted by metho

LAST() is not a MySQL function. LAST() is an Access/MSSQL agg function.

Yep, the equivalent in a sql database would be the max() function

PHP Code:


			
$last_controlled = mysql_query("SELECT MAX(`col_date`) AS last_controlled FROM `controller_log` WHERE `cid`='". $cid ."' AND `duration` >= '3600'", $con3) or die("Failed to query: " . mysql_error());;