Trium918 Posted April 17, 2007 Share Posted April 17, 2007 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> "; } ?> Quote Link to comment Share on other sites More sharing options...
Michael Lasky Posted April 17, 2007 Share Posted April 17, 2007 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 17, 2007 Share Posted April 17, 2007 ... 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> "; Quote Link to comment Share on other sites More sharing options...
Michael Lasky Posted April 17, 2007 Share Posted April 17, 2007 Thanks Barand, i missed that one. Quote Link to comment Share on other sites More sharing options...
Trium918 Posted April 17, 2007 Author Share Posted April 17, 2007 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 ?> Quote Link to comment Share on other sites More sharing options...
MadTechie Posted April 17, 2007 Share Posted April 17, 2007 can't you sort registered date by desc with a limit of 10!! Quote Link to comment Share on other sites More sharing options...
Trium918 Posted April 17, 2007 Author Share Posted April 17, 2007 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']; ?> Quote Link to comment Share on other sites More sharing options...
MadTechie Posted April 17, 2007 Share Posted April 17, 2007 looks about right Quote Link to comment Share on other sites More sharing options...
Trium918 Posted April 17, 2007 Author Share Posted April 17, 2007 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 17, 2007 Share Posted April 17, 2007 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/>" ; } ?> Quote Link to comment Share on other sites More sharing options...
MadTechie Posted April 17, 2007 Share Posted April 17, 2007 if its the exact reverse then change DESC to ASC Quote Link to comment Share on other sites More sharing options...
Trium918 Posted April 17, 2007 Author Share Posted April 17, 2007 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 17, 2007 Share Posted April 17, 2007 What was he output from my code, showing the dates? Quote Link to comment Share on other sites More sharing options...
Trium918 Posted April 18, 2007 Author Share Posted April 18, 2007 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 18, 2007 Share Posted April 18, 2007 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. Quote Link to comment Share on other sites More sharing options...
MadTechie Posted April 18, 2007 Share Posted April 18, 2007 change some of the dates and times and re-check Quote Link to comment Share on other sites More sharing options...
Barand Posted April 18, 2007 Share Posted April 18, 2007 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 Quote Link to comment Share on other sites More sharing options...
Trium918 Posted April 18, 2007 Author Share Posted April 18, 2007 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? Quote Link to comment Share on other sites More sharing options...
MadTechie Posted April 18, 2007 Share Posted April 18, 2007 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted April 18, 2007 Share Posted April 18, 2007 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted April 18, 2007 Share Posted April 18, 2007 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. Quote Link to comment Share on other sites More sharing options...
Trium918 Posted April 18, 2007 Author Share Posted April 18, 2007 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted April 18, 2007 Share Posted April 18, 2007 I prefer to restrict timestamp columns to those instances where I do want it to record instances of last change. Quote Link to comment Share on other sites More sharing options...
Michael Lasky Posted April 18, 2007 Share Posted April 18, 2007 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). Quote Link to comment Share on other sites More sharing options...
MadTechie Posted April 18, 2007 Share Posted April 18, 2007 isn't that the same as unix timestamps? ie using DEFAULT CURRENT_TIMESTAMP from MySQL ? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.