Update a mySQL DB with Primary Key

Anon

Ok, I'm totally a rookie here, so be gentle! Here's my problem. Using MySQL and PHP, I've created a web form that adds, modifies and deletes records from a DB. The problem I've encountered is that when I try to update the DB I'm unable to change any of the records. This worked fine when I did not have a primary key which auto_incremented. However, I need to have some sort of primary key to keep from writing over the same records.

Here's my SQL statement:
$sql = "UPDATE db_name SET date =\"$date\", title =\"$title\", name = \"$name\", degree = \"$degree\", other = \"$other\", type = \"$type\", address = \"$address\", phone = \"$phone\", fax = \"$fax\", email = \"$email\", description = \"$description\", salary = \"$salary\", deadline = \"$deadline\" where job_id =\"$job_id\"";

job_id is my primary key (auto_increment)
The where clause seems to be the problem for me.

It's the one thing I don't want to update. I just want to update the fields in the rest of the row. Am I using the wrong SQL statement? Please help if you can. I know it's simple, but I can't find the answers to my problem.

amcgrath

Assuming you are being passed $job_id from the form, then this looks right. What kind of problem are you having when it runs?

Anon

When it runs, no records change. THAT's the problem. If I take out the primary key and insert something like: WHERE title = '$title'";

The darn thing works. It has something to do with that primary key. I'm stumped kids.

amcgrath

OK, but can you tell us what the update query looks like when you execute it? And how do you get the $job_id variable used in your earlier example? (My guess is that you are not passing a job_id, therefore no recrods will be updated)....

Anon

First of all, thanks for your help Alen. I'm really stumped, and you are probably correct.

How would I tell you what my update query would look like upon execution? Is there a function I can run during the process?

I can cut some code here for you: (script is in early stages)

//prepare SQL Statement
$sql = "UPDATE table_name SET title ='$title', name = '$name', degree = '$degree', other = '$other', type = '$type', address = '$address', phone = '$phone', fax = '$fax', email = '$email', description = '$description', salary = '$salary', deadline = '$deadline' where job_id = '$job_id'";
function parse_string($str) {
return str_replace("'", "''", stripslashes(trim($str)));
}
//Create Connection
$connection = mysql_connect("server", "user", "pw") or die ("Could not connect the database");
//Select Database
$db = mysql_select_db("main",$connection) or die ("Couldn't select database");
//execute SQL query and get result
$sql_result = mysql_query($sql, $connection) or die ("Couldn't execute query");

All I can say is that this badboy runs, but nothing is being updated. You are probably right about the job_id aspect, but in the form previous to this I pull up all the information via the job_id and can alter all the variables for submission.

Anon

Uhh...I'm totally not passing the job_id var in the previous form. I'll get it Allen. Thanks man.

Anon

Thanks again for the heads up. It's nice to figure it out. In the preceding form I simply put in a hidden box:

When the form passed, everything was in place.

I also set up a redirect if the $job_id var did not exsist, proving that you were correct.

Wow! That was actually fun! (I hope nobody heard me say that out loud!)

hehe

amcgrath

From my earlier post: "(My guess is that you are not passing a job_id, therefore no recrods will be updated)...."

Most times, it's the simplest explanation for a perplexing problem... 😉 Good Luck