filtering problem

This site is best viewed in a modern browser with JavaScript enabled.

jeffap

I am making a simple filtering system for visitor that the criteria they choose is either in "column1" or "column2".

I had a thread regarding to sorting few days ago and I had been told that I can use the clause "IFNULL" and "AS" to solve the problem, but when it comes to filtering it does not work...

anyway my SQL statement is:

SELECT *, IFNULL(column1,column2) AS finalcolumn FROM mytable WHERE finalcolumn='$visitor_criteria'

eval_BadCode_

In most SQL flavors, you can not expect an aliased column to behave like a real column. This would drastically change the execution plan of MySQL in an undesirable way. The work-around is to replace the alias with the expression, or to store the expression (per row in a stored procedure) in some variable.

Try doing it like this:

$resource = mysql_query(sprintf(
  "SELECT *, 
   IFNULL(column1,column2) AS finalcolumn 
   FROM mytable 
   WHERE IFNULL(column1,column2)='%s'",
   mysql_real_escape_string($visitor_criteria)));

edit: I think you might mean to use COALESCE instead of IFNULL

jeffap

thank you for your help eval

but now i am converting the obsolete mysql to mysqli (which is such a pain to me), so i guess the php code would be like?

<PHP>
$resource=$connection->query(sprintf(
"SELECT *,
IFNULL(column1,column2) AS finalcolumn
FROM mytable
WHERE IFNULL(column1,column2)='%s'",
mysqli_real_escape_string($link, $visitor_criteria)));
</PHP>

ps: i use if "IFNULL" because i want the criteria to be: if column1 is not null then use column1, otherwise use column2, which always contain a value

also, is there any websites that i can learn mysqli command? i found that php.net is sometimes intimidating

gk53

I think it should be

<PHP>
$resource=$connection->query(sprintf(
"SELECT *,
IFNULL(column1,column2) AS finalcolumn
FROM mytable
WHERE IFNULL(column1,column2) like '%s'",
mysqli_real_escape_string($link, $visitor_criteria)));
</PHP>

jeffap

ok i tested the above SQL and it does not work, the error message is as below:

Fatal error: Fatal error: Call to a member function bind_param() on a non-object in xxx.php on line 132

i think i would need to use prepared statement...

i tried to put it like this:

$statement=$connect->prepare("
SELECT *, 
IFNULL(column1,column2) AS finalcolumn 
FROM mytable 
WHERE finalcolumn=?");

$statement->bind_param('s',mysqli_real_escape_string($connect,$filtercriteria));

$statement->execute();

$statement->bind_result($returned_var1);

while($statement->fetch()){
    echo $returned_var1 . '<br />';
}

am i on the right track???

ps: i followed a tutorial on http://codular.com/php-mysqli

jeffap

almost work out the whole thing...

$statement=$connect->prepare("
SELECT var1,var2 
FROM mytable 
WHERE IFNULL(column1,column2)=?");

$statement->bind_param('s',mysqli_real_escape_string($connect,$filtercriteria));

$statement->execute();

$statement->bind_result($var1, $var2);

Just asking:
the SQL command

IFNULL(column1,column2)=?

If both column1 and column2 return 'NULL' in some row in my database, will the SQL command still work???

I have all the above code run without error but the data shows no result and i suspect it has something to do with the IFNULL (or COALESCE that i later use)....