Hello,
i search a solution for Update with a jointure on lookup_table pages_tag a value with one or more value. If one value for $tag, the Update is ok.
But if there are more value, it doesn 't match. And it’s normal, because the value aren’t understand.
What is the soluce.
Thanks for advice
cordialy
$q="UPDATE pages, pages_tag SET pages.user_id=?,pages.cat_p_id=?,pages.status=?,pages.title=?,pages.summary=?,pages.ingredient=?,pages.recipe=?,pages.tips=?,pages.filename=?, pages_tag.tag_id=? WHERE pages.page_id=$page_id AND pages.page_id = pages_tag.page_id";
$stmt = mysqli_prepare($dbc,$q);
mysqli_stmt_bind_param($stmt, 'iisssssssi',$_POST['user_id'],$_POST['category'],$status, $title,$summary,$ingredient,$recipe,$tips,$filename, $tag);
$allowed='<div><p><span><br><a><img><h1><h2><h3><h4><ul><ol><li><blockquote>';
$status= strip_tags($_POST['status']);
$title= strip_tags($_POST['title']);
$summary= strip_tags($_POST['summary'], $allowed);
$ingredient= strip_tags($_POST['ingredient'], $allowed);
$recipe= strip_tags($_POST['recipe'], $allowed);
$tips= strip_tags($_POST['tips'], $allowed);
$filename = strip_tags($_POST['filename']);
//->
$tag = implode(',', $_POST['tag']);
mysqli_stmt_execute($stmt);
if(mysqli_stmt_affected_rows($stmt) >= 1) {
mysqli_stmt_close($stmt);
$_POST = array();
echo'<div class="alert alert-success"><h3>Votre page à bien été modifié.</h3></div>';
}else{
trigger_error('Systeme Error, Page non ajoutée.');
}
}//END if array existe update
}
A quick tip, when writing queries in code, use the “leading commas” convention, using your query as it is for an example:
UPDATE
pages
, pages_tag
SET
pages.user_id = ?
, pages.cat_p_id = ?
, pages.status = ?
, pages.title = ?
, pages.summary = ?
, pages.ingredient = ?
, pages.recipe = ?
, pages.tips = ?
, pages.filename = ?
, pages_tag.tag_id = ?
WHERE
pages.page_id = $page_id
AND
pages.page_id = pages_tag.page_id
which is easier to read then
[COLOR=#000000][COLOR=#DD0000]UPDATE pages, pages_tag SET pages.user_id=?,pages.cat_p_id=?,pages.status=?,pages.title=?,pages.summary=?,pages.ingredient=?,pages.recipe=?,pages.tips=?,pages.filename=?, pages_tag.tag_id=? WHERE pages.page_id=[/COLOR][COLOR=#0000BB]$page_id[/COLOR][COLOR=#DD0000] AND pages.page_id = pages_tag.page_id[/COLOR][/COLOR]
and allows errors and mistakes to be spotted more easily.
What error is MySQL giving?
Hello,
it’s right for the presentation.
The error message is : array
extract of error :
[stmt] => mysqli_stmt Object
(
[affected_rows] => 0
[insert_id] => 0
[num_rows] => 0
[param_count] => 10
[field_count] => 0
[errno] => 0
[error] =>
[error_list] => Array
(
)
[sqlstate] => 00000
[id] => 1
)
My print_r($_POST);
and the problem is on $tag .
How attribute for the table “pages_tag”
with index ( page_id, tag_id)
tag_id ( as $tag) to the page_id
if think about Merge function ?
Thanks to advices
Array
(
[status] => live
[title] => test page BBcw
[category] => 1
[tag] => Array
(
[0] => 1
[1] => 2
[2] => 4
[3] => 3
[4] => 5
)
[summary] => rrr
[ingredient] => rrr
[recipe] => rrr
[tips] => rrr
[filename] => vegetarien2014-07-31-190258.jpg
[user_id] => 27
[page_id] => 184
[update] => update
)
Change
mysqli_stmt_execute($stmt);
to
$result = mysqli_stmt_execute($stmt);
if ( $result === false ) {
printf("Error: %s.\
", mysqli_stmt_error($stmt));
}
then try one of the updates that is failing
Ok, i tested an there are no error .
because the update is done but in the true check
if one tag_id = 3 is checked, for the Update one value is registred in bd -> page_id=183, tag_id=3
but if i check 3 tag_id like tag_id= (1, 2, 3);
the Update is right but report the same value for all tag_id in the bd-> page_id=184, tag_id=1 ; page_id=184, tag_id=1 ; page_id=184, tag_id=1
Doing a little digging it looks like MySQL doesn’t like the join of a multi-table update being in the WHERE clause so it’ll nee to be moved, eg
UPDATE table1 t1 INNER JOIN
table2 t2 ON t2.id = t1.id
SET t1.value = t2.value