MySQL select all and update specific column fields based on another column

Question

I am trying to select all rows from a table, and update a column's values within the same table (basically an automatic slug/permalink rewrite of a title column). However, when I do that, it just inserts the same "slug/permalink" rewrite from the very first ID in all of the rows, instead of rewriting and inserting the title for each row individually.

<?php
$sql = "SELECT * FROM titles"; 
$query = mysqli_query($con, $sql) or die (mysqli_error());
while ($row = mysqli_fetch_array($query)) { 
    $id = $row["id"];
    $title = $row["title"];

    // title rewrite function goes here
    $permalink = preg_replace ..... etc

    //add new permalink rewrite to slug column
    $sql2 = "UPDATE titles SET permalink='$permalink' WHERE id='$id'"; 
    $query2 = mysqli_query($con, $sql2) or die (mysqli_error());
} 
?>

Currently, the outcome is as follows:

ID   Title              Permalink
1    This is Title 1    this-is-title-1
2    This is Title 2    this-is-title-1
3    This is Title 3    this-is-title-1

I have also tried it without the WHERE id='$id' part, but that does not change anything ...

Any suggestions much appreciated ! Thanks

1. What is returned if you echo the query $sql2? 2. What's the value (result) of $query2 ? — zeflex, Dec 02 '13 at 22:18
If a simple "preg_replace" (one that can be done with str_replace and concat) then you can do all in one SQL query. 'UPDATE titles SET permalink = CONCAT("http://mysite/", title, ".", id)' — Robbie, Dec 02 '13 at 22:21
@zeflex: i have updated the question to contain the current outcome how it looks at the moment — rainerbrunotte, Dec 02 '13 at 22:27

score 0 · Answer 1 · edited May 23 '17 at 11:56

If all you need out of your table for your permalink function is ID and title, only select those fields (avoid select *).

$sql = "SELECT id, title FROM titles";

Also, if you can write the permalink function in SQL, you can accomplish this in one UPDATE statement instead of retrieving data, repeating logic, and updating one record at a time. For example:

UPDATE titles set permalink = REPLACE(REPLACE(REPLACE(title," ","-"),".",""),",","")

If it is more complicated than a couple of string replaces, you could write a stored routine or stick with your original approach. Can you share what preg_replace is doing? The error may be in that block of code you omitted.

Finally, watch for SQL Injection. Executing a SQL statement with the $permalink wrapped in single quotes is susceptible to a SQL Injection attack. Consider using a prepared statement: How Can I Prevent SQL Injection in PHP?

Thanks, Lou

Thanks Lou for your comment. I have actually used the function from Philipp, however thanks for your support! I will look into the SQL injection prevention, thanks for the link! — rainerbrunotte, Dec 04 '13 at 10:37

score 0 · Accepted Answer · edited May 23 '17 at 12:19

Are you sure, your $permalink assignment is correct? Based on your wanted output, I created a snippet for the permalink and tested the code(also corrected the mysqli_error function..)

$sql = "SELECT * FROM titles";
$query = mysqli_query($con, $sql) or die (mysqli_error($con));
while ($row = mysqli_fetch_array($query)) {
    $id = $row["id"];
    $title = $row["title"];

    // title rewrite function goes here
    //$permalink = preg_replace ..... etc
    $permalink = preg_replace('~\s+~', '-', strtolower($title));

    //add new permalink rewrite to slug column
    $sql2 = "UPDATE titles SET permalink='$permalink' WHERE id='$id'";
    $query2 = mysqli_query($con, $sql2) or die (mysqli_error($con));
}

This code works fine for me. If this doesn't create the correct output, something might be wrong with your database table data

Btw. you could use the sql procedure from https://stackoverflow.com/a/8419128/1043150 and break the wohle code down to a single sql statement

UPDATE titles SET permalink = slugify(title)

thanks Philipp. Your code worked perfectly. I noticed that the "mess" came from the clean URL function that I used (https://gist.github.com/chluehr/1632883). I have adapted the clean URL rewrite and now it works like a charm, thanks for your help! :) — rainerbrunotte, Dec 04 '13 at 10:36

MySQL select all and update specific column fields based on another column

2 Answers2

Linked