[SOLVED] My function reurns nothing but Resource id #3 and ends up being 0

[Bisa]

This function is supposed to grab data from my database and calculate how many times out of a total that members have been participating in events the past 30 days. The function itself is a rip off from eqdkp 1.3.2 viewmember.php with slight modifications to match my database.

 

The code looks like this

function raid_count($start_date, $end_date, $member_name)
{

$raid_count = mysql_query('SELECT count(*) FROM eqdkp_raids WHERE raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
//$raid_count = mysql_fetch_array($raid_count);
echo $raid_count . ' - ';

    $sql = 'SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE ra.raid_id = r.raid_id AND ra.member_name=' . $member_name . ' AND r.raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '';

    $individual_raid_count = mysql_query($sql);
echo $individual_raid_count . ' - ';

    $percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

    $raid_count_stats = array(
        'percent'     => $percent_of_raids,
        'total_count' => $raid_count,
        'indiv_count' => $individual_raid_count);

    return $raid_count_stats['percent']; // Only thing needed ATM
}

$result = mysql_query('SELECT member_name FROM eqdkp_members');

while($row = mysql_fetch_array($result))
{
	// Find the percent of raids they've attended in the last 30, 60 and 90 days
    	$percent_of_raids = array(
        	'30'       => raid_count(mktime(0, 0, 0, date('m'), date('d')-30, date('Y')), time(), $row['member_name'])
    	);

	echo $percent_of_raids['30'] . ' - ' . $row['member_name'] . '<br />';
	//mysql_query('UPDATE eqdkp_members SET member_attendance = ' . $attendance . ' WHERE member_name = ' . $row['member_name'] . '');
}

 

While running this (It's supposed to be a cron job when I'm done) I get the following output via my echos:

Resource id #3 - - 0 - UserName1
Resource id #4 - - 0 - UserName2
Resource id #5 - - 0 - UserName3
Resource id #6 - - 0 - UserName4

 

As you can see the $percent_of_raids['30'] is always 0 and I get no errors whatsoever =/

 

The date data from eqdkp_raid_attendees and eqdkp_raids uses the same format "1228086789" to store dates (something which works in the original function but not for mine)

 

Ideally feeding the function with this data would where this is the only event that has taken place the past 30 days all users should have a 100% attendance as they are all listed in the eqdkp_raid_attendees table...

 

I suppose I'm tired and such but banging my head long enough against the screen isnt helping me much here  any help or pointers are greatly appreciated 

[MatthewJ]

If

$percent_of_raids['30']

is a reference to an array, it should be

$percent_of_raids[30]

without the quotes

[revraz]

$individual_raid_count = mysql_query($sql);

echo $individual_raid_count . ' - ';

 

$individual_raid_count will always be the resource ID, which is what you are seeing.

[Bisa]

If

$percent_of_raids['30']

is a reference to an array, it should be

$percent_of_raids[30]

without the quotes

 

Cheers, but changing this and running the code again gives me the same echo output =/

[flyhoney]

mysql_query returns a mysql resource, to actually retrieve the data, you need to use something like mysql_fetch_row.

[Bisa]

mysql_query returns a mysql resource, to actually retrieve the data, you need to use something like mysql_fetch_row.

 

Modifying to fetch row and the code looks like this

function raid_count($start_date, $end_date, $member_name)
{

$raid_count = mysql_query('SELECT count(*) FROM eqdkp_raids WHERE raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
$raid_count = mysql_fetch_row($raid_count);
echo $raid_count . ' - ';

    $individual_raid_count = mysql_query('SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE ra.raid_id = r.raid_id AND ra.member_name=' . $member_name . ' AND r.raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
//$individual_raid_count = mysql_fetch_row($individual_raid_count);
echo $individual_raid_count . ' - ';

    $percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

    $raid_count_stats = array(
        'percent'     => $percent_of_raids,
        'total_count' => $raid_count,
        'indiv_count' => $individual_raid_count);

    return $raid_count_stats['percent']; // Only thing needed ATM
}

 

While running this I'm getting this printed on my screen:

Array - -
Fatal error: Unsupported operand types in /dkp_attendance.php on line 28

Line 28 is:

$percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

[wildteen88]

$raid_count_result = mysql_query('SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE ra.raid_id = r.raid_id AND ra.member_name=' . $member_name . ' AND r.raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');

list($individual_raid_count) = mysql_fetch_row($raid_count_result);

echo $individual_raid_count . ' - ';

[Bisa]

$raid_count_result = mysql_query('SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE ra.raid_id = r.raid_id AND ra.member_name=' . $member_name . ' AND r.raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');

list($individual_raid_count) = mysql_fetch_row($raid_count_result);

echo $individual_raid_count . ' - ';

 

hmm, thnx for trying to help me but I'm generating yet another set of erros and now the function looks like this with wildteens additions

 

function raid_count($start_date, $end_date, $member_name)
{

$raid_count = mysql_query('SELECT count(*) FROM eqdkp_raids WHERE raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
$raid_count = mysql_fetch_row($raid_count);
echo $raid_count . ' - ';

    //$individual_raid_count = mysql_query('SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE ra.raid_id = r.raid_id AND ra.member_name=' . $member_name . ' AND r.raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
//$individual_raid_count = mysql_fetch_row($individual_raid_count);
//echo $individual_raid_count . ' - ';

$raid_count_result = mysql_query('SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE ra.raid_id = r.raid_id AND ra.member_name=' . $member_name . ' AND r.raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
list($individual_raid_count) = mysql_fetch_row($raid_count_result);
echo $individual_raid_count . ' - ';

    $percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

    $raid_count_stats = array(
        'percent'     => $percent_of_raids,
        'total_count' => $raid_count,
        'indiv_count' => $individual_raid_count);

    return $raid_count_stats['percent']; // Only thing needed ATM
}

 

Getting the following echo output:

 

Array -
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /dkp_attendance.php on line 29
-
Fatal error: Unsupported operand types in /dkp_attendance.php on line 32

 

where line 29 is:

list($individual_raid_count) = mysql_fetch_row($raid_count_result);

 

and line 32 is:

$percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

[Bisa]

I was under the impression that using count(*) on this line

	$raid_count_result = mysql_query('SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE ra.raid_id = r.raid_id AND ra.member_name=' . $member_name . ' AND r.raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');

would give me the number of rows where the same member name shows up in both tables in the same raid_id between now and 30 days ago, albeit this seems wrong in some way as when I run

$percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

I'm presented with the error

Fatal error: Unsupported operand types in /dkp_attendance.php on line 32

 

I suspect that the warning

mysql_fetch_row(): supplied argument is not a valid MySQL result resource

for this line

list($individual_raid_count) = mysql_fetch_row($raid_count_result);

is what's causing the trouble...

[wildteen88]

Modified your function:

 

function raid_count($start_date, $end_date, $member_name)
{
    $result = mysql_query('SELECT count(*) FROM eqdkp_raids WHERE raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
    if(mysql_num_rows($result) > 0)
        list($raid_count) = mysql_fetch_row($result);

    $result = mysql_query('SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE ra.raid_id = r.raid_id AND ra.member_name=' . $member_name . ' AND r.raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
    if(mysql_num_rows($result) > 0)
        list($individual_raid_count) = mysql_fetch_row($result);

    if(isset($raid_count, $individual_raid_count) && is_numeric($raid_count) && is_numeric($individual_raid_count))
    {
        $percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

        $raid_count_stats = array(
            'percent'     => $percent_of_raids,
            'total_count' => $raid_count,
            'indiv_count' => $individual_raid_count);

        return $raid_count_stats['percent']; // Only thing needed ATM
    }

    return false;
}

[Bisa]

Cheers, works like a charm now - thnx 

[Bisa]

I hate to bring up old problems but here goes...

In my previous post I praise the lord for having things working my way  however, that is not the case any more  :-\

 

The code:

<?php 

$result = mysql_query("SELECT member_name FROM eqdkp_members");

while($row = mysql_fetch_array($result))
{
// Find the percent of raids they've attended in the last 30 days
$percent_of_raids = raid_count(mktime(0, 0, 0, date('m'), date('d')-30, date('Y')), time(), $row['member_name']);

mysql_query("UPDATE eqdkp_members SET member_attendance = " . $percent_of_raids . " WHERE member_name = " . $row['member_name'] . "");

echo  "Member Name: " . $percent_of_raids . "%<br /><br />";
}

//Attendance Function
function raid_count($start_date, $end_date, $member_name)
{
    $result = mysql_query('SELECT count(*) FROM eqdkp_raids WHERE raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
    if(mysql_num_rows($result) > 0)
        list($raid_count) = mysql_fetch_row($result);

    $result = mysql_query('SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE ra.raid_id = r.raid_id AND ra.member_name=' . $member_name . ' AND r.raid_date BETWEEN ' . $start_date . ' AND ' . $end_date . '');
    if(mysql_num_rows($result) > 0)
        list($individual_raid_count) = mysql_fetch_row($result);

    if(isset($raid_count, $individual_raid_count) && is_numeric($raid_count) && is_numeric($individual_raid_count))
    {
        $percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

        $raid_count_stats = array(
            'percent'     => $percent_of_raids,
            'total_count' => $raid_count,
            'indiv_count' => $individual_raid_count);

        return $raid_count_stats['percent']; // Only thing needed ATM
    }

    return false;
}

?>

 

Presents me with the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in [path]/includes/cron/custom/member_attendance.php on line 23
Member Name: %

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in [path]/includes/cron/custom/member_attendance.php on line 23
Member Name: %

 

note that if things would work as I'd like them to you would see the two only members printed as:

 

Member Name: Aa 100%

Member Name: Bb 50%

 

Also note that adding echo  "<br />" . $result; as the second line of the raid_count function echos "Resource id #20" and "Resource id #19"...

 

No idea as of why this is really, any help would be appreciated =/

[Snart]

Add an echo mysql_error(); before the num_rows to see if there might be any errors in your query.

[Bisa]

Cheers, but now I'm getting:

Fatal error: Unsupported operand types in /includes/cron/custom/member_attendance.php on line 32

 

What I did was echoing $raid_count to discover it's an array  ??? I then proceeded with print_r($raid_count); and get the following output:

Array ( [count(*)] => 2 )

Fatal error: Unsupported operand types in /includes/cron/custom/member_attendance.php on line 34

[Snart]

Well, count(*) will always return 1 row, so the condition if(mysql_num_rows($result) > 0) will always be true.  But count(*) cannot be an array key.  Try this:

 

'SELECT count(*) as count FROM eqdkp_raids WHERE raid_date BETWEEN ' . $start_date . ' AND ' . $end_date

[Bisa]

cheers, but I worked around it by adding

 

$raid_count = $raid_count['count(*)'];

 

and

 

$individual_raid_count = $individual_raid_count['count(*)'];

 

before putting those into the calculation

 

$percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

 

Now the calculation is correct, the data is fetched from the db correctly BUT 

The data is not inserted into the db at all...

 

any hints?

I guess I'm doing something wrong here:

mysql_query("UPDATE eqdkp_members SET member_attendance = " . $percent_of_raids . " WHERE member_name = " . $row['member_name'] . "");

 

(perhaps using some $vbulletin->db-> update? could do the trick.... be right back ^^ )

 

Edit*

$vbulletin->db->query_write(); is working and as a result this is how my finished code looks like, thanks to them who helped me

 

<?php

$result = mysql_query("SELECT member_name FROM eqdkp_members");

while($row = mysql_fetch_array($result))
{
// Find the percent of raids they've attended in the last 30 days
$percent_of_raids = raid_count(mktime(0, 0, 0, date('m'), date('d')-30, date('Y')), time(), $row['member_name']);

$vbulletin->db->query_write("UPDATE eqdkp_members SET member_attendance = " . $percent_of_raids . " WHERE member_name = '" . $row['member_name'] . "'");

echo  "%<br /><br />(In While Loop) Member Name: " . $row['member_name'] . " " . $percent_of_raids . "%";
}

function raid_count($start_date, $end_date, $member_name)
{
    global $vbulletin;

    $raid_count = $vbulletin->db->query_first("SELECT count(*) FROM eqdkp_raids WHERE (raid_date BETWEEN '" . $start_date . "' AND '" . $end_date . "')");
$raid_count = $raid_count['count(*)'];

    $sql = "SELECT count(*) FROM eqdkp_raids AS r, eqdkp_raid_attendees AS ra WHERE (ra.raid_id = r.raid_id) AND (ra.member_name='" . $member_name . "') AND (r.raid_date BETWEEN '" . $start_date . "' AND '" . $end_date . "')";

    $individual_raid_count = $vbulletin->db->query_first($sql);
$individual_raid_count = $individual_raid_count['count(*)'];

echo "<br />(In Function) Member Name: " . $row['member_name'] . " Raid Count: " . $raid_count . " Ind. Raid Count: " . $individual_raid_count . "<br />";

    $percent_of_raids = ( $raid_count > 0 ) ? round(($individual_raid_count / $raid_count) * 100) : 0;

    $raid_count_stats = array(
        'percent'     => $percent_of_raids,
        'total_count' => $raid_count,
        'indiv_count' => $individual_raid_count);

    return $raid_count_stats['percent']; // Only thing needed ATM
}

?>

[Snart]

Assuming that member_name is a string, its value should be between quotes:

 

mysql_query("UPDATE eqdkp_members SET member_attendance = " . $percent_of_raids . " WHERE member_name = '" . $row['member_name'] . "'");