0

So long story short: I have table A which might expand in columns in the future. I'd like to write a php pdo prepared select statement with a WHERE clause which applies the where condition to ALL columns on the table. To prevent having to update the query manually if columns are added to the table later on, I'd like to just tell the query to check ALL columns on the table.

Like so:

  $fetch = $connection->prepare("SELECT product_name
                                 FROM products_tbl
                                 WHERE _ANYCOLUMN_ = ?
                                ");

Is this possible with mysql?

EDIT: To clarify what I mean by "having to expand the table" in the future:

MariaDB [foundationtests]> SHOW COLUMNS FROM products_tbl;
+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| product_id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| product_name         | varchar(100) | NO   | UNI | NULL    |                |
| product_manufacturer | varchar(100) | NO   | MUL | diverse |                |
| product_category     | varchar(100) | NO   | MUL | diverse |                |
+----------------------+--------------+------+-----+---------+----------------+
4 rows in set (0.011 sec)

Here you can see the current table. Basically, products are listed here by their name, and they are accompanied by their manufacturers (say, Bosch) and category (say, drill hammer). Now I want to add another "attribute" to the products, like their price. In such a case, I'd have to add another column, and then I'd have to specify this new column inside my MySQL queries.

Narktor
  • 977
  • 14
  • 34
  • 2
    _.. I have table A which might expand in columns in the future..._ This might be a sign of a poor db design – B001ᛦ Sep 04 '19 at 14:21
  • Possible duplicate https://stackoverflow.com/q/18415820/5581565 – Salim Djerbouh Sep 04 '19 at 14:23
  • Erm, why? How can I circumvent this? I mean, lets take cars. If some new motor is released and I want these cars added to my database as distinct NEW MOTOR cars, how could I prevent not expanding some table??? – Narktor Sep 04 '19 at 14:23
  • @Caddy DZ I saw this Thread before but here the accepted answer does specify columns, doesnt it? – Narktor Sep 04 '19 at 14:24
  • _.. lets take cars. If some new motor is released..._ Not sure what you really mean before seeing the table structure . In general it doesn't matter what the object is that we store in the table - If I understand you correctly so "motors" would belong to the motors-table – B001ᛦ Sep 04 '19 at 14:27
  • 4
    @baryon123, a good db always expects to grow with rows not with columns. If a new motor is anywhere added, A new row is expected to be added in DB instead of new column. – Ankit Bajpai Sep 04 '19 at 14:34
  • 1
    You might read over [database-normalization](https://www.studytonight.com/dbms/database-normalization.php) – B001ᛦ Sep 04 '19 at 14:36
  • @B001ᛦ See my Edit. – Narktor Sep 04 '19 at 14:36
  • _See my Edit...._ Already saw it, seems you don't care about normalization? see my comment above :) – B001ᛦ Sep 04 '19 at 14:37
  • @B001ᛦ Yeah maybe this would help...still, I did put some thought into the DB as it is now. For example, product_manufacturer and product_category reference tables containing the to be inserted values (like Siemens, Bosch, adidas or swimsuit, dishwascher, drill hammer) by foreign key. This way, I want to maintain consistency across the DB. I could've made products_tbl with a "generic" column "product_attributes" but then I'd have the problem that I can't reference multiple tables from this single column via foreign key constraint Oo – Narktor Sep 04 '19 at 14:39
  • 1
    normalise, normalise , normalise, 3rd normal form will get you around most bad DB design problems. – jimmy8ball Sep 04 '19 at 14:40
  • But still: Is there the possibility to check ALL columns without specifying them? – Narktor Sep 04 '19 at 14:47
  • 1
    Short answer, no there isn't any syntax shortcut for that. It's generally not an expected scenario – ADyson Sep 04 '19 at 14:53
  • "Now I want to add another "attribute" to the products, like their price. In such a case, I'd have to add another column" if you were designing your tables correctly you would store product price as a seperate dimension, in somthing like a slowly changing dimension table (SCD), which could track price changes over time – jimmy8ball Sep 04 '19 at 14:55

0 Answers0