Can't get mysql transaction to complete

[mbhanley]

Does anyone know a better way of getting this code to execute the mysql queries.

I am trying to Insert table records to a new table then update it.

so I need to call the table cms_homecontent first then insert those values to the new table cms_homecontent_versions and also ignore the id so that it is null and it can auto increment.

Then once that has completed continue updating cms_homecontent table with the data that was submitted.

Any help would be greatly appreciated.

function update_homecontent($p) {
$id = mysql_real_escape_string($p['id']);
$pageorder = mysql_real_escape_string($p['page_order']);
$page = mysql_real_escape_string($p['page']);
$title = mysql_real_escape_string($p['title']);
$body = mysql_real_escape_string($p['body']);
if(!$title || !$body):
if(!$title):
echo '<div class="bad">The title is required!</div>';
endif;

if(!$body):
echo '<div class="bad">The body is required!</div>';
endif;

echo '<p><a href="update-home-content.php?id=' . $id . '">Try Again</a></p>';

else:

$sql=("SELECT * FROM cms_homecontent WHERE id = '$id'");
$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) != 0):
while($row = mysql_fetch_assoc($res)) {
$old_id= $row['id'];
$old_page_order= $row['page_order'];
$old_page= $row['page'];
$old_title= $row['title'];
$old_body= $row['body'];

$id = mysql_real_escape_string($id);

$sql .= (mysql_query("begin"));
$sql .= (mysql_query("INSERT INTO cms_homecontent_versions(id, page_order, page, title, body, timestamp) 
VALUES(null, '$old_page_order', '$old_page', '$old_title', '$old_body', NOW()) SELECT * FROM cms_homecontent WHERE id='$id';"));
$sql .= (mysql_query("UPDATE cms_homecontent SET page_order = '$pageorder', page = '$page', title = '$title', body = '$body', timestamp = NOW() WHERE id ='$id';"));
if (mysql_error()) {
$sql = (mysql_query("rollback"));
}
else {
$sql = (mysql_query("commit")); 
}

echo "<div class='good'>Content Updated Successfully</div>";
}
endif;
endif;
}

[LastMitch]

@mbhanley

Does anyone know a better way of getting this code to execute the mysql queries.

I stop using MYSQL and I'm using MYSQLI. Just update the query. So it will be easier to update and managed! Plus you get more errors in your other files too.

Read this about mysql_query():

http://php.net/manual/en/function.mysql-query.php

Read this aboutmysql_real_escape_string():

http://php.net/manual/en/function.mysql-real-escape-string.php

What CMS are you working?

[mbhanley]

Thanks for that Im looking through the manual now,

I am working on a CMS that I have built from scratch it has some awesome features already.

I included features like a url rewrite and also opens the main files in editarea so that the main temlpate can be hardcoded.

I have also made a trash table so that if any page is deleted it can be recovered from the trash hence the reason I want to be able to get old saved versions so that changes made can be reverted when anyone updates a page.

Just having a problem getting the insert and update to work wright it updates but skips the insert part :/

[LastMitch]

@mbhanley

I am working on a CMS that I have built from scratch it has some awesome features already.

I included features like a url rewrite and also opens the main files in editarea so that the main temlpate can be hardcoded.

I have also made a trash table so that if any page is deleted it can be recovered from the trash hence the reason I want to be able to get old saved versions so that changes made can be reverted when anyone updates a page.

Just having a problem getting the insert and update to work wright it updates but skips the insert part :/

Wow you create your own CMS. That's good. The only bad part that you are still using MYSQL? You have alot of updating from MYSQL to MYSQLI. That is alot of work.

Your query is very confusing here:

$sql .= (mysql_query("begin"));

$sql .= (mysql_query("INSERT INTO cms_homecontent_versions(id, page_order, page, title, body, timestamp)
VALUES(null, '$old_page_order', '$old_page', '$old_title', '$old_body', NOW()) SELECT * FROM cms_homecontent WHERE id='$id';"));

$sql .= (mysql_query("UPDATE cms_homecontent SET page_order = '$pageorder', page = '$page', title = '$title', body = '$body', timestamp = NOW() WHERE id ='$id';"));

You need to change the query

Once you update this file you need to update the files that are connected to this one.

[mbhanley]

I made some changes as suggested just starting to understand and get the hang of mysqli
but I still must be missing something as the transaction still does not complete it
exicutes the update query but not the first insert query I must be missing something :/

function update_homecontent($p) {
$id = mysql_real_escape_string($p['id']);
$pageorder = mysql_real_escape_string($p['page_order']);
$page = mysql_real_escape_string($p['page']);
$title = mysql_real_escape_string($p['title']);
$body = mysql_real_escape_string($p['body']);
if(!$title || !$body):
if(!$title):
echo '<div class="bad">The title is required!</div>';
endif;

if(!$body):
echo '<div class="bad">The body is required!</div>';
endif;

echo '<p><a href="update-home-content.php?id=' . $id . '">Try Again</a></p>';

else:


$id = mysql_real_escape_string($id);
include './inc/connect.inc.php';
$query = "SELECT * FROM cms_homecontent WHERE id='$id'";
if ($result = $mysqli->query($query)) {
while ($row = $result->fetch_assoc()) {
$old_id= $row['id'];
$old_page_order= $row['page_order'];
$old_page= $row['page'];
$old_title= $row['title'];
$old_body= $row['body'];
//
$mysqli->query("begin");
$mysqli->query("INSERT INTO cms_homecontent_versions(id, page_order, page, title, body, timestamp) 
VALUES(null, '$old_page_order', '$old_page', '$old_title', '$old_body', NOW()) SELECT * FROM cms_homecontent WHERE id='$id';");
$mysqli->query("UPDATE cms_homecontent SET page_order = '$pageorder', page = '$page', title = '$title', body = '$body', timestamp = NOW() WHERE id ='$id';");
if (mysql_error()) {
$mysqli->query("rollback");
}
else {
$mysqli->query("commit"); 
}
}
$result->free();
}
$mysqli->close();

echo "<div class='good'>Content Updated Successfully</div>";

endif;
}

[LastMitch]

@mbhanley

You have alot of updating from MYSQL to MYSQLI. That is alot of work.

I think I confused you. When I said updating from MYSQL to MYSQLI, it means update ALL of your files not just this file.

You can't used both MYSQL & MYSQLI function at the same time. It won't work at all! It has to be either all MYSQL query in all of your files or all MYSQLI query in all of your file.

Are you getting any errors? There should be an error base on the changes.

[mbhanley]

I have updated most of the file and everything except this query works.

$mysqli->query("INSERT INTO cms_homecontent_versions(id, page_order, page, title, body, timestamp) 
VALUES(null, '".$old_page_order."', '".$old_page."', '".$old_title."', '".$old_body."', NOW()) SELECT * FROM cms_homecontent WHERE id='".$id."';");

I used:

echo $mysqli->error;

To display the error and it returns.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM cms_homecontent WHERE id='1'' at line 2

I also have echo $old_page_order; etc to see if all variables are indeed being sent and they are so I think its the query its self more than anything.

I can use:

INSERT INTO cms_homecontent_versions SELECT * FROM cms_homecontent WHERE id='$id';

And it will work except what I am trying to prevent is the error of having a duplicate id entry and so hence why I need to use Insert INTO VALUES

[LastMitch]

@mbhanley

I don't have a DB nor a table in front of me. So it's hard for me to see the issue basically read this I know you udnerstand the query but I need to see what INSERT you are trying to used:

http://www.techonthenet.com/sql/insert.php

Tell me which INSERT is suited for your situation.

Let me know what changes base on the examples.

[mbhanley]

I couldn't see it on the website.

Its more like;

INSERT INTO table1 (f1, f2, f3)
SELECT 'a', 'b', field FROM table2 WHERE id = 1;

But nothing I try seems to work :/

[LastMitch]

And it will work except what I am trying to prevent is the error of having a duplicate id entry and so hence why I need to use Insert INTO VALUES

Have you try subquery?

    INSERT INTO cms_homecontent_versions (column1, column2, column3)
    SELECT id, NOW() cms_homecontent
    WHERE id = '$id'

You can read it more here:

http://www.techonthenet.com/oracle/subqueries.php

Since you are trying to INSERT to the DB do it in MYPHPADMIN

INSERT INTO `DB`.`table` (`f1`, `f2`, `f3`) VALUES ('1', '2', '3') 

UPDATE table1, table2, table3 AS `a` SET table1.id = 1, table2.val = 2,

[mbhanley]

At last I have figured it out I was making things more complicated than they needed to be
the following query worked;

$mysqli->query("INSERT INTO cms_homecontent_versions (page_order, page, title, body, timestamp) SELECT page_order, page, title, body, timestamp FROM cms_homecontent");

So now if I run the following queries:

$mysqli->query("begin");
$mysqli->query("INSERT INTO cms_homecontent_versions (page_order, page, title, body, timestamp) 
SELECT page_order, page, title, body, timestamp FROM cms_homecontent");
$mysqli->query("UPDATE cms_homecontent SET page_order = '$pageorder', page = '$page', title = '$title', body = '$body', timestamp = NOW() WHERE id ='$id';");
if (mysqli_error($mysqli)) {
$mysqli->query("rollback");
}
else {
$mysqli->query("commit"); 
}

it moves the original table data to the new table
and then continues to update the original table sweet :)

Thanks LastMitch your a diamond.