CODEWITHSUNDEEP
phpmysqlvariables
Legend1989
Legend1989

Reputation: 664

PHP SQL UPDATE using variables

I currently have a script to upload photos, at the moment it uploads multiple images adding a new row to the mySQL database each time a new one is created. However I want the user to only be able to upload 1 image maximum.

I have therefore changed the register script to insert a row straight in to the database with a default image for each new user that registers.

The image upload script once logged in currently uses the below line to put the data in to the database

$sql2= "INSERT INTO `profile_photo` (`profile_id`,`title`,`size`,`type`,`reference`)
            VALUES ('".$_SESSION['id']."','$title','$size','$type','$pic'); ";

However this is not how I want this to work; I believe the resolve would be to change this to an UPDATE row.

This is causing an issue when I change the line, I believe I have not quite grasped the concept of updating rows in mySQL.

Please can you advise?

Upvotes: 0

Views: 396

Answers (5)

Fluffeh
Fluffeh

Reputation: 33502

Assuming that profile_id is your unique key:

$sql2= "
INSERT INTO 
    `profile_photo` (`profile_id`,`title`,`size`,`type`,`reference`)
    VALUES ('".$_SESSION['id']."','$title','$size','$type','$pic')
    on duplicate key update set title='$title', size='$size', type='$type', reference='$reference'; ";

You can also use the replace into:

$sql2= "
Replace into 
    `profile_photo` (`profile_id`,`title`,`size`,`type`,`reference`)
    VALUES ('".$_SESSION['id']."','$title','$size','$type','$pic')";

Upvotes: 1

long
long

Reputation: 4318

Maybe the solution is Replace?

Upvotes: 0

Aleks G
Aleks G

Reputation: 57316

What you need is to execute the UPDATE sql statement:

$sql3= "UPDATE `profile_photo` SET `title`='" . mysql_real_escale_string($title) .
       "', `size`='" . mysql_real_escape_string($size) . 
       "', `type`='" . mysql_real_escape_string($type) .
       "', `reference`='" . mysql_real_escape_string($pic) .
       "' WHERE `profile_id`=" . $_SESSION['id'];

Upvotes: 0

dualed
dualed

Reputation: 10502

I think this question misses some information.

Upvotes: 0

Robin Castlin
Robin Castlin

Reputation: 10996

$sql2= 
"INSERT INTO `profile_photo` (`profile_id`,`title`,`size`,`type`,`reference`)
VALUES ('".$_SESSION['id']."','$title','$size','$type','$pic')
ON DUPLICATE KEY UPDATE title = VALUES(title), size = VALUES(size), type = VALUES(type), reference = VALUES(reference)";

Would solve this if the profile_id column got the index unique.

Behavior
This will INSERT all the data in a new row if the profile_id isn't already added. If it is, it will run an UPDATE instead.

However your code is pretty much begging for mysql injections. Do read up on this before it ruins your site.

Bobby tables, for your consideration.

Read up on DUPLICATE KEY here.

Upvotes: 2

Related Questions