Will Hay Will Hay - 1 year ago 52
MySQL Question

How can I pull records out of the database, adjust them and insert them into a different column?

I need to pull story names out my database table (story), change them to url friendly format (Game of Thrones --> game-of-thrones) and insert them into a different column in the same table (story.permalink). Is there a good way to do this? My current php function which I'm running from mac terminal isn't working.

$conn = update_dao_connect();
$get_stmt = $conn->prepare("select id, name from story");
$update_stmt = $conn->prepare("update story set permalink=? where id=?");
$update_stmt->bind_param("si", $permalink, $id);
if($get_stmt->execute()){
$get_stmt->bind_result($id, $name);
while($get_stmt->fetch()){
$permalink = generate_story_permalink($name);
if(!$update_stmt->execute()){
error_log("update permalink failed for kamp id: ".$id.", name: ".$name.", permalink: ".$permalink);
}
}
}
else {
error_log("execute get all story for copy_story_names_to_permalinks fail");
}


terminal output (for all records in database):

update permalink failed for story id: 198, name: Funding Circle's story, permalink: funding-circles-story

update permalink failed for story id: 199, name: Rentah, permalink: rentah-1

update permalink failed for story id: 200, name: Kano Computing, permalink: kano-computing

update permalink failed for story id: 201, name: Outplacement, permalink: outplacement-1

Thank you in advance!

Answer Source

The solution is to add $get_stmt->store_result(); after $get_stmt->execute(); like in this answer Commands out of sync; you can't run this command now

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download