query count coming up blank

Question

I am trying to get a query to count the number of rows it finds, all i get is a warning: Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in D:\xampp2\htdocs\birth\email2.php on line 58

function newMessages( $profile_id )
    {
        global $prefix;

        $INITIATOR_FLAG = 1;
        $INTERLOCUTOR_FLAG = 2;

        $newMessageCountCache = array();

        define('TBL_MAILBOX_CONVERSATION', $prefix.'mailbox_conversation');
        define('TBL_MAILBOX_MESSAGE', $prefix.'mailbox_message');


        $query = mysql_query("SELECT DISTINCT `c`.`conversation_id`
            FROM `".TBL_MAILBOX_CONVERSATION."` AS `c`
            LEFT JOIN `".TBL_MAILBOX_MESSAGE."` AS `m` ON (`c`.`conversation_id` = `m`.`conversation_id`)
            WHERE (`initiator_id`=".$profile_id."  OR `interlocutor_id`=".$profile_id.")
            AND (`bm_deleted` IN(0,".$INTERLOCUTOR_FLAG.") AND `initiator_id`=".$profile_id." OR `bm_deleted` IN(0,".$INITIATOR_FLAG.") AND `interlocutor_id`=".$profile_id.")
            AND (`bm_read` IN(0,".$INTERLOCUTOR_FLAG.") AND `initiator_id`=".$profile_id." OR `bm_read` IN(0,".$INITIATOR_FLAG.") AND `interlocutor_id`=".$profile_id.") AND `m`.`recipient_id`=".$profile_id."
        ");

        $newMessageCountCache[$profile_id] = mysql_num_rows($query);
        return $newMessageCountCache[$profile_id];
    }

mysql_close($cxn);

$emails  = newMessages( $profile_id );
echo $emails;

Line 58 is : $newMessageCountCache[$profile_id] = mysql_num_rows($query);

not sure what i am missing here. any help would greatly be appreciated i have dug sql is not my strong suit for sure.

Since this is a function, the established connection is not available inside the function. Hence your `$query` is empty hence no resource. — csaw, Dec 14 '14 at 18:22
the connection is opened already before this function. the connection works fine. — zaphieon, Dec 15 '14 at 01:21

score 0 · Answer 1 · answered Dec 14 '14 at 18:10

$query = mysql_query("
SELECT DISTINCT `c`.`conversation_id`
FROM `".TBL_MAILBOX_CONVERSATION."` AS `c`
LEFT JOIN `".TBL_MAILBOX_MESSAGE."` AS `m` 
ON 
(`c`.`conversation_id` = `m`.`conversation_id`)
WHERE 
(`initiator_id`=".$profile_id."  OR `interlocutor_id`=".$profile_id.")
AND 
(`bm_deleted` IN(0,".$INTERLOCUTOR_FLAG.") AND `initiator_id`=".$profile_id.") OR 
(`bm_deleted` IN(0,".$INITIATOR_FLAG.") AND `interlocutor_id`=".$profile_id.")
AND 
(`bm_read` IN(0,".$INTERLOCUTOR_FLAG.") AND `initiator_id`=".$profile_id.") OR 
(`bm_read` IN(0,".$INITIATOR_FLAG.") AND `interlocutor_id`=".$profile_id.") 
AND `m`.`recipient_id`=".$profile_id." 
");

$newMessageCountCache[$profile_id] = mysql_num_rows($query);

1

not sure what you did here – zaphieon Dec 15 '14 at 01:22

score 0 · Answer 2 · answered Dec 14 '14 at 18:10

0

// Add this line after $query = ...
$result = mysql_query($query);
// Now count rows in result
$newMessageCountCache[$profile_id] = mysql_num_rows($result);
return $newMessageCountCache[$profile_id];

answered Dec 14 '14 at 18:10

Ataboy Josef

1,993
2
19
27

you just duplicated " $query = mysql_query("SELECT DISTINCT `" your solution did nothing.. but thanks for the effort. – zaphieon Dec 15 '14 at 01:22

query count coming up blank

2 Answers2