CODEWITHSUNDEEP
phpmysqlsql
rookieloops
rookieloops

Reputation: 47

Running an SQL query with PHP/MySQL then reordering the results

I am trying to run a subquery so that I can sort & ouput results according to which tag column a search term appears in. The query runs fine without the subquery but with it I am getting a 'mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given' error. Any help much appreciated.

$sql = "SELECT * FROM (SELECT trackname, tag1, tag2, tag3, tag4, tag5, songtitle, zip_link, album_id, description, album_name, album_desc,  small_logo  FROM albums, tracks WHERE 

        (tag1 = '{$search_tag}' OR
        tag2 = '{$search_tag}' OR
        tag3 = '{$search_tag}' OR
        tag4 = '{$search_tag}' OR
        tag5 = '{$search_tag}')    AND   tracks.album_id = albums.id ) ORDER BY tag1
        ";  

}  else {
    echo '<br/>please enter a search word or phrase<br/><br/>';
    die;
}


        $result = mysqli_query ($mysqli, $sql);
        if($result === false)  { echo 'mysql error'; }
        $count=mysqli_num_rows($result);
        if ($count < 1) {echo '<span class="sorry">sorry - your search returned no results</div><br/>';}    
            else    {   while ($row = mysqli_fetch_array($result)) 
                `       {

Upvotes: 0

Views: 125

Answers (3)

vee
vee

Reputation: 38645

It looks like there is an error in your query which is why $result is false and ultimately mysqli_num_rows($result) complaining.

Another problem is, although you are checking for $result you are simply letting the execution continue. The if($result === false) { echo 'mysql error'; } is going to print mysql errror and then continue. So in order to stop the execution you could add a die in there so that in the event of invalid result your program stops execution.

Another suggestion I have for you is to utilize the provided functions to see the error better. You should at least have this:

if($result === false) { 
    print mysqli_error($mysqli);
    die;
}

Doing so will lead you to identifying where the exact error is. And in this case your query.

There already seem to be plenty of answers showing you the fix for the query, so I am not going to just copy and paste them! Please see other answers to fix your query.

Upvotes: 1

Balaji Perumal
Balaji Perumal

Reputation: 850

Try

"SELECT * FROM (SELECT trackname, tag1, tag2, tag3, tag4, tag5, songtitle, zip_link, album_id, description, album_name, album_desc,  small_logo  FROM albums, tracks WHERE 

    (tag1 = '{$search_tag}' OR
    tag2 = '{$search_tag}' OR
    tag3 = '{$search_tag}' OR
    tag4 = '{$search_tag}' OR
    tag5 = '{$search_tag}')    AND   tracks.album_id = albums.id )temp ORDER BY tag1
    ";

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Why are you using a subquery? The following seems to be the query you want:

SELECT trackname, tag1, tag2, tag3, tag4, tag5, songtitle, zip_link, album_id,
       description, album_name, album_desc,  small_logo
FROM albums join
     tracks
     on tracks.album_id = albums.id
WHERE '{$search_tag}' in (tag1, tag2, tag3, tag4, tag5)
ORDER BY tag1;

Also note the use of proper join syntax and the use of in to simplify the where logic.

Upvotes: 1

Related Questions