Create URL and insert into mysql

i,

I have around 5000 rows in mysql each with unique i.d. Now a field in each row caled boxart holds the url to an image i.e

images/uploads/abcdef.jpg

Now i have had a brainwave to save me alot of time. I have around 3500 images to upload, so i will upload these via FTP instead of individually. Each image is uniquely identified by a 4 digit number which is the same as the row in the DB that the url will be on.

So for example row 4543 has an image 4543.jpg to be uploaded and the following url images/uploads/abcdef.jpg to be inserted into the url. The images/uploads/ is the same directory for all of the images. Only diff is the image name which is the ending of the url. All images are jpg

Now could i insert the url for all the rows by only SQL? So just do it in phpmyadmin? Or would i have to create a php script?

Would the below work?

<?php

include 'config.php';
include 'opendb.php';


$result = mysql_query("SELECT * FROM ccms_gameindex ORDER BY id ASC") 
or die(mysql_error());  

INSERT INTO ccms_gameindex (boxart) VALUES ("images/uploads/".$id.".jpg")



include 'closedb.php';

?>

PHP:

 

Well if the id and image have the same value you don't have to insert it to the DB, just make the url each time you want to work with it. this way is easier and better since even if you had to change the location of all the images you'd still be able to make the url without changing the database!

<?php
...
//by this point in code you have the id in say $row[id] and you want to show the image
$url="images/uploads/".$row[id].".jpg";
echo '<img src="'.$url.'" alt=""/>'
...

?>

PHP:

 

Either you do as Sepehr suggests or use the concat function in MySQL to create the URLs to insert into the db. See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

 

No idea how to. You can search on google for mysql support forums.

 

Hi Sepehr,

Only issue is i have already uploaded around 1000 images that arent like this. They are named all different and dont contain the ID.

 

I thought you said the ID and filename are the same.

If your problem is that you have a folder of images and want to insert their name into the database without entering each and every filename, you can use the scandir() function to get the filenames and then create an SQL query to insert all of them into your database!