Search form selecting results

Question

I have two tables, one called users with user_id, user_name, school_id and subcat_id, and the other called schools with school_id, school_name, and school_decile. I've made a search form with select statement which gathers results from the first table based on which subcat_id and/or school_id the user has chosen on the search form. Now, I want to display the results with the user_name, school_name and school_decile. But as in my select statements, I only selected from the first table and not from the second one. How can I display my results of the users with the school_decile and school_name if they weren't in the select statement?

if ($subcat_number !== '0') {
    if ($school_number === '0') { //if a school isn't selected
        $sql = "SELECT * FROM users WHERE subcat_id=$subcat_number";
    } else { //if a school is selected
        $sql = "SELECT * FROM users WHERE subcat_id=$subcat_number AND school_id=$school_number ";
    }
}

$result = mysqli_query($con, $sql);
$found = mysqli_num_rows($result);

//results table
echo "<table>
<tr>
<th id='namecol'>Search Results:</th>
<th id='schoolcol'></th>
</tr><tr>";

if ($found > 0) {
    while ($row = mysqli_fetch_array($result)) {
        echo "<td id='namecol'>Name: " . $row['name'] . " </td><td>School: " . $row['school_name'] . " <br>Decile of School: " . $row['school_decile'] . "</td>";
//obviously, the $row['school_name'] and $row['school_decile'] doesn't work as they weren't selected in the select statements        
    }
} else {
    echo "<td>No Expert Found</td></tr>";
}
echo "</table>";

New Edit After following the suggestion below of doing a JOIN, this warning came up and the shown result is wrong.

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /var/www/projectv5/search.php on line 74

the line 74 is

$found = mysqli_num_rows($result);

You probably should do a `JOIN` as described [here](http://www.sitepoint.com/understanding-sql-joins-mysql-database/) — Rasclatt, Oct 25 '14 at 02:28
You should make sure that you place everything that is conditional on the `$sql` inside your `if` statement because everything from the `$result = mysqli_query($con, $sql);` requires `$sql`. If the `if ($subcat_number == '0')` then you are left with a query that doesn't have a statement. — Rasclatt, Oct 25 '14 at 03:30
I have recoded so that everything is in the if statement but still showing no results :( — user3603705, Oct 25 '14 at 03:38
I think the sql is wrong. You have to prepend the table name to the columns you want to pull up. Now I'm not saying my is right (I don't do much in the way of joining tables) but I do know you need to prepend your columns with table names like `users.user_id` — Rasclatt, Oct 25 '14 at 03:44
i found where i went wrong with my select statement, there was a syntax error. Thanks for your help — user3603705, Oct 25 '14 at 03:50

score 0 · Accepted Answer · answered Oct 25 '14 at 02:51

As @Rasclatt suggested, you need to use JOIN

if ($subcat_number !== '0') {
    if ($school_number === '0') { //if a school isn't selected
        $sql = "SELECT name, school_name, school_decile  FROM users INNER JOIN schools on users.school_id = schools.school_id  WHERE subcat_id=$subcat_number";
    } else { //if a school is selected
        $sql = "SELECT name, school_name, school_decile FROM users INNER JOIN schools on users.school_id = schools.school_id WHERE subcat_id=$subcat_number AND school_id=$school_number ";
    }
}

$result = mysqli_query($con, $sql);
$found = mysqli_num_rows($result);

//results table
echo "<table>
<tr>
<th id='namecol'>Search Results:</th>
<th id='schoolcol'></th>
</tr><tr>";

if ($found > 0) {
    while ($row = mysqli_fetch_array($result)) {
        echo "<td id='namecol'>Name: " . $row['name'] . " </td><td>School: " . $row['school_name'] . " <br>Decile of School: " . $row['school_decile'] . "</td>";

    }
} else {
    echo "<td>No Expert Found</td></tr>";
}
echo "</table>";

it then gives this warning and result is wrong: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /var/www/projectv5/search.php on line 74 — user3603705, Oct 25 '14 at 03:15

Rasclatt · Answer 2 · 2014-10-25T03:49:52.997

You should be doing something like this:

<?php
    if($subcat_number !== '0') {
            $sql = "SELECT users.name, users.school_name, users.school_decile FROM users INNER JOIN schools on users.school_id = schools.school_id  WHERE users.subcat_id = $subcat_number";

            if($school_number === '0')
                $sql .= " AND schools.school_id = '$school_number'";

            $result =   mysqli_query($con, $sql);
            $found  =   mysqli_num_rows($result); ?>
    <table>
        <tr>
            <th id='namecol'>Search Results:</th>
            <th id='schoolcol'></th>
        </tr><?php

            if($found > 0) {
                    while ($row = mysqli_fetch_array($result)) { ?>
        <tr>
            <td id='namecol'>Name: <?php echo $row['name']; ?></td>
            <td>School: <?php echo $row['school_name']; ?><br>Decile of School: <?php echo $row['school_decile']; ?></td>
        </tr>
                <?php   }
                }
            else { ?>
        <tr>
            <td>No Expert Found</td>
        </tr><?php 
                } ?>
    </table><?php
        } ?>

Search form selecting results

2 Answers2