php/mysql search question
Posted 14 July 2006 - 01:55 AM
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.
Posted 14 July 2006 - 02:03 AM
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'
Posted 14 July 2006 - 02:06 AM
EDIT: hitman beat me to it w/ the same idea.
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
Posted 14 July 2006 - 02:37 AM
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?
Posted 14 July 2006 - 02:42 AM
you say incrementing a field to sum the hits is bad, but why?
You're not able to determine if a user has voted previously the way it's working now, right?
i can still increment from the articles table correct?
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)
with the table being so many rows, will that slow down the site that much?
Posted 14 July 2006 - 02:43 AM
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
Posted 14 July 2006 - 02:46 AM
Posted 14 July 2006 - 02:51 AM
Try the MySQL manual...although sometimes they don't always write in a manner that's understandable:
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.
Posted 15 July 2006 - 10:15 PM
$query_insert = "INSTERT INTO votes(username, articleid) VALUES('" .$username. "','" .$articleid. "')"; $result_insert = mysql_query($query_insert) or die(mysql_error());
The error I get is
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
Posted 15 July 2006 - 11:08 PM
so yea, 45 minutes of trial and error testing. . .i have gotten no where
If anyone would like to help.
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
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.
Posted 16 July 2006 - 11:29 PM
am I missing some kind of information?
If anyone needs to see the code above body.php(the above link)
here it is
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?
Posted 17 July 2006 - 04:12 PM
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.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users