getting an effect like xml's for-each?

[yahn]

If I have something like this in xml:

<?xml version="1.0" encoding="ISO-8859-1"?>

<tags>

<tag>
<name>a</name>

<attribute>shape</attribute>
<value>circ</value>
<value>circle</value>
<value>rect</value>
<value>rectangle</value>
<value>poly</value>
<value>polygon</value>
</attribute>
</tag>

</tags>I can use for-each to get each tag and for-each to get each value of each attribute. In MySql it seems like I would have to create a table inside a table to get this same type of effect. How do you work arround this in PHP/MySql? I would appreciate any help.

[hemebond]

Is this some round-about way of asking how to represent that XML in a relational database?

[yahn]

well that depends. If that means I want to know how you get a field to be able to have more then one value then yes. I just need to know how I can have a field be able to different amounts of values.

[hemebond]

I just need to know how I can have a field be able to different amounts of values.You don'tTABLE tag_attr
tag attribute
---------------------
a shape

TABLE attr_val
attribute value
---------------------
shape circ
shape circle
shape rect
shape rectangle
shape poly
shape polygon

[yahn]

ok I don't get how that works though. Each tag can have a different amount of attributes and each attribute can have a different amount of values. I thought you had to know how many fields you needed in the table when you created it. Is that not the case?

[hemebond]

CREATE TABLE `tag_attr` (
`tag` VARCHAR( 16 ) NOT NULL ,
`attribute` VARCHAR( 16 ) NOT NULL
);

CREATE TABLE `attr_val` (
`attribute` VARCHAR( 16 ) NOT NULL ,
`value` VARCHAR( 16 ) NOT NULL
);Insert a new row into `tag_attr` for each tag+attribute combination. Insert a new row into `attr_val` for each value an attribute can have.

[yahn]

ok, well what about the values of the attributes? not all aligns can equal justify. So do I just create another table? But then how do I know which type of align it is? I might want the one that can't equal justify but both of them are named align. Am I missing something?

Do I just create the table for the tags then use where to get the attributes for that tag? Or am I missing the point?

[hemebond]

ok, well what about the values of the attributes? not all aligns can equal justify.Then that makes things a little more complicatedCREATE TABLE `tags` (
`tag` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 16 ) NOT NULL ,
PRIMARY KEY ( `tag` )
);

CREATE TABLE `attributes` (
`attribute` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 16 ) NOT NULL ,
PRIMARY KEY ( `attribute` )
);

CREATE TABLE `values` (
`tag` INT NOT NULL ,
`attribute` INT NOT NULL ,
`value` VARCHAR( 16 ) NOT NULL ,
PRIMARY KEY ( `tag` , `attribute` , `value` )
);Create your list of tags, list of attributes and then create tag-attribute-value combinations in the `values` table.

[yahn]

I still don't get this. How does it work if one element has 6 attributes and another only has 2? I know how you would get them, but I thought you had to know the amount of fields for each table you create. Is that not the case?

[hemebond]

How does it work if one element has 6 attributes and another only has 2?The first element will have a row for each value each of those 6 attributes have. The second element will have a row for each value for each of its 2 attributes.TABLE tags
tag name
------------
1 a
2 b
3 div

TABLE attributes
attribute name
---------------------
1 shape
2 align

TABLE values
tag attribute value
---------------------------------
1 1 circ // a-shape-circ
1 1 circle // a-shape-circle
1 1 rect // a-shape-rect
1 1 rectangle // a-shape-rectangle
1 1 poly // a-shape-poly
1 1 polygon // a-shape-polygon
1 2 left // a-align-left
1 2 right // a-align-right
3 2 left // div-align-left
3 2 right // div-align-right

[yahn]

don't i need a field in the TABLE attributes to know which tag it points to? how would I get all of the attributes for a tag if i didn't do that?

[hemebond]

don't i need a field in the TABLE attributes to know which tag it points to? how would I get all of the attributes for a tag if i didn't do that?SELECT `name`
FROM `attributes`
LEFT JOIN `values` ON `attributes`.`attribute`=`values`.`attribute`
WHERE `values`.`tag`=1
GROUP BY `name`Selects all attribute names for the 'a' tag.

[yahn]

I still don't see how that could get all the attributes for the first tag if the attributes don't save which tag they point to. Maybe I should learn a little more php/mysql. I've never seen left join before.

[Kurashu]

A left join simply preserves all rows in the left table and any rows that meet the join critreia in the right table (the joining table).

[hemebond]

I still don't see how that could get all the attributes for the first tag if the attributes don't save which tag they point to.But they do, in the values table.Maybe I should learn a little more php/mysql. I've never seen left join before.Yes, you should learn more SQL.

[yahn]

Ahh.. I didn't get that at first but now I see how the where at the end makes sure it only gets the attributes you want. Thank you very much.

edit: now when I got the result for the query it said not valid argument for mysql_numrows();. This is what I typed in:
$query = "SELECT name
FROM html_attributes
LEFT JOIN html_values ON html_attributes.attribute = html_values.attribute
WHERE values.tag = 1
GROUP BY name";

$result = mysql_query($query);

$num = mysql_numrows($result);
Sorry to ask another question so soon, but I *think* I did exactly what I'm suppose to, or I wouldn't be here asking. I would appreciate any further help. Thank you again.