Solved

Update Oldest Timestamp in an if else

Posted on 2008-10-27
8
411 Views
Last Modified: 2013-12-13
I need to update a record based on the oldest timestamp from returned records in another query. The Timestamp variable has been named: RTime and shows on the page and there are pending records in the table.

$Off = 3
$AppOff = 1

I'm not getting an error, it's just not happening.

Please tell me where I'm going wrong. Thanks very much.
if ( "$Off" > $result3['AppOff']) {

   $query = "UPDATE Requests SET Approval = 'Approved' WHERE Timestamp = (select min($Timestamp) from Requests WHERE Approval = 'Pending' ) ";

} else {
 

}

Open in new window

0
Comment
Question by:Mada123
  • 4
  • 4
8 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
How do you know it does not happen? You are not executing the query, just assigning it to $query.

$AppOff is not invloved, but $result3['AppOff'] is.

min($Timestamp) should probably be  min(Timestamp)

Put in some echo statements to see what is going on:
if ( "$Off" > $result3['AppOff']) {

   $query = "UPDATE Requests SET Approval = 'Approved' WHERE Timestamp = (select min(Timestamp) from Requests WHERE Approval = 'Pending' ) ";

  echo "did it";

} else {

  echo "didn't do it!";

  echo 'Off='.$Off.', AppOff='.$result3['AppOff'];

}

Open in new window

0
 

Author Comment

by:Mada123
Comment Utility
Good idea. I actually changed it a bit:

I get the following results

Andrew - Yep, that's true
Pending - Should be Approved. That's what I'm trying to update.
if ( "$Off" > $result3['AppOff']) {

   $query = "UPDATE Requests SET Approval = 'Approved' WHERE Timestamp = (select min(Timestamp) from Requests WHERE Approval = 'Pending')";

  echo "$VFname"; echo "$Status";

} else {

  echo "didn't do it!";

  echo 'Off='.$Off.', AppOff='.$result3['AppOff'];

}

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
Is the value of $VFname == 'Andrew' ? If that is the case, the $query variable has been set. If you want to change anything in the database you would have to execute the query. That is done by using the mysql_query() function.
0
 

Author Comment

by:Mada123
Comment Utility
No, Andrew is not coded in. It's pulling that from the table. I've copied the entire php so you could see.

I'm a novice at this, so I have to create multiple queries to do what I need to get done.
<?
 

	include_once ("../../auth.php");

	include_once ("../../authconfig.php");

	include_once ("../../check.php");
 

if (!($check['team']=='Pharmacy'))

    {

        echo 'Your Primary Skillset is not Pharmacy.';

		exit();

    }

    //Get the Month, Day and Year from URL

    $Month = $_GET['Month']; 

    $Day = $_GET['Day']; 

    $Year = $_GET['Year']; 

    $Skillset = $_GET['Skillset']; 

    
 

    //Get the DOW and how many people allowed off that day 

	$connection = mysql_connect($dbhost, $dbusername, $dbpass);

	$SelectedDB = mysql_select_db($dbname);    

  $query = "SELECT DOW, Pharmacy from Cal WHERE Month = '$Month' AND Day = '$Day' AND Yr = '$Year'";

  $result = mysql_query ($query, $connection) or die ("Unable to execute query.");  

    while ($row = mysql_fetch_array($result))

    {

    $DOW = $row['DOW'];

    $Off =  $row['Pharmacy'];

    }

    // Get the users First Name, Last Name, Email

  $query2 = "SELECT uname, EFname, ELname, Email FROM Employees WHERE uname = '$USERNAME'";

  $result2 = mysql_query ($query2, $connection) or die ("Unable to execute query2.");  

    while ($row = mysql_fetch_array($result2))

    {

    $EFname = $row['EFname'];

    $ELname =  $row['ELname'];

    $Email =  $row['Email'];

    }  

    

    //Count how many people are already off

  $query3    = "SELECT COUNT(*) as AppOff FROM Requests WHERE Month='$Month' AND Day='$Day' AND Year ='$Year' AND Skillset='$Skillset' AND Approval = 'Approved'";

  $rs_result3   = mysql_query($query3) or die(mysql_error());

  $result3=mysql_fetch_array($rs_result3);
 

     //Count how many are pending

  $query4    = "SELECT COUNT(*) as AppPend FROM Requests WHERE Month='$Month' AND Day='$Day' AND Year ='$Year' AND Skillset='$Skillset' AND Approval = 'Pending'";

  $rs_result4   = mysql_query($query4) or die(mysql_error());

  $result4=mysql_fetch_array($rs_result4);
 

     //Get everyone who has or wants this day off

  $query5    = "SELECT * From Requests WHERE Month='$Month' AND Day='$Day' AND Year ='$Year' AND Skillset='$Skillset' ORDER BY Timestamp ";

  $result5 = mysql_query ($query5, $connection) or die ("Unable to execute query5.");  

    while ($row = mysql_fetch_array($result5))

    {

    $RID =  $row['RID'];

    $VFname = $row['RFname'];

    $VLname =  $row['RLname'];

    $Status =  $row['Approval'];

    $RTime = $row['Timestamp'];

    $List_block .="$VFname $VLname - $Status ($RTime)<br>

    ";} 
 

    if ( "$Off" > $result3['AppOff']) {

    // If someone cancels or we are allowing more people off, approve oldest timestamp for skillset

   $query6 = "UPDATE Requests SET Approval = 'Approved' WHERE Timestamp = (select min(Timestamp) from Requests WHERE Approval = 'Pending')";

  echo "$VFname"; echo "$Status";

} else {

  echo "didn't do it!";

  echo 'Off='.$Off.', AppOff='.$result3['AppOff'];

}
 

?> 

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
Ok, I see. $VFname is the last RFname from $query5. That means that "$Off" > $result3['AppOff'] is true. The reason nothing is happening, is because you are not executing the query. After assigning the SQL statement to the $query6 variable, you need to call mysql_query(), just like you do with all the other queries. Below I have reformatted your query for improved readbillity, and I added a call to mysql_error(). Note that if two (or more) records have the same minimum timestamp, both/all will be updated. If you want to make sure that only one record is updated, you can add "LIMIT 1" at the end of the query.
   $query6 = "UPDATE Requests SET Approval = 'Approved' 

              WHERE Timestamp = (

                select min(Timestamp) 

                from Requests 

                WHERE Approval = 'Pending')";

   $result6 = mysql_query($query6, $connection) or 

     die("Unable to execute query6. Error: ".mysql_error());

Open in new window

0
 

Author Comment

by:Mada123
Comment Utility
I'm getting this error:

Unable to execute query6. Error: You can't specify target table 'Requests' for update in FROM clause

0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
Comment Utility
Ok, great, now we are getting somewhere! :)

The update statement is wrong, you can't update the same table you are using in the where clause. But luckily there is an easy workaround. You can split the query in two, first fetch the minimum timestamp, then use it in the next query. We could use a "normal" select and fetch the timestamp into PHP, but we don't need to, because mysql can keep a variable for us in the database server:
   mysql_query("SELECT @min_timestamp:=min(Timestamp) 

                FROM Requests 

                WHERE Approval = 'Pending'") 

     or die('temp table: '.mysql_error());

   $query6 = "UPDATE Requests SET Approval = 'Approved' 

              WHERE Timestamp = @min_timestamp";

   $result6 = mysql_query($query6, $connection) or 

     die("Unable to execute query6. Error: ".mysql_error());

Open in new window

0
 

Author Closing Comment

by:Mada123
Comment Utility
You are magnificent!!! Thank you so much for explaining the why behind the code instead of just handing it to me.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now