Whats wrong with the syntax in my mysql select statement (php, mysql)

Question

I have a table and i'm trying to extract the maximum value of a column called order_num, which has 33 intries of integers 1 through 33. I want the value "33" in this instance as its the highest number.

$userid is an integer derived from a one row table with a field id that I am trying to retrieve

//get the currentUser ID so we know whos deck to ammend
$userIDSQL = "SELECT id FROM currentUser";
$userIdResult = mysqli_query($db, $userIDSQL) or die("SQL Error on fetching user ID: " . mysqli_error($db));

$result_array = array();
while ($row = mysqli_fetch_assoc($userIdResult)) {
    $result_array[] = $row['id'];
}

//the actual user id
$userId = $row['id'];
echo "user id is " . $userId;

Doing a print_r on $userId shows the array to be empty, that's why the code below doesn't work.. :(

...

$reOrderDeckSQL = "SELECT MAX(order_num) AS order_num FROM decks WHERE id='$userId'";
$reOrderDeckResult = mysqli_query($db, $reOrderDeckSQL) or die("SQL Error on reOrder: " . mysqli_error($db));

        $result_array = array();
        while ($row = mysqli_fetch_array($reOrderDeckResult)) {
            $result_array[] = $row['MAX(order_num)'];
            echo "the result is" . $result_array['order_num'];
            echo "the result is" . $row['order_num'];
            echo "the result is" . $result_array['MAX(order_num)']; //tried different methods to get the output.
        }

The output I get is

the result is  the result is  the result is

Does anyone know why i'm not able to get the result from the table?

try $result_array = $row['MAX(order_num)']; - you're currently adding the row as a new element in $result_array - if you want to do that, I think you'll need to echo $result_array[0]['order_num']; — andrewsi
– andrewsi, Commented Jun 14, 2013 at 13:55
i wonder why $row['order_num'] doesn't print anything, maybe the $userId have no recors? — Fabio
– Fabio, Commented Jun 14, 2013 at 13:56
Maybe just try to give it a different alias from its actual name? MAX(order_num) AS total and print with $row['total'] — Fabio
– Fabio, Commented Jun 14, 2013 at 13:57
I suspect there is just no rows for the given userId. In your question you refer to both $user_id and $userId so maybe that's the issue. — MrCode
– MrCode, Commented Jun 14, 2013 at 13:59

Jessica · Accepted Answer · 2013-06-14 13:59:45Z

2

Edit:

Okay, try this:

while ($row = mysqli_fetch_array($reOrderDeckResult)) {
   print_r($row);
}

What do you get?

edited Jun 14, 2013 at 13:59

answered Jun 14, 2013 at 13:53

Jessica

6,99630 silver badges39 bronze badges

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

5 Comments

David Folksman Over a year ago

Sorry, i'm quite new to this and i'm not sure what you mean by your last answer print_r would have saved me what? how can I do that?

Jessica Over a year ago

first of all, the php manual tells you how to use functions, so if you don't know what one does, the first thing you should do it look it up. print_r() shows you exactly what an array contains.

David Folksman Over a year ago

It's empty! :( Must be a problem with $userID like someone said above.. Sorry for leading you up the garden path.

David Folksman Over a year ago

Thanks for this, it helped me find the answer to the problem.

Jessica Over a year ago

You're welcome, sorry it wasn't more clear before. BTW, you should do some reading on MySQL JOINS.

MrCode · Accepted Answer · 2013-06-14 14:12:24Z

1

Your first code to get the userId doesn't work because of your usage of the array, change to:

$userId = 0;
while ($row = mysqli_fetch_assoc($userIdResult)) {
    $userId = $row['id'];
}

As I've shown below, if you only expect a single row then remove the while loop and just call fetch_assoc a single time.

Given you only want the single row you don't need the while loop:

$reOrderDeckSQL = "SELECT MAX(order_num) AS order_num FROM decks WHERE id='$userId' LIMIT 1";
$reOrderDeckResult = mysqli_query($db, $reOrderDeckSQL) or die("SQL Error on reOrder: " . mysqli_error($db));

if($reOrderDeckResult && mysqli_num_rows($reOrderDeckResult) == 1)
{
    $row = mysqli_fetch_assoc($reOrderDeckResult);
    echo 'result: ' . $row['order_num'];
}
else
{
    echo 'No rows found!!';
}

I also added LIMIT 1 to the query, and a check to see if there are any rows.

edited Jun 14, 2013 at 14:12

answered Jun 14, 2013 at 14:05

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

4 Comments

gen_Eric Over a year ago

Don't you mean $row['order_num']?

David Folksman Over a year ago

Getting rid of the while loops and arrays fixed it for me, so I'll mark this as best answer. Thanks for your help.

David Folksman Over a year ago

In addition, the next thing im trying to do is add one to the result of the max number and then store it to a variable I do $newOrder = ((int) $row) + 1; echo "<br> new order is: " . $newOrder; but the result is "1" and it should be "34" any ideas?

MrCode Over a year ago

I think it should be $newOrder = ((int) $row['order_num']) + 1; echo "<br> new order is: " . $newOrder; That should work if you do it after the fetch assoc call.

gen_Eric · Accepted Answer · 2013-06-14 14:06:11Z

0

You have renamed your MAX(order_num) AS order_num so you should try to get value using order_num only as echo $result_array['order_num']

and in while you should check whether you are getting value or not by placing below code in while loop

echo '<PRE>';
print_r($row);
echo '</PRE>';

edited Jun 14, 2013 at 14:06

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

answered Jun 14, 2013 at 14:05

Anil Gupta

6324 silver badges16 bronze badges

1 Comment

Anil Gupta Over a year ago

if (!$reOrderDeckResult) { die('Invalid query: ' . mysql_error()); }else{ while loop }

Sundar · Accepted Answer · 2013-06-14 14:06:32Z

0

This may helps you..,

<?php

$reOrderDeckSQL = "SELECT MAX(order_num) AS order_num FROM decks WHERE id='$userId'";
$reOrderDeckResult = mysqli_query($db, $reOrderDeckSQL) or die("SQL Error on reOrder: " . mysqli_error($db));

$result_array = array();
while ($row = mysqli_fetch_array($reOrderDeckResult)) {

   //you have to use the alias name not aggregate function title
    $result_array[] = $row['order_num'];

    echo "the result is" . $result_array['order_num'];
    echo "the result is" . $row['order_num'];

    //you have to use the alias name not aggregate function title
    echo "the result is" . $result_array['order_num']; //tried different methods to get the output.
}

edited Jun 14, 2013 at 14:06

answered Jun 14, 2013 at 13:58

Sundar

4,6506 gold badges40 silver badges63 bronze badges

1 Comment

andrewsi Over a year ago

Could you add some description of what you've changed?

Collectives™ on Stack Overflow

Whats wrong with the syntax in my mysql select statement (php, mysql)

4 Answers 4

5 Comments

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related