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

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.

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

1 Like

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

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.

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>";

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/

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

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

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

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";
}
}
}

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.

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?

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

@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

I solved the problem as below

IFNULL(CONCAT(p.input_id,'X',p.output_id,' '),'')

now i have another problem
I collect properties of all products in “product_types” table


some columns are null by product, A section is visible in the example image

$sql = "SELECT c.name, p.id, 
CONCAT(
IFNULL(CONCAT(p.input_id,'X',p.output_id,' '),''),
IFNULL(CONCAT(p.product_type_id,' '),''),
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";

i think this is what i have to do

INNER JOIN product_types pt ON p.input_id = pt.id
INNER JOIN product_types pt ON p.output_id = pt.id
INNER JOIN product_types pt ON p.product_type_id = pt.id

I guess I need to do something like this, but it doesn’t work. How should I do INNER JOIN with more than one column of a table?

Databases are not speadsheets. You should only store data that exists. That table should have one row for each separate piece of product/property data.

1 Like

I solved this problem by using LEFT JOIN instead of INNER JOIN but another problem appeared

Example output_type, multiple products are using this column name.
Output type name appeared in unrelated products because I used output_type column name in CONCAT()

Even if I collect the Product Types in a table, each product’s type, feature, etc. columns will be separate.
You’re saying that no column will be common, right?

For each product. Do I create a separate table for its type, property, etc. or do I create them all in one table? What is your suggestion?

You would have one product_type(s) table. It would have columns for -

  1. an id (which you apparently have)
  2. product_id (which you apparently have)
  3. type (which would be whatever the existing column names are)
  4. value (which would be whatever the existing stored values are)

For each product separately, I have a table of product type, property etc.
Ekran görüntüsü 2022-10-17 094958

Ekran görüntüsü 2022-10-17 095021

Ekran görüntüsü 2022-10-17 095041

Ekran görüntüsü 2022-10-17 095119

Since I have gathered all the products in a single table, Is it correct to tap into a single table for product type, properties, etc.?

It is necessary to create a separate column for each product type.
ID | a_product_type | b_product_type | c_product_type | c_product_type | ....
This means that there will be close to 100 columns in a table in this format.

I used a “product_type” column in the “product_types” table for all products. The table below is shown in the picture


The problems shown in the picture below appear
Product type that does not belong to the product appears
Ekran görüntüsü 2022-10-17 101010

The problem in the picture below is this.
input_name | output_name | there are columns
I used below query to show like 17X8, 17X12, 17X16
IFNULL(CONCAT(t.input_name,'X',t.output_name,' '),'')
I also used the following query for just the output of another product
IFNULL(CONCAT(,t.output_name,' '),''),
As seen in the picture below, these products should not have underlined data in the problem, but they are visible.
This is because all products use the same column.
Is there a solution to this?
Or should I make separate columns for each product type? This means that there will be close to 100 columns in a table, is this the right way?
Ekran görüntüsü 2022-10-17 101208

Sponsor our Newsletter | Privacy Policy | Terms of Service