coding problem on selecting data from database

Report · Jan 09, 2023

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.

Report · Jan 09, 2023

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

Report · Jan 09, 2023

Report · Jan 09, 2023

it just show nothing in the content

Report · Jan 09, 2023

Are you creating a product details page? This is the basic logic:

1. Connect to your server's database.

<?php

$con = mysqli_connect("localhost","your_username","your_password","database_name");

2. Define a variable and query your database table.

$Recordset1->setQuery ("SELECT * from table_name WHERE Prod_ID = ?")

(isset($_GET['Prod_ID'])?$_GET['Prod_ID']:"") ."", "-1"); //colname
$Recordset1->execute();?>

3. Output details from table:

<p><?php echo($Recordset1->getColumnVal("Prod_Name")); ?></p>

<img src="<?php echo($Recordset1->getColumnVal("Prod_Image")); ?>"
<p><?php echo($Recordset1->getColumnVal("Prod_Details")); ?></p>

Hope that helps.

Nancy O'Shea— Product User, Community Expert & Moderator

Report · Jan 09, 2023

Are you creating a product details page? This is the basic logic:

By @Nancy OShea

I think the OPs issue here is getting data from 2 tables where a column in table 1 matches a column in table 2 plus matches a url parameter.

Report · Jan 10, 2023

yes

Report · Jan 09, 2023

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>

Report · Jan 10, 2023

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

Report · Jan 10, 2023

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

Report · Jan 10, 2023

changed as you suggestd but the pages doesnt work