Re: Row Count

Jim Lucas wrote:

Dan Shirah wrote:

Hello all, I am having a problem with trying to count the number of rows returned by my query. I connect to the database fine, my query displays and runs fine, but my row count is incorrect. If I do not put in any serch criteria and my basic query ends up being "SELECT * FROM brev_pending_summary_detail" then the value of $rowcount is 110796. If one search criteria is used the value of $rowcount becomes 11080. If both criteria are used the value of $rowcount is 1108. 110796 is the correct value of my total rows in the table, but the other two values are inncorrect and appear to just be one digit shorter and rounded off. My query is below: <?php if (!$connect_id = ifx_connect("$database@$host", $user, $pass)) { // THE ACTUAL CONNECTION

     echo "Unable to connect to Informix Database\n"; // DISPLAY IF

CONNECTION FAILS

    exit();

} $query = "SELECT * FROM brev_pending_summary_detail"; // BASIC QUERY if (!empty($judge_code) && empty($case_age)) { $query.=" WHERE judge_name = '$judge_code'"; } if (empty($judge_code) && !empty($case_age)) { $query.=" WHERE case_age_group = '$case_age'"; } if (!empty($judge_code) && !empty($case_age)) { $query.=" WHERE judge_name = '$judge_code' AND case_age_group = '$case_age'"; } $count_query = ifx_query ($query, $connect_id); $rowcount = ifx_affected_rows($count_query); echo $rowcount; ?> Thanks in advance, Dan

I would suggest a re-write. Something along the lines of this: <?php // THE ACTUAL CONNECTION if (!$connect_id = ifx_connect("$database@$host", $user, $pass)) { // DISPLAY IF CONNECTION FAILS echo "Unable to connect to Informix Database\n"; echo ifx_ errormsg(); exit(); } // Escape Input since the ifx extension doesn't have a tool for it $judge_code = htmlspecialchars(addslashes($judge_code)); $case_age = htmlspecialchars(addslashes($case_age)); $count = 'SELECT COUNT(*) FROM brev_pending_summary_detail '; // BASIC QUERY $where = array(); if ( !empty($judge_code) ) { $where[] = "judge_name = '{$judge_code}'"; } if ( !empty($case_age) ) { $where[] = "case_age_group = '{$case_age}'"; } if (count($where) > 0) { $query .= ' WHERE '. join(' AND ', $where); $count .= ' WHERE '. join(' AND ', $where); } $result_id = ifx_query($query, $connect_id); list($rowcount) = ifx_fetch_row($result_id); echo $rowcount; ?> I think this will do what you are wanting to do. Hopefully they add a function like mysql_real_escape_string() that will escape/filter you input. But until then, you will need to sanitize it yourself.

I should slow down also and make sure all is well with my messages before sending. <?php // THE ACTUAL CONNECTION if (!$connect_id = ifx_connect("$database@$host", $user, $pass)) { // DISPLAY IF CONNECTION FAILS echo "Unable to connect to Informix Database\n"; echo ifx_ errormsg(); exit(); } // Escape Input since the ifx extension doesn't have a tool for it $judge_code = htmlspecialchars(addslashes($judge_code)); $case_age = htmlspecialchars(addslashes($case_age)); $query = 'SELECT COUNT(*) FROM brev_pending_summary_detail '; // BASIC QUERY $where = array(); if ( !empty($judge_code) ) { $where[] = "judge_name = '{$judge_code}'"; } if ( !empty($case_age) ) { $where[] = "case_age_group = '{$case_age}'"; } if (count($where) > 0) { $query .= ' WHERE '. join(' AND ', $where); } $result_id = ifx_query($query, $connect_id); list($rowcount) = ifx_fetch_row($result_id); echo $rowcount; ?> -- Jim Lucas "Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them." Twelfth Night, Act II, Scene V by William Shakespeare