how to implement posting a message and retrieving it?

[sid0972]

it is similar to posts on forums, or comments in wordpress.

 

What i was thinking, to have a table structure like this

 

username varchar,

actual_msg text,

post_id smallint

 

now when submitting the content to the database, i will run the query to increment the post_id, and hold it in a variable, say $var.

When it has been saved in the database, i will use the $var to fetch the message.

 

But, if a user submits the message and it is not submitted and the post_id is incremented, that row would remain empty.

And if a deadlock occurs, i dont know what will happen in that case.

 

Can anyone tell me what approach should i use?

Also, using locks in mysql is feasible?

[kicken]

Your post_id should be an AUTO_INCREMENT field in mysql.  When you add a message just specify the username and message text, mysql will automatically assign the next available ID number to the row, which you can then fetch after the fact (exactly how depends on which DB API you're using).

 

As a sample, with PDO:

$message = 'blah blah blah blah';
$username = 'kicken';

try {
   $sql = 'INSERT INTO messages (username, actual_message) VALUES (:user, :msg)';
   $stmt = $db->prepare($sql);
   $stmt->bindValue(':user', $username);
   $stmt->bindValue(':msg', $message);
   $stmt->execute();

   $post_id = $db->lastInsertId(); //fetches the newly assigned ID
} catch (Exception $e){
   echo 'Oops, something failed.';
   echo $e;
}

[PFMaBiSmAd]

I would store the user_id, rather than the username.

[sid0972]

i know about the auto_increment field, was not using it cause it had to be primary....

anyways, i could store them alright, but how would i retrieve them???

 

consider there is a particular user, who has uploaded a photo.

Now, if someone comments on it, how would i know that "that" particular comment belongs to that photo ?

How would i assign id's to that thing?