[thelist] mysql query help

Jeffrey Barke jeffrey.barke at themechanism.com
Fri Aug 17 16:27:39 CDT 2007


> Is there a way to write the above query differently to achieve this?

> How would I get this structure:
>
> Username
>   - item
>   - number of times accessed
>   - last date accessed
>
>   - item#2
>   - number of times accessed#2
>   - last date accessed#2

Mark,

As far as I know, you won't be able to get the above structure. The  
results of queries are always tables and so look like this:

> Username
> item (item)
> number of times accessed (iCount)
> last date accessed (myDate)
>
> Username
> item#2 (item)
> number of times accessed#2 (iCount)
> last date accessed#2 (myDate)


This is how I return the data when I run similar queries. Then I  
output the data like this:

$strSql = 'some statement here';
$intSqlRes = mysql_query($strSql);
var $strUser = '';
while ($arrData = mysql_fetch_assoc($intSqlRes)) {
if ($strUser != $arrData['Username']) {
echo '<h2>' . $arrData['Username'] . '</h2>';
$strUser = $arrData['Username'];
}
echo $arrData['item'];
}

Jeffrey

--
Cheers.
Jeffrey Barke
jeffrey.barke at theMechanism.com
Lead Developer, US
theMechanism - New York City
440 9th Avenue, 8th Floor
New York, NY 10001-1631
t: +1 212.404.3150
c: +1 917.941.1232
f: +1 212.404.3228



http://www.theMechanism.com

Subscribe to theMechcast, our monthly Podcast:
http://feeds.feedburner.com/theMechcast

theMechanism - London
3rd Floor
405 The Strand
London E14 9FW
United Kingdom
t: +44 (0)20 7240 4964
f: +44 (0)20 7240 2262

--
The information contained in this Electronic mail message is attorney  
privileged and confidential information intended only for the use of  
the individual or entity named above. Such information also is  
intended to be privileged, confidential, and exempt from disclosure  
under applicable law. If the reader of this message is not the  
intended recipient or the employee or agent responsible to deliver it  
to the intended recipient, you are hereby notified that any  
dissemination, distribution, or copying of this communication is  
strictly prohibited. If you have received this communication in  
error, please notify us immediately by telephone.

Blah, blah, blah...
--


On Aug 17, 2007, at 2:18 PM, Mark Joslyn wrote:

> <snip>
> group by username, item
>
> also you can then go ahead and list 'item' in the select list
> </snip>
>
>
> Thank you!
>
> "SELECT username, COUNT(username) AS uCount, item, COUNT(item) AS  
> iCount, date, MAX(date) AS myDate FROM myTable GROUP BY username,  
> item ORDER BY username"
>
> This got me a bit closer, but this query would give me this structure
>
> This gives me the following structure:
>
> Username
>   - item (item)
>   - number of times accessed (iCount)
>   - last date accessed (myDate)
>
> Username
>   - item#2 (item)
>   - number of times accessed#2 (iCount)
>   - last date accessed#2 (myDate)
>
> etc..
>
> How would I get this structure:
>
> Username
>   - item
>   - number of times accessed
>   - last date accessed
>
>   - item#2
>   - number of times accessed#2
>   - last date accessed#2
>
> Thanks,
>
> markJ
>
>
> -- 
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !




More information about the thelist mailing list