How to insert date and user_id to mysql

Question

I'm quite new in PHP and MySQL. I tried to make message board where user can post some message on wall and the every logged user can read it.

Now when someone add message doesn't write in table author_id and date_added. I need them when results are displayed.

Here is new.php

if(isset($_POST['formSubmit']))
{
    $errorMessage = "";

    if(empty($_POST['formTitle'])) 
    {
        $errorMessage .= "<li>Doesn't have title!</li>";
    }
    if(empty($_POST['formContent'])) 
    {
        $errorMessage .= "<li>The field for content is empty!</li>";
    }

    if(empty($errorMessage)) 
    {
        $db = mysql_connect("localhost","root","");
        if(!$db) die("Error connecting to MySQL database.");
        mysql_select_db("homework3" ,$db);

        $sql = "INSERT INTO massages (author_id, date_added, title, content) VALUES ('$_POST[author_id]', '$_POST[date_added]', '$_POST[formTitle]', '$_POST[formContent]')";
        mysql_query($sql);

        header("Location: index.php");
        exit();
    }
}

<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post">
<div><label for='formTitle'>Title<input type="text" name="formTitle" value=""   style="width: 350px;"></label></div></br>

<div><label for='formContent'>Content</div><textarea name="formContent" style="width: 344px; height: 100px;"></textarea>

<input type="submit" class="formbutton" name="formSubmit" value="Send"/>

</form>

Edit: I don't know if you need this but this is how I display massages:

$sql = 'SELECT username, msg_id, title, content, date_added FROM massages as m, users  as u WHERE author_id = user_id ORDER BY m.date_added DESC';

  $result = mysqli_query($link, $sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
    $real_date = date('d.m.Y', $row['date_added']);
    echo '<table>
   <tr>
   <td>' . $row['msg_id'] . '. ' . $row['title'] . '</td>
   </tr>
   <tr>
     <td>' . $row['content'] . '</td>
   </tr>
   <tr>
<td>By<span style="color: #CC0033;">' . $row['username'] . '</span> on   <span style="color: #CC0033;">' . $real_date . '</span></td></br>
</tr>
</table>';
}

}

[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about prepared statements instead, and use [pdo](https://wiki.php.net/rfc/mysql_deprecation) or [mysqli](http://stackoverflow.com/questions/tagged/mysqli). — zessx, Oct 04 '13 at 08:08
ok I have updated my answer. http://stackoverflow.com/a/19176360/829533 check it and see if it works — zzlalani, Oct 04 '13 at 09:22
also if you dont have started the session in this page, do `session_start();` in the top of the page — zzlalani, Oct 04 '13 at 09:23

Sumit Bijvani · Answer 1 · 2013-10-04T09:25:56.777

2

When author login, Store author_id in session.

$author_id=$_SESSION['username'];

Then store it in database.

$sql = "INSERT INTO massages (author_id, date_added, title, content) VALUES ('$author_id', 'NOW()', '$_POST[formTitle]', '$_POST[formContent]')";

NOTE

Don't forget to start the session on the top

<?php
    session_start();
    // then your all code

edited Oct 04 '13 at 09:25

answered Oct 04 '13 at 08:15

Sumit Bijvani

8,154
17
50
82

@zzlalani yes it will work, it returns data and time in `YYYY-MM-DD h:i:s` format – Sumit Bijvani Oct 04 '13 at 08:24
Because when I tried it with quotes `"'NOW()'"` it says `Warning: #1264 Out of range value for column 'date' at row 1`.. and added `0000-00-00 00:00:00` in the row. – zzlalani Oct 04 '13 at 08:27
@zzlalani yes don't add any quotes, it will work without quotes – Sumit Bijvani Oct 04 '13 at 08:28
NOW() write only year (2013) – Goro Oct 04 '13 at 08:32
@Goro it depends on the data type of your field. if it is set to `year` it will save year only and if it is set to `datetime` or 'timestamp' it will save it as `YYYY-mm-dd H:i:s` – zzlalani Oct 04 '13 at 08:34
@Goro you need `datetime` field to store date – Sumit Bijvani Oct 04 '13 at 08:34
Oh, yes i forgot about this. I will change it now. But still problem with author_id .. is 0 on every new add. – Goro Oct 04 '13 at 08:35
@Goro this is because you are not storing user in session. do you have implement login system? – Sumit Bijvani Oct 04 '13 at 08:38
Date is ok now. I edited my question a little with how I display the massages. – Goro Oct 04 '13 at 08:45
@Goro which session variable you are using for author? – Sumit Bijvani Oct 04 '13 at 08:55
$_SESSION['username'] – Goro Oct 04 '13 at 09:15

score 1 · Accepted Answer · edited May 23 '17 at 12:21

You can use hidden attributes i.e. type = 'hidden' for auther_id

For example in your form

<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post">
<div><label for='formTitle'>Title<input type="text" name="formTitle" value=""   style="width: 350px;"></label></div></br>

<div><label for='formContent'>Content</div><textarea name="formContent" style="width: 344px; height: 100px;"></textarea>

<input type="hidden" name="author_id" value="<?php echo $_SESSION['what_ever']; ?>"/>
<input type="submit" class="formbutton" name="formSubmit" value="Send"/>

</form>

NOTE: <?php echo $_SESSION['what_ever']; ?> is just an assumption of how your author_id could be

and for date_added you can create add this directly in the query no need to post it via form

$sql = "INSERT INTO massages (author_id, date_added, title, content) VALUES ('$_POST[author_id]', NOW(), '$_POST[formTitle]', '$_POST[formContent]')";

You should also avoid sending author_id via post and add it rather in this manner

$auther_id = $_SESSION['username'];
$sql = "INSERT INTO massages (author_id, date_added, title, content) VALUES ('$auther_id', NOW(), '$_POST[formTitle]', '$_POST[formContent]')";

IMPORTANT

PHP is deprecating the mysql functions you must need to use mysqli Why shouldn't I use mysql_* functions in PHP?

Nes · Answer 3 · 2013-10-04T09:26:24.527

1

In your table change author_id to auto increment, No need to add it in INSERT query.

Try below change:

$date_added = date('Y-m-d');

$sql = "INSERT INTO massages (`date_added`, `title`, `content`) VALUES ( '$date_added', '$_POST[formTitle]', '$_POST[formContent]')";
mysql_query($sql);

edited Oct 04 '13 at 09:26

answered Oct 04 '13 at 08:13

Nes

304
1
10

score 0 · Answer 4 · edited Oct 04 '13 at 08:23

As mentioned in the comments, you should avoid using the mysql_* commands, however the problem is with the following line:

$sql = "INSERT INTO massages (author_id, date_added, title, content) VALUES ('$_POST[author_id]', '$_POST[date_added]', '$_POST[formTitle]', '$_POST[formContent]')";

In order to embed an array variable in string you have to surround it with braces, eg.

"{$_POST['author_id']}"

BUT you should not do this in your example as it would leave you wide open to a mysql injection attack. The old way of dealing with this is to escape each of posted variable using mysql_escape_string(), but the better way of dealing with this is to use the PDO Data objects e.g. http://www.php.net/manual/en/pdostatement.bindvalue.php

How to insert date and user_id to mysql

4 Answers4