I have a database with rows of "parents" and "children". Similar entries, but one entry is generic version of the more specific child. However, I want these entries to match exactly in certain columns.
Here's an example of my database:
| ID | IsChildOfID | Food | Type |
| 1 | | | Fruit |
| 2 | 1 | Apple | Fruit |
| 3 | 1 | Pear | Vegetable |
| 4 | 1 | Banana | Vegetable |
| 5 | | | Vegetable |
| 6 | 5 | Lettuce | Fruit |
| 7 | 5 | Celery | Vegetable |
| 8 | 5 | Cabbage | Fruit |
In this example there are 2 parents and 6 children. The value of "type" field is inconstant with some of the children. I want to be able to find any children in the database and replace it with their parent's value in only some of the columns. Is this possible with purely MySQL or do I need do it with php? Thanks.
Generally, when you use parent/children relationships in sql, you should make two separate database tables for each. In your case, you should create a database entitled "types" and include a type_id for each element in the child table.
Example
Child table:
| ID | TYPE_ID | Food |
| 2 | 1 | Apple |
| 3 | 2 | Pear |
| 4 | 2 | Banana |
| 6 | 1 | Lettuce |
| 7 | 2 | Celery |
| 8 | 1 | Cabbage |
Type table:
| ID | Type |
| 1 | Fruit |
| 2 | Vegetable |
You can then reference it by looping through the type table, and using a sql statement like
$types = mysql_query ( 'SELECT * FROM type_table');
WHILE ( $type = mysql_fetch_array ( $types ) )
{
$sql = 'SELECT * FROM child_table WHERE TYPE_ID = "' . $type['type'] . '"';
}
UPDATE name_of_table SET Type = "Fruit" WHERE IsChildOfID = 1
and
UPDATE name_of_table SET Type = "Vegetable" WHERE IsChildOfID = 5
Also I would prefer to use 2 tables for this kind of data...
Similar answer here: UPDATE multiple tables in MySQL using LEFT JOIN
I was going to write this:
UPDATE foods c
JOIN foods p ON p.id = c.IsChildOfId
SET c.type = p.type
WHERE p.isChildOfId IS NULL
But then upon further reading of the link above, not sure you can reference the target table. Worth a try.