multilanguage database struct

[eluser]nofx[/eluser]
I want to setup a database structure for a multilingual site. But i'm not sure if my current design is good.

Suppose i have two tables. One for news items and one for the languages.

Code:

Table: lang
id  code
1   en
2   nl

(these news items only have titles, just for the sake of simplicity)

Code:

Table: news
id  title      sequence  lang_id
1   test1_nl   1         2
2   test2_nl   2         2
3   test3_nl   3         2
4   test1_en   1         1
5   test2_en   2         1
6   test3_en   3         1

Now suppose i'm logged into my CMS system: http://www.mysite.com/en/cms/
Obviously i load in the English content and i get to see a nice list of the three English news titles:

test1_en
test2_en
test3_en

But also suppose i'm able to change the order of the titles in my CMS. So i can drag the title "test3_en" to the top which would update the 'sequence' column where the last item is now the first item and vice versa.

It's easy to do this. Because i can easily get the ID of the title that i just dragged and dropped. After i drop it i simply go through all the titles and get their ID and update the sequence number respectively. (I do this with JQuery/Ajax calls)

But here comes the tricky part:

When i change the order of the 'en' news items, then i also want to change the 'nl' news items to the exact same 'sequence'. But i have no idea how to do this...

Anyone any idea how this is done...???

[eluser]Ochetski[/eluser]
Let me see if I got it.
Are these correspondents?
test1_nl <=> test1_en
test2_nl <=> test2_en
test3_nl <=> test3_en

You can try a JS cookie that will help you to select from DB, using not the ID, but the sequence number. PHP will be able to read it easily.

[eluser]Bui Duc Long[/eluser]
This is mine

Code:

Table: news
id  title      sequence  lang_id     translated_id
3   test1_nl   1         2            3
4   test2_nl   2         2            4
5   test3_nl   3         2            5
6   test1_en   1         1            3
7   test2_en   2         1            4
8   test3_en   3         1                  5

[eluser]Cristian Gilè[/eluser]
Every news in your html page should have an id value indicating its sequence. For example:

<li id='1'>Bad news</li>
<li id='2'>Wake up</li>
<li id='3'>Good news</li>

Make an AJAX call posting the sequence id of the news you want to drag (starting position) and the target position. If you want to drag "Good news" to the first place, serialize 3 and 1. Your AJAX request calls a controller method to update the news table with a simple query, that, according to the above data, should be like this:

Code:

$this->db->select('id');
$this->db->where('sequence',3);
$query = $this->db->get('news');

$blocked_id = array();
foreach($query->result_array() as $row)
{
   $blocked_id[] = $row['id'];
}

$this->db->set('sequence', 1);
$this->db->where('sequence',3);
$this->db->update('news');

$this->db->set('sequence', 3);
$this->db->where('sequence',1);
$this->db->where_not_in('id',$blocked_id);
$this->db->update('news');

I have used numbers only to make plain the query. Substitute numbers with variables that fit your needs.

Cristian Gilè