Jump to content


Photo

php/mysql search question


  • Please log in to reply
13 replies to this topic

#1 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 14 July 2006 - 01:55 AM

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.

#2 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 14 July 2006 - 02:03 AM

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'

#3 micah1701

micah1701
  • Members
  • PipPipPip
  • Advanced Member
  • 613 posts
  • LocationEllington, CT USA

Posted 14 July 2006 - 02:06 AM

thats a valid idea - the table cell might get kind of long though - depending on how many things they vote for.

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
"Confidence in the face of risk."

#4 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 14 July 2006 - 02:37 AM

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?


#5 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

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?


No.

#6 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 14 July 2006 - 02:43 AM

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

#7 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 14 July 2006 - 02:46 AM

anyone know of a good mysql sum and count article/tutorial?

#8 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 14 July 2006 - 02:51 AM

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

#9 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 15 July 2006 - 10:15 PM

So I am trying to do this, and seem to get an error. The code I have is

$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



#10 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 15 July 2006 - 10:30 PM

it's probably because there's no such thing as an INSTERT query in MySQL.

#11 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 15 July 2006 - 11:08 PM

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.

#12 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 16 July 2006 - 11:29 PM

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?

#13 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 17 July 2006 - 04:12 PM

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.

#14 Dville

Dville
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 20 July 2006 - 01:48 PM

this thread can be closed, i figured out the join query




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users