samedi 26 septembre 2015

building in an if statement into a mysqli search

I built an ad system for my website that first tries to find a specific ad based on location and category. Failing that it picks only based on location, failing that it picks a generic ad:

$sql = "SELECT * FROM ads WHERE city='$city' AND category='$category' LIMIT 1";
$user_query = mysqli_query($db_conx, $sql);

if( mysqli_num_rows($user_query)==0){
    $sql = "SELECT * FROM ads WHERE city='$city' AND category='All' LIMIT 1";
    $user_query = mysqli_query($db_conx, $sql);

if( mysqli_num_rows($user_query)==0){ // 'City' is the generic all locations
    $sql = "SELECT * FROM ads WHERE city='City' AND category='All' LIMIT 1";
    $user_query = mysqli_query($db_conx, $sql);
    }
}

I thought it might be possible to write a single mysqli statement to do all 3 operations, but nothing seemed to work properly. (I tried using various versions of union and limit 1). Is it possible to do something like this, or is it best left as is?

Aucun commentaire:

Enregistrer un commentaire