-3

I am asking for help on two things. So basically I have made a messaging website for my friends and I but I have came across two things:

  1. My messaging website tells you what time the message was sent, but at the minute each time you hit refresh the time changes to the current time

  2. At the minute you have to refresh to view a new message so I was wondering how I would make this automatic, I've heard of Ajax but I didn't understand how it works so you would need to explain it to me

Here's the code:

<?php
  $username = "";
  $password = "";
  $server = "";
  $database = "";
  mysql_connect($server, $username, $password);
  @mysql_select_db($database) or die("Unable to select database");
?>

<!DOCTYPE HTML>
<html lang="en">

  <head>
    <meta charset="UTF-8"/>
    <title>b!ip</title>
  </head>

  <body bgcolor="#0000FF">

    <?php
      date_default_timezone_set('Europe/London');

      $searchQuery = "SELECT * FROM messages";
      $searchResults = mysql_query($searchQuery);

      $firstnameData = mysql_result($searchResults, 0, 'forename'); 
      $lastnameData = mysql_result($searchResults, 0, 'surname');
      $messageData = mysql_result($searchResults, 0, 'message');
      $datetimeData = mysql_result($searchResults, 0, 'datetime');

      if(isset($_POST["submit"]))
      {
        $firstname = $_POST["forename"];
        $surname = $_POST["surname"];
        $message = $_POST["message"];
        $datetime = date('Y-m-d H:i:s');

        mysql_query("UPDATE messages SET `forename` = '$firstname' WHERE `id` = '1'");
        mysql_query("UPDATE messages SET `surname` = '$surname' WHERE `id` = '1'");
        mysql_query("UPDATE messages SET `message` = '$message' WHERE `id` = '1'");
        mysql_query("UPDATE messages SET `datetime` = '$datetime' WHERE `id` = '1'");

      }

      // date_default_timezone_set('Europe/London');
      // echo date('Y-m-d H:i:s');
    ?>

    <center>
      <table cellpadding="0" cellspacing="0" width="100%" height="50">
        <!-- MSCellFormattingTableID="12" -->
        <tr>
            <td height="50" width="100%">
            <!-- MSCellFormattingType="content" -->
            <p align="center"><font face="Comic Sans MS" color="#FFFFFF">
            <span style="font-size: 60pt">b!ip</span></font></td>
        </tr>
        </table>
    </center>
        <table cellpadding="50" cellspacing="0" width="100%" height="50%">
            <tr>
                <td height="50" width="100%">
                    <form action="#" method="POST">
                        <center><p><font color="#FFFFFF">First name: <input type="text" name="forename"/>&nbsp;&nbsp;&nbsp;
                        Last name: <input type="text" name="surname"/>&nbsp;&nbsp;&nbsp;
                        Message: <input type="text" name="message"/>&nbsp;&nbsp;&nbsp;&nbsp;
                        <input type="submit" value="Send" name="submit"/></font></p></center>
                    </form>
                </td>
            </td>
        </table>
        <table cellpadding="50" cellspacing="0" width="100%" height="50%">
            <tr>
                <td height="50" width="100%">
                    <center><h1><font color="#FFFFFF" face="Comic Sans MS"><?php echo "$messageData <strong>by $firstnameData $lastnameData at $datetimeData</strong>"; ?></font></h1></center>
                </td>
            </td>
        </table>
  </body>
</html>
Alexander Baine
  • 21
  • 5
  • 1 - You are using `$datetime = date('Y-m-d H:i:s');`, this will always return the current time. When are you saving it so it doesn't change? You are probably storing the `message`, why not the `date/time`?. You should maybe insert it into the DB too. 2 - I don't think tutorials fit here. – FirstOne Oct 21 '15 at 18:42
  • You can update multiple columns at once. You are open to SQL injections with this. – chris85 Oct 21 '15 at 18:43
  • I'm kind of a beginner at this kind of thing, plus i'm 14, so can you explain it more, thanks – Alexander Baine Oct 21 '15 at 18:44
  • You can do `UPDATE messages SET \`forename\` = '$firstname', \`surname\` = '$surname' WHERE \`id\` = '1'` etc., separate column and its new value by a comma. The user input though should be separated or at least escaped http://php.net/manual/en/function.mysql-real-escape-string.php. The preferred and more secure way to do this is with prepared statements with the `mysqli` or `pdo` driver. `mysql_` functions don't support prepared statements and are outdated. https://dev.mysql.com/doc/refman/5.0/en/update.html http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – chris85 Oct 21 '15 at 18:48
  • How would I solve the date/time problem – Alexander Baine Oct 21 '15 at 18:52

1 Answers1

0

You should probably switch to PDO or MySQLi as MySQL is depreciated and will be removed in the future. Pressing on, a future error you will encounter is that you can't use quotes (also vulnerable to SQL injection) this is because you are not doing anything to prevent this. To fix this, assure that anything from $_POST or $_GET is wrapped with mysql_real_escape_string

Addressing your time issue, you can use the MySQL function now()

Addressing your AJAX issue, you can learn from this convenient YouTube tutorial playlist : https://www.youtube.com/watch?v=tp3Gw-oWs2k&list=PL6gx4Cwl9DGDiJSXfsJTASx9eMq_HlenQ

After patching everything mentioned you code should look something like this: 

if(isset($_POST["submit"]))
{
    $firstname = mysql_real_escape_string($_POST["forename"]);
    $surname = mysql_real_escape_string($_POST["surname"]);
    $message = mysql_real_escape_string($_POST["message"]);

    mysql_query(
        "UPDATE messages SET 
            `forename` = '$firstname',
            `surname` = '$surname',
            `message` = '$message',
            `datetime` = now() 
        WHERE `id` = '1'"
    ); 

}

Something to take note of is that I left id = '1' as is because I'm not familiar with your DB; regardless it appears that you're only changing the values of one message.

Hope this helps!

Jujunol
  • 457
  • 5
  • 18