Variable mysql query (where $variable = everything)

Question

I am thinking about the most efficient way to make a function that returns the number of rows I get on a certain query. I need to have a lot of variations of the query as I use it for a lot of statistics.(in the real version it is about 5 variables, not 2) Instead of posting my full code I created a small snippet that will point out what I'm trying to overcome. $conn is my PDO connection. the name of the table as well as the names of the fields are fixed.

function stackOverflowDemo($var1,$var2){
global $conn;
$totalrows = $conn->query("select count(*) from $table WHERE firstfield = $var1 AND secondfield = $var2 ")->fetchColumn();
return $totalrows;
}

$output = stackOverflowDemo('1','30');

So right now it will give me the amount of rows who meet the above conditions. (firstfield = 1 AND secondfield = 30) but now comes the clue : now I want the number of rows where firstfield is 1 and secondfield is anything (like *). How can I make that work for me , without having to write a new function or a dedicated query ?

possible duplicate of [Which is fastest? SELECT SQL\_CALC\_FOUND\_ROWS FROM \`table\`, or SELECT COUNT(\*)](http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count) — Álvaro González, Apr 09 '14 at 10:04
Not even close to being duplicate. I'm having a query question , not a speed question. — developers-have-no-vacation, Apr 09 '14 at 10:04
Just make the function take variable number of arguments, and skip the check for fields which wouldn't have criterias defined for them. Or, even better, make this function more flexible by taking an associative array of (field => value) structure. — raina77ow, Apr 09 '14 at 10:05

Brian Ponne · Accepted Answer · 2014-04-09T12:24:51.917

2

function stackOverflowDemo($var1,$var2){
global $conn;
$query = "select count(*) from $table WHERE firstfield IS $var1 AND secondfield IS $var2";
$totalrows = $conn->query($query)->fetchColumn();
return $totalrows;
}

$output = stackOverflowDemo('1','NOT NULL');

maybe this works for you?

edited Apr 09 '14 at 12:24

answered Apr 09 '14 at 10:40

Brian Ponne

68
7

score 1 · Answer 2 · answered Apr 09 '14 at 10:40

now I want the number of rows where firstfield is 1 and secondfield is anything (like *)

Your query should find occurrences of each firstfield and secondfield separately and then count them.

Omitting null entries for secondfield, you can try the following:

select  
    sum(firstfield) first_field_count
  , sum(secondfield) second_field_count
from (
select
    case when firstfield=1 then 1 else 0 end as firstfield
  , case when secondfield is not null then 1 else 0 end as secondfield
from table_name
) t;

Variable mysql query (where $variable = everything)

2 Answers2