How to implement an accurate rating system

[Saman]

Good to hear that Asaf. Spread the message of ROBOT.LK among your friends & colleagues as much as possible. Encourage humans to share knowledge and help each other which will make this world a better place for everyone.

[Asaf]

Hello Saman,

giving the table you have suggested:
ITEM | TOTAL_NUMBER_OF_VOTES | TOTAL_RATING

I need a MySQL statement that will do everything you said:
1. update new rating of an existing item in the rating table (update and select of current NUMBER _OF VOTES and THIS_RATING in one statement).
2. insert a new rating of a new item in the rating table.

is it possible to do it in one statement?

Thanks

[Asaf]

I tried something like:
UPDATE tbl_RATING SET NUM_OF_VOTES = ((select NUM_OF_VOTES from tbl_RATING where ITEM=5) +1)
WHERE (((tbl_RATING.ITEM)=5))

but it didn't work...

[Saman]

It seems you are lacking knowledge on SQL and databases. To work with databases you need to have a good knowledge on database design & normalisation principles with SQL. You can refer the following books to get a good idea.

Fundamentals of Database Systems (4th Edition) by Elmasri & Navathe - This book is simple to understand. Highly recommended for a beginner.

An Introduction to Database Systems by C. J. Date - This is usually used as the standard university reference which covers almost all fields of database systems. However this is a bit advance book.

You can update both fields at ones.

Code: Select all

UPDATE tbl_RATING
SET NUM_OF_VOTES = NUM_OF_VOTES + 1, TOTAL_RATING = TOTAL_RATING + 1
WHERE tbl_RATING.ITEM = 5

If you have more database related questions, could you please submit them under Computer Science ‹ SQL & Database.

[Asaf]

Hi Saman,
first of all thank you very much for you advice.

please tell me what do you think of my code.
there are two functions setRating and getRating.
the DB tables are:
table: tbl_RATING:
ITEM | TOTAL_NUMBER_OF_VOTES | THIS_RATING (average votes for this item)
table: tbl_TOTAL_RATING:
AVG_NUM_VOTES | AVG_RATING

Code: Select all

function setRating(ItemID, iRating)
    on error resume next
    setRating = False
    if not IsNumeric(ItemID) or not IsNumeric(iRating) then
        exit function
    end if

    dim oConnection
    oConnection = openConnection ()

    if not oConnection then
        exit function
    end if
    
    Dim mySQL
    mySQL = "SELECT COUNT(1) as numberOfRecords FROM tbl_RATING WHERE ITEM = " & ItemID & " "
    Dim rs ' RecordSet object
    set rs=Server.CreateObject("ADODB.recordset")
    
    rs.Open mySQL, oConnection

    if CLng(rs("numberOfRecords")) > 0 then  ' item found, update item
        mySQL = "UPDATE tbl_RATING SET tbl_RATING.NUM_OF_VOTES = (NUM_OF_VOTES+1), RATING = ((tbl_RATING.NUM_OF_VOTES * tbl_RATING.RATING)+" & iRating & ")/(tbl_RATING.NUM_OF_VOTES+1) WHERE tbl_RATING.ITEM=(" & ItemID & ") "
    else ' no item found, insert new item
        mySQL = "INSERT INTO tbl_RATING (NUM_OF_VOTES, RATING, ITEM) VALUES (1, " & iRating & ", " & ItemID & ") "
    end if
    rs.Close

    oConnection.execute(mySQL)

    mySQL = "SELECT AVG(NUM_OF_VOTES) as avgNumOfVotes, AVG(RATING) as avgRating FROM tbl_RATING GROUP BY NUM_OF_VOTES "
    rs.Open mySQL, oConnection

    if rs.EOF =true and rs.BOF=true then  ' no item found
        rs.Close
        Set rs = Nothing
        closeConnection (oConnection)
        exit function
    end if

    dim avgNumOfVotes, avgRating
    avgNumOfVotes = rs("avgNumOfVotes")
    avgRating = rs("avgRating")

    rs.Close
    Set rs = Nothing

    Application.Lock 
    Application("AVG_NUM_VOTES") = avgNumOfVotes
    Application("AVG_RATING") = avgRating
    Application.Unlock 

    'update total rating

    mySQL = "UPDATE tbl_TOTAL_RATING SET AVG_NUM_VOTES = " & avgNumOfVotes & ", AVG_RATING = " & avgRating & " WHERE 1"
    oConnection.execute(mySQL)

    closeConnection (oConnection)

end function


function getRating(ItemID)
    on error resume next
    getRating = ""
    if not IsNumeric(ItemID) then
        exit function
    end if

    dim oConnection
    oConnection = openConnection ()

    if not oConnection then
        exit function
    end if

    mySQL = "SELECT NUM_OF_VOTES, RATING FROM tbl_RATING WHERE ITEM =(" & ItemID & ") "
    rs.Open mySQL, oConnection

    if rs.EOF =true and rs.BOF=true then  ' no item found
        rs.Close
        Set rs = Nothing
        closeConnection (oConnection)
        exit function
    end if

    dim numOfVotes, rating
    numOfVotes = rs("NUM_OF_VOTES")
    rating = rs("RATING")

    rs.Close
    Set rs = Nothing
    closeConnection (oConnection)

    'br = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) ) / (avg_num_votes + this_num_votes)

    getRating = ( CLng(Application("AVG_NUM_VOTES")) * CLng(Application("AVG_RATING")) ) + ( CLng(numOfVotes) * CLng(rating) ) / ( CLng(Application("AVG_NUM_VOTES")) + CLng(numOfVotes) )
    

end function

I will be happy to hear any comment you have.

Thanks

[Saman]

Hi, sorry for taking lot of time to give you a reply. The reason was robot.lk wasn't working due to a server repair I guess.

I see you are writing using VBScript, Is that right? Have a look at following VBScript ADO example and use it as a guideline.

Code: Select all

<%
	' Define our variables which will be used as references to our ActiveX Data Objects
	Dim con   
	Dim rst

	' Create two strings for use with the creation of a connection and a recordset
	Dim sConString
	Dim sSQLString


	' Create a new instance of an ADO Connection object
	Set con = Server.CreateObject("ADODB.Connection")

	Response.Write "Connection object created.<BR>"

	' Open the Mydb data source (Access database) with the Connection object
	sConString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\My DB\\Mydb.mdb"
	con.Open sConString

	Response.Write "Connection opened.<BR>"

	' Create a Recordset object from a SQL string
	sSQLString = "SELECT TOP 10 Author FROM Authors"
	Set rst = con.Execute(sSQLString)

	Response.Write "SQL statement processed.<BR>"

	' Retrieve all the data within the Recordset object
	Response.Write "Getting data now...<BR><BR>"

	Do Until (rst.EOF)
		Response.Write rst("Author") & "<BR>"
		rst.MoveNext
	Loop

	Response.Write "<BR>End of data.<BR>"

	' Close and remove the Recordset object from memory
	rst.Close
	Set rst = Nothing

	Response.Write "Closed and removed Recordset object from memory.<BR>"

	' Close and remove the Connection object from memory
	con.Close
	Set con = Nothing
	Response.Write "Closed and removed Connection object from memory.<BR>"
%>

In your code, I don't see you have opened a connection to a database. Here we have opened a connection to an MS Access database as follows.

Code: Select all

	' Open the Mydb data source (Access database) with the Connection object
	sConString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\My DB\\Mydb.mdb"
	con.Open sConString

If you are using MS SQL Server, you can use the following connection string.

Code: Select all

	sConString = "Provider='sqloledb'; Data Source=ServerName; Integrated Security='SSPI';Initial Catalog='Mydb';"

After that I do see lots of SQL syntax errors & places where we can improve the efficiency in your code but it really takes quite a lot of time of mine to explain all these things. I'll brief them as I can.

A.

Code: Select all

mySQL = "UPDATE tbl_RATING SET tbl_RATING.NUM_OF_VOTES = (NUM_OF_VOTES+1), RATING = ((tbl_RATING.NUM_OF_VOTES * tbl_RATING.RATING)+" & iRating & ")/(tbl_RATING.NUM_OF_VOTES+1) WHERE tbl_RATING.ITEM=(" & ItemID & ") "

Can be simply used as,

Code: Select all

mySQL = "UPDATE tbl_RATING SET NUM_OF_VOTES = (NUM_OF_VOTES + 1), RATING = ((NUM_OF_VOTES * RATING) + " & iRating & ")/(NUM_OF_VOTES + 1) WHERE ITEM = (" & ItemID & ")"

When you accessing a single table using SQL, you don't need to put table name in-front of fields. This will improve readability.

B.

Code: Select all

mySQL = "SELECT AVG(NUM_OF_VOTES) as avgNumOfVotes, AVG(RATING) as avgRating FROM tbl_RATING GROUP BY NUM_OF_VOTES"

Should not group since you need the average of all. Don't you?

Code: Select all

mySQL = "SELECT AVG(NUM_OF_VOTES) as avgNumOfVotes, AVG(RATING) as avgRating FROM tbl_RATING"

You need to improve your SQL knowledge. In cases where you need to handle about 5 tables linked together using different join methods to output a recordset you will find difficulties. If you are going to be on the field of database programming, SQL knowledge is a must.

I would like to ask you to write a little introduction about yourself under Member Area -> Introduction to know you better.