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

Question

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)) 
                `       {

possible duplicate of [PHP & MySQL: mysqli\_num\_rows() expects parameter 1 to be mysqli\_result, boolean given](http://stackoverflow.com/questions/2546314/php-mysql-mysqli-num-rows-expects-parameter-1-to-be-mysqli-result-boolean) — user555, Dec 14 '13 at 14:57
If you have a new question, please use the [Ask Question](http://stackoverflow.com/questions/ask) link. Don't edit your question to include a different question. — Duncan Jones, Dec 19 '13 at 16:05

score 1 · Answer 1 · answered Dec 14 '13 at 14:58

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.

score 1 · Answer 2 · answered Dec 14 '13 at 15:02

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.

score 0 · Answer 3 · answered Dec 14 '13 at 15:00

0

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

answered Dec 14 '13 at 15:00

Balaji Perumal

830
1
6
20

Explaining *why* this code solves the problem makes a better Answer. Check the guide [answer] – brasofilo Dec 19 '13 at 16:54

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

3 Answers3