How do i store array of image names dynamically into the database using PHP

Question

Hi there,

i am using the jQuery Plugin Uploadify to upload files, and i store the image name with path into the database with simple mysql query and then retrieve it.

my MySQL database table is using the following entities,

id int(20) 
image_url varchar(255)

my image store procedure is such as, i first rename the file, give it a unique name and move it to the desired directory. and then store the image name with path into the database.

currently by using simple mysql INSERT statement i am storing a single file name.

now i want to upload the array of image names with path. or i want to store as much image as i want dynamically into the single table col image_url, for some reason i plan to store 4 or more images, i want all the four image to have one particular id number.

how do i do it?

thank you

Answer 1

Edit 2
I removed the previous scripts, here's a new one:

basically what i want to achieve is when inserting into the database i would like to store the image path with names as arrays. so that i could store as much images i want into a particular id, for example an id 1 will have 3 images, id 2 will have 5 images , id 3 will have 4 images and so on

Best way to do this is to add a separate entry for each image url. You can also use a single entry and store all the urls as an array, but since you're storing this in a database that isn't very useful. Storing each entry separately makes it a lot easier to access and update the data.

Also, make sure the "id" field in your table isn't a primary key / auto incrementing. Otherwise you won't be able to use the same id more than once. I fixed that here:

// array with your images
$images = array('url1','url2','url3');

// determine a new id
$sql = "SELECT MAX(id) FROM images";
$res = mysql_query($sql);
list($id) = mysql_fetch_row($res);

// highest current id, plus one
$id++;

// now create a values array to insert in the database
$values = array();

foreach ($images as $image) {
  $values[] = "($id, '".mysql_real_escape_string($image)."')";
}

// comma-separate the values
$values = implode(',',$values);

// run the query
$sql = "INSERT INTO images (id, image_url) VALUES $values";
mysql_query($sql);

If you do want to store arrays, it's best to serialize the data and use unserialize when you retrieve it. Example:

$images = array(...);

$images = serialize($images);

$sql = "INSERT INTO images (image_url) VALUES ($images)";

To add images, you should then first retrieve the data, unserialize the array, update the array with the new images, serialize it again, update the database entry.

Answer 2

It's not normalized well at all, but you could do this:

<?php

// ... your code here ...

try {
 $dbh = new PDO( 'mysql:dbname=' . $your_database_name . ';host=' . $your_database_host, $your_database_username, $your_database_password );
} catch ( PDOException $e ) {
 throw new Exception( 'Database connection failed: ' . $e->getMessage() );
}

$stmt_insert = $dbh->prepare("INSERT INTO images('url_array') VALUES (:image_array_json)");
$stmt_insert->execute( array(':image_array_json', json_encode( $image_array )) );

// ... et cetera ...

?>

... and to retrieve the array (assuming you know the image_id):

<?php

// ... your code here ...

try {
 $dbh = new PDO( 'mysql:dbname=' . $your_database_name . ';host=' . $your_database_host, $your_database_username, $your_database_password );
} catch ( PDOException $e ) {
 throw new Exception( 'Database connection failed: ' . $e->getMessage() );
}

$stmt_retrieve = $dbh->prepare("SELECT url_array FROM images WHERE image_id=:image_id");
$stmt_retrieve->execute( array(':image_id', $image_id) );

$ret = $stmt_retrieve->fetch( PDO::FETCH_ASSOC );

$image_array = json_decode( $ret['url_array'] );

// ... et cetera ...

?>