Sql query working as intended in sql workbench, but when added to a php file has extreme loading times

Question

Here is the SQL query, as mentioned in the title, this query works instantly in sql workbench, but when added to my php document, the site uses ages to load, and the column for max amount is not displaying, is this most likely caused by my php or is the query badly written? Thank you!

        SELECT max(bid.amount), item.img, item.expirydate, item.iditem, 
item.description, item.min_price, seller.name  
FROM item,seller,bid 
WHERE seller.idseller=item.idseller 
AND idcategory=1  
AND bid.iditem=1
AND
bid.iditem=bid.iditem
AND item.expirydate> curdate();

PHP section

    <?php while ($rad =mysqli_fetch_array($datasett)) { ?>
        <tr>
             <div class="artikkelbilde" id="tabell">
           <td> <img class="artikkelbilde" src="../bilder/<?php echo 
     $rad ["bilde"];?>"> </td>
            </td>
        </div>
            <td>
                <?php echo $rad["iditem"]; ?>
            </td>
            <td>
                <?php echo $rad["description"]; ?>
            </td>
            <td>
                <?php echo $rad["min_price"]; ?>
            </td>
            <td>
                <?php echo $rad["name"]; ?>
            </td>
            <td>
                <?php echo $rad["expirydate"]; ?>
            </td>

                <?php echo $rad["amount"]; ?>
            </td>
          <?php } ?>

If the sentence is the same think that the problem is in the PHP as @NigelRen suggests — Ignacio Ara, Apr 18 '18 at 14:03
I would say though that your SQL should have a GROUP BY clause. — Nigel Ren, Apr 18 '18 at 14:04
i added the php code, i am a complete beginner at php, so please go easy on me;) thank your for your response — Jesper Lybeck, Apr 18 '18 at 14:07
"ages" is around 5 minutes to load the site, and the amount column data is not getting fetched to the site. I have only added 4 rows of data to each table, so the issue is likely somewhere else. — Jesper Lybeck, Apr 18 '18 at 14:43
@chris85 I get one row on both, but in the interface, it is able to fetch the highest bid, but on the php site it displays the table cell as blank, but the other tables cells in the row are fine. — Jesper Lybeck, Apr 18 '18 at 15:07
@chris85 Thank you! the values is showing correctly in the table now. Allthough, it still loads extremely slow. If i try to remove the "while" i get an syntax error, i assume something should be in its place? Sorry for my extremely basic knowledge. — Jesper Lybeck, Apr 18 '18 at 15:31
@chris85 unfortunatly that causes the table to load inn blank, still severe loading time;( — Jesper Lybeck, Apr 18 '18 at 16:24
@chris85 i am unfamiliar with the site, how do i use it, where do i paste in my code and should i copy the entire document including the connection and html? — Jesper Lybeck, Apr 18 '18 at 16:47
@chris85 no worries its all good, turns out the issue was with my localhost not getting enough memory to execute the query quickly. Thank you so much, for all the great suggestions and helping me solve my issue. You sir are a champ! — Jesper Lybeck, Apr 18 '18 at 18:54

score 0 · Accepted Answer · answered Apr 18 '18 at 19:10

The reason your amount is empty is because you are accessing the wrong index. Each index is the name of the column (if the associative return is used, if numerical than it is the order the column appears), or the alias it is assigned to. So for

SELECT max(bid.amount), item.img, item.expirydate, item.iditem, 
item.description, item.min_price, seller.name

Your index is max(bid.amount). You can change this assignment by using an alias

SELECT max(bid.amount) as amount, item.img, item.expirydate, item.iditem, 
    item.description, item.min_price, seller.name

This will allow your index to stay as it was:

<?php echo $rad["amount"]; ?>

otherwise change the index. You can use var_dump or print_r to see all indices an array has, and their corresponding value. You also should use error reporting so notices about unassigned indices/variables are thrown. How do I get PHP errors to display?

Also not related to the PHP but you're missing an opening td for the amount.

Sql query working as intended in sql workbench, but when added to a php file has extreme loading times

1 Answers1