The proper way to do this is to use multiple tables and JOIN
them in your queries.
For example:
CREATE TABLE person ( `id` INT NOT NULL PRIMARY KEY, `name` VARCHAR(50) ); CREATE TABLE fruits ( `fruit_name` VARCHAR(20) NOT NULL PRIMARY KEY, `color` VARCHAR(20), `price` INT ); CREATE TABLE person_fruit ( `person_id` INT NOT NULL, `fruit_name` VARCHAR(20) NOT NULL, PRIMARY KEY(`person_id`, `fruit_name`) );
The person_fruit
table contains one row for each fruit a person is associated with and effectively links the person
and fruits
tables together, I.E.
1 | "banana" 1 | "apple" 1 | "orange" 2 | "straberry" 2 | "banana" 2 | "apple"
When you want to retrieve a person and all of their fruit you can do something like this:
SELECT p.*, f.* FROM person p INNER JOIN person_fruit pf ON pf.person_id = p.id INNER JOIN fruits f ON f.fruit_name = pf.fruit_name
Related Posts:
- MySQL Error: : ‘Access denied for user ‘root’@’localhost’
- Unknown column in ‘field list’ error on MySQL Update query
- Unknown column in ‘field list’ error on MySQL Update query
- MySQL – Operand should contain 1 column(s)
- MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050
- MySQL Error: : ‘Access denied for user ‘root’@’localhost’
- MySQL create table if not exists and insert record only if table was created
- How do I use MySQL through XAMPP?
- How do I use properly CASE..WHEN in MySQL
- How can I do a FULL OUTER JOIN in MySQL?
- MySQL query String contains
- What’s the difference between VARCHAR and CHAR?
- Case statement in MySQL
- How to declare a variable in MySQL?
- MySQL Cannot Add Foreign Key Constraint
- How to split the name string in mysql?
- How do I import an SQL file using the command line in MySQL?
- Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
- mysql Foreign key constraint is incorrectly formed error
- MySQL: Invalid use of group function
- How can I return pivot table output in MySQL?
- Compare dates in MySQL
- MySQL “WITH” clause
- MySQL Multiple Joins in one query?
- Error Code: 2013. Lost connection to MySQL server during query
- MySQL syntax for Join Update
- Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
- how to drop partition without dropping data in MySQL?
- MySQL combine two columns into one column
- Simple way to calculate median with MySQL
- Error 1046 No database Selected, how to resolve?
- MySQL Error 1264: out of range value for column
- PHP & MySQL: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given [duplicate]
- Remove duplicate rows in MySQL
- How to SUM and SUBTRACT using SQL?
- Create mysql table directly from CSV file using the CSV Storage engine?
- Cast from VARCHAR to INT – MySQL
- SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
- How do you force mysql LIKE to be case sensitive?
- MySQL – UPDATE multiple rows with different values in one query
- How to create a MySQL hierarchical recursive query?
- ERROR 1148: The used command is not allowed with this MySQL version
- Insert into a MySQL table or update if exists
- How to round down to nearest integer in MySQL?
- MySQL starts with searching issue
- Query to convert from datetime to date mysql
- Is it possible to GROUP BY multiple columns using MySQL?
- MySQL error: key specification without a key length
- Best way to test if a row exists in a MySQL table
- Check if MySQL table exists without using “select from” syntax?
- MySQL equivalent of DECODE function in Oracle
- mysql update column with value from another table
- The used SELECT statements have a different number of columns
- How to delete from multiple tables in MySQL?
- MySQL: Can’t create table (errno: 150)
- How to copy a row and insert in same table with a autoincrement field in MySQL?
- Join vs. sub-query
- Alternative to except in MySQL
- Equivalent of explode() to work with strings in MySQL
- How to store arrays in MySQL?
- ROW_NUMBER() in MySQ
- MySQL OPTIMIZE all tables?
- MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
- MySQL – Get row number on select
- PHP Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/website/public_html/facebook/fbclogin.php on line 57 [duplicate]
- MySQL error: Unknown column in ‘where clause’
- How to truncate the text returned for a column in a MySQL query
- Using union and order by clause in mysql
- MySQL LIKE IN()?
- Data truncated for column?
- MySQL date format DD/MM/YYYY select query?
- How to escape apostrophe (‘) in MySql?
- Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot
- The MySQL alternatives: Do Percona Server and MariaDB work well with WordPress, and do they make WordPress go better?
- Changing user_nicename
- wp_insert_user is not working for me?
- Sortable admin column for one meta key with three possible meta values
- AJAX in WordPress, sending coords data to MySQL and show after into map
- How to Query Database for posts with certain Taxonomy Term
- Importing Geo data into wordpress database
- Can I restart MySQL engine when WordPress website is live?
- How to remove /year/month from uploaded medias?
- SQL to order by CPT results by user->display_name w/only user->ID known
- wpdb custom tables without plugin
- Same database; different WordPress
- How can I programmatically change a MySql variable (foreign_keys_check)?
- How can I Use 2 databases with one WordPress install
- WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version [closed]
- Sql query does not work well
- AWS Lightsail WordPress – connect to database on instance using mysqli
- Display a number value from mysql query in WP
- WordPress how do I echo SUM from a column of a MySQL table by user id AND type_operation
- Using wpdb to connect to a different database is not working
- Why can’t i come back to older working version of php after ugrade with error log on php 7.2 and 7.3
- OR condition not working
- Dev and prod on the same database- dev version active for IOS
- wp_postmeta – lot of meta fields
- How to create a post based on data in a table?
- MySQL: get post_tag items that are tags (not SEO keywords)
- $wpdb->get_var returns 0