how to select last value for a particular value both in the same table

[udaystrad]

I m finding it difficult to extract or say select last value for a particluar value(remeber this particluar value is at multiple locations in the same column). Corresponding the last particular value is a value which i want to extract.

 

theres some mistake in my selection syntax i.e.

 

"SELECT LAST('$timeout') FROM $attendance WHERE MemberId='$mid'"

 

here is the complete code

 

 

<?php
 
 
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="india123"; // Mysql password 
$db_name="test"; // Database name 
 
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
 
$attendance="attendance";
 
$timeout="TimeOut";
 
$mid='007';
// $takevalue = mysql_query("SELECT $timeout FROM $attendance ORDER BY $mid DESC LIMIT 1;");
$takevalue=mysql_query("SELECT LAST('$timeout') FROM $attendance WHERE MemberId='$mid'");
$array = mysql_fetch_array($takevalue); 
 
$arr=$array['TimeOut'];
echo $arr;
 
mysql_close($con);
?>

 

 

 

If u see in the above code $mid='007'; is the particular value ie '007'. There are many number of '007' value in a column.

Out of this the Time out value corresponding the last '007' value needs to be extracted. how to do it. pls give me the syntax.

 

 

thnks in advance....

 

[udaystrad]

• phpMyAdmin 2.11.11.3 

NO errors

[Barand]

Does the table have a datetime value so you can determine which is the LAST record for 007?

 

What does your table look like?

[udaystrad]

yes....

 

there are number of id like 007..... there is 008 ,009.... all repeated many times..... i want to select the last values coresponding these ids.....

 

here are the columns 

 

MemberId        TimeIn       TimeOut         Day           OutDay               Month                         Year

007                 12:32:50       Missed            16              16                 November                    2013

007                  Missed        12:34:45          16              16                 November               2013

004                 11:25:12       Missed           19              19                 November               2013

009                 13:39:2       Missed             19              19                 November               2013

006                 11:1:44       Missed             20              20                 November               2013

006                 11:2:40       Missed            20               20                 November               2013

006                 Missed       11:2:57           20              20                 November               2013

007                 11:52:42       Missed           23               23                 November               2013

008                  15:7:47       Missed             20               20                 November               2013

 

 

Now like this many entries will follow..... i wont to work on the last entries for a particular id....so i need to extract those values...

in the above code last timeout for 007 should be 'Missed' whereas for 006 it should be 11:2:57

[Barand]

I've cleaned up the provided data

MemberId    TimeIn      TimeOut     Day OutDay  Month       Year
----------+----------+-----------+-----+-------+---------+--------+ 
007         12:32:50    Missed      16          November    2013 
007         Missed      12:34:45    16          November    2013 
004         11:25:12    Missed      19          November    2013 
009         13:39:2     Missed      19          November    2013 
006         11:1:44     Missed      20          November    2013 
006         11:2:40     Missed      20          November    2013 
006         Missed      11:2:57     20          November    2013 
007         11:52:42    Missed      23          November    2013 
008         15:7:47     Missed      20          November    2013

You certainly don't believe in structuring your data to make life easy for yourself, do you? You data as it is is unusable for this task.

 

1. You are using month names instead of numbers. If you had a December in there it would sort before November, so sorting by date is out.

2. 11:2:40 would sort after 11:15:00 so time sorting too is out.

 

Store your dates in a single DATE type field (format yyyy-mm-dd) and store your times in TIME type fields (format hh:mm:ss). Then they become usable for sorts and comparisons and use by MySQL date/time functions. Or use combined DATETIME fields

 

This should help. You currently have

+----+----------+----------+----------+------+--------+----------+------+
| id | MemberId | TimeIn   | TimeOut  | Day  | OutDay | Month    | Year |
+----+----------+----------+----------+------+--------+----------+------+
|  1 | 007      | 12:32:50 | Missed   |   16 |   NULL | November | 2013 |
|  2 | 007      | Missed   | 12:34:45 |   16 |   NULL | November | 2013 |
|  3 | 004      | 11:25:12 | Missed   |   19 |   NULL | November | 2013 |
|  4 | 009      | 13:39:2  | Missed   |   19 |   NULL | November | 2013 |
|  5 | 006      | 11:1:44  | Missed   |   20 |   NULL | November | 2013 |
|  6 | 006      | 11:2:40  | Missed   |   20 |   NULL | November | 2013 |
|  7 | 006      | Missed   | 11:2:57  |   20 |   NULL | November | 2013 |
|  8 | 007      | 11:52:42 | Missed   |   23 |   NULL | November | 2013 |
|  9 | 008      | 15:7:47  | Missed   |   20 |   NULL | November | 2013 |
+----+----------+----------+----------+------+--------+----------+------+

If you then run these two queries

CREATE TABLE attendancenew (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
MemberId    VARCHAR(3),
TimeIn      DATETIME,
TimeOut     DATETIME
);

INSERT INTO attendancenew
SELECT
    null
    , MemberID
    , CASE TimeIn
        WHEN 'Missed' THEN null
        ELSE STR_TO_DATE(CONCAT_WS(' ', Day, Month, Year, TimeIn), '%d %M %Y %T')
        END
    , CASE timeOut
        WHEN 'Missed' THEN null
        ELSE STR_TO_DATE(CONCAT_WS(' ', IFNULL(OutDay, Day), Month, Year, TimeOut), '%d %M %Y %T')
        END
FROM attendance;

You get a usable table like this

+----+----------+---------------------+---------------------+
| id | MemberId | TimeIn              | TimeOut             |
+----+----------+---------------------+---------------------+
|  1 | 007      | 2013-11-16 12:32:50 | NULL                |
|  2 | 007      | NULL                | 2013-11-16 12:34:45 |
|  3 | 004      | 2013-11-19 11:25:12 | NULL                |
|  4 | 009      | 2013-11-19 13:39:02 | NULL                |
|  5 | 006      | 2013-11-20 11:01:44 | NULL                |
|  6 | 006      | 2013-11-20 11:02:40 | NULL                |
|  7 | 006      | NULL                | 2013-11-20 11:02:57 |
|  8 | 007      | 2013-11-23 11:52:42 | NULL                |
|  9 | 008      | 2013-11-20 15:07:47 | NULL                |
+----+----------+---------------------+---------------------+

Now it is processable for what you want with this query

SELECT a.MemberId, IF(a.timeout IS NULL,'Missing', a.TimeOut) as TimeOut
FROM attendancenew a
JOIN (
    SELECT MemberId, MAX(COALESCE(TimeIn, TimeOut)) as latest
    FROM attendancenew
    GROUP BY MemberId
    ) as last ON a.MemberId = last.MemberId AND latest IN (a.TimeIn, a.TimeOut)

+----------+---------------------+
| MemberId | TimeOut             |
+----------+---------------------+
| 004      | Missing             |
| 009      | Missing             |
| 006      | 2013-11-20 11:02:57 |
| 007      | Missing             |
| 008      | Missing             |
+----------+---------------------+

[udaystrad]

I am so thankful to you sir the way you have answered and i m sorry for my structure. To be honest i dont kno how to insert a table as u did. If u help through that i would be much more thankful. Even if you dont i would find it by myself. For sure you wont find this kind of conversation again on my side.

 

 

I will run this code and give you feedback.

[Barand]

you can just run it all as a single PHP script

$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);         // use your host and credentials

$db->query("DROP TABLE IF EXISTS attendancenew");             // delete existing table

$sql = "CREATE TABLE attendancenew (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    MemberId    VARCHAR(3),
    TimeIn      DATETIME,
    TimeOut     DATETIME
    )";
$db->query($sql);

$sql = "INSERT INTO attendancenew
    SELECT
        null
        , MemberID
        , CASE TimeIn
            WHEN 'Missed' THEN null
            ELSE STR_TO_DATE(CONCAT_WS(' ', Day, Month, Year, TimeIn), '%d %M %Y %T')
            END
        , CASE timeOut
            WHEN 'Missed' THEN null
            ELSE STR_TO_DATE(CONCAT_WS(' ', IFNULL(OutDay, Day), Month, Year, TimeOut), '%d %M %Y %T')
            END
    FROM attendance";
$db->query($sql);

$sql = "SELECT a.MemberId, IF(a.timeout IS NULL,'Missing', a.TimeOut) as TimeOut
    FROM attendancenew a
    JOIN (
        SELECT MemberId, MAX(COALESCE(TimeIn, TimeOut)) as latest
        FROM attendancenew
        GROUP BY MemberId
        ) as last ON a.MemberId = last.MemberId AND latest IN (a.TimeIn, a.TimeOut)
    ";

$res = $db->query($sql);
echo '<pre>';
printf("%-10s%-20s\n\n", 'MemberId', 'TimeOut');
while ($row = $res->fetch_row()) {
    vprintf("%-10s%-20s\n", $row);
}
echo '</pre>';

[udaystrad]

hey could you also give me the query for extraxting id coresponding those values ie. along with memberid and timeout.

 

As i want to update values in that row only.

[udaystrad]

i tried few queries editing those lines of yours but its not workin to get id, memberid and time out all together.

 

And also sir your level at which you have writen the codes is amazing.

 

Could you guide me through some good sites or ebooks so that i can refer to for php and sql.

 

I kno i m askin too many questions and too much but again i would be indeed thankful for it.

[Barand]

 

here are the columns 

 

MemberId        TimeIn       TimeOut         Day           OutDay               Month                         Year

007                 12:32:50       Missed            16              16                 November                    2013

007                  Missed        12:34:45          16              16                 November               2013

 

What id?

[udaystrad]

i want you to edit query for id also.....

 

 

SELECT a.MemberId, IF(a.timeout IS NULL,'Missing', a.TimeOut) as TimeOut
FROM attendancenew a
JOIN (
SELECT MemberId, MAX(COALESCE(TimeIn, TimeOut)) as latest
FROM attendancenew
GROUP BY MemberId
) as last ON a.MemberId = last.MemberId AND latest IN (a.TimeIn, a.TimeOut)

 

 

This code gives a table of memberid and timeout.

 

I also want it to include the respective id.

 

 

For eg.

 

id          Memberid               timeout

8             007                       Missing

 

 

its becoz after i get the id i can update that respective row.

[Barand]

Adding an extra field to the query field list is perhaps the easiest thing you could do in a query and shouldn't be beyond the problem solving capabilities of stradsolutions dot com

 

It does, however raise the question of which table you want to update, your original attendance table or the attendancenew table that my code creates? Are you sticking with your old format or are you intending to continue with the revised version? If you do want to continue with the new version then the conversion in the code should be a one-time operation and not run every time.

 

As there was no mention of "id" column in your original specification the id is not transferred currently with the rest of the data. If you want to update your original table you will need to amend the query transferring the data to ensure the ids match.

[udaystrad]

Wel strad solutions is the company where i work and the boss of the company has allocated me this app. Like i said the style and level at which you have writen the code is a bit dificult for me at the moment being an amateur.

 

I m continuing with the new version that you have designed and i want to update the same atendance table.

You can see in the above table TIme in and time out both get inserted diferently. I want to keep time in n timeout both in the same row which can be done by updating the time in row. Bt updation depends on the last timeout value for that particular memberid. Only if the last time out value is missed that row wil be updated or else insertion wil take place as there was no time in value inserted. I hope i am able to make you understand.

 

Time in is login. (which always is inserted and will give a value of only time in and timeout value would be missing)

Time out is logout. (which will be updated if timeout mising else new row inserted)

 

So in order to do that i now also need the id so that the system can locate the exact row.

I m sorry i should have asked for the entire row or these three value(ie id, member-id and timeout).

 

Well i do use the attendancenew table but after i extract those two values i have to del all the values in the table in order to run the fresh values when it will be inserted.

 

I also want to tell you why i have separated Time day date and Month as when the atendance is to be checked my boss can take ny of these values separate to check particular attendance.

 

thankful to you i have traced the last memberid and timeout but the funny part is that i dont have that id to update it back in attendance table.

I wasnt aware of the id concept before. I thought the system numbers itself the entries that come in. 

[Barand]

I have amended the code to pick up up the id from your attendance table and transfer it to attendancenew table. The final query then selects the id for display.

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$db->query("DROP TABLE IF EXISTS attendancenew");             // delete existing table

$sql = "CREATE TABLE attendancenew (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    MemberId    VARCHAR(3),
    TimeIn      DATETIME,
    TimeOut     DATETIME
    )";
$db->query($sql);

$sql = "INSERT INTO attendancenew
    SELECT
        id
        , MemberID
        , CASE TimeIn
            WHEN 'Missed' THEN null
            ELSE STR_TO_DATE(CONCAT_WS(' ', Day, Month, Year, TimeIn), '%d %M %Y %T')
            END
        , CASE timeOut
            WHEN 'Missed' THEN null
            ELSE STR_TO_DATE(CONCAT_WS(' ', IFNULL(OutDay, Day), Month, Year, TimeOut), '%d %M %Y %T')
            END
    FROM attendance";
$db->query($sql);

$sql = "SELECT a.id, a.MemberId, IF(a.timeout IS NULL,'Missing', a.TimeOut) as TimeOut
    FROM attendancenew a
    JOIN (
        SELECT MemberId, MAX(COALESCE(TimeIn, TimeOut)) as latest
        FROM attendancenew
        GROUP BY MemberId
        ) as last ON a.MemberId = last.MemberId AND latest IN (a.TimeIn, a.TimeOut)
    ";

$res = $db->query($sql);
echo '<pre>';
printf("%5s  %-10s%-20s\n\n", 'ID', 'MemberId', 'TimeOut');
while ($row = $res->fetch_row()) {
    vprintf("%5d  %-10s%-20s\n", $row);
}
echo '</pre>';

Also note that to display the day month and year separately on output is not not necessary to store them separately in the database.

SELECT
      id
    , MemberId
    , DATE_FORMAT(TimeIn, '%d %M %Y') as DayIn
    , TIME(TimeIn) as TimeIn
    , DATE_FORMAT(TimeOut, '%d %M %Y') as DayOut
    , TIME(TimeOut) as TimeOut
 FROM attendancenew;

+----+----------+------------------+----------+------------------+----------+
| id | MemberId | DayIn            | TimeIn   | DayOut           | TimeOut  |
+----+----------+------------------+----------+------------------+----------+
|  1 | 007      | 16 November 2013 | 12:32:50 | NULL             | NULL     |
|  2 | 007      | NULL             | NULL     | 16 November 2013 | 12:34:45 |
|  3 | 004      | 19 November 2013 | 11:25:12 | NULL             | NULL     |
|  4 | 009      | 19 November 2013 | 13:39:02 | NULL             | NULL     |
|  5 | 006      | 20 November 2013 | 11:01:44 | NULL             | NULL     |
|  6 | 006      | 20 November 2013 | 11:02:40 | NULL             | NULL     |
|  7 | 006      | NULL             | NULL     | 20 November 2013 | 11:02:57 |
|  8 | 007      | 23 November 2013 | 11:52:42 | NULL             | NULL     |
|  9 | 008      | 20 November 2013 | 15:07:47 | NULL             | NULL     |
+----+----------+------------------+----------+------------------+----------+

[udaystrad]

My app is complete and its running 5n.

 

Well  i m a new bee in this php world. The basic coding i had learned was from w3schools as per my boss(also many here in India advised me to w3schools) and whenever an app is to be developed i search codes in google.

 

Is my approach ryt? or Cn you guide me good sites for learning php and sql. If you help me for Ajax also that would be great.

 

The administrator of this group said that w3schools is the worst site to study php although whatever i learnt from it havnt found it wrong yet. May be the standard of writing the code and so i have found it myself is low you guys being expert in php.

 

Can you guide me over it.

[udaystrad]

Also wanted to kno wheter how do you guys solve these scripts .....

By actual performing it on your system creating a database???

 

or

 

Or making the best out of the information given and directly giving out codes without trying it on your system????

Pls answer this.

Both ways are obviously gud bt if its the second one you guys are seriously amazing....

phpfreaks rocks.....

[Barand]

It's bit of both. Usually the solution is known but if I have the time I prefer to test out my proposed solution to make sure I haven't made any stupid typos (like forgetting to remove a comma from a query). Other times I know roughly how to tackle it but a bit of experimentation with test data and a visit to the manual is required, especially if it requires something I haven't used before.