How to increment a mysqli query based on if values exist

PHP
1

Hi, I’m having problems working out how to update a MySQL table based on some images for a record and whether they exist or not.

Basically in my edit screen I have some fields, some of which are for images of the product. The user can change the image using a file / browse form input, if the image exists, or they can also add new ones.

I used this code successfully back when using the old MySQL query:

$query = "UPDATE forsale SET Price = '$price', PropertyType = '$propertytype', Bedrooms = '$bedrooms', Location = '$location', Status = '$status', Description = '$apostrophefix_description', OverseasYN = '$overseas', ArchivedYN = '$archived'";

if ($image1) {	$query .= ", Image1 = '$image1'"; }
if ($image2) {	$query .= ", Image2 = '$image2'"; }
if ($image3) {	$query .= ", Image3 = '$image3'"; }
if ($image4) {	$query .= ", Image4 = '$image4'"; }
if ($image5) {	$query .= ", Image5 = '$image5'"; }
if ($image6) {	$query .= ", Image6 = '$image6'"; }
if ($image7) {	$query .= ", Image7 = '$image7'"; }

$query .= " WHERE `PropertyID` = '$id'"; 
}

But because I’m now using mysqli and the syntax has to include brackets for it work, I can’t find a way of incrementing the query based on whether or not a certain field from the form has a value.

For example:


$query = $mysqli->query("UPDATE forsale SET Price = '$price', PropertyType = '$propertytype', Bedrooms = '$bedrooms', Location = '$location', Status = '$status', Description = '$apostrophefix_description', OverseasYN = '$overseas', ArchivedYN = '$archived'");

If I then try to use if ($image1) {$query .= “, Image1 = ‘$image1’”; } etc. etc. it doesn’t work.

I can’t simply assign all the values from the form inputs, e.g set Image1, Image2, Image3 etc. to have the values of $image1, $image2, $image3 and so on, because the user might have not changed the photos- so the empty value would overwrite the value already in the database.

Any ideas? Thanks.

2

So my first response is “use a better database layout”. You should be using 2 tables for this instead of just one.

But assuming you wish to stick with 1 table, SELECT the current values from the table, use them as the defaults, and then overwrite as needed.

3

What happens with Image8 etc?

4

Ok thanks, but I don’t see how selecting the current values and then overwriting them helps me create the overall query? I’m already trying to overwrite them (regardless of what they are already) provided that that field has a new photo to be inserted. If the input is blank it should just be left alone, regardless of the current value in the database.

So if a new Photo 3 is added, overwrite pic3 in the db. If Photo 2 is changed, overwrite pic2. If Photo 1 is left alone, then keep the current value / image and so on.

I set up this code:

if ($photo1) {	$query .= ", pic1 = '$photo1'"; }

And further up in the code I included pulling through the value from the form:

$photo1 = basename( $_FILES['_Pic1']['name']);

I also checked in the form page that the input was correctly named.

It doesn’t throw an error, it just doesn’t grab the value…

if I echo $photo1 after it’s declared, it does output the value from the form however…

I think the problem is that my $query also includes a “WHERE” clause so maybe the $photo1 statement to increment it is being ignored? problem is I have to have that in place (WHERE id = a specific record) or else the changes will impact every record in the table!

The whole query:

$query = $mysqli->query("UPDATE forsale SET Price = '$price', PropertyType = '$propertytype', Bedrooms = '$bedrooms', Location = '$location', Status = '$status', Description = '$apostrophefix_description', OverseasYN = '$overseas', ArchivedYN = '$archived' WHERE `id` = $id");
5

I will digress to POST 2 and 3… Imagine a query that could grab property info and say 20 images.

$sql = "SELECT 
      PropertyID
    , Price
    , PropertyType
    , Bedrooms
    , Location
    , Status
    , Description
    , OverseasYN
    , ArchivedYN
    , image
    FROM forsale
        LEFT JOIN images
            ON images.PropertyID = forsale.PropertyID
    WHERE PropertyID = '$id' ";

Think about it. As I’m currently house shopping, I see properties all the time that have listings with 15 to 20 images.

6

Thanks, but how does that help fix the issue with the Update query? If I select all the existing images, how then do I write the update query to add the updated image names to the db? Basically it all depends on whether or not the image was changed in the preceding form- for example, if Photo 2 was changed, then regardless of what’s currently in the Pic2 field in the db table, it should be overwritten.

7

Rough example based on StarLoin’s suggestion.

$sql = "SELECT Image1,Image2,Image3,Image4,Image5,Image6,Image7 FROM forsale  WHERE PropertyID = '$id'";
//execute query and set variables
// while...{
    $default1 = $row['Image1'];
    $default2 = $row['Image2'];
    $default3 = $row['Image3'];
    $default4 = $row['Image4'];
    $default5 = $row['Image5'];
    $default6 = $row['Image6'];
    $default7 = $row['Image7'];
//}

$image1 = (isset($image1) ? $image1 : $default1);
$image2 = (isset($image2) ? $image2 : $default2);
$image3 = (isset($image3) ? $image3 : $default3);
$image4 = (isset($image4) ? $image4 : $default4);
$image5 = (isset($image5) ? $image5 : $default5);
$image6 = (isset($image6) ? $image6 : $default6);
$image7 = (isset($image7) ? $image7 : $default7);

$query = "UPDATE forsale SET 
  Price = '$price'
  , PropertyType = '$propertytype'
  , Bedrooms = '$bedrooms'
  , Location = '$location'
  , Status = '$status'
  , Description = '$apostrophefix_description'
  , OverseasYN = '$overseas'
  , ArchivedYN = '$archived'
  , Image1 = '$image1'
  , Image2 = '$image2'
  , Image3 = '$image3'
  , Image4 = '$image4'
  , Image5 = '$image5'
  , Image6 = '$image6'
  , Image7 = '$image7'
 WHERE `PropertyID` = '$id'";
8

You need to get the database design right first. Whenever you can have more than one of something then that should be a separate table.

Table forsale has PropertyId, Price, ProprtyType, Bedrooms, Location, Status, Description, Overseas, Archived

Table images has PropertyId, PhotoNumber, Image

You then have one update for the forsale table and a loop to run a separate update for each image.

Then if you need to change the form to allow for extra images you will not have to change the database. After a ll a property that is for sale can have as many images as the photographer decides to take so arbitrarily choosing to only allow for 7 is inappropriate.

1 Like
9

Or you simply never UPDATE images at all.

The query is either an INSERT to insert a new image, or a DELETE to get rid of one, but never an UPDATE.

1 Like
10

Also, seriously consider taking the time to learn how to use prepared statements - those are far more secure. I cringe anytime I see variables in an SQL query string, especially if I can’t see the validation code.

1 Like
11

Ok, I’ve done this on a separate site that does exactly the same thing just with some different fields. It changed the first photo ok, but it also removed the second photo, even though I didn’t specify a second photo in the form, I just wanted to change the 1st one?


//Grab the photo names from the record in the db
$getphotos = $mysqli->query("SELECT pic1, pic2 FROM cars  WHERE carid = '$carid'");

while ($row = $getphotos->fetch_assoc()) {
	$pic1 = $row['pic1'];
	$pic2 = $row['pic2'];
}

$image1 = (isset($photo1) ? $photo1 : $pic1);
$image2 = (isset($photo2) ? $photo2 : $pic2);

//The photo1 and photo2 variables are the values from the form if they exist, which I declare beforehand, added below for info:
//  $photo1 = basename( $_FILES['_Pic1']['name']);
//  $photo2 = basename( $_FILES['_Pic2']['name']);

//Update the record
$updatestockdetails = $mysqli->query("UPDATE cars SET Hidden = '$hidden', Year_of_car = '$yearofcar', is_archived = '$isarchived', Colour = '$extcolour', Interior = '$intcolour', title = '$title', Hood = '$hood', Mileage = '$odometer', Price = '$price', Steering = '$steering', Transmission = '$transmission', Body_type = '$bodytype', Four_wheel_drive = '$fourwheeldrive', Model_type = '$modeltype', Doors = '$doors', Chassis_no = '$chassisnumber', Engine_size = '$enginesize', Engine_no = '$enginenumber', Fuel_type = '$fueltype', Full_registration = '$regnumber', Previous_owners = '$previousowners', First_registration = '$datefirstreg', Options = '$options', car_description = '$description', vehicle_history = '$vehiclehistory', pic1 = '$image1', pic2 = '$image2'  WHERE carid = '$carid'");

I don’t want pic2 to be overwritten unless it’s specified in the form, i.e the user browses to a new photo that they want to use in the file input.???

12

Doesn’t this bit:

$image1 = (isset($photo1) ? $photo1 : $pic1);
$image2 = (isset($photo2) ? $photo2 : $pic2);
//The photo1 and photo2 variables are the values from the form if they exist, which I declare beforehand, added below for info:

cause the problem? isset() will return true because you created the variables earlier as noted in your comment. You need to check whether there is anything in the variables, not just whether they exist.

That would be my suggestion too, from a usability point of view. Present all the photos to the user, allow them to delete as required, and add new ones. Remember, user doesn’t care (and shouldn’t have to care) which is photo 1, which is photo 2 and so on - just give them a chance to choose the ‘main’ photo if that matters.

1 Like
13

Ok, so this seems to be getting more complicated- should I keep the query that does a SELECT on the existing images in the db, then have another one that adds new ones (INSERT) and another that does DELETE?

I did an echo of $photo1 and the value is definitely getting carried through. As I mentioned that first photo updated fine. But the script also removed the 2nd photo from the database whereas it should have kept it.

So if I don’t use the isset statement, what should I have instead of it? And do I need three queries? The other issue is I’d rather the user be able to just update the picture (change it) rather than have to delete it and then have to add a new one, as it’s easier from their point of view.

14

You could check the length of the string, or see if it’s not blank:

if ( strlen($photo1) > 0 ) ....
if ( $photo1 != "" ) ...

You probably want to be doing some validation on the photo name in any case, so maybe write a function that takes the basename() result, checks it for being a valid image type, checks it has something in it, check maximum sizes, whatever you like, then go from there.

Did you echo $photo2? My suspicion is that you create the $photo2 variable as you noted in the comments, but it’s empty because the user didn’t supply a new photo in the form. Then you use isset() to see if the variable exists (which it does) and doing that causes your update query to overwrite the original value with a blank.

15

Thanks, ok so how do I change the isset so that if the form value passed through is blank it doesn’t then update the database table? (echo of $photo2 was blank as I suspected, as I didn’t specify a new image)

16

There may be better ways than this:

$image1 = ( ( strlen($photo1) > 0) ? $photo1 : $pic1);
$image2 = ( ( strlen($photo2) > 0) ? $photo2 : $pic2);

So if either of the $photo variables from the form have anything at all in them, they will overwrite the values already in the table, if not the original values will be used.

1 Like
17

Thanks- that seems to have worked (well, the upload script that runs afterwards uploaded the 2nd pic but not the first one, but that’s a separate issue) Thanks!

18

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.