Simply query... something I forgot?

[ryeman98]

So I'm grabbing a pet from one table and I have all the clothing on another table. Each pet has a column in the Clothing table. How can I grab the specific pets clothing from the second table?

 

I hope that makes sense.

 

My code:

$petquery = mysql_query("SELECT * FROM CustomisationPets WHERE name='$name'");
$petresult = mysql_fetch_array($petquery);

echo "<img src=\"".$petresult['img_url']."\" alt=\"".$petresult['name']."\" /><br />";
echo "<b>".$petresult['name']."</b><br />";
echo "Below you can see all the clothes the ".$petresult['name']." can wear.";

$clothesquery = mysql_query("SELECT * FROM CustomisationClothes ORDER BY name WHERE $name='yes'");

[trq]

$clothesquery = mysql_query("SELECT * FROM CustomisationClothes WHERE name='{$petresult['name']}' ORDER BY name");

[suma237]

can you post the table structure?.using the key relation ,join it with id and display .

[ryeman98]

Still isn't working...

 

I'm not quite sure what you mean but it's like

id

name

img_url

petname

petname

...

 

There are 54 different pet names... they are ENUM 'yes', 'no'

The ones that are yes mean that the peice of clothing is wearable, if it isn't, I don't want it to display.

[trq]

Do you mean you have a different filed for each pet name? Thats a backward design.

 

I suggest you look at some tutorials on normalization.

[ryeman98]

Well there are two tables:

 

Pets:

id

name

img_url

 

and

Clothing:

id

name

img_url

petname

petname

...

 

Is that wrong or can I still do something with it? Everything works find except for that one single query...

[suma237]

how the table CustomisationPets and CustomisationClothes related ?without any relation how can you retrieve the data?

[ryeman98]

The pets table has all of the pets and the clothes table has all of the clothes. If the pet name in clothes is yes for the certain peice of clothing, then it should display on that pet's page (GET Method) and if it says no, as in it isn't a wearable peice of clothing, it shouldn't display.

 

In a few months there will be hundreds of clothing items. That's why I created two tables...

[mmarif4u]

You have to make a unique id for both table for relate to each other.

Like:

Pets:

P_id

id

name

img_url

 

and

Clothing:

id

p_id(it will not be incremented auto but the id will stored for p_id as in pets table)

name

img_url

petname

petname

 

Or if u cant get this U have to lookup for normalization as thorpe mention earlier.

[trq]

In a few months there will be hundreds of clothing items. That's why I created two tables...

 

And what if you ever need to add new pets? Your going to need to alter the table structure to add a new field. If this is forr a serious project, Ide really recommend reading that link I provided.

[ryeman98]

I'm pretty sure I know what you mean, I just don't know how you'd enter these into the db...

[mmarif4u]

If i understand ur q correctly than see this example.

 

Pets:

P_id  id  name              img_url

100    1  something        url1

101    2  nova              url2 

and

Clothing:

id    p_id    name              img_url    petname

1      100      something        url1        somename

2      100      Something        url1        somename

3      101    nova                url2        somename2

 

Hope it will get u some idea.

[ryeman98]

Ok. I think I know now. Only thing... do the P_id's have to be the same?

[mmarif4u]

See the above example.

[ryeman98]

lol Alright, thanks!