Jump to content

Two Tables One Query


Gatsu
 Share

Recommended Posts

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.

Edited by Gatsu
Link to comment
Share on other sites

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];

Edited by RobberBaron
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Edited by Gatsu
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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))) {???

Edited by Gatsu
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...