CODEWITHSUNDEEP
phpmysqlsqlif-statement
user6790913
user6790913

Reputation:

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

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:

  1. Check if there's a row containing all in column city.

  2. 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?

Upvotes: 0

Views: 1598

Answers (2)

user6790913
user6790913

Reputation:

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));
}
?>

Upvotes: 0

Don&#39;t Panic
Don&#39;t Panic

Reputation: 41810

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);

Upvotes: 1

Related Questions