[SOLVED] Problem: Select 10 Newest Members

[Michael Lasky]

isn't that the same as unix timestamps?

ie using DEFAULT CURRENT_TIMESTAMP  from MySQL ?

 

You're most likely correct, but I'm not 100% sure on that and don't have time to research it now.

[Barand]

isn't that the same as unix timestamps?

ie using DEFAULT CURRENT_TIMESTAMP  from MySQL ?

 

http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html

[Trium918]

Problem: There are 15 users registered in the

database as User1, User2, User3, etc... What

I am trying to do is display 10 Newest Members

that will help promote website. If there are 15

users already and 10 more were to be added.

Then the newest members would be User25

down to User15, or something of that sort.

 

This is all that I am getting with ASC and DESC.

 

User1 : 20070417000000

User2 : 20070417000000

User3 : 20070417000000

User4 : 20070417000000

User5 : 20070417000000

User6 : 20070417000000

User7 : 20070417000000

User8 : 20070417000000

User9 : 20070417000000

User10 : 20070417000000

 

<?php
$sql = 'SELECT user_name,register_date FROM members_info ORDER BY register_date ASC LIMIT 10';
    $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
    while (list($u, $d) = mysql_fetch_row($res)) {
    echo "$u : $d <br/>" ;
}
?>

[Barand]

Problem: You are not putting the time element into the datetime field.

 

When you add a new user , put NOW() into the date field

INSERT INTO table (user, register_date) VALUES ('$user', NOW() )

[Trium918]

Problem: You are not putting the time element into the datetime field.

 

When you add a new user , put NOW() into the date field

INSERT INTO table (user, register_date) VALUES ('$user', NOW() )

 

Sorry about that. Should I change back to register_date datetime not null default'0000-00-00'

instead of using register_date timestamp null default 'current_timestamp'?

[Trium918]

I got it. Thanks Barand.

 

This is the output that I am getting

User15 : 2007-04-18 15:07:33

User14 : 2007-04-18 15:07:25

User13 : 2007-04-18 15:07:18

User12 : 2007-04-18 15:07:11

User11 : 2007-04-18 15:07:00

User10 : 2007-04-18 15:06:53

User9 : 2007-04-18 15:06:44

User8 : 2007-04-18 15:06:36

User7 : 2007-04-18 15:06:29

User6 : 2007-04-18 15:06:20

 

<?php
$query = "INSERT INTO members_info (members_id,user_name,first_name,last_name,gender,birth_month,birth_day,
birth_year,contact_number,email_address,register_date) VALUES(NULL,'$user_name','$first_name','$last_name'
,'$gender','$birth_month','$birth_day','$birth_year','$contact_number','$email_address',Now())";

$sql = 'SELECT user_name,register_date FROM members_info ORDER BY register_date DESC LIMIT 10';
    $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
    while (list($u, $d) = mysql_fetch_row($res)) {
      echo "$u : $d <br/>" ;
    }
?>

 

Question: Would it be better to put register_date into a

different table because I haven't done a password table yet?

[Barand]

Good  You're welcome

[Barand]

Question: Would it be better to put register_date into a

different table because I haven't done a password table yet?

 

The only reason to split data about a person over more than one table is so you can grant differnt access permissions to different groups of people.

 

eg

 

employee1        employee2

----------        -----------

emp_id            emp_id

name              salary

job_title        tax_paid

phone

 

Data in employee1 is available to all employees but that in employee2 is only available to the payroll department and managers.

 

If you don't have to split it, it's easier just to keep it all together.