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
3
4
5
1
3
4
7
2
4
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.
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
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 "
";
}
?>
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>";
}
}
}
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