Dville Posted July 14, 2006 Share Posted July 14, 2006 I am coding a digg.com clone, and am looking into how to restrict each member from voting/digging multiple times on one article.So each article is assigned an 'id' number. That is how I distinguish between the articles, which voting for, reporting, etc.What is the best way to go about this?Add another field to my users table. Maybe call it 'voted', that will insert the article id(with maybe a comma and a space?) that was just voted on, by that member. For each article that is voted on by that member, maybe it will just add that article id to the end of the string held in that field? Then before voting takes place for each article, do a if else statement to search that db field for it's own article id, if shown it will tell them they can't vote twice?If anyone has any ideas I would be very greatful. If not, and this is how it has to be done, how would I go about updating the db, and just 'adding' a string to the end? Also how would I go about doing the mysql search as well.Thanks in advanced to anyone who has any ideas and/or can help. Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted July 14, 2006 Share Posted July 14, 2006 How are you tracking the number of votes for each article? By incrementing a value in a field? If you are, you shouldn't be. The best way to do what you want, and this is simplified, is to have three tables:1. The table containing your users(tbl_user), including a user id2. The table containing the articles(tbl_article), including an article id3. A table containing votes(tbl_votes)...it only needs three fields...vote id, article id, and user id.When a user votes for an article, obviously insert the article id and the user id into tbl_votes.To determine if a user has previously voted for an article:SELECT COUNT(user_id) FROM tbl_votes WHERE article_id = '$id_of_article'To determine the number of votes for a particular article use sql similar to:SELECT SUM(vote_id) FROM tbl_votes WHERE article_id = '$id_of_article' Quote Link to comment Share on other sites More sharing options...
micah1701 Posted July 14, 2006 Share Posted July 14, 2006 thats a valid idea - the table cell might get kind of long though - depending on how many things they vote for.[b]EDIT: hitman beat me to it w/ the same idea.[/b]another idea is to have a table called "votes" that just has three or four columns:1) voteID (unique primary)2) articleID (the article they voted for)3) userID (the user who voted)4) timeStamp (or any other data you want to track)this table will end up with a LOT of rows, one for every vote made... but it would be easy to search later.when a user goes to vote, just check this table first."SELECT voteID FROM votes WHERE articleID='$seletedArticle' AND userID='$userID'"if it returns more then zero, don't let them vote again Quote Link to comment Share on other sites More sharing options...
Dville Posted July 14, 2006 Author Share Posted July 14, 2006 wow, this site rocks. i've been sitting here not coding, trying to figure out how i could do this, and that's just so simple, lolcreating a new table is a great ideahitman, you say incrementing a field to sum the hits is bad, but why?right now i have a table for users, articles, and comments. . .and if i add a vote table, to check the way micah says, i can still increment from the articles table correct?and micah - with the table being so many rows, will that slow down the site that much? Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted July 14, 2006 Share Posted July 14, 2006 [quote]you say incrementing a field to sum the hits is bad, but why?[/quote]You're not able to determine if a user has voted previously the way it's working now, right?[quote]i can still increment from the articles table correct?[/quote]Sure, but why? The vote counting should be handled by counting them from the "votes" table since the data is already there...no reason to execute two queries that do the same thing (one to insert the vote, one to update the number)[quote]with the table being so many rows, will that slow down the site that much?[/quote]No. Quote Link to comment Share on other sites More sharing options...
Dville Posted July 14, 2006 Author Share Posted July 14, 2006 ooooooo, man i love when it just clicksi see why it's different, since you're using the 'sum' mysql. i have not used sum or count yet(very new coder) and i actually need to learn how to use the sum command for showing how many comments there are on each article(like digg.com has), i just haven't tried coding it yet due to me not using it previously, lol Quote Link to comment Share on other sites More sharing options...
Dville Posted July 14, 2006 Author Share Posted July 14, 2006 anyone know of a good mysql sum and count article/tutorial? Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted July 14, 2006 Share Posted July 14, 2006 hehe...I actually just realized that SUM was incorrect there...it should be COUNT for that particular SQL statement.Try the MySQL manual...although sometimes they don't always write in a manner that's understandable:http://dev.mysql.com/doc/refman/5.0/en/counting-rows.htmlThe gist is that it counts the number of nonnull instances of the field specified in the query. It's very similar to the php function mysql_num_rows, however it will only count the row if the field specified has a value, where as php will count all of the rows returned. Quote Link to comment Share on other sites More sharing options...
Dville Posted July 15, 2006 Author Share Posted July 15, 2006 So I am trying to do this, and seem to get an error. The code I have is[code]$query_insert = "INSTERT INTO votes(username, articleid) VALUES('" .$username. "','" .$articleid. "')";$result_insert = mysql_query($query_insert) or die(mysql_error());[/code]The error I get is[quote]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSTERT INTO votes(username, articleid) VALUES('admin','84')' at line 1[/quote] Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 15, 2006 Share Posted July 15, 2006 it's probably because there's no such thing as an INS[b]T[/b]ERT query in MySQL. Quote Link to comment Share on other sites More sharing options...
Dville Posted July 15, 2006 Author Share Posted July 15, 2006 ha, yea. . .that's funny. i can't believe i spelled it like that, lolso yea, 45 minutes of trial and error testing. . .i have gotten no whereIf anyone would like to help.http://www.phpfi.com/131736there is the main code of body.phpI think im having issues since it's in a while statment.But at line 15 it saysecho $row['hits'];that was where i had the 'total votes' but now since i've created a whole new table for voting.Can I access this 2nd table from this while loop. . .that is already accessing the 1st table to pull the initial data?I am very new to php, even newer to mysql, thanks in advanced for anyone that can help. Quote Link to comment Share on other sites More sharing options...
Dville Posted July 16, 2006 Author Share Posted July 16, 2006 bump. . .am I missing some kind of information?If anyone needs to see the code above body.php(the above link)here it ishttp://www.phpfi.com/132092this code will show you the query im using in $resultSo can I just insert another while loop right under line 2(of the phpfi.com/131736 page)?On that same page you see on line 5, I put data in the variable $id. I need this variable to use in my count query. So wouldnt it have to be inside the original while loop? Quote Link to comment Share on other sites More sharing options...
Dville Posted July 17, 2006 Author Share Posted July 17, 2006 should I just manually combine the 3 tables I use. . .into 1 mega-table? I mean. . .I have no other idea of what to do. That will make a lot of empty fields due to the votes most likely being more than the actual articles.I was going to set this 'feature' aside, and start working on another. I plan on changing a 'discuss this article' link into one that says (X)comments, where X is the total(from a mysql count query). . .yet again, that involves a 2nd table into the mix(the comments table).I believe I need to do a JOIN query with mysql. . .yet all the info I can find about it is plain mysql, not php/mysql. . .so I believe my errors were all syntax, due to me not familiar with how it should be. . .and don't worry, that typo issue was a one time thing. I don't have those all over my script. . .that was after a long day at work, my mind was just somewhere else. Quote Link to comment Share on other sites More sharing options...
Dville Posted July 20, 2006 Author Share Posted July 20, 2006 this thread can be closed, i figured out the join query Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.