PHP MySql Query into Array

Hi, i have a table that has three fields:

- username (primary key)
- date (when they joined)
- status (active, inactive, pending)

My table (called accounts) has over 300 users and i want a report that shows the last 7 users who are active. Therefore i need a query that puts the last 7 active users usernames into an array.

I have tried a tutorial on this but i can't seem to get it working. Can any of the experienced users help me with this (using my values)

I think i have the query right but i don't really know how to complete the entire script

$query_lastseven = "SELECT * FROM accounts WHERE status = 'active' ORDER BY date DESC LIMIT 7";

PHP:

thanks in advance to anyone who can help me complete this script correctly, thanks

 

the SQL query looks right...

what format are the dates in the date field?

 

Hi,

thanks for replying, the dates are listed as: 2008-08-19 16:49:39

thanks

 

Add 'or die(mysql_error())' as shown below:

$resource=mysql_query($sql) or die(mysql_error());

Code (SQL):

Also make sure that you are iterating correctly through the resource. The correct way would be:

$sql="SELECT username,date FROM accounts WHERE status='active' ORDER BY date DESC LIMIT 0,7";
$resource=mysql_query($sql) or die(mysql_error());
while($array=mysql_fetch_assoc($resource))
{
$username=$array['username'];
$date=$array['date'];
//rest of the code
}

Code (PHP):

Also, in your SQL statement, you actually don't need to select the 'status' field because it will always be 'active' as you are conditionally choosing only those records where status='active'!

 

I have tried what you said, however only one client is listed instead of seven

	$sql="SELECT MainClientID, datefirstcontact FROM tblclients WHERE status='live' ORDER BY datefirstcontact DESC LIMIT 0,7";
	
	$resource=mysql_query($sql) or die(mysql_error());
	
	while($array=mysql_fetch_assoc($resource))
	
	{
	$aClientList=$array['MainClientID'];
	}



    // assign the list for your template
    $this->oSmarty->assign('aList', $aClientList);

PHP:

any ideas

 

what is "datefirstcontact "? Is it date or something else

and whether you have executed this query in database first ? and in a sample php file? ahd whether you checked the results?