CODEWITHSUNDEEP
phpmysql
nsilva
nsilva

Reputation: 5614

PHP - MySQL Update if value not set

I have the following:-

    $pdo = new SQL();
    $dbh = $pdo->connect(Database::$serverIP, Database::$serverPort, Database::$dbName, Database::$user, Database::$pass);

    if ($fieldBookingdate == NULL) {$fieldBookingdate = 'bookingdate';}
    if ($fieldReturndate == NULL) {$fieldReturndate = 'returndate';}
    if ($fieldBookingtime == NULL) {$fieldBookingtime = 'bookingtime';}
    if ($fieldReturntime == NULL) {$fieldReturntime = 'returntime';}
    if ($fieldPassengername == NULL) {$fieldPassengername = 'passengername';}
    if ($fieldPassengeremail == NULL) {$fieldPassengeremail = 'passengeremail';}
    if ($fieldPaddress == NULL) {$fieldPaddress = 'paddress';}
    if ($fieldVaddress == NULL) {$fieldVaddress = 'vaddress';}
    if ($fieldDaddress == NULL) {$fieldDaddress = 'daddress';}
    if ($fieldCartype == NULL) {$fieldCartype = 'cartype';}
    if ($fieldFlightnumber == NULL) {$fieldFlightnumber = 'flightnumber';}
    if ($fieldComments == NULL) {$fieldComments = 'comments';}

    $this->sql = "UPDATE  tblbookings SET bookingdate = '$fieldBookingdate', returndate = '$fieldReturndate', bookingtime = '$fieldBookingtime'
    returntime = '$fieldReturntime', passengername = '$fieldPassengername', passengeremail = '$fieldPassengeremail', paddress = '$fieldPaddress'
    vaddress = '$fieldVaddress', daddress = '$fieldDaddress', cartype = '$fieldCartype', flightnumber = '$fieldFlightnumber', comments = '$fieldComments' WHERE
    bookref = '$fieldBookingreference';";

Basically the UPDATE will amend 12 database fields, but it may be that only 11 values have been set. (MySQL)

For values not set I know you can update like UPDATE tblbookings SET bookingdate = bookingdate and it will work. However, because I have these values within quotes in the query it is updating as UPDATE tblbookings SET bookingdate = 'bookingdate' - and failing.

How could I get this to work?

UPDATE 1:-

Example;

UPDATE  tblbookings SET bookingdate = 'bookingdate' WHERE

        bookref = 'BR1360'

I get the following error:-

1292 - Incorrect date value: 'bookingdate' for column 'bookingdate' at row 133

If I do:-

UPDATE  tblbookings SET bookingdate = bookingdate WHERE

        bookref = 'BR1360'

This will work, and leave bookingdate value as it is.

Upvotes: 1

Views: 984

Answers (2)

nsilva
nsilva

Reputation: 5614

Managed to get it working as follows:-

if ($fieldBookingdate == NULL) {$fieldBookingdate = "bookingdate = bookingdate";} else {$fieldBookingdate = "bookingdate = '$fieldBookingdate'";}

So if the value is not set, it will leave the field as it is (bookingdate = bookingdate), for the values that are set it will update them as ($fieldBookingdate = "bookingdate = '$fieldBookingdate'";)

Upvotes: 0

Fabio
Fabio

Reputation: 23490

The error is caused by a missing comma in your query

bookingtime = '$fieldBookingtime'    returntime = '$fieldReturntime', 
                               //^ here the comma is missing

So the composer is trying to set the bookingtime column as 'fieldBookingtime' returntime..., it's like you are passing characters that are not escaped by quote.

As I already explained in my comment the query is not failing because of the quote you used as you thought.

Upvotes: 1

Related Questions