Url() based on product selection

Question

So im new to php, Smarty and also mysql. I would just like to know the best way to approach the following:

(ill show you my code but since i just want experienced guidance im keeping it short)

What i need to achieve: I have a product listing page which shows my 3 products in a list. This list is populated in my .tpl file. And my database connection and query reads from the .php file.

Now when ever i click on a product i want to generate an new(product-details) url and display only that product with only this products attributes.

I would like to know how to approach this , i think im going to create only one new .tpl file for the product-details page. And when you click on a link(product) on the product-listing page it redirects you to only that products product-detail page.

HOW (guidance please)

So what way will be the best to generate an url for each product? (to display only the selected product and its details in a new .tpl).

I would like to generate maybe an url from the product attributes which can be retrieved from database table? is this possible and maybe is there a link to read up about this?

Would i have to create an column in my database for the url() i generate?

Any help on this would be great!

My CODE : i have not set up an .tpl for product-details page

.php

<?php

$new = ['product_id','product_category','product_price','product_quantity','product_about','product_color'];

//Database connection
 $db = mysqli_connect('xxx','xxx','xxx','xxx')
 or die('Error connecting to MySQL server.');

//access Smarty template engine
require_once('Smarty-3.1.30/libs/Smarty.class.php');

$smarty = new Smarty();
$smarty->template_dir = 'views';
$smarty->compile_dir = 'tmp';


//query product page
$query = "SELECT * FROM cs_shop";
mysqli_query($db, $query) or die('Error querying database.');

$result = mysqli_query($db, $query);


//query an array of products
$rows = array();

 //loop start
 while ($row = mysqli_fetch_array($result)) {
    $rows[] = array(
        'product_id' => $row['product_id'],
        'product_category' => $row['product_category'],
        'product_price' => $row['product_price'],
        'product_quantity' => $row['product_quantity'],
        'product_about' => $row['product_about'],
        'product_color' => $row['product_color']
    );
}

//db collect data
$smarty->assign('row', $rows); 
//template
$smarty->display('index.tpl');

mysqli_close($db);

?>

.tpl (this is loop for displaying product-listing)

<div class="test divider">
    {section name=prod loop=$row}
    <div class="colm3">
        <div class="col3 r1">
            <div class="products-container">
                <h5>{$row[prod].product_name}</h5>
                <a class="producy_img_link" href="#" >
                    <img src="{$row[prod].prod_img}" style="width:auto; height:255px;">
                </a>
            </div>
        </div>

        <a href="#">
            <div class="block-right">
                <h6>{$row[prod].product_vintage}</h6>
                <h5>{$row[prod].product_veriatel}</h5>
                <div>
                    <span class="price">R {$row[prod].product_price}</span>
                </div>
            </div>
        </a>
    </div>
    {/section}
</div>

Answer 1

Create a page that will display the product details product.php and product.tpl and use the product id in href to link to a different product based on the product_id.

<div class="test divider">
    {section name=prod loop=$row}
    <div class="colm3">
        <div class="col3 r1">
            <div class="products-container">
                <h5>{$row[prod].product_name}</h5>
                <!-- create a link for each product based on the product id -->
                <a class="producy_img_link" href="product.php?prod_id={$row[prod].product_id}" >
                    <img src="{$row[prod].prod_img}" style="width:auto; height:255px;">
                </a>
            </div>
        </div>

        <a href="#">
            <div class="block-right">
                <h6>{$row[prod].product_vintage}</h6>
                <h5>{$row[prod].product_veriatel}</h5>
                <div>
                    <span class="price">R {$row[prod].product_price}</span>
                </div>
            </div>
        </a>
    </div>
    {/section}
</div>

Then in your PHP get the product id using $_GET

<?php

// Database connection
$db = mysqli_connect('xxx', 'xxx', 'xxx', 'xxx')
or die('Error connecting to MySQL server.');

$product_id = $_GET['prod_id']; // gets the product id

$sql = "SELECT * FROM cs_shop WHERE product_id = $product_id";
$result = mysqli_query($db, $sql);
//query an array of products
$rows = array();

//loop start
while ($row = mysqli_fetch_array($result)) {
    $rows[] = array(
        'product_id' => $row['product_id'],
        'product_category' => $row['product_category'],
        'product_price' => $row['product_price'],
        'product_quantity' => $row['product_quantity'],
        'product_about' => $row['product_about'],
        'product_color' => $row['product_color']
    );
}

//db collect data
$smarty->assign('row', $rows);
//template
$smarty->display('product.tpl');

mysqli_close($db);

?>