[SOLVED] Array question

[sebastiaandraaisma]

Hi, I have a little problem with an array

 

Right now I have the working code:

echo '<td>' . $row->countryid . '_' . 'agent' . '-' . $row->memid </td>';

 

The problem I have is that $row->countryid contains a number, this number is related to a number in a different table which is connected to a name in a third table (unbelievable).

 

                                        Table 1    Table 2  Table 3

So basically it looks like this: variable > number > text

 

Let me tell you that it was a "professional" company who had designed the database.

I won't mention any names, but I would not reccomend Hyperskins AB from Sweden.

 

Anyways, as you probably guessed, I need the variable $row->countryid to hold the text from table 3.

 

If some one could give me some directions, that would be great!

 

All help is appreciated!

 

Kind regards,

Sebastiaan

 

[Barand]

So I guess you have a structure like this

[pre]

Table1          Table2              Table3

==========      ==========          ==========

a          +--- b            +---- c

b      -----+    c        ----+    d

[/pre]

 

you know "a" and you want "d"?

 

SELECT t3.d 
FROM table1 t1
INNER JOIN table2 t2 ON t1.b = t2.b
INNER JOIN table3 t3 ON t2.c = t3.c
WHERE t1.a = '$somevalue'

[sebastiaandraaisma]

Thank you for you reply, yes that is correct. I know value A and get this value displayed correctly. And like your drawing shows is value A mentioned as an id in table 2. In table 2 the id is connected to a number which is the id in table 3 and in table 3 that id is connected to a word.  And that word should replace value A in my variable $row->countryid.

 

Its sounds quite complicated like the friend of my friend's friend

But that's basically what it is.

 

Thank you for your reply, I will now experiment a little and see how it goes.

 

Kind regards,

Sebas.

[sebastiaandraaisma]

I have some additional information that might make it easier to understand.

 

I tried some variations but it didn't work for me (most likely I'm doing something wrong).

I wont have much time to experiment today as I'm moving to a new apartment (with no internet connection there yet), but I will definitely try to read the messages  during the breaks

 

Table 1
CREATE TABLE `objects` (
  `id` int(11) NOT NULL auto_increment,
  `memid` int(11) default NULL,
  `countryid` int(11) default NULL,
)
INSERT INTO `objects` VALUES (37, 34, 268); (268 is the value I have now)


Table 2
CREATE TABLE `countries` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
)
INSERT INTO `countries` VALUES (268, '971');


Table 3
CREATE TABLE `phrases` (
  `id` int(11) default NULL,
  `language` int(11) default NULL,
  `phrase` text,
)
INSERT INTO `phrases` VALUES (971, 1, 'Sweden');

 

My last attempt was:

SELECT phrases.phrase WHERE `language`='1' 
FROM objects
INNER JOIN countries ON objects.countryid = countries.name
INNER JOIN phrases ON countries.name = phrases.phrase WHERE `language`='1'
AND objects.countryid = '$row->countryid'

 

This attempt did not work.

 

My old code gives me a list like this:

268_agent-34

371_agent-19

271_agent-28

etc.

etc.

 

All help is appreciated.

 

Kind regards,

Sebastiaan

 

 

[Barand]

try

SELECT o.id, o.countryid, p.language, p.phrase
FROM objects o
INNER JOIN countries c ON o.countryid = c.id
INNER JOIN phrases p ON c.name = p.id
WHERE o.countryid = 268

 

If you have the situation where

- an object can have many phrase records associated with

- a phrase can have many objects associated with it

 

then you need an intermediate link table (countries in this case) to handle the many-to-many relationships.

[sebastiaandraaisma]

Thank you for your advise.

That gave me another idea.

 

Right now I get a list that looks like this:

268_agent-34

371_agent-19

271_agent-28

etc.

etc.

 

Could one solution be a search and replace function and have the whole list run through an if else if section and have that way the numbers replaced by text (around 200 parameters) or would that become to slow? Or maybe not possible?

 

The problem is that I have everything coming in as a query so the list is not there yet. Would it be possible to have a search and replace after the query? 

 

Just a thought.

 

Kind regards,

Sebastiaan

[Barand]

do you mean this?

 

<?php

/**
* starting with this
*/
$text = "268_agent-34
268_agent-35
268_agent-36";

/**
* then
*/
$sql = "SELECT c.id,  p.phrase
FROM countries c 
INNER JOIN phrases p ON c.name = p.id";

$convert = array();

$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while (list($code, $phrase) = mysql_fetch_row($res)) {
    $convert[$code] = $phrase;
}

/**
* now you have the conversion array, apply it to the text
*/

$newtext = strtr($text, $convert);

echo '<pre>', $newtext, '</pre>';
?>

gives-->

Sweden_agent-34
Sweden_agent-35
Sweden_agent-36

[sebastiaandraaisma]

Wow thank you, I get a lot further this time!

 

I now see the list like this:

Sweden_agent-Bonaire

Sweden_agent-Bosnia and Herzegovina

Sweden_agent-Botswana

 

So it also converts the agent number, that was not supposed to happen

I will play a little with the code and see if I have enough skills to make it work

 

Other than that it works great! I tried it with different numbers and that came out fine.

 

Thank you for your help so far!

[Barand]

change

      $convert[$code] = $phrase;

to

 

      $convert[$code.'_'] = $phrase.'_';

[sebastiaandraaisma]

Thank you so much for taking the time to help me out, it was way to complicated for me to figure it out by myself.

 

Thanks again!

 

Kind regards,

Sebas.