retrieving all images in a folder then placing them into a mysql database

[seany123]

Basically i have a folder with 100+ images they are NOT all the same extension, what im wanting to do is use PHP to find all the images and put them all in a database. how would i go about doing this?

 

thanks

[seany123]

Okay so i have this very basic code which displays all the images in my directory:

 

<?php 
$files = glob("uploads/*.*"); 
for ($i=1; $i<count($files); $i++) 
{ 
	$num = $files[$i]; 
	echo '<img src="'.$num.'" alt="random image">'."  "; 
}
?>

 

the first thing i noticed is that a couple of the images are broken, how can i remove these? secondly how can i change it so instead of echoing out the images instead its running a mysql query on them??

[seany123]

So i have added this to try and echo an error when it finds a not working image but it doesnt seem to be working:

 

<?php 
$files = glob("uploads/*.*"); 
for ($i=1; $i<count($files); $i++) 
{ 
	$num = $files[$i]; 	
	$filename = $num;

	if (file_exists($filename)) {
		echo '<img src="'.$num.'" alt="random image">'."  ";
	} else {
		echo "The file $filename does not exist";
	} 
}
?>

[PFMaBiSmAd]

You are probably getting 'broken' images for any folders within the folder and for the . and .. entries. If so, you would need to use is_file to test for actual files.

[seany123]

You are probably getting 'broken' images for any folders within the folder and for the . and .. entries. If so, you would need to use is_file to test for actual files.

 

thanks for the reply, i have found out that all the images that was broken had %s in the filename eg funny%monkey%looks%really%cool.jpg

 

which i think was the problem, so that problem is sorted but how do i know insert each image into the database...?

[PFMaBiSmAd]

You need to urlencode anything being put into a URL that might contain characters that are not normally permitted in a URL.

 

As to INSERTing the data into a database table, where exactly are you stuck at? You form an insert query with the data you want in it and execute it. If you want to save on the number of queries being executed, you would use one multi-value insert query.  Here's a link showing how to form a multi-value insert query - http://www.phpfreaks.com/forums/index.php?topic=354447.msg1674108#msg1674108

[Psycho]

Also, you need to be specific regarding ". . . how do i know insert each image into the database...?". That could mean two different things:

 

1. Save the "path" of the images in the database

2. Save the actual image "content" in the database.

 

I assume you mean #1. It is definitely the simplest solution. Assuming that is what you want you would need to decide on the structure of the table that you are saving the paths to. Typically, records are associated with other records. For example, the images might be associated with a user or some other object. That will be dependent upon how and what you are using the data for. So, it is not apparent to us what the structure should be.

 

But, let's assume the simplest scenario. You are going to have a table to store the image paths in and you want two fields in the table: image_id and image_path. We'll further assume that the image_id field is an auto-incrementing int field. So, we do not need to consider it when adding new records.

 

NOTE: for an array you should use foreach() not a for() loop

 

You could then create records for all the images using something such as:

//Set folder path
$folder = "uploads/"

//Get list of all records in foldeer
$images = glob("{$folder}*.*"); 

//Process data into an INSERT values
$insertValues = array(); //Temp array
foreach ($images as $img) 
{ 
    if(is_file($img))
    {
        $insertValues[] = '(' . mysql_real_escape_string($img) . ')';
    }
}

//If there were files, create complete INSERT query
$query = "INSERT INTO `table_name` (`image_path`) VALUES " . implode(', ', $insertValues);
$result = mysql_query($query);

if(!$result)
{
    echo "There was a problem insertng the records.";
    //Debug lines - not for production
    echo "<br>Query {$querey}";
    echo "<br>MySQL Error" . mysql_error();
}
else
{
    echo "Records were inserted successfully";
}

 

Note: I did not use urlencode() on the values being stored since I would personally store the values in their original format and make that change when outputting the values based on the needs.

[seany123]

Also, you need to be specific regarding ". . . how do i know insert each image into the database...?". That could mean two different things:

 

1. Save the "path" of the images in the database

2. Save the actual image "content" in the database.

 

I assume you mean #1. It is definitely the simplest solution. Assuming that is what you want you would need to decide on the structure of the table that you are saving the paths to. Typically, records are associated with other records. For example, the images might be associated with a user or some other object. That will be dependent upon how and what you are using the data for. So, it is not apparent to us what the structure should be.

 

But, let's assume the simplest scenario. You are going to have a table to store the image paths in and you want two fields in the table: image_id and image_path. We'll further assume that the image_id field is an auto-incrementing int field. So, we do not need to consider it when adding new records.

 

NOTE: for an array you should use foreach() not a for() loop

 

You could then create records for all the images using something such as:

//Set folder path
$folder = "uploads/"

//Get list of all records in foldeer
$images = glob("{$folder}*.*"); 

//Process data into an INSERT values
$insertValues = array(); //Temp array
foreach ($images as $img) 
{ 
    if(is_file($img))
    {
        $insertValues[] = '(' . mysql_real_escape_string($img) . ')';
    }
}

//If there were files, create complete INSERT query
$query = "INSERT INTO `table_name` (`image_path`) VALUES " . implode(', ', $insertValues);
$result = mysql_query($query);

if(!$result)
{
    echo "There was a problem insertng the records.";
    //Debug lines - not for production
    echo "<br>Query {$querey}";
    echo "<br>MySQL Error" . mysql_error();
}
else
{
    echo "Records were inserted successfully";
}

 

Note: I did not use urlencode() on the values being stored since I would personally store the values in their original format and make that change when outputting the values based on the needs.

 

Yes you understand what im trying to do, however i have a problem with the code you provided its showing this error:

 

Parse error: syntax error, unexpected T_VARIABLE on line 7:

 

which is this

 

//Get list of all records in foldeer
$images = glob("{$folder}*.*"); 

 

[Psycho]

As my signature state

I do not always test the code I provide, so there may be some syntax errors.

 

Any code I provide is typically meant as a guide to actually implementing it in your specific scenario. I expect that the person I am providing the code to is able to debug minor syntax errors.

 

In this case, the actual error is on a previous line

$folder = "uploads/"

where there is no closing semi-colon. It can be valid to have a statement span multiple lines and not have a semi-colon till the statement is complete. This was not one of those times. But, the PHP parser could not detect the error until line 7. This is normal. So you should always look at preceding lines when you get such an error.

[seany123]

As my signature state

I do not always test the code I provide, so there may be some syntax errors.

 

Any code I provide is typically meant as a guide to actually implementing it in your specific scenario. I expect that the person I am providing the code to is able to debug minor syntax errors.

 

In this case, the actual error is on a previous line

$folder = "uploads/"

where there is no closing semi-colon. It can be valid to have a statement span multiple lines and not have a semi-colon till the statement is complete. This was not one of those times. But, the PHP parser could not detect the error until line 7. This is normal. So you should always look at preceding lines when you get such an error.

 

sorry my apologies its because im messing around with 2 scripts at once:

 

the code is now giving another error:

 

There was a problem insertng the records.

Query

MySQL ErrorUnknown column 'uploads' in 'field list'

 

i have setup my mysql database with

table name: uploads

1st column: id

2nd column: url

 

and have set that in the script.

[seany123]

i wasn't able to get what you posted to work, however i was able to mess around with my code and get it to work:

 

<?php
require("header.php"); //Holds the code for connecting to db
$files = glob("uploads/*.*"); 
for ($i=1; $i<count($files); $i++) 
{ 

	$num = $files[$i];
	$sql="INSERT INTO uploads (url) VALUES ('$num')";


		if (!mysql_query($sql))
		  {
		  die('Error: ' . mysql_error());
		  }

	//echo '<img src="'.$num.'" alt="random image">'."  ";
}
?>

 

Could you help improve this code?

 

thanks

[PFMaBiSmAd]

Do you just want the image filename.ext and not the uploads/ path to be inserted into the database table?

[seany123]

Basically what im trying to achieve is a personal image upload website, but instead of using an upload php script I want to upload my images straight to the folder via FTP, then have a script insert each image into the mysql database with a unique ID so only people with the ID can view the image...

 

the script i have posted does insert all the images into the data like this

/uploads/imagename.jpg

I'm wondering if i should put the entire path or keep it as is or just have the imagename.jpg inside the database.

 

also how secure is the script i have already posted?

 

EDIT: yeah upon thinking maybe it would be best to just have the filename.ext

[PFMaBiSmAd]

Sample code that would just insert the filename.ext -

 

<?php
require("header.php");
$path = "uploads/";
$files = array_map('mysql_real_escape_string',array_map('basename',array_filter(glob("{$path}*.*"),'is_file')));
if(empty($files)){
echo "There were no matching files to insert into the database.";
} else {	
$query = "INSERT INTO uploads (url) VALUES ('" . implode("'),('",$files) . "')";
if(!mysql_query($query)){
	echo "There was a problem inserting the data.";
	trigger_error("Query failed: $query<br />Error: " . mysql_error());
} else {
	echo "The data was inserted successfully.";
}
}

[seany123]

This is really nooby question but how can i also insert the variable $nextval into the mysql straight after inserting the url?

 

$query = "INSERT INTO uploads (url) VALUES ('" . implode("'),('",$files) . "')";

 

i have tried this

 

$query = "INSERT INTO uploads (url,nextval) VALUES ('" . implode("'),('",$files) . "'), ('$nextval')";

 

but that doesnt work....

[PFMaBiSmAd]

The link I posted in reply #5 in this thread shows how you would insert multiple values/variables in each row of the multi-row insert query.

[Psycho]

Each record must be separated into it's own 'complete' record. So you will need to prepare the data before creating the query

 

$insertValues = array();
foreach($files as $file)
{
    $insertValues[] = "('{$file}', '{$nextval}')";
}$query = "INSERT INTO `uploads` (`url`, `nextval`) VALUES " . implode(', ', $insertValues);

[seany123]

Okay thanks, i got that working good, now im thinking i would like to have the directory included in the now instead of just the filename.ext how can i modify this script to do that??

 

 

Sample code that would just insert the filename.ext -

 

<?php
require("header.php");
$path = "uploads/";
$files = array_map('mysql_real_escape_string',array_map('basename',array_filter(glob("{$path}*.*"),'is_file')));
if(empty($files)){
echo "There were no matching files to insert into the database.";
} else {	
$query = "INSERT INTO uploads (url) VALUES ('" . implode("'),('",$files) . "')";
if(!mysql_query($query)){
	echo "There was a problem inserting the data.";
	trigger_error("Query failed: $query<br />Error: " . mysql_error());
} else {
	echo "The data was inserted successfully.";
}
}

[PFMaBiSmAd]

Just remove the part of the code that is applying the basename() function to the array of files.

[seany123]

Thankyou for all the help. yet again phpfreaks and the people here have been absolutely fantastic i really appreciate everything that this forum does for me!