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.

[ZeldorBlat]

Either don't include them in the insert:

insert into data (name, email) values ('$name', '$email')

or make the variable 'null' (literally). Something like:

$age = !empty($age) ? $age : 'null'; //'null' is a string containing
the word 'null'

Then do your query as before.

[Dave Thomas]

ZeldorBlat wrote:

Either don't include them in the insert:

insert into data (name, email) values ('$name', '$email')

or make the variable 'null' (literally). Something like:

$age = !empty($age) ? $age : 'null'; //'null' is a string containing
the word 'null'

Then do your query as before.

I was trying to do it without having to take it out of the query or
assign it NULL... just because there are a large number of variables
that could be empty.

[ZeldorBlat]

If you want the value to be null in the database, then you need some
way of telling MySQL that's what you want. One way is to ommit it from
the insert -- in which case the default value (in this case, null) is
used. The other way is to specify the value as the literal string
'null'.

[ranii4u]

set default value to 0 gor AGE field in structure of database!

[ranii4u]

just dont enter in that field while executing anu INSERT query it will
automatically take that field DEFAULT value NULL without any error.

[ranii4u]

Dont include that field in ur INSERT clause while inserting record it
will take DEFAULT value as NULL as u have specified and set NULL
attribute as 'yes' in ur structure of table and DEFAULT value as 'null'

OR IF,

ur getting values dynamically from user then store all values in
variable and then execute INSERT query even if any value will come null
it will be store in variable and that all variables u used in ur INSERT
query!

[kerul4u]

Just change your insert query with

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

don't use $age, instead use '$age'

this means when query will be executed it will look like
INSERT INTO data (Name, Email, Age) VALUES ( 'myname', 'email', '' ) if
user is not specifying his/her age.

currently your query looks like this
INSERT INTO data (Name, Email, Age) VALUES ( 'myname', 'email', ) if
user is not specifying his/her age.
and it gives you syntax error.

Do you understan wat i mean?

just do it for all your numeric vaule to be entered.

and recall your PHP fundamentals.

KERUL
['ProDesignZ']

[johan]

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

[Kimmo Laine]

"johan" <va*******@hotmail.com> kirjoitti
viestissä:pa****************************@hotmail.c om...

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.

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

--
SETI @ Home - Donate your cpu's idle time to science.
Further reading at <http://setiweb.ssl.berkeley.edu/>
Kimmo Laine <et****************@5P4Mgmail.com>

[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_year">
<?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="selected"';
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($_POST["submit"]) and
($_POST["submit"] == "insert"))) {
if(isset($debug)) print "<!-- submit is set -->\n";
$query =" INSERT INTO jobs SET";
if(isset($_POST["PatientLastName"]))
$query = $query." PtNameLast = \"".
mysql_escape_string($_POST["PatientLastName"])."\", ";
if(isset($_POST["PatientFirstName"]))
$query = $query." PtNameFirst = \"".
mysql_escape_string($_POST["PatientFirstName"])."\", ";
if(isset($_POST["Prescriber"]))
$query = $query." Prescriber = ".$_POST["Prescriber"].", ";
if(isset($_POST["office"]))
$query = $query." OriginatingOffice = ".$_POST["office"].", ";
if(isset($_POST["DueDate"]))
$query = $query."DueDate = \"".
mysql_escape_string($_POST["DueDate"])."\" ,";
if(isset($_POST["arrivialDate"]))
$query = $query."arrivialDate = \"".
mysql_escape_string($_POST["arrivialDate"])."\", ";
if(isset($_POST["RxDate"]))
$query = $query."RxDate = \"".
mysql_escape_string($_POST["RxDate"])."\",";
if(isset($_POST["WOnumber"]))
$query = $query." WOnumber = \"".
mysql_escape_string($_POST["WOnumber"])."\"";
if(isset($debug)) print "<!-- $query -->\n";
$result = mysql_query($query) 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?