[TigerC10]

Originally Posted by lazyseller

I was able to reduce this down to just one query using

$sumgiveaways3 = $vbulletin->db->query_first("SELECT count(field6)AS CountTotal, sum(field6) as SumTotal FROM thread 
WHERE field6 IS NOT NULL AND thread.forumid = '25'");

$giveaways3 = $sumgiveaways3['CountTotal'];
$giveaways4 = $sumgiveaways3['SumTotal'];

Now i want to query field7 and display the content. Im a bit unsure where to start with that.

There would be multiple rows of content for field7 ex. name1, name2, name3

Cool, you got the idea of manipulating the SQL queries. One suggestion I would make, though. NULL is not the same thing as 0. If you have a 0 in the field, it WILL not be null - so be very carful with that.

Next, query_first is only going to return one row... So if you don't need to display lots of different field7 data, you should be okay to use it (but you're really going to have to be sure that what you're querying is correct - check out the MySQL syntax for ORDER BY to make sure of that).

Just tack it on like you did the others...

$sumgiveaways3 = $vbulletin->db->query_first("SELECT field7, count(field6)AS CountTotal, sum(field6) as SumTotal FROM thread 
WHERE field6 IS NOT NULL AND thread.forumid = '25'");

$giveaways3 = $sumgiveaways3['CountTotal'];
$giveaways4 = $sumgiveaways3['SumTotal'];

$giveaways5 = $sumgiveaways3['field7']

Though... You should probably change some of the names of those variables...

For getting multiple rows of content for field7 you'll want to use $vbulletin->db->query(); or $vbulletin->db->query_read(); (query_read is more "secure" since it doesn't accept update or delete/drop statements) instead of query_first... Though you might want to look into the LIMIT syntax for MySQL if you only want a few...

$sumgiveaways3 = $vbulletin->db->query("SELECT field7, count(field6)AS CountTotal, sum(field6) as SumTotal FROM thread 
WHERE field6 IS NOT NULL AND thread.forumid = '25' ORDER BY field7 DESC LIMIT 5");

$giveaways3 = $sumgiveaways3['CountTotal'];
$giveaways4 = $sumgiveaways3['SumTotal'];

$giveawayfield7 = array();
while( $row = $vbulletin->db->mysql_fetch_array($sumgiveaways3) )
{
     array_push($giveawayfield7, $row['field7']);
}

^That will only pull 5 rows, and it sorts the rows and selections of the rows by field7, the DESC keyword sorts them "backwards" eg. if it's a field of numbers the highest number is first - but if it's a field of strings then it will go backwards z->a... Alternatively you can sort with the ASC keyword for ascending to go 1->9 or a->z.

Finally, the while statement takes each row, and assigns all of the values in that row to a variable called $row, which you can treat as a regular array (print it, store it in another array - it doesn't matter. The query_first automagically does that for you, if you use the regular query you'll have to do that yourself.



The one thing I think you're doing wrong is trying to stuff it all into a single query. Queries are not always bad, especially if they're just reading the DB. See, by doing the counts and trying to get multiple rows of data for field7, you're wasting memory. Big time. When you use a count like on a multiple row select statement, every single row is going to have the count information... So here's what you could theoretically do:

$sumgiveaways3 = $vbulletin->db->query("SELECT field7, count(field6)AS CountTotal, sum(field6) as SumTotal FROM thread 
WHERE field6 IS NOT NULL AND thread.forumid = '25' ORDER BY field7 DESC LIMIT 5");

$giveaways3 = $sumgiveaways3['CountTotal'];
$giveaways4 = $sumgiveaways3['SumTotal'];

$giveawayfield7 = array();
while( $row = $vbulletin->db->mysql_fetch_array($sumgiveaways3) )
{
     array_push($giveawayfield7, $row['field7']);
     echo "Watch me waste memory!<br />Count Total: ".$row['CountTotal']."<br />Sum Total: ".$row['SumTotal'];
}

$name1 = $giveawayfield7[0];
$name2 = $giveawayfield7[1];
//etc, you get the idea.  The array $giveawayfield7 is 0 based.

Even if you didn't print the data, it's still in the array. This is a far worse toll on your server than doing two different database queries.

But hey, it's your server. Do what you want. Personally? I'd use the counts in a single query_first like you did and then do the field7 work separately.