| SitePoint Sponsor |
0000-00-00 date automatically added?
I am new to PHP-mySQL and have two questions
1. The web page automatically inserts or updates an empty/null date field with 0000-00-00. Why and how do you turn it off?
2. If you create/update a record in mySQL you can have null fields (including date). When you open the same record in the browser it displays nasty error messages - "Warning: Undefined index: (fieldname) in (\\blah\blah.php) on line ###". Why and how do you avoid it?
tha
Bill
You're asking us to troubleshoot a script that we don't know anything about, namely because you haven't provided any information.
None of these problems are inherent to PHP/MySQL in general, rather they are obviously the result of something in some script you're using.
Chris Beasley - I publish content and ecommerce sites.
Featured Article: Free Comprehensive SEO Guide
My Guide to Building a Successful Website
My Blog|My Webmaster Forums
For question 1 what are the settings on the dat column. If you have 'allow null' with no default, you can in fact have a NULL value saved within the db.
Maelstrom Personal - Apparition Visions
Development - PhP || Mysql || Zend || Devshed
Unix - FreeBSD || FreeBsdForums || Man Pages
They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.
The date column can accept a null value. If you add a record in mysql it is displayed as 'null'.
Sorry about not including a code sample, but what I had would not be concise or understandable. I just created a page based off of one of the excellent tutorials available and added a date field. When a record was submitted it automatically added the 0000-00-00 value. When viewed in mysql the record created by mysql has a null value and the one created by the web page has 0000-00-00.
Did I configure some default setting somewhere incorrectly?
tha Bill
Post the code and you'll get a good answer. Don't post the code and you'll get guesses.
Why do you do you think your code won't be understandable? If someone can't understand your code they probably can't fix your problem. Also, don't worry about posting a big hunk of code... experienced eyes can skim for the pertinent parts. Do keep it formatted with the php formatting controls though... that helps.
Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
I'm back and bringing code...
I have enclosed my code for your review. Unfortunately, it doesn't run and I am completely stumped as to why. It gets to the last line and blows up with a "parsing error".
Is this a reasonable way to build a multi-action page?
Like I said, I'm a newbie and really appreciate your comments (please be kind).
As far as the date field... In a rare moment when the enclosed code actually worked, it did add 0000-00-00 to the date field even though the form data field was blank. It's probably something very simple, so simple the books gloss over it, but it has me stumped.
Thanks, Bill
<!-- Created: bmais
Date: 5-1-2002
Purpose: test date entry
Tools: php and mySQL latest production versions
Database create statement
create table datetest (
ID int not null auto_increment primary key,
tname varchar(50) not null,
tdate date,
tnumber int(11)
);
HOW THIS FORM WORKS
The form has two areas. The upper part which displays form fields for
data entry. The lower part displays a table with all the records. Clicking in
the left column of the table should move that record to the upper portion for
editing. There is a href for adding a new record.
Nice plan too bad it doesn't work...
-->
<html><head><title>DateTest</title></head>
<body>
<?php
// login to db
$cnx = @mysql_connect("localhost", "", "") or die ("Could not connect");
$db = @mysql_select_db("olb") or die ("Could not select db");
switch ($mode): //what button was clicked
case("newrecord"): //add record href - line 116
$tname = "";
$tdate = "";
$tnumber = "";
break;
case("submit"): //submit button on form - row 86
if (!isset($id)) { // no id - must be new
$sql = "INSERT INTO datetest
(tname, tdate, tnumber)
VALUES('$tname', '$tdate', $tnumber)";
$result = @mysql_query($sql);
if (!$result) {
echo("<p>Insert failed: ". mysql_error(). "</p>");
}
$id = mysql_insert_id();
} else { //edited record - commit changes to db
$sql = "UPDATE datetest SET
(tname, tdate, tnumber)
VALUES('$tname', '$tdate', $tnumber)
WHERE ID=$id";
$result = @mysql_query($sql);
if (!result) {
echo("<p>Update failed: ". mysql_error(). "</p>");
}
}
break;
case("edit"): // edit button on table - build table and show all records
$sql = "SELECT id, tname, tdate, tnumber
FROM datetest
WHERE id=$id";
$result = @mysql_query($sql);
if (!$result) {
echo("<p>Unable to get record to edit: ". mysql_error(). "</p>");
}
while ($row = mysql_fetch_array($result)) {
$tid = $row["id"];
$tname = $row["tname"];
$tdate = $row["tdate"];
$tnumber = $row["tnumber"];
}
endswitch;
?>
<form action="<?=$PHP_SELF?>" method="post">
<p>
Enter/update record<br>
Name: <input type="text" name="tname" value="<?=$tname?>"><br>
Date: <input type="text" name="tdate" value="<?=$tdate?>"><br>
Number: <input type="text" name="tnumber" value="<?=$tnumber?>"><br>
<input type="submit" name="submit" value="submit">
</p>
<br><hr><br>
<table>
<tr>
<td>Edit</td><td>Name</td><td>Date</td><td>Number</td>
</tr>
<?php
$sql = "SELECT id, tname, tdate, tnumber FROM datetest";
$result = @mysql_query($sql);
if (!$result) {
echo("<p>Unable to get records: ". mysql_error(). "</p>");
}
while ($row = mysql_fetch_array($result)) {
$rid = $row["id"];
$rname = $row["tname"];
$rdate = $row["tdate"];
$rnumber = $row["tnumber"];
?>
<tr>
<td><a href="<?=$PHP_SELF?>?mode=edit&id=$rid">Edit</a></td>
<td><?=$rname?></td>
<td><?=$rdate?></td>
<td><?=rnumber?></td>
</tr>
}
<tr>
<td colspan="4">
<a href="<?=$PHP_SELF?>?mode=addrecord">Add Record</a>
</td>
</tr>
</table>
</form>
</body>
</html>
One parse eror is here (towards the bottom of the code, end of the last while loop):
The bracket needs to be in php tags. Right now it is not so the while loop is open.PHP Code:<td><?=rnumber?></td>
</tr>
}
<tr>
Change it to:
Or something like.PHP Code:<td><?=rnumber?></td>
</tr>
<?php } ?>
<tr>
Also: if you put [php] and [\php] (use a / instead of a \) around your code it'll be syntax highlighted and indents won't be lost.
Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
well to answer your unanswered 2nd question: the warning is due to a particular field not having a value when you retrieve it... this would cause $row (assuming you use $row=mysql_fetch_array()... i didn't look at your code cos i'm lazy and prefer syntax-highlighted codeOriginally posted by bmais
2. If you create/update a record in mySQL you can have null fields (including date). When you open the same record in the browser it displays nasty error messages - "Warning: Undefined index: (fieldname) in (\\blah\blah.php) on line ###". Why and how do you avoid it?
you can fix this by testing wheter the index exists first using isset() before trying to echo it out...
PHP Code:if(isset($row['user']))
{
echo $row['user'];
}
else
{
echo "";
}
For mySQL manualOriginally posted by bmais
I am new to PHP-mySQL and have two questions
1. The web page automatically inserts or updates an empty/null date field with 0000-00-00. Why and how do you turn it off?
PaulIllegal DATETIME, DATE, or TIMESTAMP values are converted to the ``zero'' value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000)
... or by changing error report in php to a lower level, just put at the top of your scriptOriginally posted by redemption
well to answer your unanswered 2nd question: the warning is due to a particular field not having a value when you retrieve it... this would cause $row (assuming you use $row=mysql_fetch_array()... i didn't look at your code cos i'm lazy and prefer syntax-highlighted code
you can fix this by testing wheter the index exists first using isset() before trying to echo it out...
PaulPHP Code:error_reporting(E_ALL & ~(E_WARNING | E_NOTICE));
Not usre about this, but shouldn't it be $rnumber?Originally posted by samsm
One parse eror is here (towards the bottom of the code, end of the last while loop):
The bracket needs to be in php tags. Right now it is not so the while loop is open.PHP Code:<td><?=rnumber?></td>
</tr>
}
<tr>
Change it to:
Or something like.PHP Code:<td><?=rnumber?></td>
</tr>
<?php } ?>
<tr>
Also: if you put [php] and [\php] (use a / instead of a \) around your code it'll be syntax highlighted and indents won't be lost.
- Nathan
LOL... about ten characters from the bracket I pointed out and I missed itOriginally posted by someonewhois
Not sure about this, but shouldn't it be $rnumber?
Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
Posting Permissions
Bookmarks