Updating problem

[lkeeney]

Can someone tell me why this doesn't work the way I want it to work?

I start out by setting all the variables to null.
I then read the variables from a form.
I then try to change only the variables in the database table that are not null from the form. I am doing this so I can change only one or two variables if I want.

The problem is, all the variables are being placed in the addr1 column in the database instead in their own respective columns.

I should point out that all of the variables have the correct data. I inserted echo statements in each of the "if" statements to verify the variable data was correct.

    $addr1=null;
    $addr2=null;
    $city=null;
    $state=null;
    $zip=null;
    $phone=null;
    $email=null;
    $star=null;
    $gender=null;
    $age=null;
    $number=null;
    $source=null;
    $contact=null;
    $note=null;

    $month = $_POST['month'];
    $day  = $_POST['day'];
    $year  = $_POST['year'];

    $date = "$year"."-"."$month"."-"."$day";

    switch ($year)
    {
        case 2009:
            $table = "nine";
            break;
        case 2010:
            $table = "ten";
            break;
        case 2011:
            $table = "eleven";
            break;
        case 2012:
            $table = "twelve";
            break;
        case 2013:
            $table = "thirteen";
            break;
        case 2014:
            $table = "fourteen";
            break;
        case 2015:
            $table = "fifteen";
            break;
    }

    $today = date("Y-m-d");
    $fname=$_POST['fname'];
    $lname=$_POST['lname'];
    $addr1=$_POST['addr1'];
    $addr2=$_POST['addr2'];
    $city=$_POST['city'];
    $state=$_POST['state'];
    $zip=$_POST['zip'];
    $phone=$_POST['phone'];
    $email=$_POST['email'];
    $star=$_POST['star'];
    $gender=$_POST['gender'];
    $age=$_POST['age'];
    $number=$_POST['number'];
    $source=$_POST['source'];
    $contact=$_POST['contact'];
    $note=$_POST['note'];

    if (isset($addr1)) {mysql_query("UPDATE $table SET addr1 = '$addr1' WHERE reservation_date = '$date' AND lname='$lname' AND fname='$fname'");
    }

    if (isset($addr2)) {mysql_query("UPDATE $table SET addr2 = '$addr2' WHERE reservation_date = '$date' AND lname='$lname' AND fname='$fname'");
    }

    if (isset($city)) {mysql_query("UPDATE $table SET city = '$city' WHERE reservation_date = '$date' AND lname='$lname' AND fname='$fname'");
    }

    if (isset($state)) {mysql_query("UPDATE $table SET state = '$state' WHERE reservation_date = '$date' AND lname='$lname' AND fname='$fname'");
    }
More similar code here................

I will appreciate any help I can get on this problem.

Lawrence

[Mittineague]

I'm guessing the problem is somewhere within the sequential queries (a later one is over-writing an earlier one?). In any case the code is very inefficient with a separate SET query for each field. IMHO it would be better to use the ifs to create a SET and have only 1 query. eg

$set = "";
if (isset($_POST['name']) $set .= "name = '" . $_POST['name'] . "', ";
if (isset($_POST['city']) $set .= "city = '" . $_POST['city'] . "', ";
if (isset($_POST['state']) $set .= "state = '" . $_POST['state'] . "' ";
.....
rtrim($set, ", ");
..... SET $set WHERE ..... 

[lkeeney]

I'm guessing the problem is somewhere within the sequential queries (a later one is over-writing an earlier one?). In any case the code is very inefficient with a separate SET query for each field. IMHO it would be better to use the ifs to create a SET and have only 1 query. eg

$set = "";
if (isset($_POST['name']) $set .= "name = '" . $_POST['name'] . "', ";
if (isset($_POST['city']) $set .= "city = '" . $_POST['city'] . "', ";
if (isset($_POST['state']) $set .= "state = '" . $_POST['state'] . "' ";
.....
rtrim($set, ", ");
..... SET $set WHERE ..... 

Thank you. I like your concept much better than what I came up with.

I am still curious though as to why my code would not work. In my research, I could not find any reference saying this would not work.

Lawrence