update multiple

[this_is_me]

I have a table which does not have one field as a uniqe key in mysql but two:
The PRIMARY KEYs are together
categories_id
language_id
because the categories_id is a foreign key, belonging to the parent table. The language_id is numeric as well.
Both are not auto_incremental, but are written into the table.
In this way, web assists "Multiple Record Inserts" is working properly.
Not so with Update Multiple Records:
If I try to bind categories_id as the categories_id (which is only half of the truth), updates simply don't work. There is also no error message.
How shall I proceed?

[Ray Borduin]

Maybe create a view of the joined columns so that they can be treated as a single column.

[this_is_me]

Maybe create a view of the joined columns so that they can be treated as a single column.

sorry, I am at loss. This is php/mysql server model. How do I create a "view of the joined columns"? Did I miss something?

[Ray Borduin]

What version of mySQL? mySQL 5 supports the use of views. There is a function in php called CONCATINATE that will join column values into a single column.

[this_is_me]

Oh, I see. I thought there is a hidden function in Dreamweaver or DA

[this_is_me]

i did this:
// WA DataAssist Multiple Updates
....
$WA_indexField = "CONCAT(categories_id,language_id)";
....

is there something else which needs changing?
any advice welcome!

[Ray Borduin]

You would have to debug it. It may have SQL injection code preventing complex values like that to be passed in directly. This is not part of our testing, so it isn't really directly supported in this way. You may need to do some hand edits to get something like that working properly. Write the resulting SQL statement to the page and see if it writes what you would expect. It may work I just haven't tried that technique which is why I recommended creating a view so the concatinated fields appeared as a single column.

[this_is_me]

You would have to debug it.

I think, I should not. I'd rather suggest, DA should update its extension to provide this feature, BECAUSE:

Even with update single record, it does not work.
BUT Dreamweaver's basic "update record" server behaviour recognizes the two primary fields immediately and does a correct job.:

If my update form looks something like

<form method="POST" name="update_descr">
<input name="language_id" type="hidden" value="<?php echo $_GET['sp']; ?>" />
<input name="category_id" type="hidden" value="<?php echo $_GET['catid']; ?>" />
<input name="language_description" type="text" value="<?php echo $_GET['inhalt']; ?>" />
<input type="submit" name="update" id="update" value="update" />
</form>

Dreamweavers built in update behaviour does this:

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "update_descr")) {
$updateSQL = sprintf("UPDATE categories_description SET categories_name=%s WHERE categories_id=%s AND language_id=%s",
GetSQLValueString($_POST['language_description'], "text"),
GetSQLValueString($_POST['category_id'], "int"),
GetSQLValueString($_POST['language_id'], "int"));

mysql_select_db($database_string, $string);
$Result1 = mysql_query($updateSQL, $string) or die(mysql_error());
}

// ....

shortly: Dreamweaver does recognize both PRIMARY key fields and correctly updates the record.
But Dreamweaver only understands "update single record behaviour".

My question is, why does DATA ASSIST's not even offer this functionality in its "update single record" not to mention the multiple one?
(I am only offered to select a single id as primary key here.)

Because that was the reason I bought this extension. To go beyond Dreamweavers basic functions.

Again this is the table structure:

CREATE TABLE IF NOT EXISTS `categories_description` (
`categories_id` int(11) NOT NULL default '0',
`language_id` int(11) NOT NULL default '1',
`categories_name` varchar(64) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`categories_id`,`language_id`),
KEY `idx_categories_name` (`categories_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Please don't ask me to hack DA server behaviour but show me the right way!

[Ray Borduin]

I believe the correct way is to define the view in your mySQL database as I have contended. Thank you for pointing out this missing feature. You are the first to report it, so it isn't very common for our users to deal with multiple primary keys. I will add it as a feature suggestion for the next release, but no amount of hand waving will make it work the way you want. You either need to create a view in the mySQL database or possibly debug and hack your way through adding this feature to DataAssist because the feature does not exist currently.

[this_is_me]

Thank you for insisting on creating a "VIEW". You meant it litterally. This I did not understand.
Yes, I think this is the best solution

CREATE VIEW categories_description_v AS SELECT categories_id, language_id, CONCAT(categories_id, language_id) AS mainkey, categories_name FROM categories_description;

I guess, thats what you had in mind.
And it works without hacking DA's extension. Still, as a suggestion: do include it in a further update because some servers don't allow creating views.

Thanks again for your assistance!

[Ray Borduin]

And to think it was all there in my first reply the whole time ;) Glad to hear that it worked for you and I appreciate the feedback.