Problems with ORDER BY after LEFT JOIN

Question

I have been working on hours with this one, but it's time to get a second pair of eyes to look at it....

I have this mySQL-query:

 SELECT
     vaktliste.id AS id,
     brukere.navn AS bruker,
     steder.navn AS sted,
     vaktliste.start AS vl_start,
     vaktliste.slutt AS vl_slutt,
     timeliste.start AS tl_start  
 FROM brukere, steder, vaktliste  
 LEFT JOIN timeliste
 ON timeliste.sted=vaktliste.sted
 AND timeliste.bruker=vaktliste.bruker
 AND DATE(timeliste.start)=DATE(vaktliste.start)  
 WHERE brukere.id=vaktliste.bruker
 AND steder.id=vaktliste.sted
 AND timeliste.start IS NULL
 AND vaktliste.firma=15
 AND vaktliste.bruker = 381
 AND MONTH(vaktliste.start) = 6
 AND YEAR(vaktliste.start) = 2016  
 ORDER BY vaktliste.start ASC, brukere.navn

When "run through" a browser, it gives no result, but in phpMyAdmin, it get a result.

When I remove the ORDER BY, I get a result in the browser aswell...

Can anyone tell me why the ORDER BY line is messing up the query?

This is some code from the PHP-file producing the query:

echo("<table id=\"ansatt_tabell\" class=\"ui unstackable celled table\">
                <thead>
                    <tr>
                        <th>Ansatt</th>
                        <th>Kunde</th>
                        <th>Dato</th>
                        <th>Start</th>
                        <th>Slutt</th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>");



        //Til sammenlangt
        $timer_totalt = 0;
        $kilometer_totalt = 0;

        //Kobler til mySQL-databasen
        include("../koble_til_db.php");


        //Kjører spørring
        $query = "SELECT vaktliste.id AS id, brukere.navn AS bruker, steder.navn AS sted, vaktliste.start AS vl_start, vaktliste.slutt AS vl_slutt, timeliste.start AS tl_start FROM brukere, steder, vaktliste LEFT JOIN timeliste ON timeliste.sted=vaktliste.sted AND timeliste.bruker=vaktliste.bruker AND DATE(timeliste.start)=DATE(vaktliste.start) WHERE brukere.id=vaktliste.bruker AND steder.id=vaktliste.sted AND timeliste.start IS NULL AND vaktliste.firma=$firma";

        //Sorterer i henhold til bokser over
        if($timeliste_ansatt != 0){
            $query = $query . " AND vaktliste.bruker = $timeliste_ansatt";
        }

        if($timeliste_sted != 0){
            $query = $query . " AND vaktliste.sted = $timeliste_sted";
        }

        if($timeliste_maaned != 0){
            $query = $query . " AND MONTH(vaktliste.start) = $timeliste_maaned";
        }

        if($timeliste_aar != 0){
            $query = $query . " AND YEAR(vaktliste.start) = $timeliste_aar";
        }

        $query = $query . " ORDER BY vaktliste.start ASC, brukere.navn ASC";


        $result = mysqli_query($connect, $query);

        while($newArray = mysqli_fetch_array($result)){

            $id = $newArray['id'];
            $bruker = $newArray['bruker'];
            $sted = $newArray['sted'];
            $start = $newArray['vl_start'];
            $start_dato = date('d.m.y', strtotime($start));
            $start_tid = date('H:i', strtotime($start));
            $slutt = $newArray['vl_slutt'];
            $slutt_tid = date('H:i', strtotime($slutt));


            //Innhold tabell
            echo("<tr>
                    <td>$bruker</td>
                    <td>$sted</td>
                    <td class=\"center aligned\">$start_dato</td>
                    <td class=\"center aligned\">$start_tid</td>
                    <td class=\"center aligned\">$slutt_tid</td>
                    <td class=\"collapsing center aligned\"><div class=\"mini ui green basic button\" onClick=\"adm_timeliste_overfor(this, $id)\">Registrer timer</div></td>
                </tr>");


        }//while

        //Avslutter tabell
        echo("</tbody>

The error message received was:

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

Write 1) ORDER BY vaktliste.start ASC, brukere.navn ASC **OR** 2) ORDER BY vaktliste.start , brukere.navn — Ravi Hirani, Jun 07 '16 at 08:42
Same result there aswell sagi. Works in phpMyAdmin, but not in the browser... — Alexander N, Jun 07 '16 at 08:46
The file has 1500 lines of code, so I guess that doesn't give the desired result :) I can paste some more though. — Alexander N, Jun 07 '16 at 08:55
@RonDadon: I create the query in a PHP-file, and run that file in Chrome with no result (Code above). If I do an echo $query; after the table in the same file and paste that text into phpMyAdmin, I get a result. If I remove the line with ORDER BY, I get a result in Chrome aswell.... — Alexander N, Jun 07 '16 at 09:01
@AlexanderN you do not have any error handling around the mysqli_query() call. Check the returned $result and print out and share any MySQL error message that pops up. — Shadow, Jun 07 '16 at 09:01
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay — Alexander N, Jun 07 '16 at 09:21
That is the problem - you JOIN is too big. phpMyAdmin must likely uses SET SQL_BIG_SELECT=1 to overcome this. Try to run SET SQL_BIG_SELECT=1 as a query first, and than your query. — Ron Dadon, Jun 07 '16 at 09:23
@RonDadon this is a huge oversimplification of the issue. See the duplicate topic. Proper indexing, use of explicit joins throughout the entire query will probably bring the query within the realms of the setting. — Shadow, Jun 07 '16 at 09:31
@Shadow How would you use proper indexing or explicit joins in this example? Thanks to everyone for helping me getting this solved! — Alexander N, Jun 07 '16 at 12:41
Explicit joins easy: just explicitly use inner join expression on the tables that are listed in the from clause separated by comma and move the join conditions from the where clause to specific on clause(s). Indexing, however, should be a separate question with existing indexes and the result of the explain provided. — Shadow, Jun 07 '16 at 13:43

Problems with ORDER BY after LEFT JOIN

0 Answers0