SQL IF STATEMENT with PHP variable

Question

I am trying to make a single query with SQL conditions like the above:

Let's say first that we have the PHP variable $reco that will let us update the data when we want:

mysql_query("
INSERT INTO app_table (views, average)
VALUES
(
IF($reco = 'yes', '$views', views),
IF($reco = 'yes', '$aver', average)
)
ON DUPLICATE KEY UPDATE
views = IF($reco = 'yes', $views, views),
average = IF($reco = 'yes', $aver, average)
");

The problem is that $reco doesn't works as I want inside the query but a column of table will work, so how can make it work with a PHP variable?

For example if I had reco inside the table would work like this:

Notice that I have the $reco in php variable not inside the table.

mysql_query("
INSERT INTO app_table (views, average)
VALUES
(
IF(reco = 'yes', '$views', views),
IF(reco = 'yes', '$aver', average)
)
ON DUPLICATE KEY UPDATE
views = IF(reco = 'yes', $views, views),
average = IF(reco = 'yes', $aver, average)
");

It's really hard to tell what the problem and/or question is. And that is besides the fact I tried to find the above query. — PeeHaa
– PeeHaa, Commented Nov 14, 2013 at 21:39
what's in $reco? If it's something like blahblahblah, and you don't have a blahblahblah field in your table, then the query is going to blow up on you. mysql_query(...) or die(mysql_error()) will tell you exactly WHERE/how the query's failing. That or you're totally not understanding how PHP and SQL operate... — Marc B
– Marc B, Commented Nov 14, 2013 at 21:40
Guys, the $reco is 'yes' or 'no' php variable string and i am saying for example in "IF($reco = 'yes', '$views', views)" if $reco is 'yes' make views = '$views' else leave views as views ($views -> php, views -> sql colum) — Stavros Koureas
– Stavros Koureas, Commented Nov 14, 2013 at 21:43

alephreish · Accepted Answer · 2013-11-14 22:00:05Z

3

Well, if it's kind of a proof of the principle, and you still want to do it on the SQL side:

$query = "... IF('$reco' = 'yes', '$views' ...";

Note the quotes around $reco. So, if $reco were 'yes' the query would be interpreted as ... IF ('yes' = 'yes', 'some_value' ...

answered Nov 14, 2013 at 22:00

alephreish

5493 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Stavros Koureas Over a year ago

har-wradim many thanks, it will be a pure string inside the sql query, i am searching this for hours and my mind don't went there! Thanks!

alephreish Over a year ago

Don't forget to up-vote the answer and/or mark it as the accepted one, if you like :). I want to stress it again: it's just a general principle useful in some situations, but I wouldn't do it this way in your particular case (if I understand it correctly).

Stavros Koureas Over a year ago

I can not vote because i dont have enough reputation, but i have another question, how to make if condition to include limit or not? like: $query = "SELECT **** FROM **** WHERE **** GROUP BY **** ORDER BY **** DESC IF(some_condition) LIMIT ***** END IF;

alephreish Over a year ago

It's not straightforward, but you can try: stackoverflow.com/questions/7899639

Stavros Koureas Over a year ago

Thanks i found a solution to do this with IF(A,B,C)

alephreish · Accepted Answer · 2013-11-14 21:54:10Z

0

The easiest way, I think, is to do your string comparison purely in PHP:

if ($reco == 'yes') {
   $views = $views_value;
   $average = $average_value;
}
else {
   $views = $views_default;
   $average = $average_default;
}

//escape your $views and $average strings, if necessary
$query = "INSERT INTO app_table (views, average) VALUES ('$views', '$average') ON DUPLICATE KEY UPDATE //whatever";

edited Nov 14, 2013 at 21:54

answered Nov 14, 2013 at 21:48

alephreish

5493 silver badges18 bronze badges

3 Comments

Stavros Koureas Over a year ago

Thanks har-wradim this was my original code and dont forget to get views and average inside else statement i need a select query so the code becomes bigger but i am trying to short it as i am changing the code often.

alephreish Over a year ago

Actually this way you perform string comparison only once. If you do it the way you proposed: it's done 4 times (on the SQL side)

Stavros Koureas Over a year ago

Yes i know, but i wanna do it as i want short code and this way i am escaping from make a Select query to get views or average. Actualy the query is more more bigger and this way it will be shorten

Collectives™ on Stack Overflow

SQL IF STATEMENT with PHP variable

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related