Solved

Update Oldest Timestamp in an if else

Posted on 2008-10-27
8
415 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
ID: 22817977
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
ID: 22818036
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
ID: 22818093
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Mada123
ID: 22818146
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22818248
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
ID: 22818293
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
ID: 22818378
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
ID: 31510578
You are magnificent!!! Thank you so much for explaining the why behind the code instead of just handing it to me.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to change the link of an image using md5 in php ? 3 47
Get a subdirectory name from a url 5 27
XML extra information 8 28
Accessing specific element - DOM PHP HTML 8 34
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

790 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