php function to execute sql doesn't return anything

Question

I am making a webshop and need to retrieve the price of an item I've been reading and searching for a few weeks now without any luck, anybody here with an answer?

-> little explanation:

Every item has a few sizes it can come in and every size has a price for that item.
The price I need to retrieve is the lowest price available for that item.

the problem:

the function getPrice() does not return anything as far as I can check.

It also does not seem to produce any error.

my code:

<?php 
$servername =   "localhost";
$username =     "beegeeky";
$password =     "pwd";
$dbname =       "beegeeky";
$conn = new mysqli($servername, $username, $password, $dbname);


function getPrice($id){
$sql = "SELECT MIN(price) FROM item_price WHERE size_id IN (SELECT id FROM item_size WHERE item_id =".$id.");";
$lowestprice = $conn->query($sql);
return $lowestprice;
}



$sql = "SELECT * FROM item ORDER BY id DESC;";
$qresult = $conn->query($sql);
if ($qresult->num_rows > 0) {
while($item = $qresult->fetch_assoc()) {
    ?>
    <div class="item" id="item_<?php echo $item["id"]; ?>" onclick="itemClick('item_<?php echo $item["id"]; ?>')">

        <div class="likeHitBox" onclick="like('like_<?php echo $item["id"]; ?>')"></div>
            <div class="like<?php if ($conn->query("SELECT * FROM item_like WHERE item_id = ". $item["id"] . " AND user_id = ". $userID .";")->num_rows > 0){?> liked<?php } ?>" id="like_<?php echo $item["id"]; ?>"></div>
        <div class="iconContainer"></div>

        <div class="itemImg">
            <img src="../img/<?php echo "BoneConductingHeadphones/BCH.png"; ?>">
        </div>

        <div class="price">
            <?php echo getPrice($item["id"]); ?>
        </div>
        <div class="priceContainer"></div>

    </div>
    <?php
}
}
$conn->close();
?>

if I need to add anything, just ask :)

**EDIT: ** thanks already, I made some changes to the function, what I have now is:

function getPrice($id){
    global $conn;
    $sql = "SELECT MIN(price) FROM item_price WHERE size_id IN (SELECT id FROM item_size WHERE item_id =".$id.");";
    $lowestprice = $conn->query($sql)->fetch_assoc();
    return $lowestprice[0];
}

this returns an array (yay something is returned now), but the array seems to be empty, Thats work for another SO-question

Thank you!

You are not `fetch`ing the result. You also should be using parameterized queries. This is open to SQL injections. — chris85, May 03 '16 at 20:25

score 0 · Accepted Answer · edited May 23 '17 at 12:31

0

The $conn variable doesn't exist inside the getPrice function.

http://php.net/manual/en/language.variables.scope.php

Also, look into sql injection.

PHP MySQLI Prevent SQL Injection

edited May 23 '17 at 12:31

Community

1
1

answered May 03 '16 at 20:26

MajorCaiger

1,893
1
12
18

then how do I fix that? – Sam Apostel May 03 '16 at 20:28
@SamApostel pass it in, or use `global`. You also need to `fetch`. – chris85 May 03 '16 at 20:29
am I right when I state that sql injection cannot be done in this case when no user input is used in the query? – Sam Apostel May 03 '16 at 20:30
@SamApostel No, see second level SQL injections. Malicious data could already be in the DB. https://en.wikipedia.org/wiki/SQL_injection#Second_order_SQL_injection – chris85 May 03 '16 at 20:36
@chris85 aah tnx, didn't think of that :) I'll fix it right away. It's just something I have to make for school so dont worry, it's never going to be online with a real webshop it indeed is good practice though, my problem is solved so THANK YOU! – Sam Apostel May 03 '16 at 20:49

score 0 · Answer 2 · answered May 03 '16 at 20:32

0

There's a few lines missing between

$lowestprice = $conn->query($sql);

and

return $lowestprice;

You need to tech result from the recordset, like you did with $qresult.

answered May 03 '16 at 20:32

Webomatik

844
7
7

score 0 · Answer 3 · edited May 15 '16 at 10:18

0

Try this:

function getPrice($conn, $id)
{
    $sql = "SELECT MIN(price) FROM item_price WHERE size_id IN ( " .
           "     SELECT id FROM item_size WHERE item_id =" . $id .
           ")";
    $lowestprice = $conn->query($sql);

    return $lowestprice;
}

You have to include the MySQLi connection as an input into the function for it to work.

edited May 15 '16 at 10:18

luchaninov

6,792
6
60
75

answered May 03 '16 at 20:44

William Casey

312
1
8

php function to execute sql doesn't return anything

3 Answers3