Page 1 of 1

MYSQL insert id in url

Posted: Fri Apr 15, 2011 2:49 pm
by blade_922
Hi,

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?

Code: Select all

<?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';

?>


Re: MYSQL insert id in url

Posted: Sat Apr 16, 2011 3:39 am
by social_experiment
It would be easier to use a while loop or you will just insert one row into your table.

Code: Select all

<?php

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


$result = mysql_query("SELECT * FROM ccms_gameindex ORDER BY id ASC") or die(mysql_error());  
while ($resultObj = mysql_fetch_object($result)) {
 $value = 'images/uploads/' . $resultObj->id . '.jpg';
 $query = "INSERT INTO ccms_gameindex (boxart) VALUES 
('" . mysql_real_escape_string($value) . "')";
 $sql = mysql_query($query);
}

include 'closedb.php';

?>

Re: MYSQL insert id in url

Posted: Sat Apr 16, 2011 9:06 am
by blade_922
Hi Social_Experiment,

Thanks. I did just what you suggested and loaded the page that code was on. Checked the DB and there were no URL's updated. Also, around 1000 urls have already been entered so i dont want those to be changed. Only where the box column is NULL should they be filled with the url.

I would have thought the code you adjusted would have worked. Am i missing something?

Re: MYSQL insert id in url

Posted: Sat Apr 16, 2011 10:23 am
by blade_922
I managed to echo out all the urls which are perfect, they just arent storing into the mysql DB

Re: MYSQL insert id in url

Posted: Sat Apr 16, 2011 10:39 am
by blade_922
Im trying as hard as i can, im at the point the url is saved into the DB but as a new row and, for some reason the script ended up creating 20000 rows or so! So i figured INSERT INTO will create a new row, so have changed it to UPDATE to update the row, because the row already exists.

So im at this point just now. BUT it wont update the box column on each row in the DB, but it echo's out onto the page okay for some reason

Code: Select all

<?php

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


$result = mysql_query("SELECT * FROM ccms_gameindex WHERE box='' ORDER BY id ASC") or die(mysql_error());  
while ($resultObj = mysql_fetch_object($result)) {
 $value = 'images/box/' . $resultObj->id . '.jpg';
 $query = "UPDATE ccms_gameindex (box) VALUES 
('" . mysql_real_escape_string($value) . "')";
 $sql = mysql_query($query);
echo $value;
echo "</br>";
}

include 'closedb.php';

?>
 

Re: MYSQL insert id in url

Posted: Sat Apr 16, 2011 11:29 am
by blade_922
Thank you Social_Experiment for pointing me in the right direction, i have worked it out.

Thank you.