Jump to content


Photo

how to check if field has a value ?


  • Please log in to reply
7 replies to this topic

#1 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 28 October 2006 - 12:01 AM

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.

# 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 ;

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

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 />";
		
	}
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
takasi.8008@docomo.ne.jp
tourokum@0508.jp

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 October 2006 - 04:09 PM

Well, you could use a derived table to pull the desired language "first", and then JOIN it back to the parent table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 29 October 2006 - 01:17 AM

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

takasi.8008@docomo.ne.jp
tourokum@0508.jp

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 October 2006 - 07:25 PM

With a UNION?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 30 October 2006 - 05:26 AM

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

takasi.8008@docomo.ne.jp
tourokum@0508.jp

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 30 October 2006 - 11:31 AM

You should be able to use the following query to get the desired language and the default
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

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

}
?>

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.

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 October 2006 - 05:10 PM

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

I thought you mean "AND" in the english sense, not the database sense.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 406 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 31 October 2006 - 09:19 AM

Hey shoz,

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

anatak

takasi.8008@docomo.ne.jp
tourokum@0508.jp




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users