[SOLVED] Problem: Select 10 Newest Members

[Trium918]

Problem: Select 10 Newest Members and display

it as a link.

 

I just need for somebody to talk me through it,

and tell me what I am missing.

<?php
//connect to database
//run query

for($i = 0; $i < $num_result; $i++){
$row = mysql_fetch_array($result);
  echo"<a href ="  ">$row</a> ";
}
?>

[Michael Lasky]

The result returned by mysql_fetch_array is an array. 

var_dump($row);

That will spit out all the contents of the array (in an ugly manner).  Useful for debugging.

 

You are just trying to print (echo) an array.  What you want to do is print the values in the array.

 

echo"<a href ="  ">$row[0]</a> ";

 

That will print the value of the first column returned ($row[1] contains the second column, and so on).  You can also specify that mysql_fetch_array returns an associative array.  This allows you to reference the array values by the column name they come from. 

Example:

 

Suppose you have this table layout, the table is called USERS.

USERS

  FIRST_NAME

  LAST_NAME

  TELEPHONE_NUMBER

 

and you want to display the first names.  The sql query is "SELECT FIRST_NAME FROM USERS";  This will select all the first names in the database.  Then, when you want to display this data:

 

for($i = 0; $i < $num_result; $i++){
$row = mysql_fetch_array($result, MYSQL_ASSOC);
  echo"<a href ="  ">$row['FIRST_NAME']</a> ";
}

 

Note the addition of MYSQL_ASSOC.  This allows you to reference $row['COLUMN_NAME']; rather than $row[0].

 

Hope that helps.

Mike

[Barand]

... and escape double quotes as the entire string is enclosed in double quotes (or use single quotes inside the string)

 

eg

echo"<a href =\"  \">$row[0]</a> ";

[Michael Lasky]

Thanks Barand, i missed that one.

[Trium918]

Thanks, I understand. I got the link, but I still am trying to

display the 10 Newest User.

 

Problem: What if there are 15 users registered into the

database. 10 user will register today and the addiction

5 will register another day. Five out 10 from the first day

and the addiction five = 10 Newest Members.

 

I just need for somebody to talk me through it,

and tell me what I am missing.

<?php
$num_results = mysql_num_rows($result); 
for ($i = 0; $i < $num_results; $i++) {
		$row = mysql_fetch_array($result, MYSQL_ASSOC);
	    echo"<a href =\"user_profile.php\">$row[user_name]</a> ";
}// End of For Loop
?>

 

 

[MadTechie]

can't you sort registered date by desc with a limit of 10!!

[Trium918]

can't you sort registered date by desc with a limit of 10!!

 

Something like this?

<?php
$sql = 'SELECT register_date FROM members_login ORDER BY 
register_date DESC LIMIT 10'; 

$query = mysql_query($sql); 

$data = mysql_fetch_assoc($query); 

echo $data['columnname']; 
?>

[MadTechie]

looks about right

[Trium918]

Instead of greating User15 through User6 ,User15 being the last

person who registered, I am getting

 

User9 User8 User7 User6 User5 User4 User3 User2 User15 User14 

 

should be

User15 User14 User13 User12 User11 User10 User9 User8 User7 User6 

[Barand]

Take the limit off and list users and dates and see if order looks OK

 

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

 

 

[MadTechie]

if its the exact reverse then change DESC to ASC

[Trium918]

Take the limit off and list users and dates and see if order looks OK

 

This is the order I was given.

9 : User9

8 : User8

7 : User7

6 : User6

5 : User5

4 : User4

3 : User3

2 : User2

15 : User15

14 : User14

13 : User13

12 : User12

11 : User11

10 : User10

1 : User1

[Barand]

What was he output from my code, showing the dates?

[Trium918]

What was he output from my code, showing the dates?

 

Sorry about that Barand. Here it is:

 

User1 : 2007-04-17 00:00:00

User2 : 2007-04-17 00:00:00

User3 : 2007-04-17 00:00:00

User4 : 2007-04-17 00:00:00

User5 : 2007-04-17 00:00:00

User6 : 2007-04-17 00:00:00

User7 : 2007-04-17 00:00:00

User8 : 2007-04-17 00:00:00

User9 : 2007-04-17 00:00:00

User10 : 2007-04-17 00:00:00

User11 : 2007-04-17 00:00:00

User12 : 2007-04-17 00:00:00

User13 : 2007-04-17 00:00:00

User14 : 2007-04-17 00:00:00

User15 : 2007-04-17 00:00:00

[Barand]

As they all have the same date-time value then any combination of 10 records out of the 15 would be correct for the 10 latest.

[MadTechie]

change some of the dates and times and re-check

[Barand]

If you have an auto_increment id column then sorting by "id DESC" would give the newest records, even if the dates were the same

[Trium918]

As they all have the same date-time value then any combination of 10 records out of the 15 would be correct for the 10 latest.

 

What if I go in tomorrow and enter 15 more user. Then

that will be a total of User30, and I donnot like the way the time is

being displayed. What would be the best way to enter the time into

the database?

 

 

 

[MadTechie]

try this

SQL statement

 

ALTER TABLE `members_login` CHANGE `register_date` `register_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 

 

basically sets the table to add the current date and time from when the record is created.

[Barand]

As they all have the same date-time value then any combination of 10 records out of the 15 would be correct for the 10 latest.

 

What if I go in tomorrow and enter 15 more user. Then

that will be a total of User30, and I donnot like the way the time is

being displayed. What would be the best way to enter the time into

the database?

 

 

 

 

If the register_date column isn't DATETIME then change it to that type. When you add a record put the MySQL value NOW() into that column.

 

That will store the register_date to the nearest second into that column.

[Barand]

The problem with TIMESTAMP columns is that they will update automatically so if, a few months down the line, you change the value of, say, the name field because of a misspelling, the timestamp field becomes the date of the change and not the original date registered.

[Trium918]

As they all have the same date-time value then any combination of 10 records out of the 15 would be correct for the 10 latest.

 

What if I go in tomorrow and enter 15 more user. Then

that will be a total of User30, and I donnot like the way the time is

being displayed. What would be the best way to enter the time into

the database?

 

 

 

 

If the register_date column isn't DATETIME then change it to that type. When you add a record put the MySQL value NOW() into that column.

 

That will store the register_date to the nearest second into that column.

 

I had it at datetime, but I change it to timestamp. Should I change it

back?

[Barand]

I prefer to restrict timestamp columns to those instances where I do want it to record instances of last change.

[Michael Lasky]

Another simple way to do it is to make he column an integer type and just store the timestamp returned by mktime() (number of seconds passed since Jan 1, 1970).

[MadTechie]

isn't that the same as unix timestamps?

ie using DEFAULT CURRENT_TIMESTAMP  from MySQL ?