[Resolved] Problems populating database

helios76

<html>
<? 
$db =mysql_connect ("localhost" ,"root" ,"x" ); 

mysql_select_db ("csttrrep", $db); 

$result =mysql_query ("INSERT INTO repinfo (empID, firstName, lastName, location, district, courseName, instructor, date, duration) 
VALUES ('$empID', '$firstName', '$lastName', '$location', '$district', '$courseName', '$instructor', '$date', '$duration')"); 
$result = mysql_query($sql);
echo "thank you\n";
?> 
</html>

it sends an empty row to the database and not the data I enter.

Any suggestions? I am using a Mac with OSX 10.2.4 and PHP 4.3.0 and MySQL

helios76

I put in an error code and I got

Query was empty

Ravenous

What does $sql contain?

DeltaRho2K

Just to expand on Ravenous' question a bit...

$result =mysql_query ("INSERT INTO repinfo (empID, firstName, lastName, location, district, courseName, instructor, date, duration) 
VALUES ('$empID', '$firstName', '$lastName', '$location', '$district', '$courseName', '$instructor', '$date', '$duration')");  

$result = mysql_query($sql);

Your second $result will superseed the first one, so it really depends on what the value of $sql is.

My guess is that you are trying to first define your query, and then apply it, so you should rewrite your code in either of the following two ways, and it should work for you...

Method 1)

$sql = "INSERT INTO repinfo (empID, firstName, lastName, location, district, courseName, instructor, date, duration) 
VALUES ('$empID', '$firstName', '$lastName', '$location', '$district', '$courseName', '$instructor', '$date', '$duration')";  

$result = mysql_query($sql);

Method 2)

$result =mysql_query ("INSERT INTO repinfo (empID, firstName, lastName, location, district, courseName, instructor, date, duration) 
VALUES ('$empID', '$firstName', '$lastName', '$location', '$district', '$courseName', '$instructor', '$date', '$duration')");

helios76

Thanks for the help!! I now get a "Duplicate entry for key 1" so maybe this is more of a MySQL problem now?

DeltaRho2K

Is your empID field a primary key field set to auto-increment? If so, you shouldn't try to add the values yourself.

Try something like this: (Note that is 2 single quotes for the first inserted value.)

$sql = "INSERT INTO repinfo (empID, firstName, lastName, location, district, courseName, instructor, date, duration) 
VALUES ('', '$firstName', '$lastName', '$location', '$district', '$courseName', '$instructor', '$date', '$duration')";   

$result = mysql_query($sql);

helios76

It's not set to auto increment, but I do need it to be primary and unique.

DeltaRho2K

Then you should set it to autoincrement, and use the SQL query I posted last...

The problem with using a Primary Key that doesn't auto-increment is that mySQL remembers what numbers have already been used so you can't duplicate a key. Keeping track of used numbers is a pain in the neck, and if you are using it as a Primary Key and need the unique indexing, then auto-increment is your best bet.

Castle

Although autoincrement would work. if you ever plan to move this db to somthing a little nicer that will not work. I would recommend setting up a sequence, which will do the same thing and when you dump the db to rebuild it in postgres or oracle you won't have the same problem later.

helios76

OK, I think I should tell you what the purpose of this is - its to keep track of employees' training classes. Each employee has a unique ID number that is not their SSN. What we want to keep on file is basically who took what class when and what office are they located in. So, I don't want the empID to auto-increment. This may be a MySQL problem after all... I'm working both possible solutions.

Thanks for your help past, present and future!

DeltaRho2K

Then what you would probably want to do is to have the field as a unique value, but not as the primary key... Have a separate column assigned as the primary key.

helios76

I did that, now do I need to add anything to my script?

I made a new column, id, and it is the primary key. I changed the empID to unique with an index name of eid.

DeltaRho2K

Just adjust your query accordingly.

$sql = "INSERT INTO repinfo (id, eid, firstName, lastName, location, district, courseName, instructor, date, duration) 
VALUES ('', '$empID', '$firstName', '$lastName', '$location', '$district', '$courseName', '$instructor', '$date', '$duration')";    

$result = mysql_query($sql);

Just put them in the proper order, and name your variable accordingly. As long as you have a primary key set to auto increment, you only need to insert a NULL value, and mySQL will take care of the auto assigning of it's value. Since your Employee IDs are unique anyhow, you should be able to index and search on them pretty easily if you need to.

helios76

Still getting default info in database... grrrr!

DeltaRho2K

What do you mean by "default info"? You never mentioned anything about "default info".

Did you echo the variables to make sure they are what you are expecting to be entered into the DB?

In other words, before you do the query, just echo each of your variables to make sure they have the right values. If they don't then we need to address that, if they do, then post a table structure dump here so I can take a look at your table.

helios76

I am assuming they are default items - I get a 0 in empID and 0000-00-00 in the date and nothing else.

DeltaRho2K

That's exactly right, the variables don't appear to be passing which means your register globals is probably off in the php.ini file. Are you using POST or GET?

Depending on which method you are using, add one off the two following lines to the top of your script.

// If using POST
extract($_POST);

// If using GET
extract($_GET);

helios76

I am using POST - should I turn the register_globals on? I looked at the php.ini file and it looked more like a read me file than a code file, so I wasn't sure what I could/should change.

Again, thank you for helping me - I really do appreciate your time and effort.

helios76

THANK YOU!! THANK YOU!! THANK YOU!! You have made my day, week, fortnight!

helios76

I'm still having problems with the date in the database, but that's not a PHP problem, I would guess. Again, thank you!