CODEWITHSUNDEEP
phpsqldatabasedatetime
nowayyy
nowayyy

Reputation: 917

Storing the date in database

Well I have a task to store "quotes" into a database (Already done this) and display them & sort them out for the most recent quotes. I'm assuming to get the "most recent", I'd need to store date/time of the submitted quote.

I am new to PHP and trying to learn, so I don't know how to exactly do this.

Here is the PHP for adding the quotes to the database. There are two columns in the table called "quotes" and "id". I'm guessing I will also need to make a column for the date too?

require('includes/connect.php');


    $quote = $_POST['quote'];
    $quotes = mysql_real_escape_string($quote);


    mysql_query("INSERT INTO entries (quote) VALUES('$quotes')")
    or die(mysql_error());

How would I also insert the date?

Upvotes: 0

Views: 237

Answers (5)

Erik
Erik

Reputation: 222

If you only want the most recent quotes, you can simply sort your result set by their id DESC assuming the id is an auto-incremented value.

Upvotes: 1

John Woo
John Woo

Reputation: 263723

use CURDATE() if you want to insert the current date

example:

$query_auto = "INSERT INTO tablename (col_name, col_date) VALUE ('DATE: Auto CURDATE()', CURDATE() )";

but if you wqant it manually then should use this:

$query_manual = "INSERT INTO tablename (col_name, col_date) VALUES ('DATE: Manual Date', '2008-07-04')";

UPDATE

CREATE TABLE auto_ins
    (
        `MySQL_Function` VARCHAR(30),
        `DateTime` DATETIME,
        `Date` DATE,
        `Time` TIME,
        `Year` YEAR,
        `TimeStamp` TIMESTAMP
    );

INSERT INTO auto_ins
    (`MySQL_Function`, `DateTime`, `Date`, `Time`, `Year`, `TimeStamp`)
VALUES
    (“CURDATE()”, CURDATE(), CURDATE(), CURDATE(), CURDATE(), CURDATE());

SCREEN SHOT

Upvotes: 1

Frederick Marcoux
Frederick Marcoux

Reputation: 2223

Use this code:

require('includes/connect.php');


$quote = $_POST['quote'];
$quotes = now().' - '.mysql_real_escape_string($quote);
// THIS WILL ADD THE DATE AND TIME TO YOUR $quotes STRING.


mysql_query("INSERT INTO entries (quote) VALUES('$quotes')")
or die(mysql_error());

Upvotes: 0

Sergey Benner
Sergey Benner

Reputation: 4431

You will need at least couple of tables who submitted the quote and the quote table itself.

create table users(id int primary key not null, username varchar(32),pwd varchar(32));

you can add any info to that table like email address and so on.

create table quotes (
id int not null , 
user_id integer,
quote_text varchar(256),
inserted_date timestamp default current_timestamp ,primary key (id));
alter table quotes add constraint fk_users foreign key(user_id) references users(id);

Otherwise feel free to modify them. It's not about php here its about DB design in general.

Upvotes: 0

capi
capi

Reputation: 1453

Yes, you need a third column lets say most_recent (defined as date or datetime) : 

mysql_query("INSERT INTO entries (quote, most_recent) VALUES('$quotes', now())")

Upvotes: 0

Related Questions