Jump to content

Archived

This topic is now archived and is closed to further replies.

Dville

php/mysql search question

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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 id
2.  The table containing the articles(tbl_article), including an article id
3.  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'

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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, lol

creating a new table is a great idea

hitman, 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?

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
ooooooo, man i love when it just clicks

i 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

Share this post


Link to post
Share on other sites
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.html

The 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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
it's probably because there's no such thing as an INS[b]T[/b]ERT query in MySQL.

Share this post


Link to post
Share on other sites
ha, yea. . .that's funny. i can't believe i spelled it like that, lol

so yea, 45 minutes of trial and error testing. . .i have gotten no where
If anyone would like to help.

http://www.phpfi.com/131736
there is the main code of body.php

I think im having issues since it's in a while statment.
But at line 15 it says
echo $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.

Share this post


Link to post
Share on other sites
bump. . .

am I missing some kind of information?
If anyone needs to see the code above body.php(the above link)
here it is
http://www.phpfi.com/132092

this code will show you the query im using in $result

So 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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.