Gather Specific Data from tables

[Eliasen]

Hi everyone,

 

My problem is, I have a Database with the tables called:

Items

Items_stats

 

The Items table contains the standard values that apply to all items, like name, value, picture, etc.

The Items_stats table contains the specific customer stats for the individual items, and looks like this: id | item_id | stat | value

 

Here is my problem.

Since the "stat" and "value" values can be anything, how can I make it so that checks: if the "stat" value is called "age", display the "value" value?

 

I tried with the join syntax, but no luck.

 

Hope you can help me.

 

Sincerely

 

(MySQL v: 5.5.8 - PHP v: 5.3.3 - Apache v: 2.2.17)

 

[Muddy_Funster]

i'm not getting it, could you post the code you tried with already?  from your question all you need is

SELECT value FROM item_stats WHERE stat = "age"

Now i'm sure that's not all you want to do, so help us help you  .

[BoarderLine]

Assuming your Items table has the primary key item_id.

 

You query should be like this:

 

SELECT Items.*, Items_stats.*
FROM (Items LEFT JOIN Items_stats ON Items_stats.item_id=Items.item_id) 
WHERE Item_stats.stat LIKE 'age'

[Eliasen]

Thanks for the quick reply!

 

The problem is that the "items_stats" table, which looks like this: [id | items_id | stat | value], the "stat" and "value" can be anything i choose to put in it.

 

eg. some entries might look like this:

 

[id | items_id | stat | value]

1  | 1            | sex  | male

2  | 1            | age | 18

3  | 2            | zip  | 4000

4  | 2            | server | Dell

 

 

the Stat and the value can be anything, so if I need to show the items stats where I want them to be shown eg.:

 

        <td>'. $data['name'] .'</td>

<td>'. $data['sex'].'</td>

<td>'. $datas['zip'] .'</td>

<td>'. $data['server'] .'</td>

 

how can I do this? because when I do a normal JOIN syntax query it It just spews out the information in random order or like this:

sex, age, zip,server,male,18,4000,Dell

[BoarderLine]

Just read your reply, you may want to replace the LEFT JOIN with RIGHT JOIN to give ALL the stat results for each item.

[Muddy_Funster]

whoa there....I think what you need to do is revisit your table structures.  You should have seporate columns for most of the things you have in stat.

[Eliasen]

whoa there....I think what you need to do is revisit your table structures.  You should have seporate columns for most of the things you have in stat.

 

Yea I was thinking this as well.. maybe add all rows to one table and take the information with a regular query? Or is this a bad idea?

 

 

Thank you all for your replies! I appreciate it!

[Eliasen]

Sorry for the double post,

 

If i put all the items into one table, many of the items will have rows which they do not use, say I merge a "personal info" table and "cars" table, so the cars will have rows with "sex" "age" "zipcode" etc, and the personal info will have "Manufactura" "make" etc.

 

Will this be a problem? or should I just ignore this and assign a default value like 0?

[Muddy_Funster]

From your inital description I don't think merging into a single table will be the answer.  Items has inital information regarding, well, an item.  Item_stats has some more in-deapth information.  You arn't going to want to access the information on Item_stats untill the end user shown an intrest in a perticular item.  The question is, how many real records do you have in Item_stats?  You either have the choice of moving the info out, to another differently named table, OR you can just delete the content and start from scratch....

[fenway]

No need to cram into one table.