CODEWITHSUNDEEP
phpsqldatabasemysqli
phoon
phoon

Reputation: 369

Unable to get max id from database

I tried to get the max id from the database. However, it returns me the error

undefined index: id in $maxID=$rowsInit["id"]; and $response["maxID"] = $rowsInit["id"];

This is my code

if ($_POST['maxID'] == 0) {
    $queryInit = "SELECT MAX(id) FROM trade";
    try {
        $stmtInit = $db->prepare($queryInit);
        $resultInit = $stmtInit->execute();
    } catch (PDOException $ex) {
        $response["success"] = 0;
        $response["message"] = $ex;
        die(json_encode($response));
    }

    $rowsInit = $stmtInit->fetchAll();

    if ($rowsInit) {
        $maxID = $rowsInit["id"];
        $response["maxID"] = $rowsInit["id"];
    } else {
        $response["success"] = 0;
        $response["message"] = "No Trade Available!";
        die(json_encode($response));
    }
} else {
    $maxID = $_POST['maxID'];
}

There is a column call id in my trade table. I don't know which part is wrong. Maybe I miss some part.

Upvotes: 2

Views: 1935

Answers (4)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Alias the function call as id:

$queryInit="SELECT MAX(id) as id FROM trade";

You also need to fetch first row's data. so provide row index as well. Try 

$rowsInit[0]["id"]

Upvotes: 4

Your Common Sense
Your Common Sense

Reputation: 157870

PDO is much more than everyone is taking it for. Beside omniprescent fetchAll() it has methods to get the result in a dozen other formats, including a single scalar value, so you won't have to alter your query as it suggested in other answers. So actually you need just one single line to get the max id:

 $id = $pdo->query("SELECT MAX(id) FROM trade")->fetchColumn();

note that you don't have to prepare a query if there are no placeholders in it.

What is more important, your idea on the error reporting is also wrong. You should never ever leak the actual system error message outside. Only a generic error message should be returned.

try {
    if ($_POST['maxID'] == 0) {
        $maxID = $pdo->query("SELECT MAX(id) FROM trade")->fetchColumn();
        if (!$rowsInit) {
            $response["success"] = 0;
            $response["message"] = "No Trade Available!";
            die(json_encode($response));
        }
    } else {
        $maxID = $_POST['maxID'];
    }

    // here goes your code to get the response

} catch (Exception $ex) {
    log_error($ex);
    $response["success"] = 0;
    $response["message"] = "Server error";
    die(json_encode($response));
}

here you are wrapping your whole code in a global try catch block that will handle not only PDO exceptions but also any other exception that may occur.

Upvotes: 1

Abdulla Nilam
Abdulla Nilam

Reputation: 38584

Change this to

SELECT MAX(id) FROM trade

this

SELECT MAX(id) AS id FROM trade

And

change this to

$maxID=$rowsInit["id"];

this

$maxID=$rowsInit[0]["id"]; # or  $maxID=$rowsInit[0]->id

As I know Fetched data indexed with 0. Check these examples

If Failed add this print_r($rowsInit);die; next to if ($rowsInit) { and check how it placed in array

Upvotes: 2

axiac
axiac

Reputation: 72226

The query reads:

SELECT MAX(id) FROM trade

Why do you expect to have a column named id in the result set? There is no such column in SELECT clause. There is only the MAX(id) expression and its column in the resultset is named MAX(id) unless you provide an alias for it:

SELECT MAX(id) AS id FROM trade

Read more about the syntax of the SELECT statement.

$rowsInit still doesn't have an id index because of:

$rowsInit = $stmtInit->fetchAll();

PDOStatement::fetchAll() returns an array of rows. You should either use $rowsInit = $stmtInit->fetchAll()[0]; or, even better, use PDOStatement::fetch() to get only the first row (the result set contains exactly one row, anyway):

$rowsInit = $stmtInit->fetch();

Upvotes: 0

Related Questions