p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Re: MySQL is cranky


Message #1 by "Gellings, C.O." <gellingsco@p...> on Sun, 16 Feb 2003 22:41:16 +0100
I'd like to come back on this subject. I have to revise my opinion on MySQL 
is cranky because it is NOT! (maybe a bit)
this was the posting:
>I have a MySQL column defined like this:
>
>    entryStatus enum('p','d') DEFAULT 'p' NOT NULL,
>
>I have a form that I've been testing. I've been delibrately not filling 
>the input that is meant to correspond to this column. MySQL is supposed to 
>put in 'p' whenever the user forgets to put in anything. This is not 
>happening. I've just made 3 entries and, looking at the database through 
>phpMyAdmin, I see that this column is being left blank.
>
>How can this possibly be? An ENUM column that's NOT NULL should have 
>nothing in it save something from the ENUM set, yes?
It's rather logical when one looks at it.
The ENUM value is empty or '' after having inserted a record when using 
$_POST or $_GET variables, because the value from the form is set to '' or 
NULL if not checked or filled in. Thus, after the insert-statement has been 
built it will have an empty value for the ENUM field.
Example:

table: example
field_id -> autoincrement
field_gender -> ENUM('m','f') NOT NULL DEFAULT 'm'
field_name ->   CHAR(30) NOT NULL
(etc.)

create the INSERT-statement with the $_POST variables:
$sql = "INSERT INTO example VALUES('','{$_POST["field_gender"]}', 
'{$_POST["field_name"]}')";

this will result in an incorrect record .....
it's wiser to build the INSERT-statement as follows:

// Get the column names ...
$1st SQL = "SHOW FIELDS FROM example";
$colname = array();
$result = mysql_query($1stSQL);
while($rs = mysql_fetch_array($result))
         $colname[] = $rs['Field'];
// get the data ...
$fields = array();
for($i == 0; $i < count($colname); ++$i){
         if(isset($_POST[$colname[$i]]) && $_POST[$colname[$i]] != ''){
                 $fields['col'] [] = $colname[$i];
                 $fields['val'] [] = $_POST[$colname[$i]];
// or
//              $col .= ($col != '' ? ",'{$colname[$i]}'" : 
"'{$colname[$i]}'");
//              $val .= ($val != '' ? ",'{$_POST[$colname[$i]]}'" : 
"'{$_POST[$colname[$i]]}'");
                 }
         }
// the above code only adds values and skips fields which are NULL or empty 
thus creating an insert statement with fields which do have a value
$2ndSQL = INSERT INTO example (".implode(',' , $fields['col']).") 
VALUES(".implode(',', $fields['val']).")";
// or
// $2ndSQL = INSERT INTO example ($col) VALUES($val)";
$result = mysql_query($2ndSQL );

Enjoy

Carl


  Return to Index