0

I have this SQL statement which pulls ONE value (the OrderID) from the orders table.

"SELECT DISTINCT (MAX(OrderID)) FROM orders WHERE Username = '[email protected]';

I run that via PHPMyAdmin and it returns one Order ID for that user which is EXACTLY what I want. However, I am trying to run this in PHP...

"SELECT DISTINCT (MAX(OrderID)) FROM orders WHERE Username = '". $_SESSION['Username'] ."'";

I want that to pull the Order ID so I can then set $OrderID = (the result from the select statement), then run an update statement as below.

$sql = "UPDATE orders SET Status = 'Failed' WHERE Status = 'Success' and OrderID = ". $OrderID ."";

If I hard code $OrderID = 90; it works perfectly but I need the OrderID to pull through via the session of who is logged in.

Complete code:

<?php
$url = 'http://' . $_SERVER['SERVER_NAME'] . $_SERVER['REQUEST_URI'];

if (strpos($url,'order-failed') !== false) {
    $servername = "NULL";
    $username = "NULL";
    $password = "NULL";
    $dbname = "NULL";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check Connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $query = "SELECT DISTINCT (MAX(OrderID)) FROM orders WHERE Username = '". $_SESSION['Username'] ."'";
    echo $query . "<br />";

    $OrderID = 93;
    echo $OrderID;
    echo "<br />";
    $sql = "UPDATE orders SET Status = 'Failed' WHERE Status = 'Success' and OrderID = ". $OrderID ."";
    echo $sql;

    if ($conn->query($sql) === TRUE) {
        echo "<br />Record updated successfully";
    }

    else {
        echo "<br />Error updating record: " . $conn->error;
    }

    $conn->close();
}

else {
    session_destroy();
}
?>
Improve this question
13
  • so then dont you think the proplem in storing $_SESSION['Username']? show as the code about it Commented Sep 11, 2015 at 13:11
  • Are you sure you get something from your session $_SESSION['Username'] ? Commented Sep 11, 2015 at 13:11
  • Have you verified that your session variables are being created and are active within the current page? Commented Sep 11, 2015 at 13:11
  • The Session is fine and I can echo that out perfectly. :) If I echo out the Select statement I get... SELECT DISTINCT (MAX(OrderID)) FROM orders WHERE Username = '[email protected]' This is exactly what I need. Why is it echoing out the query rather than the result? Commented Sep 11, 2015 at 13:13
  • 1
    I can't see session_start() at the top of your file. Are you sure it isn't the issue? Commented Sep 11, 2015 at 13:17

4 Answers 4

Reset to default
3

It seems like this would have the same effect...

$sql = "
UPDATE orders 
   SET Status = 'Failed' 
 WHERE Status = 'Success'
   AND Username = '{$_SESSION['Username']}'
 ORDER 
    BY OrderID DESC 
 LIMIT 1;
    ";

... although obviously rewrite it to use prepared statement syntax

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I think without the (WHERE) Status = 'Success' condition it would be identical. The original code might select an order_id for a record already having status==failed, so the update would be without consequence (if orderid really is unique) while your query could in this case update another record.
@VolkerK You've lost me there I'm afraid.
Consider the table contains only the two records [oid=17,usrname=usrA,status=f],[oid=16,usrname=usrA,status=s]. The original script picks orderid=17 and the update does nothing/the table keeps unchanged. Your query results in [oid=16,usrname=usrA,status=f] because it just skips the oid=17 record because status=f doesn't match the WHERE clause.
OK - fair point, so you could use an uncorrelated subquery (for instance) instead
no no, just remove the Status = 'Success condition. The query then will always work on the "first" record (with the highest orderid). If this record already contains failed, nothing changes but the LIMIT clause still is "triggered" -> same behaviour as the original query (unless there are more possible states for status, but this could be handled, too). That only one query is required is a good catch anyway :-)
|
0

add session start on top

<?php
session_start();
// your code here
Improve this answer

2 Comments

I have that. It's nothing to do with the Sessions, they are all working correctly. :)
@DanAshbridge can you var_dump($_SESSION); to see what was in the belly
0

replace 

echo $query . "<br />";

$OrderID = 93;
echo $OrderID;
echo "<br />";

with

$result = $conn->query($query);
$row = $result->fetch_row();
$OrderID = $row[0];

and do what all other guys said

http://php.net/manual/en/mysqli-result.fetch-row.php

http://php.net/manual/en/mysqli-result.fetch-array.php

http://php.net/manual/en/mysqli-result.fetch-assoc.php

Improve this answer

1 Comment

not sure author bothered about it right now. later - sure
-1

Firstly it might be wise to test that the session variable exists and in the select statement assign an alias to the max() function.

if( isset( $_SESSION['Username'] ) ){
    /* Only gets called if the session var exists */
    $sql="SELECT DISTINCT MAX(`OrderID`) as 'OrderID' FROM `orders` WHERE `Username` = '". $_SESSION['Username'] ."';";

    /* run this query, get the OrderID record from results and prepare the next sql statement */

    $sql = "UPDATE `orders` SET `Status` = 'Failed' WHERE `Status` = 'Success' and `OrderID` = ". $OrderID ."";


    /* Alternatively perhaps do it in one statement */
    $sql="UPDATE `orders` SET `Status` = 'Failed' 
          WHERE `Status` = 'Success' and `OrderID` = (
              SELECT DISTINCT MAX(`OrderID`) FROM `orders` WHERE `Username` = '". $_SESSION['Username'] ."'
          )";
}
Improve this answer

1 Comment

I don't know how to "run this query, get the OrderID record from results"... This is my only problem, the rest I can do! There will only ever be one value returned from the Select Statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.