Need Programming Help

[lazyseller]

$sumgiveaways = $vbulletin->db->query_first("SELECT SUM(field6) AS GiveTotal FROM " . TABLE_PREFIX . "thread");
$giveaways = $sumgiveaways['GiveTotal'];


I was able to use the code above to add up all the values in field6 now i would like to count how many rows in field6 that are not equal to 0.

Im really new at this stuff and would like some help with an example so i can understand the logic.

Thanks

[TigerC10]

It'll look like this:

$countgiveaways = $vbulletin->db->query_first("SELECT COUNT(field6) AS GiveTotal FROM " . TABLE_PREFIX . "thread WHERE field6!=0");

[lazyseller]

Thanks for the fast response much appreciated.

I was able to get my data to display but it adds 2 queries to my forum display page.


$sumgiveaways = $vbulletin->db->query_first("SELECT SUM(field6) AS GiveTotal FROM " . TABLE_PREFIX . "thread");
$giveaways = $sumgiveaways['GiveTotal'];

$countgiveaways = $vbulletin->db->query_first("SELECT COUNT(field6) AS GiveTotal FROM " . TABLE_PREFIX . "thread WHERE field6!=0");
$giveaways2 = $countgiveaways['GiveTotal'];


This doesnt seem optimal if i had to add additional data to be displayed.

that would be many queries

What are my options here? Would i have to write this data into a new table which i could call and it would only use 1 query right? Sorry im very new to this.

--------------- Added 1235187964 at 1235187964 ---------------

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

[Dismounted]

$dataresult = $vbulletin->db->query_read("
SELECT field7
FROM " . TABLE_PREFIX . "thread
");

while ($data = $vbulletin->db->fetch_array($dataresult))
{
// process it here
}

[lazyseller]

Would i be able to combine it all with one query?

im trying to count field6, sum of field6, and display all results of field 7 with value.

[TigerC10]

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.

[lazyseller]

Much thanks for helping me.

In my attempt to seperate the query i tried the following.


$dataresult = $vbulletin->db->query_read("
SELECT field7
FROM " . TABLE_PREFIX . "thread
ORDER BY field7 DESC LIMIT 5
");

while ($data = $vbulletin->db->fetch_array($dataresult))
{
// process it here
$wow1 = $data[0];
$wow2 = $data[1];

echo "<PRE>";
print_r($data);
echo "</PRE>";

print_r($wow1);
print_r($wow2);
}



$stack = array("orange", "banana", "apple", "raspberry");

$name1 = $stack[0];
$name2 = $stack[1];

print_r($stack);
echo "<br />";
print_r($name1);
print_r($name2);


Ok i have no idea what im doing wrong. I printed the array out to see how its displayed but i do not see the (array #) such as [0], [1]

So im totally lost.

[TigerC10]

while ($data = $vbulletin->db->fetch_array($dataresult))
{
// process it here
$wow1 = $data[0];
$wow2 = $data[1];

echo "<PRE>";
print_r($data);
echo "</PRE>";

print_r($wow1);
print_r($wow2);
}


Ok i have no idea what im doing wrong. I printed the array out to see how its displayed but i do not see the (array #) such as [0], [1]

So im totally lost.

Well, you're printing the data inside the while loop... See, while you're inside that while loop, you only have the scope of the single row. That is to say, the only field that you selected was field7, so every row will only have 1 value. Therefore your print_r statement doesn't see anything but 1 value - and since it's not an array (or rather, since it gets translated to a single variable by some under-the-cover stuff in PHP) there is no index.

Do this instead.



$dataresult = $vbulletin->db->query_read("
SELECT field7
FROM " . TABLE_PREFIX . "thread
ORDER BY field7 DESC LIMIT 5
");

$queriedfields = array();

while ($data = $vbulletin->db->fetch_array($dataresult))
{
// process it here
array_push( $queriedfields, $data[field7] );
}
print_r( $queriedfields );