PHP / SQL不确定如何处理

Question

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

Answer 1

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