MySQL Query

[Dave Thomas]

If I have a table set up like this:

Name | VARCHAR
Email | VARCHAR
Age | TINYINT | NULL (Default: NULL)

And I want the user to enter his or her name, email, and age - but AGE
is optional.

My insert would look something like:

INSERT INTO data (Name, Email, Age) VALUES ('$name', '$email', $age)

This is all good, except if the user doesn't enter an age. Then $age is
an empty variable. I thought that since the table is set up where Age
can be NULL, that this should be fine. But MySQL is giving me an error.
If $age is a number, it is no problem.

Anyway to make it accept $age as an empty variable (and just make it
NULL)? I know I could say:

if (empty($age)) { $age = 0; }

and write it that way, but this is just an example and there are many
more variables that could be empty.

Thanks.

[Hilarion]

> > BTW, a field containg an AGE has no real value. What will happen net year.

Youd'better take care of the birth day, a fixed data.

Asking age is easier on the user in my opnion, but he could store
(date('Y') - $age) instead, to get the birthyear.

He could also use HTML <select> and show "available" ages in it, but
use birth year as values (or show both but send year only). Something
like:

Age: <select name="birth_yea r">
<?php
$curr_year = intval( date( 'Y' ) );
$birth_year = @intval( $_REQUEST['birth_year'] );
for( $i = ($curr_year-110); $i <= $curr_year; $i++ )
{
echo ' <option value="', $i, '"';
if ($i == $birth_year)
echo ' selected="selec ted"';
echo '>', ($curr_year - $i), ' (', $i, ')</option>', "\n";
}
?>
</select>
Hilarion

[Christopher Pomasl]

On Tue, 30 Aug 2005 11:52:35 +0200, johan wrote:

On Mon, 29 Aug 2005 11:24:55 -0400, Dave Thomas wrote:

If I have a table set up like this:

Name | VARCHAR
Email | VARCHAR
Age | TINYINT | NULL (Default: NULL)

And I want the user to enter his or her name, email, and age - but AGE
is optional.

My insert would look something like:

INSERT INTO data (Name, Email, Age) VALUES ('$name', '$email', $age)

This is all good, except if the user doesn't enter an age. Then $age is
an empty variable. I thought that since the table is set up where Age
can be NULL, that this should be fine. But MySQL is giving me an error.
If $age is a number, it is no problem.

Anyway to make it accept $age as an empty variable (and just make it
NULL)? I know I could say:

if (empty($age)) { $age = 0; }

and write it that way, but this is just an example and there are many
more variables that could be empty.

Thanks.

BTW, a field containg an AGE has no real value. What will happen net year.
Youd'better take care of the birth day, a fixed data.

Johan

This is a response to the OP....
When I have items that are nullable, I build the list and the values
sections as I parse the page. So if itemx is there, I build the DB_ItemX
field into the the ongoing column list:
(DB_ItemA, DB_ItemB, DB_ItemX)
This is relatively easy using:
$insertList.= ',DB_ItemX';
and then it's value in the value list:
$valueList.= ",'".$formItemX ."'";

So when you get to the point of building the runnable SQL:
$insertSQL = "INSERT to your.table (";
$insertSQL.= $insertList ;
$insertSQL.= ") Values (";
$insertSQL.= $valueList ;
$insertSQL.= ")" ;

Also note that if you are supplying values for all of the columns in the
table, SQL does not require the column list. The values then need to be
in the exact same order as defined in the table though!!

Chris

[Toby Inkster]

Dave Thomas wrote:

My insert would look something like:
INSERT INTO data (Name, Email, Age) VALUES ('$name', '$email', $age)
This is all good, except if the user doesn't enter an age.

if ($age>0) $q = "INSERT INTO data VALUES ('$name', '$email', $age)";
else $q = "INSERT INTO data VALUES ('$name', '$email')";
$rv = mysql_query($q) ;

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact

[Paul C. Jackson]

Dave Thomas wrote:

If I have a table set up like this:

Name | VARCHAR
Email | VARCHAR
Age | TINYINT | NULL (Default: NULL)

And I want the user to enter his or her name, email, and age - but AGE
is optional.

My insert would look something like:

INSERT INTO data (Name, Email, Age) VALUES ('$name', '$email', $age)

This is all good, except if the user doesn't enter an age. Then $age is
an empty variable. I thought that since the table is set up where Age
can be NULL, that this should be fine. But MySQL is giving me an error.
If $age is a number, it is no problem.

Anyway to make it accept $age as an empty variable (and just make it
NULL)? I know I could say:

if (empty($age)) { $age = 0; }

and write it that way, but this is just an example and there are many
more variables that could be empty.

Thanks.

I do it this way:
if (isset($_POST["Cases"])){
if(isset($debug )) print "<!-- checking for submition of post data
-->\n";
if((isset($_POS T["submit"]) and
($_POST["submit"] == "insert"))) {
if(isset($debug )) print "<!-- submit is set -->\n";
$query =" INSERT INTO jobs SET";
if(isset($_POST["PatientLastNam e"]))
$query = $query." PtNameLast = \"".
mysql_escape_st ring($_POST["PatientLastNam e"])."\", ";
if(isset($_POST["PatientFirstNa me"]))
$query = $query." PtNameFirst = \"".
mysql_escape_st ring($_POST["PatientFirstNa me"])."\", ";
if(isset($_POST["Prescriber "]))
$query = $query." Prescriber = ".$_POST["Prescriber "].", ";
if(isset($_POST["office"]))
$query = $query." OriginatingOffi ce = ".$_POST["office"].", ";
if(isset($_POST["DueDate"]))
$query = $query."DueDate = \"".
mysql_escape_st ring($_POST["DueDate"])."\" ,";
if(isset($_POST["arrivialDa te"]))
$query = $query."arrivia lDate = \"".
mysql_escape_st ring($_POST["arrivialDa te"])."\", ";
if(isset($_POST["RxDate"]))
$query = $query."RxDate = \"".
mysql_escape_st ring($_POST["RxDate"])."\",";
if(isset($_POST["WOnumber"]))
$query = $query." WOnumber = \"".
mysql_escape_st ring($_POST["WOnumber"])."\"";
if(isset($debug )) print "<!-- $query -->\n";
$result = mysql_query($qu ery) or die("Query failed");
}
if(isset($debug )) print "<!-- finished with submitted processing
-->\n";

that way if anything is missing it doesn't bomb out the whole thing, or
make it bomb out the wjole page and send back an error to the user if
something absolutely necessary is missing

[Jim Methley]

Dave Thomas wrote:

If I have a table set up like this:

Name | VARCHAR
Email | VARCHAR
Age | TINYINT | NULL (Default: NULL)

Is it PHP that's bombing out or MySQL?