0

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!

Will Hay
  • 11
  • 3
  • If you're not able to execute, you should also log the value returned by [`mysqli_stmt_error()`](http://php.net/manual/en/mysqli-stmt.error.php) – Qirel Aug 11 '17 at 13:24
  • Thanks, I added error_log($conn->error) after the if(!$update_stmt->execute()){ line and it gave me: "Commands out of sync; you can't run this command now" – Will Hay Aug 16 '17 at 10:33
  • That means you (most likely) have to close the previous statement before running a new one. – Qirel Aug 16 '17 at 13:04

1 Answers1

0

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

Will Hay
  • 11
  • 3