Two Tables One Query

[Gatsu]

Hi I got two tables, hero and item.Im trying to get the name column of the row in item where item.id match the number in hero.helmet

$result = mysql_query("SELECT * FROM hero h WHERE h.id=$idUNIONSELECT * FROM item i WHERE i.id='h.helmet'")or die(mysql_error());

Is this query right for it and if it is then please help with printing it.echo $add['i.name'] sure dont work for me.

[RobberBaron]

Hi I got two tables, hero and item.Im trying to get the name column of the row in item where item.id match the number in hero.helmet

$result = mysql_query("SELECT * FROM hero h WHERE h.id=$idUNIONSELECT * FROM item i WHERE i.id='h.helmet'")or die(mysql_error());

Is this query right for it and if it is then please help with printing it.echo $add['i.name'] sure dont work for me.

Not sure you can ask two things at once, it might confuse PHP. Try something more simple:

$result=Array(1->mysql_query("SELECT * FROM hero WHERE id = ".$id." UNION") or die(mysql_error());2->mysql_query("SELECT * FROM item WHERE id = helmet") or die(mysql_error()););

To refer to them:

//returns first query$result[1];//returns second query$result[2];

[Synook]

UNION is a valid SQL construct. Does the SQL query work?

[Gatsu]

It works like this:

SELECT * FROM hero h WHERE h.id=10UNIONSELECT * FROM item i WHERE i.id=h.helmet

I guess its right except that i.id=h.helmet should be i.id=1

[justsomeguy]

UNION is only going to work if the hero and item tables have the same structure, which I doubt they would.If you're trying to get the name of the helmet that the hero is wearing, use a subquery:SELECT name FROM item WHERE id IN (SELECT helmet FROM heros WHERE id=10)You can also use a join:SELECT hero.helmet AS id, item.name FROM hero INNER JOIN item ON hero.id=10 AND item.id=hero.helmet

[Gatsu]

This one can only select 1 column so the rest of my page gets screwed:SELECT name FROM item WHERE id IN (SELECT helmet FROM hero WHERE id=10)This one I try make AND and use * but it makes my website totally blank:SELECT hero.helmet AS id, item.name FROM hero INNER JOIN item ON hero.id=10 AND item.id=hero.helmet example:SELECT * FROM hero INNER JOIN item ON hero.id=$id AND item.id=hero.helmet AND item.id=hero.lefta AND item.id=hero.body AND item.id=hero.righta AND item.id=hero.legs AND item.id=hero.boots

[justsomeguy]

SELECT * FROM hero INNER JOIN item ON hero.id=$id AND item.id=hero.helmet AND item.id=hero.lefta AND item.id=hero.body AND item.id=hero.righta AND item.id=hero.legs AND item.id=hero.boots

Well now you're trying to do more than just get the helmet, so yeah it's not going to work. If you want to get information about each item then you need 1 join per item. This gets 2 items, for example:SELECT hero.*, i1.name AS helmet_name, i2.name AS lefta_name FROM hero INNER JOIN item AS i1 ON hero.id=$id AND i1.id=hero.helmet INNER JOIN item AS i2 ON i2.id=hero.leftaIt's also not the best idea to use *, because each column needs to be unique. If two tables each have a column called ID, then using * isn't going to work. You need to give duplicate columns aliases.

[Gatsu]

Thats exactly what im looking for, thank you!

[Gatsu]

can I add a $result2 query and change the while like this:while (($add = mysql_fetch_array($result, MYSQL_BOTH))||($add2 = mysql_fetch_array($result2, MYSQL_BOTH))) {???

[justsomeguy]

The syntax is legal, that will run until both queries return false. Just make sure that inside the loop you're checking to make sure the thing you're trying to work with is an actual result and not false.

[Gatsu]

It makes my website display three times after eachother.

[justsomeguy]

I don't know what the rest of your code is doing. Like I said, the syntax you showed will execute the loop until neither of the result sets have any more rows. More specifically, it will loop through the first result until there are no more, then it will loop through the second result until there are no more.