• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

coding problem on selecting data from database

Explorer ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

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.

Screen Shot 2023-01-09 at 20.36.08.png
Screen Shot 2023-01-09 at 20.24.29.pngScreen Shot 2023-01-09 at 20.23.33.pngScreen Shot 2023-01-09 at 20.24.55.png

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

please assist.

 

Views

227

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

Screen Shot 2023-01-09 at 21.22.50.png

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

it just show nothing in the content

Screen Shot 2023-01-09 at 21.23.40.png

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

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
Alt-Web Design & Publishing ~ Web : Print : Graphics : Media

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

quote

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 10, 2023 Jan 10, 2023

Copy link to clipboard

Copied

yes

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 09, 2023 Jan 09, 2023

Copy link to clipboard

Copied

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>

 

 

 

 

 

 

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 10, 2023 Jan 10, 2023

Copy link to clipboard

Copied

Screen Shot 2023-01-10 at 19.36.55.png

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

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 10, 2023 Jan 10, 2023

Copy link to clipboard

Copied

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

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 10, 2023 Jan 10, 2023

Copy link to clipboard

Copied

LATEST

Screen Shot 2023-01-10 at 20.28.42.png

 changed as you suggestd but the pages doesnt work

Screen Shot 2023-01-10 at 20.30.19.png

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines