PHP linking MySql Tables

[fredundant]

Okay so I have 2 tables in my database. One called user and one called messages. A user logs in to the message board and leaves a message (eg nice website). They write in the author name and the message then after the message is posted it says

 

"Nice website"

Posted by (author) on (date).

 

All is good so far. It works. However if you look at my code you will see I have a session started. This session is storing the username of the logged in user. From the column username in the users table. (This table has has an id for each user). Ive played around with the code trying to make it so the user doesnt have to fill in the author box. I want rid of that box So the logged in user just leaves a message then it says "posted by (username) on (date).

 

Im missing something from my code.

Can anyone tell me what? Please?

 

<?php
session_start();
mysql_connect("*************", "*****************", "***************");
mysql_select_db("***********************");
$time = time();



//this checks to see if the $_SESSION variable has been not set 
//or if the $_SESSION variable has been not set to true
//and if one or the other is not set then the user gets
//sent to the login page
if (!isset($_SESSION['username'])) {
    header('Location: http://***************.com/login.php');
}

$query = "INSERT INTO messages VALUES( NULL, '". mysql_real_escape_string($_POST['message']) ."', '". mysql_real_escape_string($_POST['username']) ."', '$time' )";if( $result = mysql_query($query) ) {
        if(mysql_affected_rows() > 0 ) {
                echo "Message Posted.<br><a href='messageboard.php'>Return</a>";
        } else {
                echo 'There was an error posting your message. Please try again later.';
        }
} else {
        echo "There was a database error.";
        // comment out next line for live site.
        echo "<br>Query string: $query<br>Returned error: " . mysql_error() . '<br>';
}


;

[MatthewJ]

<?php
session_start();
mysql_connect("*************", "*****************", "***************");
mysql_select_db("***********************");
$time = time();



//this checks to see if the $_SESSION variable has been not set 
//or if the $_SESSION variable has been not set to true
//and if one or the other is not set then the user gets
//sent to the login page
if (!isset($_SESSION['username'])) {
    header('Location: http://***************.com/login.php');
}

$query = "INSERT INTO messages VALUES( NULL, '". mysql_real_escape_string($_POST['message']) ."', '". $_SESSION['username']) ."', '$time' )";if( $result = mysql_query($query) ) {
        if(mysql_affected_rows() > 0 ) {
                echo "Message Posted.<br><a href='messageboard.php'>Return</a>";
        } else {
                echo 'There was an error posting your message. Please try again later.';
        }
} else {
        echo "There was a database error.";
        // comment out next line for live site.
        echo "<br>Query string: $query<br>Returned error: " . mysql_error() . '<br>';
}


;

 

Just take out the for field for username so they don't have to enter it, and that should do it.

[fredundant]

Not to criticise as I'm sure your PHP is superior to mine. However it appears to me all you have done is taken out the escape.

As far as I'm aware you should always escape the data when passing user input into my queries?

 

However that doesn't solve my problem.

Do I need to create a new column in my table for messages?

 

My messages table has column for author, message and the date.

 

 

My user has ID name, email, username, and password.

 

Do i not need to create a user id for the messages table and cross the data over somehow? I'm way over my knowledge in what I'm trying to create as SQL is not a strong point for me.

[hoogie]

He took out the escape and also grabbed the user name from your session info rather than from your form info.  If you want to escape the session info (a good idea), just use this code:

 

<?php
session_start();
mysql_connect("*************", "*****************", "***************");
mysql_select_db("***********************");
$time = time();



//this checks to see if the $_SESSION variable has been not set 
//or if the $_SESSION variable has been not set to true
//and if one or the other is not set then the user gets
//sent to the login page
if (!isset($_SESSION['username'])) {
    header('Location: http://***************.com/login.php');
}

$query = "INSERT INTO messages VALUES( NULL, '". mysql_real_escape_string($_POST['message']) ."', '". mysql_real_escape_string($_SESSION['username']) ."', '$time' )";if( $result = mysql_query($query) ) {
        if(mysql_affected_rows() > 0 ) {
                echo "Message Posted.<br><a href='messageboard.php'>Return</a>";
        } else {
                echo 'There was an error posting your message. Please try again later.';
        }
} else {
        echo "There was a database error.";
        // comment out next line for live site.
        echo "<br>Query string: $query<br>Returned error: " . mysql_error() . '<br>';
}


;

 

Then you can safely get ride of the username textbox on your form.

 

This is the simple fix to your problem.  The advantage is that it's easy and doesn't require you to change your database structure.

 

The disadvantage is that if your user ever changes their username, it won't change the username on their past messages.  If you want it to change those automatically, you'll have to use the userid number instead.  This would mean adding a userid field to your message table and then linking the two tables together in your queries.

 

It's up to you how you want to proceed.  If you need help rewriting queries, I'm sure people here can assist you.

[MatthewJ]

Why would you need to escape the data coming from your own database to begin with?

 

I am assuming I guess that the username is put into the session from the original login process after being pulled from your database. If that is the case escaping shouldn't be an issue.

 

 

[hoogie]

Why would you need to escape the data coming from your own database to begin with?

 

I am assuming I guess that the username is put into the session from the original login process after being pulled from your database. If that is the case escaping shouldn't be an issue.

 

 

 

I guess I just figured that it doesn't hurt.  I know it's nearly impossible to fake session data, but I've heard that if you run your website off a shared server that it's technically possible to do so in some instances.  Might as well escape it and not have to worry.

[fredundant]

Thanks Hoogie for clearing it up for me.

Makes perfect sense.

 

Matthew it is a shared server there for Im just wanting to be on the safe side. Didn't mean to offend if I did.

 

A big thanks to you bith for taking the time to go over my code and implementing changes where needed