NineChime forum

Waccoon wrote:

OK, try this.  MySQL handles NULL differently than I thought, so it was easier than I expected.

Code:

// Search query
$num_users_search = '';
$active_members = 0;
if (!empty ($searchop)) {
    $num_users_search = $langop_matchysearch;
    $searchqry = "SELECT * FROM {$OekakiPoteto_MemberPrefix}oekaki WHERE `{$searchop}` != '' AND `{$searchop}` LIKE '%{$searchstr}%' ORDER BY `{$searchop}` {$order} LIMIT ".($pageno * $mypages).', '.$mypages;
    $searchqry2 = "SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki WHERE `{$searchop}` != '' AND  `{$searchop}` LIKE '%{$searchstr}%'";

    // Special cases:
    if ($searchop == 'joindate' || $searchop == 'lastlogin') {
        // Add user name to search
        trim ($searchstr) != ''
            ? $where_clause = "WHERE `usrname` != '' AND `usrname` LIKE '%{$searchstr}%'"
            : $where_clause = '';

        $searchqry = "SELECT * FROM {$OekakiPoteto_MemberPrefix}oekaki {$where_clause} ORDER BY `{$searchop}` {$order} LIMIT ".($pageno * $mypages).', '.$mypages;
        $searchqry2 = "SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki {$where_clause}";
    }
    if ($searchop == 'age') {
        // Reverse ORDER so sorting will be by age, not date
        $age_order = 'DESC';
        if ($order == 'DESC') {
            $age_order = 'ASC';
        }
        $searchqry = "SELECT * FROM {$OekakiPoteto_MemberPrefix}oekaki WHERE `age` != '' ORDER BY `age` {$age_order} LIMIT ".($pageno * $mypages).', '.$mypages;
        $searchqry2 = "SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki WHERE `age` != ''";
    }
} else {
    $searchqry = "SELECT * FROM {$OekakiPoteto_MemberPrefix}oekaki ORDER BY `usrname` ASC LIMIT ".($pageno * $mypages).', '.$mypages;
    $searchqry2 = "SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki";

    // Get active members
    if ($cfg['kill_user'] < 1) {
        $active_search = mysql_query ("SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki WHERE (DATE_ADD(lastlogin, INTERVAL {$active_login_time} DAY) <= NOW())");
        $active_members = (int) mysql_result ($active_search, 0);
    }
}

It finally worked!

I even managed to exclude hidden e-mail addresses if the user searched by e-mail as well as no gender preferences if the user searched by gender.

Waccoon wrote:

OK, I understand what you're asking, now.

The problem is that the memberlist is designed to prioritize results, not filter them (gender is the exception, because it uses a value to denote that the field has been set, but may not be applicable).  This isn't really a bug, but rather a design limitation.

Fixing it requires rewriting both the search logic to use different WHERE clauses if the search string is empty, and SQL statements need to be changed to filter out both blank and NULL fields (because MySQL doesn't have an empty() function).  Trying to pull all the fields out of the database and filter the results is a pain, especially if you want the total count to be accurate.

I can add this to my to-do list, but I have higher priorities right now.  I haven't been coding much, lately.

That would be a good idea as I have been trying to fix a lot of bugs this latest version of Wax Poteto.

>> Fixing it requires rewriting both the search logic to use different WHERE clauses if the search string is empty, and SQL statements need to be changed to filter out both blank and NULL fields (because MySQL doesn't have an empty() function).

EDIT: I tried to insert these two lines:

$searchqry = "SELECT * FROM {$OekakiPoteto_MemberPrefix}oekaki WHERE `{$searchop}` != '' AND `{$searchop}` LIKE '%{$searchstr}%' ORDER BY `{$searchop}` {$order} LIMIT ".($pageno * $mypages).', '.$mypages;

...and...

$searchqry = "SELECT * FROM {$OekakiPoteto_MemberPrefix}oekaki {$where_clause} ORDER BY `{$searchop}` {$order} LIMIT ".($pageno * $mypages).', '.$mypages;

...in a attempt to exclude blank/NULL queries, but although blank results are no longer available, pages come up blank and I even received this warning message:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/rainbow/public_html/memberlist.php on line 97

Wac, so how would I have to change the SQL statements to filter out both the blank and NULL fields in the member list? Ya, know I've tried to filter out the NULL fields from the search, but I either wound up with warning messages mentioned above or even parse errors.

Waccoon wrote:

No no no...  I was trying to explain how the iterator works internally.  You don't need to put $i=0 in there and you can't actually use a second parameter for mysql_fetch_array() that way.  Never mind.

The bottom line is that this will screw everything up, so don't use it:

Code:

<? for ($i = 0; $i < $searchrows; $i++) {
    $the_user = mysql_fetch_array ($search);
?>

OK, let me see if I understand what you want to do...

1. How can I get the member listing to properly not include blank or NULL entries when finding the results in the search.

Such as?  If the results are blank or NULL, nothing should be printed.  Do you want to put a placholder or get rid of a table column?

2. How can I also exclude search results regarding hidden e-mail addresses or someone who selected "N/A" as their gender?

If you're an admin, all e-mails will show up, although a "N/A" is printed before the e-mail to let admins know if that person has decided to hide his/her e-mail.  The results are different if you're not an admin.

As for the gender issue, look at this section:

Code:

        } else {
            echo '            <td>'.$row[$searchop]."</td>\n";
        }

REPLACE WITH:

        } else {
                        if ($searchop == 'gender' && $row[$searchop] == 'N/A') {
                echo '            <td></td>\n";
                        } else {
                echo '            <td>'.$row[$searchop]."</td>\n";
                        }
        }

>> Such as?  If the results are blank or NULL, nothing should be printed.  Do you want to put a placholder or get rid of a table column?

That's correct. If the search results come out blank or NULL, the blank rows should be excluded and it should not count towards the number of users found in the search. I also want a place holder for this as well.

As for getting rid of the $i = 0; that could result in a parse error and there needs to be someway to get rid of it without returning those erors.

Secondly, upon inserting the code for the gender search result, it didn't work. When I excluded the "N/A" part from the gender search results, it turned out blank. I somehow need to find a way to get rid of the blank search result, if I exclude the "N/A" variable from the search.

Now, what part of the code can be replaced in order to fix this bug?

Waccoon wrote:

You're getting blank entries because you're wiping out the SQL iterator with this code:

Code:

<? for ($i = 0; $i < $searchrows; $i++) {
    $the_user = mysql_fetch_array ($search);
?>

PHP automatically keeps an iterator internally that keeps track of which row it needs to get from the database.  If you fetch all the rows, the iterator must be reset before you can fetch those rows again.  So, if you do something like this:

Code:

$row = mysql_fetch_array($search);
$row = mysql_fetch_array($search);
$row = mysql_fetch_array($search);

PHP automatically does something like this:

Code:

$i=0;
$row = mysql_fetch_array($search, $i++); // $i = 0
$row = mysql_fetch_array($search, $i++); // $i = 1
$row = mysql_fetch_array($search, $i++); // $i = 2

You can reset the iterator manually, but the proper way to get the total number of entries is this:

Code:

$number = mysql_num_rows ($search);

That won't mess with the iterator.

Most database layers require that you specify which row you want, as what happens with mysql_result().  mysql_fetch_array() is special, because it will automatically get all the rows in order.  When all the rows are exhausted, the function will return FALSE.

I tried to insert the following codes as you mentioned:

<? $i=0; ?>
<? $the_user = mysql_num_rows ($search); ?>

And this:

<? $i=0; ?>
<? $the_user = mysql_fetch_array ($search, ++); ?>

And it didn't work. It either resulted in a infinite loop or a parse error.

Infact, there is already this code on line 95:

$searchrows = mysql_num_rows ($search);

What do I have to replace in or to get the search results to not display blank (or NULL) entires as well as e-mail addresses that are hidden and users who get the gender variable to "N/A"? I'm a bit confused.

I found a bug that is not easy to correct in one of the areas of the oekaki.

Upon searching for results for a specific name, e-mail address, age, gender, location, flags or template in the member listing (memberlist.php), most of the results come up blank.

For example, if you use the drop down options feature to search for all members who have entered a specific age, 164 results were found and most of the results are blanked out. There are supposed to be only 12 results from users who have specified their age.

Now, checking the phpMyAdmin mySQL database, while I found that while the information retrieved are blank, I found that a portion of my users have the NULL variable set by default in the database.

This issue also effects members who are active as well.

Here's a snippet of what the search query part of the memberlist.php file looks so far:

// Search query
$num_users_search = '';
$active_members = 0;
if (!empty ($searchop)) {
    $num_users_search = $langop_matchysearch;
    $searchqry = "SELECT * FROM {$OekakiPoteto_MemberPrefix}oekaki WHERE `{$searchop}` LIKE '%{$searchstr}%' ORDER BY `{$searchop}` {$order} LIMIT ".($pageno * $mypages).', '.$mypages;
    $searchqry2 = "SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki WHERE `{$searchop}` LIKE '%{$searchstr}%' ORDER BY `{$searchop}` {$order}";

    // Special cases: joindate/last login
    if ($searchop == 'joindate' || $searchop == 'lastlogin') {
        // Add user name to search
        trim ($searchstr) != ''
            ? $where_clause = "WHERE `usrname` LIKE '%{$searchstr}%'"
            : $where_clause = '';

        $searchqry = "SELECT * FROM {$OekakiPoteto_MemberPrefix}oekaki {$where_clause} ORDER BY `{$searchop}` {$order} LIMIT ".($pageno * $mypages).', '.$mypages;
        $searchqry2 = "SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki {$where_clause} ORDER BY `{$searchop}` {$order}";
    }
} else {
    $searchqry = "SELECT * FROM {$OekakiPoteto_MemberPrefix}oekaki ORDER BY `usrname` ASC LIMIT ".($pageno * $mypages).', '.$mypages;
    $searchqry2 = "SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki";

    // Get active members
    if ($cfg['kill_user'] < 1) {
        $active_search = mysql_query ("SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki WHERE (DATE_ADD(lastlogin, INTERVAL {$active_login_time} DAY) <= NOW())");
        $active_members = (int) mysql_result ($active_search, 0);
    }
}


$search = mysql_query ($searchqry);
$search2 = mysql_query ($searchqry2);
$searchrows = mysql_num_rows ($search);
$totalrows = mysql_result ($search2, 0);
$activerows =  $totalrows - $active_members;

$pages = ceil ($totalrows / $mypages);

$total_query = mysql_query ("SELECT COUNT(usrname) FROM {$OekakiPoteto_MemberPrefix}oekaki");
$total_members = (int) mysql_result ($total_query, 0);

Secondly, here is a snippet of how search results are counted:

<? for ($i = 0; $i < $searchrows; $i++) {
    $the_user = mysql_fetch_array ($search);
?>

A couple of things...

1. How can I get the member listing to properly not include blank or NULL entries when finding the results in the search.
2. How can I also exclude search results regarding hidden e-mail addresses or someone who selected "N/A" as their gender?

(Gah. I'm just hoping that there are no more bugs found in the Wax Poteto after this thread. )