Link two columns?

[JordanW]

I've tried searching all over for some type of solution to no avail, mainly because I'm not entirely sure what I'm looking for/if it can even be done. But I know MySQL is incredibly powerful so I'm sure it can be...

TABLE:


+--------+------------------+------------------+
| id | column1 | column 2 |
+--------+------------------+------------------+
| 1 | TEXT | Hello World |
+--------+------------------+------------------+
| 2 | TEXT2 | Hello World2 |
+--------+------------------+------------------+

I'm using PHP to use the data from the database and therefore would like something like $result['TEXT'] to display "Hello World" and $result['TEXT2'] to display "Hello World2" etc.


Could anyone at least point me into the direction of setting up this query?

Thanks,
Jordan

[angst]

are you displaying these in a list? if so you can use While() loop with mysql_fetch_array(): http://php.net/manual/en/function.mysql-query.php

otherwise, if you want to call specific column data by id, then you can query it with mysql_fetch_assoc(): http://php.net/manual/en/function.mysql-fetch-assoc.php

both of those links have some good examples of this. post back if your still having issue.

[JordanW]

Ahh ok, I'll have a look.

I'm using it to add multilanguage support to a webpage, however we need a MySQL backend rather than the usual language file.

So ideally will need to echo all over the place, and obviously, I didn't want to query the database for each phrase!

[Old Pedant]

You *can* build a query to do that. Just not sure it makes sense. I suspect that Angst has the right idea(s), but...


SELECT t1.column2 AS `TEXT`, t2.column2 AS `TEXT2`
FROM table AS t1, tables AS t2
WHERE t1.column1 = 'TEXT'
AND t2.column1 = 'TEXT2'

Kind of a hacky thing to do, but it would work.

[Old Pedant]

Ahhh...so you will actually be getting dozens or more such pairs?

THen I think you want to transform the data from the DB into a PHP associative array.

That is, you use column1 as the key for the associative array and column2 as the value thereof.

Then in other parts of the PHP code you simply do something like
$phrases['hello']
to get the text in the appropriate language.

Oh...and I would assume your actual DB table would be something like:

languageid :: english :: otherlanguage
sp :: goodbye :: adios
jp :: goodbye :: sayonara
... etc. ...

And the your query would simply be
SELECT english, otherlanguage
FROM phrases
WHERE languageid = 'jp';

[angst]

oh ok, well there might be a better way to do this then,

you should have a language types table with id's.
example:

id: 1
lang: english

id: 2
lang: french

then you can set a default, or let the user select a language.

then have a table for words or terms,

like:

id: 1
lang id: 1
word: hello

id: 2
land id: 2
word: bonjour

etc...
and then you could have a function to call words or terms for each area, or just do a replace on the existing words querying mysql for the correct result according to the word & the lang selected.

[JordanW]

So while loop the contents of the table into an array and use that?

[angst]

no, just write out an array,
or if you were set on managing this via a web page, then use mysql and export the contents of the word table to a file as an array.

[Old Pedant]

I disagree mildly with Angst's solution.

You *can* do it that way, but now you have to use
$phrase[1]
to get the current language version of "hello". Would be very tough to write the PHP/HTML pages remembering what phrase is what number.

I think it's much more natural to use the scheme I suggest, where you use the english phrase as the "key", rather than an arbitrary phrase id.

[angst]

he could use either, just depends on how he builds the array.

also, i only say to export to a flat file, because in theory the table could get very big, so a flat file would be faster to call the data from instead of running a new query for every page.

[Old Pedant]

Yes, that's why I said "mildly". I just think it will be easier to use if the array is an associative one, keyed by the English phrase. Especially if this is a multi-person project, I'd hate to be the one to have to tell people:
No, no....973 means 'purchase order'...972 means 'your mother wears combat boots'

[angst]

lol,,sweet analogy!

I never said to call it by id, but rather to build a function that gets an lang id dynamiclly via a default or user selection and so on.

once he's calling the array keys I would ofcourse agree that using words would be better.

[JordanW]

No, no....973 means 'purchase order'...972 means 'your mother wears combat boots'

Lmao, seeing as it is indeed a multi person project, I would like to avoid instances like these :rolleyes:


Basically, I've settled with created a new column specifing which page the phrase will be used on; to minimise the amount of rows fetched every time a page loads:


define("LANGUAGE", $lang); //DEFINED BY QUERYSTRING OR COOKIE ETC
define("PAGE", "index"); //REFERS TO PAGE i.e. index = index.php

$lang = array();
$select = mysql_query("SELECT tag, ".LANGUAGE." FROM language WHERE page = '".PAGE."'");
while($rows = mysql_fetch_array($select)){
$lang[$rows['tag']] = $rows[LANGUAGE];
}


I don't see any issues in terms of efficiency so all is good. Cheers guys!

[Old Pedant]

This is *NOT* normally something I would suggest, but...

If you find that several phrases are used on several pages, and you want to avoid repeating them in the table, you *could* do something like this:
tag :: english :: spanish :: ..... :: page
bye :: bye :: adios :: ..... :: logout,badpassword,nastymouth

And then you could do:

$select = mysql_query("SELECT tag, ".LANGUAGE." FROM language WHERE page LIKE '%".PAGE."%'");

Normally, of course, you'd use a separate many-to-many table for this, but as a practical matter, you don't really need one for this application, so a hack like this seems acceptable.