[SOLVED] ID's in one table and output values in other

[EagerWolf]

Here is code which replaces producers id's with producer's name (example instead outputting 45 result is Creative)

$result = mysql_query("SELECT *, pr.producer FROM toss AS p, producers AS pr WHERE p.prod_id=pr.id AND p.id=".$_GET["item_id"]);

 

And it work perfectly... Now I need to assign new value to another parameter ... example let's say for country (of origin)..

 

Countries are being stored in table countries and in toss table is stored country's id...

 

So at once I want to change producer's and country's ID ...

 

Please help!

Thanks

[Wildbug]

Throw in another JOIN (the comma represents an implicit JOIN in your case).

 

(I'll make up some names since I don't know your table structure):

SELECT *,pr.producer,c.country_name FROM toss AS p,producers AS pr, countries AS c WHERE p.prod_id=pr.id AND p.country_id=c.id AND p.id=$_GET[item_id]

// ... or explicitly...

SELECT *,pr.producer,c.country_name FROM toss AS p JOIN producers AS pr ON p.prod_id=pr.id JOIN countries AS c ON p.country_id=c.id WHERE p.id=$_GET[item_id]

[EagerWolf]

I've tried this before ... and it didn't work... Although no error is outputted countries' ids aren't being substituted with names.

 

Now I've tried to make join only for countries and ids aren't substituted as well .. but no error is shown... what should I do?

 

Thanks

[bubblegum.anarchy]

Post the three table structures, some test data and a detailed description of what you are trying to achieve.

[EagerWolf]

I've found what is wrong...

 

My ID isn't being replaced but new line is added to result array.

 

Example:

$result = mysql_query("SELECT p.*, dr.si FROM personal AS p, drzave AS dr WHERE lokacijadrzava1=dr.id AND p.id=".$_GET["addid"]);

 

This gives me all data from personal and add another insert into result array: si => country_name...

 

I've been trying to replace data with subquery, but no success...

[Wildbug]

Did you solve your problem?  'Cause I don't understand a thing you just wrote.

[EagerWolf]

Well I did with some PHP coding but the problem is when I join table countries to table personal (primary).

 

In my primary table country is stored as ID (example: 12) ... Then in my countries table under ID 12 is stored Brazil. I want that when results are created it is shown Brazil instead of 12. I have stored in countries different languages.

 

Example:

Personal (primary)

-------------------------

| ID  conutry    producer |

| 1        11          smth  |

| 2        13          smth  |

| 3        12          smth  |

| ------------------------

 

Countries

---------------------------

|ID  |  si        |      eng    |

|11  |Bolgarija  | Bolgaria    |

|12  |Brazilija  | Brasil        |

|13  |Hrvaska  | Croatia      |

----------------------------

si, eng .. are signs for language (si - Slovenian, eng - English)

 

After applying code below:

mysql_query("SELECT p.*, c.$lang FROM personal AS p, countries AS c WHERE p.country=c.id AND p.id=".$_GET["someid"]);

Ok let's see: $lang is set to si or eng etc. In this case let's say it is set to eng and $_GET["someid"] = 2...

 

So we get:

mysql_query("SELECT p.*, c.eng FROM personal AS p, countries AS c WHERE p.country=c.id AND p.id=2");

 

 

So what I should get is:

ID = 2

country = Croatia

producer = smth

 

But what I get is:

ID = 13 (country's ID)

country = 13

proudecer = smth

eng = Croatia

 

I solved it whit some PHP code ...

mysql_query("SELECT * FROM personal WHERE id=2");

 

I get results than from database end are being stored in variable $row...

 

if(isset($row('country'))
{
  // Here I call table countries ... and get country name and store it in $country 
  $row['country'] = $country;
}

 

And this is how I get proper results... But I believe there is easier way and fastest way to do that with MySQL... I hope now you can help!

 

Thanks!

[Wildbug]

So what I should get is:

ID = 2

country = Croatia

producer = smth

 

But what I get is:

ID = 13 (country's ID)

country = 13

proudecer = smth

eng = Croatia

 

You're getting what you should get:  eng = Croatia.  You'll need to alias that column in order to get it to be "Country" or whatever.  Actually, "country" would conflict with the other p.country that is a number, so you might want to do something like "SELECT p.*,c.$lang AS CountryName....." and refer to $row['CountryName'] when you retrieve it.

 

This is the row you'll retreive from that query:

SELECT p.*,c.eng FROM personal AS p JOIN countries AS c ON p.country=c.id WHERE p.id=2;

p.id	p.country	p.producer	c.id	c.si		c.eng
----	---------	----------	----	----		-----
2	13		smith		13	Hrvaska		Croatia

[EagerWolf]

OK this is what I should get...

 

But is not what i want to get... any idea how to solve this problem?

[Wildbug]

Are you really using that query as written?  If you're also retreiving country.*, the reason "id" would contain the country id value is because both columns have the same name: "id."  When you're returning the result, $row['id'], you're getting country.id instead of personal.id because, since the personal.id came first, the country.id has overwritten that array key/value.  You'd need to alias the country id if you want to return both of them uniquely.  Otherwise, I can see no reason that id would be overwritten by country.id.

[EagerWolf]

Thanks for all replies!!! I've made it