Reputation: 1274
How to Update same ID with different values in a single table?
I am trying to update a Table which has 3 images of each product. I have added a loop around the query and if I echo it, it shows me 3 queries each updating different images, but when I check the Products it shows me the last uploaded image in all 3 Images. I have understand the problem but unable to find the solution for it.
Output of Queries.
update product_images set name= '4861Gold_chain.png',
images='upload/products/4861Gold_chain.png'
where product_id= 22
update product_images set name= '4675Necklaces_Diamond.png',
images= 'upload/products/4675Necklaces_Diamond.png'
where product_id= 22
update product_images set name= '23538charms_silver.png',
images= 'upload/products/23538charms_silver.png'
where product_id= 22
Following is the complete Code of Image Updating Part.
/*-----------------
IMAGE QUERY 2
------------------*/
if (isset($_FILES['files'])
|| ($_FILES["files"]["type"] == "image/jpeg"))
{
foreach($_FILES['files']['tmp_name'] as $key=> $tmp_name)
{
//echo $tmp_name."<br>";
echo 'number<br>';
echo $image_name= $_FILES["files"]["name"][$key];
$random_name= rand().$_FILES["files"]["name"][$key];
$folder="upload/products/" .$random_name;
move_uploaded_file($_FILES["files"]["tmp_name"][$key],
"upload/products/" . $random_name);
// print_r($_FILES);
echo $sql= "update product_images set name= '$random_name',
images= '$folder' where product_id= $id ";
if ($query_run= mysql_query($sql))
{
echo '<br>';
echo 'Done';
}
else
{
echo mysql_error();
}
}
}
/*-----------------
IMAGE QUERY 2- END
------------------*/
Upvotes: 0
Views: 2028
Answers (4)
Reputation: 71
sql query that you are executing, can't distinguish images separately, and it's consider the same image/item.
For distinguishing data from database I will advise to keep some indexing field. Say, if your each product have 3 images to save in database, try to use another field named 'odr' to set re-ordering & distinguishing 3 images separately for each product. But if you never have to consider images separately for each product, then it's OK. But it's seriously now going to help you to keep sql query very simple to solve this problem, otherwise sql query may be little complex. To save all the 3 images for each product see the solution below:
Solution: 1) keeping a new field name 'odr':
update product_images set name= '4861Gold_chain.png',
images='upload/products/4861Gold_chain.png'
where product_id= 22 and odr=1
update product_images set name= '4675Necklaces_Diamond.png',
images= 'upload/products/4675Necklaces_Diamond.png'
where product_id= 22 and odr=2
update product_images set name= '23538charms_silver.png',
images= 'upload/products/23538charms_silver.png'
where product_id= 22 and odr=3
And modify your code simply like this:
/*-----------------
IMAGE QUERY 2
------------------*/
if (isset($_FILES['files'])
|| ($_FILES["files"]["type"] == "image/jpeg"))
{
/*add a line here*/
/*for odr field*/
$odr=0;
foreach($_FILES['files']['tmp_name'] as $key=> $tmp_name)
{
/*add another line here*/
/*increment the odr*/
$odr=$odr+1;
//echo $tmp_name."<br>";
echo 'number<br>';
echo $image_name= $_FILES["files"]["name"][$key];
$random_name= rand().$_FILES["files"]["name"][$key];
$folder="upload/products/" .$random_name;
move_uploaded_file($_FILES["files"]["tmp_name"][$key],
"upload/products/" . $random_name);
// print_r($_FILES);
echo $sql= "update product_images set name= '$random_name',
images= '$folder' where product_id= $id and odr= $odr";
if ($query_run= mysql_query($sql))
{
echo '<br>';
echo 'Done';
}
else
{
echo mysql_error();
}
}
}
/*-----------------
IMAGE QUERY 2- END
------------------*/
2) If you don't want to use another new field 'odr', then for simplified sql code you can use this, but, this not recommended as it's changing same field again and again (only thing sql is simplified to understand):
sql queries should be:
update product_images set name= '4861Gold_chain.png',
images='upload/products/4861Gold_chain.png'
where product_id= 22 limit 3
//update all of the 3 images to these values
update product_images set name= '4675Necklaces_Diamond.png',
images= 'upload/products/4675Necklaces_Diamond.png'
where product_id= 22 limit 2
//update first 2 images, so last image will remain with the first value
update product_images set name= '23538charms_silver.png',
images= 'upload/products/23538charms_silver.png'
where product_id= 22 limit 1
//update only last image, so, first & second keeps their value to previous two value separately
and the code for your case should be:
/*-----------------
IMAGE QUERY 2
------------------*/
if (isset($_FILES['files'])
|| ($_FILES["files"]["type"] == "image/jpeg"))
{
/*add a line here*/
$i=3;
foreach($_FILES['files']['tmp_name'] as $key=> $tmp_name)
{
//echo $tmp_name."<br>";
echo 'number<br>';
echo $image_name= $_FILES["files"]["name"][$key];
$random_name= rand().$_FILES["files"]["name"][$key];
$folder="upload/products/" .$random_name;
move_uploaded_file($_FILES["files"]["tmp_name"][$key],
"upload/products/" . $random_name);
// print_r($_FILES);
echo $sql= "update product_images set name= '$random_name',
images= '$folder' where product_id= $id limit $i";
if ($query_run= mysql_query($sql))
{
echo '<br>';
echo 'Done';
}
else
{
echo mysql_error();
}
/*add another line here*/
$i=$i-1;
}
}
/*-----------------
IMAGE QUERY 2- END
------------------*/
There are another solution in my mind, though I am not thinking to give it here, that will use a little complex sql using a sql clause with select & offset , but I think 1st one that I give here using 'odr' will be best for you, if you can add a new field odr. And don't forget to ensure that product_id field is not primary / unique (I think you know that)
Upvotes: 1
Reputation: 1521
Basically there are two options to solve this problem. When uploading new images for the existing product you might to:
- find the existing images and update them one by one or
- (preferably) delete the old images and insert the new ones. It is simpler, but works with the images only as a set (no individual access).
The first point needs to identify each product image somehow to handle it separately. You have to decide on some unique key in your table product_images. It might be Image_id as well as a combination of Product_id and Image_id. In the first case you have to generate the unique values for all the images of all the products, in the second one it's enough to number the images for each product separately.
Upvotes: 1
Reputation: 957
If you want to save all the images of a product in one id and want to use all the images of that product, then save your image paths separated by * or any specific string and split the string with * while showing the image in html.n so will have all images paths to display.
Upvotes: 0
Reputation:
If you are uploading multiple images then use <input type='file' name='images[]'>
Your php script should then use the index to loop throgh the array:
$_FILES["files"][$index]["type"]
the php manual explains it all: http://www.php.net/manual/en/features.file-upload.multiple.php
Upvotes: 0
Related Questions
- MySQL update multiple rows based on id
- How to update records with same ID in same table in MysQL
- Update multiple values with same id using php
- SQL Update multiple rows based on ID
- How to UPDATE MySQL Database if I have different ids?
- UPDATE multiple MySQL rows with different values
- Mysql update a different field (column) for each id
- mysql and php change single data for specific id
- PHP MYSql: UPDATE Multi Row With One id
- Update multiple rows in once. based on id