How to use if/else statement in with SQL in PHP

Question

Actually I have the following code:

$stmt = $pdo->prepare('SELECT id, city, date_created FROM my_table WHERE city = :city ORDER BY date_created DESC LIMIT 1');
$stmt->execute(array(':city' => $city));

I try to explain what I want to do:

Check if there's a row containing all in column city.
If so, check if the date of this row in date_created ist newer that the date in the newest entry for the query above. If so, select this row, else select the newest entry of the query below.

I hope you understand what I want to do. Unfortunately I'm not familiar with if/else statements in SQL.

Can anybody help me?

http://stackoverflow.com/questions/22252904/check-if-row-exists-with-mysql/22253579#22253579 and look under *"Or a PDO method with a prepared statement:"* - most likely a duplicate of this question. — Funk Forty Niner, Mar 15 '17 at 16:37
The answer you posted I feel it should be part of your question — Masivuye Cokile, Mar 15 '17 at 16:54

score 1 · Answer 1 · answered Mar 15 '17 at 17:24

I think this could be simplified.

It looks like you should be able to just select rows where the city is either your parameter or 'all', order by date_created descending like you already are, and take the first row.

$sql = "SELECT id, city FROM news
        WHERE city IN(:city, 'all')
        ORDER BY date_created DESC LIMIT 1";

Since you know your query will only return one row, you can just use fetch instead of fetchAll to eliminate the unnecessary outer array.

$stmt = $pdo->prepare($sql);
$stmt->execute(array(':city' => $city));
$result = $stmt->fetch(PDO::FETCH_ASSOC);

score 0 · Answer 2 · 2017-03-16T08:54:33.850

Cause I'm not familiar with if/else statements in SQL but in PHP I made the following that's working:

<?php
$stmt = $pdo->prepare('SELECT id, city, date_created FROM news WHERE city = :city ORDER BY date_created DESC LIMIT 1');
$stmt->execute(array(':city' => $city));
$results = $stmt->fetchAll();
foreach( $results as $row ) {
$date_city = $row['date_created'];
}
$stmt = $pdo->prepare('SELECT id, city, date_created FROM my_table WHERE city = "all" ORDER BY date_created DESC LIMIT 1');
$stmt->execute();
$results = $stmt->fetchAll();
foreach( $results as $row ) {
$date_all = $row['date_created'];
}
if ($date_all > $date_city) {
$stmt = $pdo->prepare('SELECT id, city, date_created FROM my_table WHERE city = "all" ORDER BY date_created DESC LIMIT 1');
$stmt->execute(array(':city' => $city));
} else {
$stmt = $pdo->prepare('SELECT id, city, date_created FROM my_table WHERE city = :city ORDER BY date_created DESC LIMIT 1');
$stmt->execute(array(':city' => $city));
}
?>

this statment `$stmt = $pdo->prepare('SELECT id, city FROM news WHERE city = "all" ORDER BY date_created DESC LIMIT 1'); $stmt->execute(); $results = $stmt->fetchAll();` can be reduced to `$stmt = $pdo->query('SELECT id, city FROM news WHERE city = "all" ORDER BY date_created DESC LIMIT 1')->fetchall();foreach($stmt as $row)` — Masivuye Cokile, Mar 15 '17 at 16:52
It doesn't look like this should work, because `$row['date_created']` shouldn't be set, since you're only selecting id and city in your query. — Don't Panic, Mar 15 '17 at 17:25

How to use if/else statement in with SQL in PHP

2 Answers2