asked on

Mysql and insert query that has default values configure on a field in a table

I have a small registration form that will allow someone to register to a class that our company offers. My table name is registration and then I have 5 field defined in the table, the first field is the ID and it is auto increments, (works fine) the second field in the table is the regdate , I like to have the system use the time stamp to populate the data, but when I try to setup the field I get an error. I have the field in MYSQL setup as type (Timestamp) Null (yes0 Default (Current_timestamp), attribute (on updateCurrent_timestamp), but when ever I post a record from the form, the data and time are not getting written to the database? No sure why?

Second
I have another field in the same table that is CampaignID, this field has a default value of "Open House" it also is not being written to the database. I need to have some help in the form or insert.php
$sql="INSERT INTO marketing.OpenHouseRegistrations (RegDate, FirstName, LastName, EmailAddress, PhoneNumber, CECredits)

VALUES

('$_POST[RegDate]','$_POST[FirstName]','$_POST[LastName]','$_POST[EmailAddress]','$_POST[PhoneNumber]','$_POST[CECredits]',$_POST'[CampaignID]')";
//THIS IS A TEST
/*if (empty ($_Post["RegDate"]))*/
/*{$$RegDate = $_POST['RegDate'];*/
$RegDate = $_Post['RegDate'];
//$IP = $_POST['IP'];
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$EmailAddress = $_POST['EmailAddress'];
$PhoneNumber = $_POST['PhoneNumber'];
$CECredits = $_POST['CECredit'];
$CampaignID = $_POST['CampaignID'];

Please advise on what I doing wrong when using default values?

Thank you

Gary

Your insert statement is inserting CampaignID value but it's not in your column list
(RegDate, FirstName, LastName, EmailAddress, PhoneNumber, CECredits)

What's the value of $_POST[RegDate]
Also all $_POST vars should be in apostrophes or speech marks i.e.
$_POST["RegDate"]

$sql="INSERT INTO marketing.OpenHouseRegistrations (RegDate, FirstName, LastName, EmailAddress, PhoneNumber, CECredits,CampaignID)
VALUES
 ('".$_POST['RegDate']."','".$_POST['FirstName']."','".$_POST['LastName']."','".$_POST['EmailAddress']."','".$_POST['PhoneNumber']."','".$_POST['CECredits']."','".$_POST['CampaignID']."')";

Open in new window

Add this after the above line and post here
echo $sql;

But this is not the correct way to build your sql, you should be escaping your input data to prevent sql injection.

Scott Johnston

ASKER

I am sorry but I copies the wrong code,
Here is my updated code and it still is not working....
$sql="INSERT INTO marketing.OpenHouseRegistrations (RegDate, FirstName, LastName, EmailAddress, PhoneNumber, CECredits, CampaignID)

VALUES

('$_POST[RegDate]','$_POST[FirstName]','$_POST[LastName]','$_POST[EmailAddress]','$_POST[PhoneNumber]','$_POST[CECredits]','$_POST[CampaignID]')";
//THIS IS A TEST
$RegDate = $_Post['RegDate'];
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$EmailAddress = $_POST['EmailAddress'];
$PhoneNumber = $_POST['PhoneNumber'];
$CECredits = $_POST['CECredit'];
$CampaignID = $_POST['CampaignID'];

Gary

Still use this and post back the result of the $sql line

$sql="INSERT INTO marketing.OpenHouseRegistrations (RegDate, FirstName, LastName, EmailAddress, PhoneNumber, CECredits,CampaignID)
VALUES
 ('".$_POST['RegDate']."','".$_POST['FirstName']."','".$_POST['LastName']."','".$_POST['EmailAddress']."','".$_POST['PhoneNumber']."','".$_POST['CECredits']."','".$_POST['CampaignID']."')";
echo $sql;

Open in new window

Scott Johnston

ASKER

I changed the coding to include the Double quote, added the Echo, the form works great, all the fields I enter data get populated into the database, the field that I want to have adefault value that is defined in the MYSQL table are not. Can I just setup the insert so that it post a default value? maybe that type of workaround will work better than using the MYSQL to write default value..???

ASKER CERTIFIED SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Johnston

ASKER

That was way to easy....So any time I am using a default value I do not need the field in the form or on the insert statement..
Thank you

Gary

Correct

Scott Johnston

ASKER

I appreciate your help, I am getting better at this but have very little expertise. Thanks for helping!!!

Renante Entera

By the way, I just want to add something that might be useful...

I have seen part of your code:

$RegDate = $_Post['RegDate'];

Open in new window

Take note that it will not give you the expected result 'coz it should be:

$RegDate = $_POST['RegDate'];

Open in new window

In addition to that, if you are including complex variables within double quotes. You can use braces around it then your code will look like this:

$sql="INSERT INTO marketing.OpenHouseRegistrations (RegDate, FirstName, LastName, EmailAddress, PhoneNumber, CECredits,CampaignID)
VALUES
 ('{$_POST['RegDate']}','{$_POST['FirstName']}','{$_POST['LastName']}','{$_POST['EmailAddress']}','{$_POST['PhoneNumber']}','{$_POST['CECredits']}','{$_POST['CampaignID']}')";
echo $sql;

Open in new window

Hope this helps a lot.