Foreign Key help!

I made a search.php that finds articles on my site. What I was trying to do was create a voting system that would sort the articles by how many up votes it has. But the articles are in one table and the votes are in another. So what I needed was a foreign key that would be in the voting table that would tell the article table how many up votes it has so search.p开发者_如何学Gohp would sort accordingly

My question was, how would I set up a foreign key that would handle that? I just dont know how to set up the foreign key in the article table and the voting table

And why don't you just update your artices's vote count along when inserting a row to votes table?

You can use it like

<?php
//vote.php
require 'db.php';
if (mysql_num_rows(mysql_query("SELECT id FROM votes WHERE article_id = '".intval($_GET['aid'])."' AND ip = '".$_SERVER['REMOTE_ADDR']."'")) {
    die("Already voted");
}

mysql_query("INSERT INTO votes (id, ip, article_id) VALUES ('', '".$_SERVER['REMOTE_ADDR']."', '".$_GET['aid']."')");
mysql_query("UPDATE articles SET votes = votes ".($_GET['vote'] == "up" ? "+ 1" : "- 1")." WHERE article = '".intval($_GET['aid'])."'");

Since you haven't told us what the tables are I make an answer assuming you have 2 tables with columns similar to this: Articles(articleId, someText, ...) and Votes(articleId, userId, ...)

Then you set the articleId in Votes to be foreign key referencing to the articleId in Articles.

Table Articles

ID   SUBJECT      CONTENT   DATE_ADDED
-----------------------------------------------
1 | How to vote | text... | 14-07-2011 14:11:23
2 | Rate me     | text... | 16-07-2011 09:04:56

Table Votes

ID   ARTICLE_ID   VOTE       IP            DATE_ADDED
-----------------------------------------------
1   |     1     |  3  |  184.153.2.62  |   14-07-2011 15:02:23
2   |     1     |  1  |  211.182.31.74 |   14-07-2011 16:11:51

If you have your tables set up like above (or similar) you can create a foreign key restriction in your MYSQL script as:

CREATE TABLE articles (id INT NOT NULL,
                       subject VARCHAR(255),
                       content TEXT,
                       date_added DATETIME,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE votes (id INT, 
                    article_id INT,
                    vote TINYINT,
                    ip VARCHAR(15),
                    date_added DATETIME,
                    FOREIGN KEY (article_id) REFERENCES articles(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

Those aren't the columns I would have chosen to use.

For the votes, you need to know a) which article b) which user c) whether they voted up or down. So I would have an ArticleID (which is your foreign key to your Article table), a UserID and a Vote, which would be either 1 or -1. Then SELECT ArticleID, SUM(Vote) AS TotalVote FROM Votes GROUP BY ArticleID would tell you the total vote for each article, and you could sort if you wanted.

But since you're searching articles too, you would want to use your ArticleID to make a left join from your article table to your votes table. Note that in this case SUM(Vote) will be NULL if your article has no votes yet, so you may need to use IFNULL(SUM(Vote), 0) instead.