is there a other way to write this?

[zetcoby]

is there a other way to write this?

 

		mysql_query("UPDATE $lang_ro SET val = '$nav_home' WHERE var = 'nav_home'");
		mysql_query("UPDATE $lang_ro SET val = '$nav_project' WHERE var = 'nav_project'");
		mysql_query("UPDATE $lang_ro SET val = '$nav_forum' WHERE var = 'nav_forum'");
		mysql_query("UPDATE $lang_ro SET val = '$nav_db' WHERE var = 'nav_db'");
		mysql_query("UPDATE $lang_ro SET val = '$nav_contact' WHERE var = 'nav_contact'");
		mysql_query("UPDATE $lang_ro SET val = '$nav_faq' WHERE var = 'nav_faq'");

[jazzman1]

Yes, you can! Don't repeat the same thing multiple times. Use arrays and loops.

[zetcoby]

but i am not repeathing the same thing every time there is a other column and a other Where condition

[Christian F.]

I'm sitting here wondering why you've made a database inside the database, instead of taking advantage of the capabilities a database does give you..?

 

What you have here is called the "Entity-Attribute-Value" (anti-)pattern, and as you've noticed it seems easier at first, but tends to bite you in the arse later on. It is recommended to stay far away from this pattern, unless you really know what you're doing and have absolute confidence that no other pattern will work (as efficiently) for you. Knowing that requires extensive knowledge about all other patterns, which means years and years of experience with database design.

 

In this case, I'd actually recommend using a flat file to store the language data in. Either as a PHP array, INI file, or something that can be just as easily parsed.

[zetcoby]

well what i did there was a db where i store all the variables and values (valeus are all text ) witch i use for language for a site, i got 2 tables. lang_ro and lang_hu each table has 4 columns `id`,`var`,`val`and `page`  in var i store the variable name, in val the content of that variable and in page the page that they belong (a keyword of the page); so to retrive data from the db i do it like this

if (isset($_SESSION['lang'])) {
if ($_SESSION['lang'] == 'ro') {

	$sql = mysql_query("SELECT var, val FROM $lang_ro");
	$lang = array();
	while ( $row = mysql_fetch_assoc($sql)) {
		$lang[$row['var']] = $row['val'];
	}


}else if ($_SESSION['lang'] == 'hu') {

	$sql = mysql_query("SELECT var, val FROM $lang_hu");
	$lang = array();
	while ( $row = mysql_fetch_assoc($sql)) {
		$lang[$row['var']] = $row['val'];
	}


}
}else{

$sql = mysql_query("SELECT var, val FROM $lang_ro");
$lang = array();
while ( $row = mysql_fetch_assoc($sql)) {
	$lang[$row['var']] = $row['val'];
}


}

 

now the reason i made the table like that is: i made an admin panel where the admin can enter, he selects the page then the language he wants to edit and after he selected everything,  all the the values from the page he selected will appear in inputs and he can easily edit what he wants and then click save and update the db only in the fields he edited.

 

P.S. now i try to make it in such way that it will check to see if the array that is needed was already retrieved from the DB so it wont retrieve it every-time when the page changes

 

[Jessica]

$cols = array(''nav_home', 'nav_project', 'nav_forum', nav_db', 'nav_contact', 'nav_faq');
foreach($cols AS $c){
$val = $$c;
mysql_query("UPDATE $lang_ro SET val = '$val' WHERE var = '$c'");
}

I think that should work.

[jazzman1]

but i am not repeathing the same thing every time there is a other column and a other Where condition

 

@ zetcoby, NEVER EVER do this! Queries in loops make me start to sweat nervously.

I wrote a simple script for you, just to make a note, how to avoid the queries in loops.

As you mentioned above you have a 2 tables and 4 columns  - `id`,`var`,`val`and `page` !

Take a look at an example:

// An array containing navigation list 
$nav_list = array('nav_home','nav_project','nav_forum','nav_db','nav_contact','nav_faq');
// start array with index 1
$iOne = array_combine(range(1, count($nav_list)), array_values($nav_list));
// implode keys of $iOne...
$ids= implode(", ", array_keys($iOne));
// build query.....
// using CASE SQL operator updating `val` column
$sql = "UPDATE `tbl_name` SET `tbl_name`.`val` = CASE `tbl_name`.`id` ";

// looping result array 
foreach ($iOne as $key => $val) {
  $sql .= sprintf("WHEN %d THEN '%s' ", $key, $val);   
}
$sql .= "END WHERE `tbl_name`.`id` IN ($ids)";

echo '<pre>'.print_r($sql, true).'</pre>'; 

// proper output
//UPDATE `tbl_name` SET `tbl_name`.`val` = CASE `tbl_name`.`id`WHEN 1 THEN 'nav_home' WHEN 2 THEN 'nav_project' WHEN 3 THEN 'nav_forum' 
//WHEN 4 THEN 'nav_db' WHEN 5 THEN 'nav_contact' WHEN 6 THEN 'nav_faq' END WHERE `tbl_name`.`id` IN (1, 2, 3, 4, 5, 6)

// execute query........
$result = mysql_query($sql) or die(mysql_error()); 

 

PS.Now in this particular example, only 6 rows are being updated, but 5 queries have been trimed....

If you need to update multiple fields, this is easily done just by adding another CASE block.

Take a look at here -> http://www.delphipraxis.net/161775-%5Bmysql%5D-multiple-row-update.html#post1112695

UPDATE `mytable` SET
  `color` = CASE id
    WHEN 2 THEN 255 
  END,
  `position` = CASE id
    WHEN 1 THEN 5 
  END

 

[ignace]

UPDATE `mytable` SET
  `color` = CASE id
    WHEN 2 THEN 255 
  END,
  `position` = CASE id
    WHEN 1 THEN 5 
  END

 

+1 for this solution.

[Jessica]

UPDATE `mytable` SET
  `color` = CASE id
    WHEN 2 THEN 255 
  END,
  `position` = CASE id
    WHEN 1 THEN 5 
  END

 

+1 for this solution.

Agreed. :-P

 

Jazzman this is one of the best posts I've seen you make and I mean that as a compliment not as a back-handed insult. :-P

[jazzman1]

I really appreciate your responses guys 

[zetcoby]

thank you for your help