MYSQL :: Curdate() Is 0000-00-00 For New Entry

MYSQL Date Entry Shows 0000-00-00
i want the user to type in the date. Everything works fine and the form submits. When i open the database and query the results, the date field shows only 0000-00-00. It doesn't show the date entered (ex 05/23/07). What am i doing wrong?

I'm thinking it has to do something with converting the string to a date? If so, how do i do that in PHP.

0000 Turns To 0 With INT
Okay I need an id that is 4 (four) digit id but when I put 0000 its turns to 0 how to I avoid this.

How Do You Enter An Entry With A Newline Inside The Entry?
I'm trying to enter an entry into the table. However, my entries must have newlines inside each entry. Further more, i'm trying to do this from an excel file(ie. first by saving it to a text file and then using LOAD DATA INFILE..).

'datetime' Shows Only '0000-00-00 00:00:00'
I currently have a form set up on a site to send data entered into a database.
Now, the form submits correctly, the data is stored in the database correctly,
but the 'datetime' shows up as '0000-00-00 00:00:00' in each record.

I set the datetime variable to 'not null' and when I update the table, it sets the
default value to '0000-00-00 00:00:00'. Am I forgetting to set something or
where and how does the form get that date and time info?

0000-00-00 00:00:00 Format Related Question ...
I am trying to count number of rows by month and year. In the mysql db, I am recording data as NOW(), means 0000-00-00 00:00:00 format.

Problem With 0000-00-00 In 'Date' Field ?
I have a field in a table called 'ShipDate'. This particular field has allow null set to no. So by default this sets all the records to show 00/00/0000 in the field for each record that has no data entered.

In my .asp pages I am trying to test against this empty field.

i.e.

If RS("ShipDate") = 0000-00-00
or
If RS("ShipDate") = 00/00/0000

The only thing that seems to work is if I am testing for records with
a date in that field,

If RS("ShipDate") > 0000-00-00

How can I test if that field is empty ?, nothing seems to work ?

Date Field Saved As 0000-00-00
I have a table with a field type date and null is set to yes. How can I insert a blank value without it being save as 0000-00-00.

Curdate()
Is there anyway to display dates different in MYSQL. I mean dates are presented as y/m/d in mysql and the year is displayed in all four digits (ie 2006/11/05). Is there any way to display dates in mysql like this: d/m/y (ie 05/11/06).

CURDATE()
I am using the following query

select DATEDIFF(CURDATE(),'2006-06-12');
It says incorrect datevalue: CURDATE().

How can I retrive and use current date in the above query.

Date Field In Database Shows 0000-00-00 00:00:00
i have the following in my list of variables (partial list):

$id = $_POST['id'];
$name = $_POST['name'];
$date = date("D, d M yyyy, h:ia T");

and my connection includes:
$sqlquery = "INSERT INTO $table VALUES('$id','$name','$date')";

all other values are posting except the date.

in the mysql database, the date field is set up as follows:

Field Type Null Default
date timestamp Yes CURRENT_TIMESTAMP

my objective is simply to add a date field with the current date and time. i want mysql to insert the date for me in the format resulting in this type of output:

Fri, 19 Aug 2005, 10:23am CST. i just wasn't sure whether i was supposed to
include the date variable or not.

Curdate() Question
I'm a total novice when it comes to PHP/MySQL and need your help.
I have a mysql db with 3 tables (date, typ of meal, meal).

If "date" holds today's date, f.e. 2007-02-27, then it should echo out the type of meal and meal. Suppose this is done by using Curdate()?

There will be 3 different typ of meals for every day, how do I set it to echo out those 3?

Using Curdate() With Mysql ?
I have an asp page running from a mysql db. A line in my asp code runs as follows:

.........WHERE ShipDate >= curdate()

This displays the data until 'shipdate' (from the database) is less than curdate(). The problem I have is that I need the data to appear until 7 days after the shipdate. I have tried things like curdate()+7, but this does not work. I have also tried Shipdate+7, but this returns incorrect records from the past ?

Error Using CURDATE()
I have a join form on my website which adds the current date to the database so I know when people have joined.For this, my INSERT query looks like:

$sql = "INSERT INTO roster SET " . "first_name='$first', " . "last_name='$last', " . "email='$email', " . "password='$password', " . "join=CURDATE()";

But, when I submit the form, I get the error:

Quote: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join=CURDATE()' at line 1

What is wrong with my syntax, as I have another INSERT query that uses exactly the same thing.

Using CURDATE With UNIX Timestamp
I have a table where the dates are stored in UNIX timestamp format. I'm trying to use the CURDATE term for a select query but it isn't working. Does CURDATE work with the UNIX time stamp?
Any help is appreciated.
here's the query:
select first_name, last_name, date_expires, title
from cl_member, `user`, cl_chapter
where cl_member.user_id=`user`.user_id
and cl_member.chapter_id=cl_chapter.chapter_id
and cl_member.chapter_id=14
and month(date_expires)=month(CURDATE())
ORDER BY last_name

Using LOAD FILE In Conjunction With CURDATE()
I'm using the LOAD FILE query to load CSV files directly into my table, but I'd like to have CURDATE() as one of the fields - is this possible?
Here is the query I am currently using:
MySQL
LOAD DATA INFILE '/www/{$csv[$i]}' INTO TABLE redirects FIELDS TERMINATED BY ',' ENCLOSED BY "'"
My CSV files currently look like this:
PHP
'some data','some more data',CURDATE()
I don't get any error messages, but when I look at the table, all the dates are 0000-00-00

Removing Year From CURDATE Or TO_DAYS
I'm trying to have a "today in history" page built with records pulled from a mysql table, and I just realized why it's not working... the year. I've read other threads here ("list birthdays in next 7 days", etc.) that point toward removing the year, but they also go by "DAYOFYEAR" in the cases I've found.

Here's the query I have now, which works if the years matchup:

SELECT DATE_FORMAT(`gamedate`, '%Y') as `gamedate`, location, opponent_id, oppname, result, score1, score2
FROM games, opponent
WHERE games.opponent_id = opponent.oppid AND TO_DAYS(CURDATE()) = TO_DAYS(`gamedate`)");
I've tried putting in a " - year " as I've noticed done on the other threads, but am getting nowhere.

How To Set The Default For A Datetime Column To Curdate()
I'd like to set the default value for a datetime type column in a table to today's date. It seems I should be able to do this with the CURDATE() function.

I tried adding a call to the function in the default value field when creating the table's column, by when I save the table the default reverts to "0000-00-00 00:00:00"

I've done this in other RDMSs. How can I do it in MySQL?

Selecting A Date Range Using CURDATE()
When a user selects a page with the recordset I want it to list dates that
fall into a certain range, related to CURDATE(). The range fields are
start_date and end_date, which are both DATETIME column types.

Using BETWEEN won't work because it won't list a date that starts BEFORE
CURDATE() e.g.

If CURDATE() is 2005-08-10

Then an event that has a start_date of 2005-08-06 and an end_date of
2005-08-26 won't show in the list, even though the date range does fall into
the CURDATE() of 2005-08-10.

I Want To Store Date With Curdate + Coming 1 Month In Database
I want to insert curdate with upcoming 1 month in the database.

For example: Actdate= 2006/12/20 expdate=2007/01/20

But it will generated auto to save in database,

My code is:

Last Entry
When i view tables and rows in phpmyadmin, the latest entry (albeit the higher ID number) is on top. Does it work that way inthe internals of mysql? If I select 10 rows with a limit of 10, will i get the latest inserts (assuming the last row id was 49, 49 48 47 46 45... instead of 1,2,3... )?

Duplicate Entry For Key 1
This has me completely stumped, and I've never see anything like it.

Here's my query:

SELECT mus_searchsynonyms.good_term
, mus_searchsynonyms.alt_term
FROM s01_MUS_SearchSynonyms AS mus_searchsynonyms
WHERE mus_searchsynonyms.active = 1
AND mus_searchsynonyms.alt_term = ".quote_smart($search_query)."
AND mus_searchsynonyms.auto_replace = 0
When I use PHP to execute the query, I get the following error message (mysql_error()): (obviously <<$search_query>> is whatever the value of the variable is)

Quote:

Duplicate entry ?-<<$search_query>>' for key 1

When I run the query in phpmyadmin, the query is fine...

First, I don't know why I'd get a key error on a select query, 2nd, there aren't any duplicates in my table...

Most Popular Entry?
I have a quite simple question which I hope can be answered.

Whenever someone downloads a video from my site, a database entry is made with the time and name of the video. I run a php script everyday which discards entries older than 7 days. So I have all that figured out.

What I want to know is, how do I work out what the 10 most popular entries are in the database (by name)?

For example, 8 entries may have the name Video1.wmv, 4 entries may have the name Video4.wmv.etc.

I'm not sure if this can be done via a mysql query, or if php will somehow have to select the most popular entries.

Can't Get Newest Entry
I am trying to select two entries from two databases with one query:

$query = "SELECT ibf_members.id,ibf_members.members_display_name,ibf_member_extra.interests,ibf_member_extra.website,ibf_member_extra.avatar_location,ibf_members.joined FROM ibf_members,ibf_member_extra ORDER BY `joined`,`id` DESC LIMIT 1";
However, it won't show the latest member. It always shows member #1 which is the earliest member. I'm probably doing this wrong. Please help if you can.

Deleting An Entry?
I am using v4.0.20, and I need to figure out how to delete an entry. The database is for a game server I run. It is private, and so I have to add and remove members manually. Here is what I enter to add a person;

INSERT INTO Permissions VALUES ('Sue', 'PRIVATE');

I have never had to remove a player until now! How would I go about removing this entry?

If Entry Not Found
I want to query my data base for a certain user and id and I want to display a message user not found if the entry isn't there or display the entry if it is.

GUI For Data Entry
I am new to MySQL. 10 years ago I was into filemaker development. FileMaker has very nice built-in tools for creating user interface forms for data entry, retreival, editing, etc. So, what tools (preferable for Mac OS X) are available do this with MySQL?

Anyone know of any good apps for taking a MySQL database and automatically creating the PHP code to interact with the database? I've seen one or two for Mac OS X but the Mac OS X solutions seem to be somewhat half-baked compared to the Windows solutions.

Selecting Last Entry
All I need some help i have been trying to write this program for a friend that owns a internet cafe. The DB he uses is in sql he wants people to be able to see their time on his website i almost have it all written except for one problem when it selects there time it brings up all the changes to their account as in if he bought a hour yesterday it would so it i want it to show the current or last entry in the columns heres the code

SELECT userid, username, usertime, datetime
FROM users a, log b
where username = 'Their UserName'
order by datetime;

Is there any command so it shows only the last entry?

Double Entry
Howe can i prevent a double entry in a database.
Users can input their data into table.
But how to prevent a double entry.

Users can enter name and date
So users can enter their names only on one date.

Data Entry Via GUI
Is there a tool for MySQL for creating data-input forms, similar to the FORM VIEW in MS Access, that do not require programming to simply insert data into existing cells or to add rows for new data (and then insert the data)? I looked at the MySQL Browser GUI tool, but didn't see anything about data entry.

Only Allowing One Entry
I am trying to create a simple guetbook. I have it working, but the thing is, it only lets me enter one entry and can't work out why.

This is the insert code i have:

PHP Code:

$insert="Insert into visitordata (name,email, location) values('$name','$email, '$location')";
mysql_query($insert) or die("Could not insert your entry");

Duplicate Entry
I'm currently developing a shopping cart for a local business using php and mysql. I was testing the registration form on the site when all of a sudden I got this error: "Duplicate entry 'tom@thisi.com' for key 1" (that's just a random test email by the way).

I worked out that I only get this error when I use an email that I've used before to signup. Even though I had deleted the previously used email address, it returns an error that it's a duplicate entry. Another strange thing is that the 'email' field is just an ordinary field so a duplicate entry shouldn't cause a problem anyway. If I truncate the table, it's all fixed of course - that's until I use that email address, then delete that account and try and use that email address again.

Match An Entry
Software receives alerts and feeds them into a database table. I need a stored procedure that takes each entry before it enters the table and compares it with the entries already in the table. If the entry about to be added matching one that is already in the table...I need it to cancel each other out and be removed from the database table. If the entry about to be added DOESN'T have a match then it should be added to the table. Is this possible?

Update Entry
Below is the code that I am using. It does the updating for me which is perfect but I cannot get the date part right. It updates all entries. I want it to update any entry that is one day older than presentday.
so update entry if curdat = yesterday.. Please help!

result = mysql_query("UPDATE `shows` SET status=81 WHERE date <= (CURDATE() - INTERVAL 1 DAY)");

Duplicate Entry
I have a table with 10 columns and i insert data into it which is unique in a column called (scrape), so to do that i use
Code:

<?
$reb = select count(*) from table where scrape = '$scrape';
if (mysql_result($reb,0) > 0) {
echo "Item Already Added!<br>";
} else {
add the item
}
?> //I know it's a n00bish way

Validate Entry
How can i validate entries based form Table1 before adding to table2. It means, all entries can be entered into table2 as long as it is already in table1

Table1
id val1
1 002
2 003
3 004

Table2
id val2
1
2
3

Duplicate Entry
I am with a hosting providing use xcart and i keep tgettin this error message:

Quote: INVALID SQL: 1062 : Duplicate entry '' for key 2
SQL QUERY FAILURE: INSERT INTO xcart_products (provider, add_date)
VALUES ('master', '1109438680')

First Letter Of An Entry
Is there a query that allows me to select an item that starts with a specific letter, or a range of letters?
for example, i have:

apple
banana
orange

in my table. Can i issue a query that selects anything that has the first letter in the range a-d?

Duplicate Entry Error ?
I have this table, now i won't it to be possible to have duplicated entrys. Is that possible with this table, or would i have to give it a column with autoincreasment or something?

Mysql, 2nd Highest Entry
how to pick 2nd highest entry from any table in mysql database??
for highest entry i can go by order by desc clause
well limit 0,2 can give me 2 highest entries
but what if i want only 2nd highest entry
any help would be gr8ly appreciated

DELETE DUPLICATE Except LAST ENTRY?
- HOW TO DELETE DUPLICATE FROM MYSQL EXCEPT LAST ENTRY?

CREATE TABLE `chatone` (
`id` int(255) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`message` varchar(250) NOT NULL default '',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `chatone`
--

INSERT INTO `chatone` VALUES (1, 'test', 'orange', &#55614;&#57159;-03-13 15:25:24');
INSERT INTO `chatone` VALUES (2, 'test', 'orange', &#55614;&#57159;-03-13 15:25:24');
INSERT INTO `chatone` VALUES (3, 'test', 'apple', &#55614;&#57159;-03-13 15:25:48');
INSERT INTO `chatone` VALUES (4, 'donald', 'hello', &#55614;&#57159;-03-13 15:25:48');

Pulling Out The 2nd Entry From A Table
i have an application already installed. It has an table to handle attachments to articles. The structure is;

attachment_id |question_id| file_path

Is it possible to pull out the 2nd entry in a table for a particular question (using its question_id)?

eg something like?
Select * From attachments WHERE question_id = $question_id Limit ROW 2

Ive search the mysql online manual but im getting really lost!

Duplicate Entry Error?
I am trying to insert new data into my database via a form, but i get this error:

Duplicate entry Ɖ' for key 2

I think I have made a mistake while made my MySQL database.

ERROR: Duplicate Entry '1' For Key 1
I'm working on this little project. I'm learning the basics right now of SQL. I'm currently getting this error

Duplicate entry '1' for key 1

I have no idea what it means. I'm working with two files here. One is creating tables, while the other is inserting data. If you have any idea what the problem is I'd appreciate it. Here's the code.

creatmovie.php

<?php
//connect to mysql
$connect = mysql_connect("localhost", "root", "pass")
or die("Check your connection");

//create the main database
$create = mysql_query("CREATE DATABASE IF NOT EXISTS moviesite")
or die(mysql_error());

//make sure our recently created database is the active one
mysql_select_db("moviesite");

/....................................................................

Check If An Entry Exists
I have a table which records information on ratings that each user has given each movie, it looks a little like this:

Table = USER_VOTES

USER_ID MOVIE_ID VOTE
1 2 5
2 3 5
3 4 5
3 5 5
4 3 5

I an using a simple SELECT command to find if a user has already voted, ie. if a row already exists with their USER_ID and MOVIE_ID. I am having a little trouble with this SELECT statement and wonder if there is a better way of doing this simple check. My code looks like this:

$sql = "SELECT vote FROM user_votes WHERE movie_id='$id' && user_id='$usrID'";
if ($result = mysql_query($sql, $db)) {
//if vote already exists display error
$error = "You have already voted for this moive
(your rating was ".mysql_result($result,0,"vote").").";
} else {
//if no vote exists process vote...
}

If a user has not voted before I get an error like this:
Unable to jump to row 0 on MySQL result index 2

To me it appears that mysql_query() does not return false when no results are found. Is there a command which does check results and return false or is there a much easier way to check, perhaps there could be some checking code in the actual SQL query?

How To Access The Last Entry For Different Systems
How would I access the values of the last datetime stamp for different system id's present in the table?

For example, if i have 5 system id's their last entered datetime stamps might be different. How would i access the related values for the systems?

Data Entry Application...
I need to build a data entry application that will allow me to seek out a record in mysql by an index number and quickly modify the records contents. This will be for dealing with direct mail response information.

Any suggestions for a language to build this in? Or existing programs to do this?

I tried to build it in a web environment with javascript, but am having trouble reducing the necessary key strokes/clicks to complete one cycle of entry.