Is it possible to post two separate values when an item is selected with the Select option?

Beginners - Learning PHP
#1

Hello,

I am listing products from more than one table with the Select option
Product IDs in each table are not unique
I am posting the ID value of the selected product
How do I know which table the product is selected for?

<select name="urunler[]">
    <option>&nbsp;</option>
        <optgroup label="Çanak Antenler">
            <option value="3">OFSET ÇANAK ANTEN TAKIMI 97 CM</option>
            <option value="4">OFSET ÇANAK ANTEN TAKIMI 97 CM</option>
            <option value="5">OFSET ÇANAK ANTEN TAKIMI 120 CM</option>
            <option value="6">OFSET ÇANAK ANTEN TAKIMI 120 CM</option>
            <option value="7">MEGA SAÇ ÇANAK ANTEN TAKIMI 135 CM</option>
            <option value="8">MEGA SAÇ ÇANAK ANTEN TAKIMI 135 CM</option>
        </optgroup>
        <optgroup label="LNBler">
            <option value="1">ONE TOUCH LNB MDU5</option>
            <option value="2">QUATRO LNB </option>
            <option value="3">QUATRO LNB </option>
        </optgroup>
        <optgroup label="Kablolar">
            <option value="2">ANTEN KABLOSU RG 6 U/4</option>
            <option value="4">ANTEN KABLOSU RG 6 U/6</option>
        </optgroup>
        <optgroup label="F Konnektörler">
            <option value="1">F KONNEKTÖR SIKIŞTIRMALI RG 6 U/4</option>
            <option value="2">F KONNEKTÖR SIKIŞTIRMALI RG 6 U/6</option>
            <option value="5">F KONNEKTÖR SIKIŞTIRMALI RG 6 U/4</option>
            <option value="6">F KONNEKTÖR SIKIŞTIRMALI RG 6 U/6</option>
            <option value="7">F KONNEKTÖR SIKIŞTIRMALI RG 11</option>
        </optgroup>
</select>
#2

This is your real problem. All the products should be in ONE table. Learn and implement “Database Normalization”.

1 Like
#3

I’ve looked at what “Database Normalization” is but it’s for advanced I think it’s too early for me

For now I would like to prefer a simpler way

I will try to solve the problem for now by adding the table name with a dash next to the ID Thank you

value="canakanten-5" as

#4

Early is when you want the database design to be correct. It is the foundation of all the code you will write using it. If the DB is incorrect, so will all the code you write to work with it.

Normalizing the DB IS the simple way. If you don’t, all your code will be a hack and you will forever incur technical debt.

#5

Although I fully agree with Benanamen about the importance of normalisation, the fact that the data isn’t normalized doesn’t restrict the solutionspace for this problem to just that. The proposed solution by Adem including the table name in the POST could help although it’s not a generic solution. For example: what if the table name includes a dash as well? This solution would fail simply because you can’t distinguish between table name and product ID anymore. Of course it’s possible to use a different, more exotic seperator than the dash sign, but in the end you’ll end up with code that will fail due to lack of uniqueness of the product data.

#6

Depending on the validation/mapping of the value to the actual table, this is likely insecure and would allow sql injection, if you are unconditionally using the externally supplied value in the sql query statement.

By not normalizing the data, everything you do concerning this data requires more code and query(ies.)

1 Like
#7

Thank you all,

I read what “Database Normalization” is in the local language, they gave example tables, but I couldn’t understand much, so I couldn’t understand the logic.
Later, I thought that “Database Normalization” was like the brand name and currency and product features I used with LEFT JOIN, and I said I know this a little bit.
In other words, all products will be in a table, I think it is to link it like a product category and call it with ID and LEFT JOIN.
If I understood the logic correctly, I will try to switch my codes gradually accordingly.

Also, this page will be in the admin panel and not public.

#8

You are on the right track. In it’s simplest form, Normalization is about reducing data redundancy and improving data integrity.

1 Like
#9

If that’s directed at the insecurity of accepting a table name from external data, if your code is open to cross site scripting (XSS), the external data can be anything and cannot be trusted. In this case, it just means that any unsafe value is used at an administrative permission level.

1 Like
#10

I understood the security issue and I will fix it as soon as possible. I’m trying it on localhost for now.
Thank you very much for this warning.

#11

I’m trying to do “Database Normalization”
I imported all products into the “products” table
I created a separate table “product_categories” for the products and entered the category names here

Now I want to write the category name I want to do here <optgroup label="Category Name">
Category name will be retrieved with this query : product_categories.category_name

I want to create the select option list like in the first post

$allproducts = $db->prepare("SELECT id, CONCAT(brands.product_brand,' ',product_name)

  FROM products

  INNER JOIN brands ON products.product_brand = brands.id

  INNER JOIN product_categories ON products.category_name = product_categories.id

  WHERE products.product_stock=? AND brands.brand_active=?");

  $allproducts->execute([1,1]);

  $all_products  = $allproducts->fetchAll(PDO::FETCH_KEY_PAIR);

 

$select_optin "<optgroup label=\"{ /*Each category name will be written once*/ }\">";

  foreach($all_products AS $id=>$value){

    $select_optin "<option value=\"{$id}\">{$value}</option>";

  }

  $select_optin "</optgroup>";
#12

If I understand what you want correctly, you will want to use PDO::FETCH_GROUP instead of PDO::FETCH_KEY_PAIR

groupby

https://www.phptutorial.net/php-pdo/pdo-fetch_group/

#13

The product(s) table should have a category_id column (the other tables which have a column that holds a foreign id, should also be named xxxxx_id so that you can tell what the query is doing without confusion.) The above JOIN condition would be - ON products.category_id = product_categories.id You would include product_categories.category_name in the SELECT list…, which it is currently missing.

I recommend that you use table alias names in your queries, e.g. b for brand, c for category, p for product, to simplify all the typing needed for a query and to always include them with each column reference, even in the cases where they are not required, so that your query becomes self-documenting.

I also wish people would quit, inconstantly, repeating parts of table names in the names of columns. You also have a product_brand column in the product table that is actually the brand name, and should be named ‘name’ and a product_brand column in the product table that is actually the brand id, and should be named ‘brand_id’.

Just about every select query should have an ORDER BY … term so that the rows in the result set will be in a specific, known order.

Lastly, to get the fetched data to be indexed/grouped by the category name when using PDO::FETCH_GROUP, the category name must be the first column in the SELECT … list.

Doing all these clean-up practices should result in -

// build sql queries in a php variable, to make debugging easier and help prevent typo mistakes in the syntax
$sql = "SELECT c.name, p.id, CONCAT(b.name,' ',p.name) b_p_name
  FROM products p
  INNER JOIN brands b ON p.brand_id = b.id
  INNER JOIN product_categories c ON p.category_id = c.id
  WHERE p.product_stock=? AND b.brand_active=?
  ORDER BY c.name, b.name, p.name";

// only the final variable name holding the data needs to be specific
// this example names the variable holding the database connection as to the type of connection it is
$stmt = $pdo->prepare($sql);
$stmt->execute([1,1]);
// index/pivot the data by the first column selected - category name
$all_products = $stmt->fetchAll(PDO::FETCH_GROUP);

// build the output
$select_optin = '';
foreach($all_products as $category_name=>$arr)
{
	// use single-quotes inside php double-quoted strings instead escaped double-quotes
	$select_optin .= "<optgroup label='$category_name'>\n";
	foreach($arr as $row)
	{
		$select_optin .= "<option value='{$row['id']}'>{$row['b_p_name']}</option>\n";
	}
	$select_optin .= "</optgroup>\n";
}
1 Like
#14

I understood your suggestions very well.
I learned some things I didn’t know
And it worked perfectly
Thank you very much

I have one last question
I want to sort by category name array list is it possible to sort?

$category_array = [
"category_name_12",
"category_name_7",
"category_name_1",
"category_name_23",
"category_name_3",
.......
];

as
I want to create an array myself like this and sort it by array

#15

https://www.php.net/manual/en/function.sort.php

#16

I guess I did as follows, but did you mean like this? Is this the correct method?

foreach($category_array AS $cate_arr)
{
foreach($all_products as $category_name=>$arr)
{
	// use single-quotes inside php double-quoted strings instead escaped double-quotes
if($cate_arr == $category_name)
{
	$select_optin .= "<optgroup label='$category_name'>\n";
	foreach($arr as $row)
	{
		$select_optin .= "<option value='{$row['id']}'>{$row['b_p_name']}</option>\n";
	}
	$select_optin .= "</optgroup>\n";
}
}
}
#17

Something like this came to mind,

Like move columns in phpMyAdmin, is it possible to sort the desired order from the category table by moving the category name in the category table?

Hereby, it will be possible for the administrator to sort as he wishes from the administration panel.

#18

I ran into a problem that I hadn’t thought of.
40 different products available
Each product has different features and types
Such as Type, Feature, Size, Number of Channels, Input, Output, MHz, RF, IF, Digital, Analog, dB, MDU
I keep the properties of each product in a separate table and call it with LEFT JOIN
While transferring all products to the “Products” table, I also transferred the IDs of the product attributes.
In the attribute columns in the “Products” table, there are blanks for some products, and IDs of their own attribute table for some products.

Sample:
When I use LEFT JOIN to call properties for a product it adds those properties to all products.

I keep the product features in a separate table so that they are standard so that they do not differ in the whole area.

Is it necessary to write the properties of each product in the “Products” table by necessity?

#19

Now I got it, I need to move the product types to single table as well

#20

@phdr I’m trying to do all of your suggestions above, column names like xx_id, xx_name as you suggested and I use alias and rearrange

All products in one table.
All product types in one table.

I’m having a problem here,
The type and feature of each product is different. Some of the types columns in the Products table are empty.

How should I encode “if not null” in the query?

$sql = "SELECT c.name, p.id, 
CONCAT(
p.input_id,'X',p.output_id,' ', // output: 10X32, Ignore if these columns are null
b.name,' ',
p.name
) b_p_name
Sponsor our Newsletter | Privacy Policy | Terms of Service