Run a query if directed to page

Question

I am running the following select statement:

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

This returns the value of highest Order ID linked to that Username, therefore it being their last order. I want to then run this query:

$Query = "update orders set Status = 'Failed' where Status = 'Success' and where OrderID = ". $oid ." ";

Which will update the orders table, setting the Status to Failed (where it's currently Success).

Why is $oid, returning nothing? If I manually input the values and run it via PHPMyAdmin it works fine.

if (strpos($url,'order-failed') !== false) {
    $query = "SELECT DISTINCT (MAX(OrderID)) FROM orders WHERE Username = '". $_SESSION['Username'] ."' ";
    $result = mysql_query($query);
    $row = mysql_fetch_array($result);
    echo $row['OrderID']; // For Debugging
    echo "<br /><br />";

    $Query = "UPDATE orders SET Status = 'Failed' WHERE Status = 'Success' and OrderID = ". $oid ." ";
    $mysqli->query($Query);
    echo $Query; // For Debugging
}

The echos return: SELECT DISTINCT (MAX(OrderID)) FROM orders WHERE Username = 'email@email.com' UPDATE orders SET Status = 'Failed' WHERE Status = 'Success' and OrderID =

Order ID is blank! :(

`$oid = (...)` does not run the query. You just create a variable with the query in it. — ByteHamster, Sep 11 '15 at 10:48
BTW: Your code is vulnerable to [SQL Injection](http://stackoverflow.com/q/60174/4193263). To fix this critical security hole, use Prepared Statements. — ByteHamster, Sep 11 '15 at 11:13
Once it's working, I'll fix up any vulnerabilities. Thanks for the heads up! — , Sep 11 '15 at 11:14
Can you add more of the code that you have? From what I understand mysql->query will return a result that contains the value that you want. You need to extract the values from it. Check out the documentation: http://php.net/manual/en/function.mysql-query.php — visola, Sep 11 '15 at 11:40
I don't see the code where you're setting `$oid` to anything else than a String which is a SQL query. Inside the `if`, where you run the query, you're justing echoeing the `OrderID` but not setting `$oid`. Am I missing something? — visola, Sep 11 '15 at 13:29

markus · Answer 1 · 2015-09-11T15:08:52.487

1

$oidis returning nothing because it is not a function/method, it's a variable of type string (implicitly). Why would you expect a string to 'return' something?

As for your second query, of course $oid is empty. You're not assigning anything to it before. Weren't you supposed to assign the result of the first query to the variable so you could use that orderID (oid) in the second query? Do it!

edited Sep 11 '15 at 15:08

answered Sep 11 '15 at 11:42

markus

40,136
23
97
142

Markus, judging by this comment I have $oid stored as a variable which equates to the Query I am running, therefore I would then need to somehow pull out the number returns i.e. 5 and then save that as a variable? – Sep 11 '15 at 11:46
1

As I said: The question is "what do you do with `$oid`? You need to show us how you run the query! Paste more code! – markus Sep 11 '15 at 11:52
Ok, let me explain. I have this code to say if you are on order-failed.php, select the OrderID from orders where the Username is equal to $_SESSION['Username']. Then, it grabs the OrderID as a number i.e. if it's order 9 I have "9" as a value then I run another statement to Update orders where the OrderID is equal to $oid... – Sep 11 '15 at 11:56
1

Don't post your code here in the comments, edit your question to show your WHOLE code where you fetch the data. – markus Sep 11 '15 at 12:22

score 0 · Answer 2 · edited May 23 '17 at 11:44

You don't need to use WHERE twice:

UPDATE orders set Status = 'Failed' where Status = 'Success' and ~~where~~ OrderID = ". $oid

Prefer to use the AS keyword in complex querys:

SELECT DISTINCT (MAX(OrderID)) AS highest_order_id FROM orders WHERE Username = ...

Do you fetch the result of the query?

$result = $mysqli->query($oid);
$row0 = $result->fetch_assoc();
$highest_order_id = $row0["highest_order_id"];

$Query = "UPDATE orders SET Status = 'Failed' " . 
         "WHERE Status = 'Success' AND OrderID = ". $highest_order_id ." ";

Please notice that my sample code is still vulnerable to SQL Injection!

Peter Lindqvist · Answer 3 · 2015-09-11T11:39:27.560

0

Why is $oid, returning nothing?

First, check for errors:

$result = $mysqli->query($oid);
if (!$result)
{
  echo("Error description: " . $mysqli->error);
}

Then, check the data.

// Query
echo $oid;
// Result
echo $result->num_rows;

It could very well be that you query is simply not returning anything (no error) and then you should be able to spot it in the query.

edited Sep 11 '15 at 11:39

answered Sep 11 '15 at 11:33

Peter Lindqvist

10,122
3
41
60

This is very helpful, however - I have no error apparently! If I run the query manually via PHPMyAdmin it brings back the value I require which is strange. – Sep 11 '15 at 11:39
Then I'm quite certain that the queries are not identical. – Peter Lindqvist Sep 11 '15 at 11:41

Run a query if directed to page

3 Answers3