-1

I would like to do a show how many promotion code left function. I already find out how to query the latest promotion. now i would like to show the code how many left.

Now I have 2 table,

this is t1.

+--------------+--------------------------+----------------------+
|    id        |  name                    | description          |
+--------------+--------------------------+----------------------+
| 1            | GG                       | GG is good           |
| 2            | ABC DEFG                 | ABC DDDDD            |
| 3            | CCARD                    | Gooooo               |
+--------------+--------------------------+----------------------+

this is t2

+---------+------------+-------------------+------------------+
| id      | kaid       | code              | timestamp        |
+---------+------------+-------------------+------------------+
| 1       | 2          | ZZZZAAAAA         | 123456789        |
| 2       | 2          | AAAZZADWWW        | 123344444        |
| 3       | 1          | ASFASDFFFF        | 123333333        |
| 4       | 2          | HHHHHFDFG         | 123222222        |
| 5       | 1          | ASDASDADDDD       | 123111111        |
| 6       | 1          | AAAAAAAA          |                  |
| 7       | 1          | DGDFGDSFG         |                  |
+---------+------------+-------------------+------------------+

I query the latest user get promotion code like this

    $querylist = mysql_query("SELECT t2.*,t1.name FROM t2,t1 where t1.id = t2.kaid  ORDER BY t2.timestamp desc limit 5");
while($rowlist = mysql_fetch_row($querylist)) {
    $idlist[] = $rowlist['id'];
    $user_list_latest[] = $rowlist;
}

After I loop query, I will get the data like this

1. GG
2. ABC DEFG
3. GG
4. ABC DEFG
5. ABC DEFG

Then I would like to show the count like this. Hypothesis the count data is $countleft

1. GG  (This promotion code only 2 left!)
2. ABC DEFG (This promotion code only 0 left!)
3. GG (This promotion code only 2 left!)
4. ABC DEFG (This promotion code only 0 left!)
5. ABC DEFG (This promotion code only 0 left!)

the number 2 and 0 is $countleft. the t2 timestamp is means the user get the valid promotion code time. so the timestamp empty means nobody get yet.

Swee Hong
  • 539
  • 2
  • 12
  • 3
    If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Jun 25 '15 at 12:46
  • $querylist = mysql_query("SELECT t1.*,t2.name FROM t1,t2 where t1.id = t2.kaid AND t.timestamp <> '' GROUP BY kaid ORDER BY t1.time desc limit 5"); – Santosh Jagtap Jun 25 '15 at 12:47
  • @SantoshJagtap how to show the $countleft string? – Swee Hong Jun 25 '15 at 12:51
  • do it in while or foreach loop where you printing the names. – Santosh Jagtap Jun 25 '15 at 12:53
  • how? can you please give an example? – Swee Hong Jun 25 '15 at 13:03
  • In your first query, you are selecting and comparing fields that don't exist in the data set you gave us; `t2.name` and `t1.timestamp` aren't correct. – the_pete Jun 25 '15 at 13:23
  • I correct the code already, thanks. – Swee Hong Jun 26 '15 at 04:18

1 Answers1

0

Change your query to this and try

select name,balance from t1 join
(SELECT kaid,COUNT(ID) as balance FROM t2 
WHERE isnull(timestamp) GROUP BY kaid)t3
on (kaid=t1.id) ORDER BY balance desc limit 5;

In the loop print every row and column(there is only two column for each row). It will give the answer.

apm
  • 525
  • 6
  • 19