CODEWITHSUNDEEP
mysqlsql
Mia
Mia

Reputation: 2676

How to insert a character into a string in sql

So, I have a table that I wish to look like this

   id      |       path
----------- -------------------------
 000000    |   images/0/0/0/0/00.jpg
 000001    |   images/0/0/0/0/01.jpg
 000002    |   images/0/0/0/0/02.jpg

where id is an auto_increment number and path is a string. I wish to make the column path be auto_generated but I couldn't find a SQL function to do that. If I were to use Python, I can easily calculate path with this expression

ID = 1
path='images/'+'/'.join(str(ID).zfill(6))[::-1].replace('/','',1)[::-1]+'.jpg'

However, I have no idea how to do that with SQL. I understand that I can retrieve the ID from database and then use a script to calculate the path, but I think it may be better just to cache all the results in the database so that my script can work faster.

Update:

I can, of course, write a script to handle this, be it a js, Python or PHP. But since I don't know what the id would be before I insert a row, I need to do an INSERT, then SELECT, to find out the id; then I have to UPDATE the calculated path. In total this will be three queries. What I wanted to do was to simplify the whole thing into one query, for I'm worrying about performance issue.

Upvotes: 0

Views: 85

Answers (1)

André Piropo
André Piropo

Reputation: 35

I don't know if I understand correctly but it looks like you want that the auto_generated path looks like a id but separated by "/". If it's this why don't you use php str_split to convert id string in other strings, and this way you can generate the path automatically.

For example:

<?php
//first you must do a mysql select to display all fields
//this define your string
$str = $id;
//this cut string with a limit of 1 character
$arr = str_split($str, 1);

//define a string with the path format you want
$path = "images/$arr[0]/$arr[1]/$arr[2]/$arr[3]/$arr[4]/$arr[5]$arr[6].jpg";

//now do a mysql update
UPDATE db_name SET path=$path WHERE id=$id;

?>

And the result will be a path updated to, where id = 000001, images/0/0/0/0/01.jpg

Upvotes: 1

Related Questions