Moving files & editing mysql

I need some help with a php script.

I have a Mysql database with 8000 picture links in. The database keeps the picture name, number, and file name. The problem is my new host only allows 1000 inodes per folder which means i need to split up my image folder into 8 new ones. I dont want to edit 8000 database records so is there an easy way of doing this?

 

That's asinine... Get a new host.

 

Can you post a sample of your database structure with at least two entries?

 

without changing file path in database, how can you determine which file is stored in what folder?

and it will be overhead to keep looking for file's location after retrieving path from database. Do one time process and change it in database itself is what i would suggest

what you can do is, order picture name in ascending order and make 8 parts, move physical files in 8 different folders and using 8 update query, modify the path in database as well..

however, actual implementation may differ depending on database structure..

 

id	int(10) 		UNSIGNED	No		auto_increment	 	 	 	 	 	 	 
title	text	latin1_swedish_ci		No			 	 	 	 	 	 	 
description	text	latin1_swedish_ci	No			 	 	 	 	 	 	 
file	text	latin1_swedish_ci		No			 	 	 	 	 	 	 [/

Code (markup):

All the images in there at to moment show the file path as being image/filename.jpg, what I want to do it change this automatically to images1/filename.jpg or images2/filename.jpg.

 

well, since its not just about changing it in database, you should first take down list of files in ascending order.

manually move 1000 files in each folder and then use update queries to update path in database.

remember, the way we can sync table with physical file path is using ascending order of files which is not 100% accurate.

Best solution is to write a script, which reads file paths from database, moves actual file in table and then updates new path of that file.

 

I was thinking put 1000 file into images/, then a script can read the file name, move the file and change the mysql. Is this the bext way?

 

that would also do as far as it serves your purpose right.

 

OK, I have got a script together and its working, but I am having problems updating the mysql..

mysql_query("UPDATE images SET file = 'img01/' . $image WHERE file = 'img/' . $image");

Code (markup):

This doesn't work, I have useding " and ' and a range of other techniquies.
Any idea on the correct way

 

mysql_query("UPDATE images SET file = 'img01/$image' WHERE file = 'img/$image'");

Code (markup):

does that work?

 

Nope, I didn't think you can add variables into ' brackets.

 

Oh, I got it working now.
Thanks for the hint tho.

 

Well, your SQL statement is surrounded by double quotes, the single quotes inside of it are being sent to mySQL, not being interpreted by PHP. So, you're free to use variables inside of it.

 

I didn't know that.

Thanks for the advice

 

Ships!

I have balls it up. It should be img/img01/filename.
Is there a way to mass edit mysql to prefix "img/" to all entried?

 

surely you made a backup before you started changing things? otherwise try this in phpmyadmin but remember to backup first:

UPDATE images SET file = 'img/img01/'

Code (markup):

 

That will make his situation worse by deleting the image name entirely.

There's a CONCAT function in mySQL, which you can use to prepend strings to other strings. I've never used it before, but a quick look through google should help.