How to check last row ref by column username using php and mysql?

Question

How to check last row ref by column username using php and mysql ?

This is my table

 _________________________________
|_id_|_____username_____|___val___|
|  1 |      AAAA        |    3    |
|  2 |      AAAA        |    4    |
|  3 |      AAAA        |    5    |
|  4 |      CCCC        |    1    |
|  5 |      CCCC        |    3    |
|  6 |      CCCC        |    4    |
|  7 |      CCCC        |    7    |
|  8 |      DDDD        |    2    |
|  9 |      DDDD        |    4    |

And this my code.

<?php
include("connect.php");
$strSQL = "SELECT * FROM table order by id asc ";
$objQuery = mysql_query($strSQL);
while($objResult = mysql_fetch_array($objQuery))
{
    echo ["val"];
    echo "<BR>";
}
?>

When i test code. I'll echo

But i want to echo username in last row like this.

3
4
5 AAAAA
1
3
4
7 CCCCC
2
4 DDDDD

How can i do that ? thank you.

I think this is purely SQL issue, thou your PHP code served to remind that `mysql_` functions are deprecated and should be changed to `mysqli_` functions, or `PDO` class — al'ein, Sep 30 '15 at 17:09

score 1 · Answer 1 · answered Sep 30 '15 at 17:14

1

try this:

SELECT t1.val,t2.username FROM `test` as t1
left join (SELECT max(id) as id,username FROM `test` group by username) as t2 on t1.id=t2.id

answered Sep 30 '15 at 17:14

Payer Ahammed

887
1
5
16

underscore · Answer 2 · 2015-09-30T17:26:02.360

Simple Answer using PHP.

<?php
include("connect.php");
$strSQL = "SELECT * FROM table order by id asc ";
$objQuery = mysql_query($strSQL);
$objResult = mysql_fetch_array($objQuery)

 foreach($objResult as $key => $value){

    echo $value["val"];

    if($value["username"] != $objResult[$key+1]["username"]){

      echo $value["username"];

     }
?>

Example what i wrote.

<?php

    $arr = array(
        array(
            3,
            'AAAA'
        ),
        array(
            4,
            'AAAA'
        ),
        array(
            5,
            'AAAA'
        ),
        array(),
        array(),
        array(),
        array()
    );

     foreach($arr as $key => $value){

        echo $value[0];

        if($value[1] != $arr[$key+1][1]){

          echo $value[1];

         }
        echo "\n";

    }

    ?>

DEMO

score 0 · Answer 3 · answered Sep 30 '15 at 17:19

if you are willing to use php then you can try like this:

<?php 

include("connect.php");
$strSQL = "SELECT GROUP_CONCAT(val) as vals,username FROM table GROUP BY username order by id asc ";
$objQuery = mysql_query($strSQL);
while($objResult = mysql_fetch_array($objQuery))
{
    $vals=explode(",",$objResult['vals']);
    for($i=0;$i<sizeof($vals);$i++){
        if($i == sizeof($vals)-1){
        echo $vals[$i]." ".$objResult['vals']."<br>";
        }else{
            echo $vals[$i]."<br>";
        }
    }
}

score 0 · Answer 4 · answered Sep 30 '15 at 17:25

You can get your solution in the query End

<?php
          include("connect.php");
        $qobject = mysql_query("SELECT u3.id,u4.username,u4.val FROM `user` as u3 left join (SELECT u1.*
                        FROM user u1 LEFT JOIN user u2
                        ON (u1.username = u2.username AND u1.id < u2.id)
                        WHERE u2.id IS NULL
                        ) as u4 on u4.id=u3.id");
        while($data = mysql_fetch_array($qobject)){
            echo $data['id'].$data['username'];
        }
?>

Query took 0.0009 seconds

@robert qewerutiyo For reference you can see this link http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — Ajeet Kumar, Sep 30 '15 at 17:30

How to check last row ref by column username using php and mysql?

4 Answers4