Help with update

[jim.davidson]

I'm new to this but here goes

 

I'm trying to update a table using mySql version 4.1.21 in dreamweaver8

 

Here's the table layout

Customers

customer_id int 10 auto

first_name varchar 50

last_name varchar 50

address_1 varchar 50

address_2 varchar 50

city varchar 50

state_id             char   2

zip varchar 10

contact_name varchar 60

contact_phone varchar 25

contact_email varchar 100

 

I'm trying to update a record that has 'ZZ' in the state_id field

here's the code

 

$test = "ZZ";

$goodstate = $_SESSION['contact_state'];

 

mysql_select_db($database_imcrecycle, $imcrecycle);

$result = mysql_query("UPDATE customers SET customers.state_id=$goodstate WHERE customers.state_id=$test") or die(mysql_error());

 

I get the following error

 

Unknown column 'ZZ' in 'where clause'

 

What am I doing wrong?

 

 

 

[grimmier]

$test = "ZZ";
$goodstate = $_SESSION['contact_state'];

mysql_select_db($database_imcrecycle, $imcrecycle);
$result = mysql_query('UPDATE Customers SET `state_id`="'.$goodstate.'" WHERE `state_id`="'.$test.'"') or die(mysql_error());

[jim.davidson]

now i get this error

 

Parse error: parse error, unexpected T_STRING in C:\Sites\testcart.php on line 71

 

Here's what I really need, I insert a record into customers table I need to get back the customer_id number that is auto incremented when the record was inserted. Is there a way to do this? if there is then I can eliminate this update code that I'm having the problem with.

 

It might sound stupid, but I'm new at this, I purposly have state_id default to 'ZZ' on insert so that I could get a recordset that would return the new record that was just inserted. All I wanted from that recordset was the new customer_id number. So now I had to update that record to put the correct state_id in the state_id field.  So if there's another way to get that customer_id number on insert I can eliminate two steps.

 

Am I way out in left field or what?

 

This is my code that inserts a record in the table, it works fine.

<?php

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "Recycle_1_ATC_12345")) {

  $insertSQL = sprintf("INSERT INTO customers (first_name, last_name, address_1, address_2, city, zip, contact_name, contact_phone, contact_email) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",

                      GetSQLValueString($_SESSION['contact_first_name'], "text"),

                      GetSQLValueString($_SESSION['contact_last_name'], "text"),                      GetSQLValueString($_POST['recycle_conntact_address1'], "text"),

                      GetSQLValueString($_POST['recycle_contact_address2'], "text"),

                      GetSQLValueString($_POST['recycle_contact_city'], "text"),

                      GetSQLValueString($_POST['recycle_contact_zip'], "text"),

                      GetSQLValueString($_SESSION['contact_name'], "text"),

                      GetSQLValueString($_SESSION['contact_phone'], "text"),

                      GetSQLValueString($_SESSION['contact_email'], "text"));

 

  mysql_select_db($database_imcrecycle, $imcrecycle);

  $Result1 = mysql_query($insertSQL, $imcrecycle) or die(mysql_error());

}

[grimmier]

The syntax might be wrong on the update string. i think i accidentally capitalized the C on customers. for the Table Name.

 

$update_query = 'UPDATE `customers` SET `state_id`="'.$goodstate.'" WHERE `state_id`= "'.$test.'"' ';
$result = mysql_query($update_query, $imcrecycle) or die(mysql_error());

 

 

this should work nicer.

[Wildbug]

If your customer id number column is AUTO_INCREMENT, then you can use the LAST_INSERT_ID() function to return the most recent customer id number rather than doing this so "round-about-ly."