Mark Lee Mark Lee - 5 months ago 9
PHP Question

How to Display MySQL Results when no Matches Found in Tables

This question seems to have been asked many times but none of the answers has worked for me. Not counting the number or rows, nothing.



I wrote the following query function to be accessed by a form where the user selects a skill from a drop down list and must enter a city. The results are correctly displayed when there is a match of skill and city. Here's the code:



function search() {
$city=$_POST['city'];
if(!isset($_POST['city'])) echo "You must enter a city to find a match.";

$sql = "SELECT skill.skill_name, team.city FROM skill INNER JOIN team ON skill.skill_id=team.skill WHERE skill.skill_name LIKE '$skill' AND city LIKE '$city' ";
foreach ($myconnect->query($sql) as $row) {
if($sql) {
echo $skill . " is available in " . $row['city'];
}
else {
if(!$sql)
echo "No result found.";
}
}
}//submit
}




Can you please help set me straight?

Thanks.

To clarify: When a match is found the code correctly displays the message. But when there is no match it does not display the message that no result was found.



The solution by @Your Common Sense with two slight modifications: (1) to the post var $_POST['skill'] to $_POST['skills'] and echoing the form's post vars instead of the results from the $row.


$sql = "SELECT s.skill_name, t.city FROM skill s JOIN team t ON s.skill_id=t.skill
WHERE s.skill_name = ? AND city = ?";
$stmt = $myconnect->prepare($sql);
$stmt->bind_param("ss", $_POST['skill'], $_POST['city']);
$stmt->execute();
$data = $stmt->get_result()->fetch_all();
if (!$data) {
echo "No result found.";
} else {
foreach ($data as $row) {
echo htmlspecialchars($_POST['skills']) . " is available in " . htmlspecialchars($_POST['city']);
}
}

Answer

there are too many wrong answers around, so it worth to write a proper one.

$sql = "SELECT s.skill_name, t.city FROM skill s JOIN team t ON s.skill_id=t.skill 
        WHERE s.skill_name = ? AND city = ?";
$stmt = $myconnect->prepare($sql);
$stmt->bind_param("ss", $_POST['skill'], $_POST['city']);
$stmt->execute();
$data = $stmt->get_result()->fetch_all();
if (!$data) {
    echo "No result found.";
} else {
    foreach ($data as $row) {
        echo htmlspecialchars($row['skill_name']) . " is available in " . htmlspecialchars($row['city']);
    }
}