Sam Apostel Sam Apostel - 7 months ago 17
SQL Question

php function to execute sql doesn't return anything

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!

Answer

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