INSERTING NULL on the fly

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Code: Select all

$position = sprintf("'%s'",$_POST['position']);
    $criteria = sprintf("'%s'",$_POST['criteria']);
    $sendto = (empty($_POST['Name']))? "DEFAULT" : sprintf("'%s'",$_POST['Name']);
    $email = (empty($_POST['Email']))? "DEFAULT" : sprintf("'%s'",$_POST['Email']);
    $exp_day = $_POST['Exp_Day'];
    $exp_month = $_POST['Exp_Month'];
    $exp_year = $_POST['Exp_Year'];
    $publish = sprintf("'%s'",$_POST['publish']);
    $image_file = (empty($_POST['file']))? "NULL" : sprintf("'%s'",$_POST['file']);
    // if the deadline date is not empty
    if((!empty($exp_day)) && !empty($exp_month)){
     $postdate = sprintf("'%s'",date("Y-m-d",mktime(0,0,0,$themonth,$theday,$theyear)));
      $deadline = sprintf("'%s'",date("Y-m-d",mktime(0,0,0,$exp_month,$exp_day,$exp_year)));
      // query string
      $query = sprintf("INSERT INTO careers VALUES ('',%s,%s,%s,%s,%s,%s,%s,%s)",$postdate,$position,$image_file,$criteria,$deadline,$email,$sendto,$publish);
the table is

Code: Select all

CREATE TABLE careers (
  rec_id int(12) NOT NULL auto_increment,
  postdate date NOT NULL default '0000-00-00',
  position varchar(255) default NULL,
  image_file varchar(255) default NULL,
  criteria mediumtext,
  deadline date NOT NULL default '0000-00-00',
  sendto varchar(255) NOT NULL default 'HR Dept.',
  sender_name varchar(255) NOT NULL default 'jobs@hadcoltd.com',
  publish set('Y','N') NOT NULL default 'Y',
  PRIMARY KEY  (rec_id),
  KEY postdate (postdate,position,deadline),
  FULLTEXT KEY criteria (criteria)
) TYPE=MyISAM;
if u look at my prvious post i saw that mysql had a insert syntax in which htey had
NOte the VALUES ({expr | DEFAULT},...
how do you use this?

Kendall
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Ths should be exactly how you use the DEFAULT syntax shown in the manual.

However I can see one failure mode. If exp_day or exp_month is empty you'll end up with a nonexistent value for postdate and deadline, which will cause the qery to have something like ",,," which will definitely trigger a SQL error.

Have you echo'd out the $query to make sure it looks correct?

Have you tried the simplified DEFAULT testing script I posted earlier?
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

However I can see one failure mode. If exp_day or exp_month is empty you'll end up with a nonexistent value for postdate and deadline, which will cause the qery to have something like ",,," which will definitely trigger a SQL error.
hence...

Code: Select all

if((!empty($exp_day)) && !empty($exp_month)){
Have you echo'd out the $query to make sure it looks correct?
Yeah... i have been using PHPMyAdmin to conduct experiments
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

If you want the default to be null, simply set your column to have whatever you want the default, in this case null, and simply to not pass a value in your query. And by default, it will be Null.

Why so much sprintf? Why are you using it in the following cases?

Code: Select all

$position = sprintf("'%s'",$_POST['position']);

Code: Select all

$image_file = (empty($_POST['file']))? "NULL" : sprintf("'%s'",$_POST['file']);

Code: Select all

$postdate = sprintf("'%s'",date("Y-m-d",mktime(0,0,0,$themonth,$theday,$theyear)));

Code: Select all

$deadline = sprintf("'%s'",date("Y-m-d",mktime(0,0,0,$exp_month,$exp_day,$exp_year)));
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Ahh thought that maybe you had missed a close '}' before the quey and the lack of an else meant that there was a path to the sprintf without the variable getting set.

I'm stumped then. Your code seems to work for me.
Post Reply