coding problem on selecting data from database

Forum|Forum|3 years ago
January 9, 2023
2 replies
459 views

Background: I would like to choose the product where product.type = menu.type
So below is the category of '"clothing", i want to choose "top" only when i click the "top" from the menu.

But it seems coding error, so the page cannot run normal

please assist.

This topic has been closed for replies.

O

osgood_

Legend

You need to JOIN the tables in the sqli query AND you should NEVER include the php variable within the sqli query string if it comes from a url $_GET parameter as its not safe and open to someone re-writing the url parameter which can result in the deletion of your database tables.

See example below using YOUR database tables/row names etc. You need to insert YOUR OWN USERNAME, PASSWORD & DATABASE-NAME in the connection string. Also I've just hard-coded the $menu_type = $_GET['menu_type'] variable - that will obviously come from a url parameter so switch it back to $_GET['menu_type'] once you have tested the query.

The code below uses a 'prepared sql statement' which stops sqli injection attacks, making querying your database safe.

<?php
// connect to database local
$conn = new mysqli('localhost' , 'USERNAME' , 'PASSWORD' , 'DATABASENAME');
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
<?php
//HARD CODED AS AN EXAMPLE
$menu_type = "A02";
// QUERY USING PREPARED STATEMENT
$sql = "SELECT Product.id, Product.menu_type, Product.name, Product.price, Product.pic, ClothingType.menu_type
FROM Product
INNER JOIN ClothingType ON Product.menu_type=ClothingType.menu_type AND Product.menu_type = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $menu_type);
$stmt->execute();
$result = $stmt->get_result();
?>


<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Output Results</title>
</head>
<body>

<?php while ($row = $result->fetch_assoc()) { ?>
<div class="item">
<a href="product.php?id=<?php echo $row['id']; ?>"><img src="<?php echo $row['pic']; ?>" alt="<?php echo $row['name']; ?>"></a>
<h3><?php echo $row['name']; ?></h3>
<p><?php echo $row['price']; ?></p>

</div>
<?php } ?>

</body>
</html>

H

Hiu Ching26169588pka2Author

Known Participant

Hi , thanks for your suggestion.
i have tried the coding u suggested, but it doesnt work....

O

osgood_

Legend

You can't have $menu_type hard coded in the sqli query string it must be a ? - as in the example code I posted.

B

Ben M

Community Expert

What error are you getting?

What I am failing to see is a WHERE clause in your SQL statement. Without that you cannot you cannot have filtered results. https://dev.mysql.com/doc/refman/8.0/en/where-optimization.html