I have two tables, categories and info.
categories looks like:
id | name
info looks like:
id | parent_id | name | url
parent_id is the id from categories.
I have a form where a user can add more than one url. There is a button that can be pressed to make more url fields appear. If there is more than one url, then url2, url3, etc are added to the database.
info will then look like:
id | parent_id | name | url | url2 | url3
Is this an appropriate approach?
If so, what if info is like this:
id | parent_id |name |url |url2 |url3
1 | 1 |One |http://cnn.com |
2 | 1 |Two |http://msn.com |http://aol.com|
When I view One or Two or w/e, how do I query to get their urls?
I know I can do:
$mysqli->query("SELECT i.url FROM info AS i LEFT JOIN categories AS c ON i.parent_id = c.id");
But this is dependent on me putting i.url, i.url2, i.url3 and I would have to create a separate query for each info. I want it so PHP determines how many and what i.url to select.
So it should be:
$mysqli->query("SELECT (PHP determines what i.url to put here depending on what info page I'm viewing) FROM info AS i LEFT JOIN categories AS c ON i.parent_id = c.id");
If you go that path you will have a bunch of columns with null values and encounter issues with queries, which is pretty much what you are describing.
I would create a different table (perhaps urls) that maps info with urls. For example:
url_id | info_id | url
This is the Repeated Attributes design pattern which is concisely explained here: http://www.tomjewett.com/dbdesign/dbdesign.php?page=phone.php
Here is an example on how to query:
SELECT i.*, url
FROM info i JOIN urls u ON i.id = u.info_id
No, the right solution here is another table, perhaps called "info_urls", which models a one-to-many with info-to-urls.
This is a "normalized" design, and will allow unlimited URLs per "info", and is quite easy to query:
SELECT name, GROUP_CONCAT(info_urls.url) as urls
FROM info JOIN info_urls ON info.id = info_urls.info_id;
The columns will look like this:
id
info_id
url
This solution models the reality that you do not know how many URLs the user will enter, and your RDBMS is made to CRUD rows , not columns :-) (and is very good at joining them together for queries)