Update Oldest Timestamp in an if else

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

Mada123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roger BaklundCommented:
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
Mada123Author Commented:
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
Roger BaklundCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Mada123Author Commented:
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
Roger BaklundCommented:
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
Mada123Author Commented:
I'm getting this error:

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

0
Roger BaklundCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mada123Author Commented:
You are magnificent!!! Thank you so much for explaining the why behind the code instead of just handing it to me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.