how to check if field has a value ?

[anatak]

Hello,

I am trying to make content available in multiple languages.

content_info table holds all the data about one item except the text
content_text table holds the text about one item

the relationship is one (content_info) to many (content_text) as the text can be available in multiple languages.
I want to setup the site that the text is displayed in the prefered language of the user if it is available in that language.
If the text is not available in that language it should be displayed in the default language.

[code]
# Table structure for table `content_info`
#

CREATE TABLE `content_info` (
  `Id` int(11) NOT NULL auto_increment,
  `Author` int(11) default NULL,
  `Type` int(11) default NULL,
  `UpdateDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `PostDate` timestamp NOT NULL default '0000-00-00 00:00:00',
  `Picture` int(11) default NULL,
  `Place` int(11) default NULL,
  `Date` date default '0000-00-00',
  `Price` int(11) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

# Table structure for table `content_text`
#

CREATE TABLE `content_text` (
  `Id` int(11) NOT NULL auto_increment,
  `LanguageId` int(11) default NULL,
  `Translator` int(11) default NULL,
  `InfoId` int(11) default NULL,
  `Title` varchar(255) default NULL,
  `Subtitle` varchar(255) default NULL,
  `Text` text,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
[/code]

The way I have it now is like this.
[code]
SELECT content_info.Id FROM content_info;
[/code]
and then loop through all the Id's.

[code]
foreach($result01 AS $row01){
echo "<br />ID: ".$row01['Id'];
//query to select the default language (english)
$Query02="SELECT $TableName01.Id,
$TableName02.Title, $TableName02.Subtitle
FROM $TableName01, $TableName02
WHERE $TableName01.Id = $row01[Id]
AND $TableName02.InfoId = $row01[Id]
AND $TableName02.LanguageId = 1
;";
//query to select the userdefined language
$Query03="SELECT $TableName01.Id,
$TableName02.Title, $TableName02.Subtitle
FROM $TableName01
JOIN $TableName02 ON $TableName01.Id = $TableName02.InfoId
WHERE $TableName01.Id = $row01[Id]
;";

echo $Query02."<br />";
echo $Query03."<br />";

}
[/code]
After I have the results I can check in php if a the text fields of $Query03 are empty and if they are I display the texts fields from $Query02

The problem is that just for listing the title and subtitle of the content I have to run 2 queries for every item.

Can anyone think about a more performant way to do this or do you think this is not a problem for the DB.
Or can you think about a better DB design ?

thank you
anatak

[fenway]

Well, you could use a derived table to pull the desired language "first", and then JOIN it back to the parent table.

[anatak]

What I need is a left join with a where clause :(

SELECT content_info.*, content_text.* FROM content_info LEFT JOIN content_text ON content_info.Id = content_text.InfoId

will give me all the records from content_info and the records from content_text
The good thing is that it also returns the content_info records if there is not matching content_text.

Now I would like to be able to get
all the records from content_info that match content_text where the language of context_text is set AND
all the records from content_info that have no match with content_text

I am not sure if it is possible in SQL

[fenway]

With a UNION?

[anatak]

A union would not work.
but I found a different approach.

I am going to run two queries
one query to select all the rows that have an english text
one query to select all the rows that have the desired language text

after that I have to check if the same id exists in both arrays that hold the results of the queries.
if the Id exists in both arrays I have to print the text in the desired language if the id only exists in the first array I have to print the text in english.

of course easier said than done.

do you know if you can manipulate a $result = mysql($query) as any array ?

anatak

[shoz]

You should be able to use the following query to get the desired language and the default
[code]
SELECT
t1.title AS dsrd_title, t2.title AS dflt_title
FROM
content_info i
LEFT JOIN
content_text AS t1
ON
t1.info_id=i.id AND t1.lang_id = $userDefLang
INNER JOIN
content_text AS t2
ON
t2.info_id = i.id AND t2.lang_id = 1
[/code]

[code]
<?php
while ($row = mysql_fet...)
{
  $title = isset($row['dsrd_title'])? $row['dsrd_title']: $row['dflt_title'];

}
?>
[/code]

You should also put a multicolumn index on (info_id, lang_id);

EDIT: Made LEFT JOIN t2 an INNER JOIN, meaning that there has to be text in the default language (lang_id = 1) or the query will not return any results for that article.

[fenway]

[quote author=anatak link=topic=112993.msg459805#msg459805 date=1162186006]
A union would not work.
but I found a different approach.
[/quote]
I thought you mean "AND" in the english sense, not the database sense.

[anatak]

Hey shoz,

What can I say ?
Pure genius.
Thanks a lot.

anatak