A 'who has used it' problem

Question

Okay... So, I came across the following problem: I have one table that works properly - it holds users' IDs, posts' IDs, posts' content, time that post was made.

I use it to have the following printed out: the content of the post, who is the author of the post and when the post was made. So far everything works just fine.

Now... I have a button that allows the users to vote up for a given post - everything is working just fine here as well. However, now I want to make one more feature that will show who and when has voted. You know, so that beneath each post there will be a list of people who have liked the post and when they've pressed the button.

My idea was to have two tables - the one holding the post's info post_table, and one more table voted_table that will have the user IDs, the time the button was pressed and the post_id that the vote has been made for... But I have no idea how to actually make it. So pretty much my question is:

How can I insert into a table information from another table + some other information on a button's click?

Answer 1

You can if your database supports transactions. In mysql look for the key words BEGIN COMMIT and ABORT. However, if you do this, you'll be storing data in two places.

Alternately, you can store your vote totals in the voted_table and you can alter your first query to join to the voted_table to tally the votes.

select P.post_id, sum(V.votes) # add other post_table fields
from post_table as P, voted_table as V
where P.post_id=V.post_id
group by P.post_id #add other post_table fields you need here also

Answer 2

CREATE TABLE `user` (
  `id` INTEGER UNSIGNED NOT NULL auto_increment,
  `username` varchar(32) default NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `post` (
  `id` INTEGER UNSIGNED NOT NULL auto_increment,
  `owner_id` INTEGER UNSIGNED default NULL,
  `content` TEXT default NULL,
 PRIMARY KEY (`id`),
 KEY `post_I_owner` (`owner_id`),
 CONSTRAINT `post_FK_user`
   FOREIGN KEY (`owner_id`)
   REFERENCES `user` (`id`)
   ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `vote` (
  `id` INTEGER UNSIGNED NOT NULL auto_increment,
  `user_id` INTEGER UNSIGNED default NULL,
  `post_id` INTEGER UNSIGNED,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   UNIQUE KEY `vote_IU_user_post` (`user_id`,`post_id`),
   CONSTRAINT `vote_FK_user`
     FOREIGN KEY (`user_id`)
     REFERENCES  `user` (`id`)
     ON DELETE SET NULL,
   CONSTRAINT `vote_FK_post`
     FOREIGN KEY (`post_id`)
     REFERENCES  `post` (`id`)
     ON DELETE CASCADE
) ENGINE=InnoDB;

So with the schema above we can do the following...

in your html/php for the viewPost page you could have

<form name="vote_form" action="postVote.php" method="POST">
  <input type="hidden" name="post_id" value="1" />
  <input type="submit" value="Vote for this Post" />
</form>

So then in votePost.php you could do something like:

if(isset($_SESSION['user']['user_id']))
{
   $userid = $_SESSION['user']['user_id'];
}
else
{
  // redirect to a login or something
}

$sql = "INSERT INTO vote (user_id, post_id) VALUES (%s,%s)"
$sql = sprintf($sql, $userid, $_POST['post_id']);
mysql_query($sql);

This way mysql will take care of the timestamp for you. Note that you need to verify/validate the input data (which i didnt really do here) before inserting it into the db and all that fun security stuff. Id also recommend using Mysqli or PDO Mysql instead of the old mysql libararies and using a prepared stament (that will take care of most of your value quouting/escaping functionality on its own)

Answer 3

To run the insert query when the user clicks a button you need a couple moving part. First, you need to add a form with the button. You'll need to add enough information to the form using "hidden" fields (the post_id, and the user_id unless you can get that from somewhere else). You'll have to set the action on the form to be another PHP page. On the second PHP page, you get the form variables you specified and use that to generate your insert query. (There are security issues you should consider, like trusting the value of user_id and checking user input, but I won't go into that.) If you used method="POST" on your form, then you get the values with $_POST[$formFieldName].