[RESOLVED] If > Else - Replacing with a NULL value

Drabin · 02-14-2006, 01:01 PM

Here's my form script:

PHP Code:


			
<?php

if ($post == "yes") {



    //THEN WE ARE ADDING A NEW RECORD SUBMITTED FROM THE FORM

    //REPLACE THE FIELD CONTENTS SO THEY DON'T MESS UP YOUR QUERY



/*-- SECTION: 9994SQL --*/

$sql = "INSERT INTO fab_zg (

guid,

coins,

bijous,

primals,

common,

rares,

epics

 $addl_insert_crit ) VALUES ( '$guid',

'$coins',

'$bijous',

'$primals',

'$common',

'$rares',

'$epics'

 $addl_insert_values )";

    if ($sql_debug_mode==1) { echo "<BR>SQL: $sql<BR>"; }

    $result = mysql_query($sql);

    if ($result == 1) {

    die('Guild Member Data Updated.<br><br><a href="'.$website_address.''.$zgfabloot_dir.'zgadmin.php">Click Here to return to zg admin</a>');

    } else {

    echo "Error inserting record (9994SQL)";

    }

//***** END INSERT SQL *****



} //END IF POST = YES FOR ADDING NEW RECORDS



if (!$post) {

//THEN WE ARE ENTERING A NEW RECORD

//***** BEGIN ADD NEW FORM*****

    /*-- SECTION: 9994FORM --*/

    ?>

    <form method="post" action="<?php echo $thispage; ?>?proc=New&post=yes&<?php echo $pagevars; ?>">

    <center>

    <TABLE>

    <TR>

        <TD>

            <B>GUID:</B>

        </TD>

        <TD>

<?php echo '<select name="guid">';for ($i = 1; $i <= 200; $i++) {echo '<option>' . $i . '</option>';} echo 'NAME="guid" SIZE="1"></SELECT>';?>

    </TR>

        <TR>

        <TD>

            <B>COINS:</B>

        </TD>

        <TD>

        <SELECT NAME='coins' SIZE='1'>

        <option></option>

        <option>Bloodscalp</option>

        <option>Gurubashi</option>

        <option>Hakkari</option>

        <option>Razzashi</option>

        <option>Sandfury</option>

        <option>Skullsplitter</option>

        <option>Vilebranch</option>

        <option>Witherbark</option>

        <option>Zulian</option>

        </SELECT>

        </TD>

    </TR>

        <TR>

        <TD>

            <B>BIJOUS:</B>

        </TD>

        <TD>

        <SELECT NAME='bijous' SIZE='1'>

        <option></option>

        <option>Blue</option>

        <option>Bronze</option>

        <option>Gold</option>

        <option>Green</option>

        <option>Orange</option>

        <option>Purple</option>

        <option>Red</option>

        <option>Silver</option>

        <option>Yellow</option>

        </SELECT>        </TD>

    </TR>

        <TR>

        <TD>

            <B>PRIMALS:</B>

        </TD>

        <TD>

        <SELECT NAME='primals' SIZE='1'>

        <option></option>

        <option>Aegis</option>

        <option>Armsplint</option>

        <option>Bindings</option>

        <option>Girdle</option>

        <option>Kossack</option>

        <option>Sash</option>

        <option>Shawl</option>

        <option>Stanchion</option>

        <option>Tabbard</option>

        </SELECT>        </TD>

    </TR>

        <TR>

        <TD>

            <B>COMMON:</B>

        </TD>

        <TD>

                    <INPUT TYPE='common' NAME='common' SIZE='20'>        </TD>

    </TR>

        <TR>

        <TD>

            <B>RARES:</B>

        </TD>

        <TD>

                    <INPUT TYPE='rares' NAME='rares' SIZE='20'>        </TD>

    </TR>

        <TR>

        <TD>

            <B>EPICS:</B>

        </TD>

        <TD>

                    <INPUT TYPE='epics' NAME='epics' SIZE='20'>        </TD>

    </TR>

     <TR>

     <TD>

     </TD>

     <TD>

      <INPUT TYPE="SUBMIT" VALUE="Add Record" NAME="submit">

      <INPUT TYPE="RESET" VALUE="Reset" NAME="reset">

     </TD>

 </TR>

    </TABLE>

    </CENTER>

    <BR>

    </FORM>



<?php

} //END if post=""



?>

For $common, $rares, and $epics, if the form is "" (blank), I want the insert value to be NULL. It has to be NULL or else it will return a row value.

I tried something along the lines of:

Code:

if ($common == "") {
  $common = NULL;
}
if ($rares == "") {
  $rares = NULL;
}
if $epics == "") {
  $epics = NULL;
}

But all that does is insert a 'NULL' value rather than a NULL value. So, I'm not sure how to properly phrase the Insert to include the variable changes. I don't want a text NULL, I want a SQL NULL to be returned when the field is blank.

Any ideas on how to do this properly?

Thanks!

codefetch · 02-14-2006, 02:44 PM

You are getting the string null because you have quotes around '$common', '$rares', and '$epics'. Change your if then clauses to include quotes only if you are inserting a non-null text value. A quick codefetch search for sql insert null shows that NULL (without quotes) is a valid SQL insert value, at least for mySQL.

Roger Ramjet · 02-14-2006, 03:17 PM

The simplest way to guarantee NULL is to specify NULL as the default value for the column affected. Then you can just omit the column from your insert and it will default to the NULL you want.

Also, the best way to build the column and value lists is as seperate strings first

PHP Code:


			
$base="INSERT INTO fab_zg ";

$cols="guid,coins,bijous,primals";

$vals= $guid . ",'" . $coins . "','" . $bijous . "','" . $primals . "'";



if ($common != '') {

   $cols .= ",common";

   $vals .= ",'" . $common . "'";

}



if ($rares != '') {

   $cols .= ",rares ";

   $vals .= ",'" . $rares . "'";

}

//etc



$query = $base . "(" . $cols . ") VALUES (" . $vals . ")";

That way you simply include the column name and value when a valid value exists

Drabin · 02-14-2006, 03:23 PM

Quote:

Originally Posted by codefetch

You are getting the string null because you have quotes around '$common', '$rares', and '$epics'. Change your if then clauses to include quotes only if you are inserting a non-null text value. A quick codefetch search for sql insert null shows that NULL (without quotes) is a valid SQL insert value, at least for mySQL.

Yes, I understand that part and stated in my post that I understood that. What I need help with is phrasing the If > Then clause so that it properly formats the Insert SQL query.

If I remove the ('quotes') within the SQL query and place a variable, it does not work.

So, again, I need help with "creating" the if then clause to properly format the sql query.

Thanks in advance to any senior members that assist.

Drabin · 02-14-2006, 03:26 PM

Quote:

Originally Posted by Roger Ramjet

The simplest way to guarantee NULL is to specify NULL as the default value for the column affected. Then you can just omit the column from your insert and it will default to the NULL you want.

Also, the best way to build the column and value lists is as seperate strings first

PHP Code:


			
$base="INSERT INTO fab_zg ";

$cols="guid,coins,bijous,primals";

$vals= $guid . ",'" . $coins . "','" . $bijous . "','" . $primals . "'";



if ($common != '') {

   $cols .= ",common";

   $vals .= ",'" . $common . "'";

}



if ($rares != '') {

   $cols .= ",rares ";

   $vals .= ",'" . $rares . "'";

}

//etc



$query = $base . "(" . $cols . ") VALUES (" . $vals . ")";

That way you simply include the column name and value when a valid value exists

thanks - this is what I was looking for!!

I'll test it out and see how it works.

Drabin · 02-14-2006, 04:27 PM

Okay mate this works terrific. Here's the new build and I love the suggestion on the columns.

PHP Code:


			
if ($post == "yes") {



    //THEN WE ARE ADDING A NEW RECORD SUBMITTED FROM THE FORM

    //REPLACE THE FIELD CONTENTS SO THEY DON'T MESS UP YOUR QUERY

    

$base="INSERT INTO fab_zg "; 



if ($guid != '') { 

   $cols .= "guid"; 

   $vals .= "'" . $guid . "'"; 

}



if ($coins != '') { 

   $cols .= ",coins"; 

   $vals .= ",'" . $coins . "'"; 

} 



if ($bijous != '') { 

   $cols .= ",bijous "; 

   $vals .= ",'" . $bijous . "'"; 

} 



if ($primals != '') { 

   $cols .= ",primals "; 

   $vals .= ",'" . $primals . "'"; 

} 



if ($common != '') { 

   $cols .= ",common"; 

   $vals .= ",'" . $common . "'"; 

} 



if ($rares != '') { 

   $cols .= ",rares "; 

   $vals .= ",'" . $rares . "'"; 

} 



if ($epics != '') { 

   $cols .= ",epics "; 

   $vals .= ",'" . $epics . "'"; 

} 



$sql = $base . "(" . $cols . ") VALUES (" . $vals . ")";

if ($sql_debug_mode==1) { echo "<BR>SQL: $sql<BR>"; }

    $result = mysql_query($sql);

    if ($result == 1) {

    die('Guild Member Data Updated.<br><br><a href="'.$website_address.''.$zgfabloot_dir.'zgadmin.php">Click Here to return to zg admin</a>');

    } else {

    echo "Error inserting record (9994SQL)";

    }



} //END IF POST = YES FOR ADDING NEW RECORDS