Show only the last result from mysql row

Question

I have a table

id | field1 | field2 | ... | field[x-1] | field[x]
1  | val1   | val2   | ... | val[x-1]   | val[x]

And I'm doing this search

for($i = 1; $i <= $x; $i++){
   $getvalue = mysql_query("SELECT * FROM table WHERE id='1' AND field".$i." LIKE 'some_value%'")or die(mysql_error());
   while($row = mysql_fetch_array($getvalue)){
      $j=$i+1;
      $val = $row['field'.$j.''];
   }
}

Some values in the table (val[1-x]) will be the same but I need to get only the LAST value. Limit 1 doesn't seem to work.

Update. Unfortunately as David suggested, I can't change the database. It has to be as it is. I have a text file (a settings dump from a sensor) that I insert it line by line into the db and do a check to see if there are any errors. Most check run ok but I have a few lines in that I need to choose only the last one to do the check. I have about 10 lines like this

D?
String
R?
String
...
D?
String
R?
String

I'm interested in the last string after R?. I use explode () and each value checked that they are with in limits.

I know that. I'm using it for something personal (old version of php and mysql to be able to use php_printer.dll) and doesn't need to be up to date. — xlucian, Feb 26 '16 at 15:14
I think your `for` loop should be inside `while` loop not the other way round. btw `mysql_*` functions are removed from PHP. — bansi, Feb 26 '16 at 15:14
In what way is `LIMIT 1` not working as expected? How did you try to use it? That would indeed limit to only one record. Also, you need to define what "last" means. You're currently not actually sorting the records in any way, so "last" is arbitrary. — David, Feb 26 '16 at 15:16
@David. Lets say in fieldA I have valW and in fieldB I have also valW (B>A). When searching for valW even if I have Limit 1 set I still get both valW from fieldA and fieldB, when the only one I need is the one from fieldB — xlucian, Feb 26 '16 at 15:22
@xlucian: `LIMIT 1` limits the number of *records* returned by the query, not the number of *columns*. Your application code would need to decide which column to use for what purpose. The SQL query simply determines which *records* you want. — David, Feb 26 '16 at 15:23
@David. Limit 1 for me doesn't work. Not for this any way. I can't say in what column the required values are as they are not always in the same. and I have 250 column and can be anywhere but only need the last value — xlucian, Feb 26 '16 at 15:36
@xlucian: You're still trying to use `LIMIT` to limit the number of *columns*. That's not what it does. SQL filters *rows*, not *columns*. Once you have the *row* you want, your application code would do whatever you want with the values from that row. Are you just asking how to get the highest value from an array of values in PHP? — David, Feb 26 '16 at 15:39
@David Not the highest value from an array, the value from the highest field (column) — xlucian, Feb 26 '16 at 15:45
@xlucian: And what exactly do you think `$row` is in your code? — David, Feb 26 '16 at 15:46
Possible duplicate of [PHP Get Highest Value from Array](http://stackoverflow.com/questions/6676768/php-get-highest-value-from-array) — David, Feb 26 '16 at 15:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/104659/discussion-between-xlucian-and-david). — xlucian, Feb 26 '16 at 15:48
I'm probably not getting something. I've tried max($row) but still nothing Some values in columns are like this 100,2.511,19.5,13.5,5.01,13.4 and need the last one. And max() shows empty for this — xlucian, Feb 26 '16 at 15:58
@xlucian: What are the actual values in your columns? Do you mean that example to be all of the columns, or just *one* of the columns? If the latter, then your columns aren't numbers. They're text. The "maximum value" of text doesn't make much sense. — David, Feb 26 '16 at 16:15
The string I posted earlier starting with 100, is all in one column — xlucian, Feb 26 '16 at 17:42
@xlucian: Then your data is text, not numbers. The more you describe this data, the more it sounds like it's not relational data at all. You should really fix the database design so that it stores data the way you want to query it. At the moment you're going to have to read the values into your PHP code, parse them all out into an array, and select the maximum from that array. — David, Feb 26 '16 at 18:46

Thomas Orlita · Answer 1 · 2016-02-26T15:28:49.237

Using `ORDER BY id DESC LIMIT 1`:

To get last row (with highest=last=newest id), you should use this:

SELECT * FROM table ORDER BY id DESC LIMIT 1;

Your updated code (with mysqli_ extension) to get last row:

$query= mysqli_query("SELECT * FROM table ORDER BY id DESC LIMIT 1") or die (mysqli_error());
}

$lastRow = mysqli_fetch_row($query);

echo $lastRow['id']; //get the last id

Using `MAX()`:

You also can do it using SQL MAX() function:

SELECT * FROM table WHERE id=(SELECT MAX(id) FROM table)

xlucian · Answer 2 · 2016-02-28T13:28:36.253

Managed to get it working as I need it by doing this:

for($i = 1; $i <= 250; $i++){
    $getvalue = mysql_query("SELECT * FROM full_dump WHERE file_name='".$_FILES['datafile']['name']."' AND field_".$i." LIKE ' 100,%'")or die(mysql_error());
    while($row = mysql_fetch_array($getvalue)){
        $i_temp = $i;
    }
}
if($i_temp != NULL){
    $getvalue = mysql_query("SELECT * FROM full_dump WHERE file_name='".$_FILES['datafile']['name']."' AND field_".$i_temp." LIKE ' 100,%'")or die(mysql_error());
    while($row = mysql_fetch_array($getvalue)){
        $r = $row['field_'.$i_temp.''];
    }
}

Show only the last result from mysql row

2 Answers2

Using ORDER BY id DESC LIMIT 1:

Using MAX():

Using `ORDER BY id DESC LIMIT 1`:

Using `MAX()`: