?
Solved

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

Posted on 2014-08-01
9
Medium Priority
?
640 Views
Last Modified: 2014-08-01
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
0
Comment
Question by:Scott Johnston
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40235290
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.
0
 

Author Comment

by:Scott Johnston
ID: 40235304
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'];
0
 
LVL 58

Expert Comment

by:Gary
ID: 40235308
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

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:Scott Johnston
ID: 40235342
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..???
0
 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 40235360
If you are adding a value for the column then it will not be the default value but whatever you pass in the SQL
If there is not going to be a value at the time you insert the record then remove the column and value from the SQL and the default value will be inserted.
0
 

Author Comment

by:Scott Johnston
ID: 40235370
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
0
 
LVL 58

Expert Comment

by:Gary
ID: 40235372
Correct
0
 

Author Closing Comment

by:Scott Johnston
ID: 40235374
I appreciate your help, I am getting better at this but have very little expertise.  Thanks for helping!!!
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 40235382
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question