PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
SELECT object.id AS object,
SUM(IF(user_choice.choice_id=object.id,1,0)) AS num_choice,
SUM(object_user.rating) AS total_rating,
SUM(IF(user_choice.choice_id=object.id,1,0)) + SUM(object_user.rating) AS final_score FROM object
LEFT JOIN object_user ON object_user.object_id = object.id
LEFT JOIN user_choice ON user_choice.user_id = object_user.user_id
GROUP BY object.id
SELECT object.id AS object,
SUM(IF(user_choice.choice_id=object.id,1,0)) AS num_choice,
SUM(object_user.rating) AS total_rating,
SUM(IF(user_choice.choice_id=object.id,1,0)) + SUM(object_user.rating) AS final_score FROM object
LEFT JOIN object_user ON object_user.object_id = object.id
LEFT JOIN user_choice ON user_choice.user_id = object_user.user_id
GROUP BY object.id
That won't work because it groups them by object.id .
I need each object to list all choices and each choice to have a different result for each object.
objects = (Retrieve list of Objects from DB)
foreach(objects)
{
users = (retrieve list of users who are linked to this object)
foreach(users)
{
profile = (retrieve list of user choices for this user)
(count each choice to a large list of choices statistics; each choice +1 if a user chose it)
}
}
(Send the choices statistics back to the database)
The reason I am asking is because I used to have some code similar looping structure. I did foreach server 5000+ then foreach sever owner, get some info.
That script used to take maybe 10 to 15 minutes to run. I started using arrays to do my comparisions and the page loads almost instantly now.
objects = (Retrieve list of Objects from DB)
foreach(objects)
{
users = (retrieve list of users who are linked to this object)
foreach(users)
{
profile = (retrieve list of user choices for this user)
(count each choice to a large list of choices statistics; each choice +1 if a user chose it)
}
}
(Send the choices statistics back to the database)
The reason I am asking is because I used to have some code similar looping structure. I did foreach server 5000+ then foreach sever owner, get some info.
That script used to take maybe 10 to 15 minutes to run. I started using arrays to do my comparisions and the page loads almost instantly now.
Cool- If you think about it it either opens a new connection to the db or querys the db as many times and you loop.
So if you can minimize that I think you won't have this issue anymore. The script I was writing is huge it querys Active directory, WSUS(microsoft patching server) and custom database. When I converted over to this style of coding it, the speed improvement was huge.
Now I try and minimize the number of times I hit the database and use the arrays instead.....
kingconnections wrote:Cool- If you think about it it either opens a new connection to the db or querys the db as many times and you loop.
So if you can minimize that I think you won't have this issue anymore. The script I was writing is huge it querys Active directory, WSUS(microsoft patching server) and custom database. When I converted over to this style of coding it, the speed improvement was huge.
Now I try and minimize the number of times I hit the database and use the arrays instead.....
I will have to give this thing a shot. Thanks for the direction
SELECT *, SUM(object_user.rating)
FROM user_choice, object_user
WHERE user_choice.user_id=object_user.user_id
AND object_user.object_id=1
GROUP BY user_choice.choice_id
ORDER BY user_choice.choice_id
Yet I want to find a way to do the same thing for all object together to save database access time. In this method I still need to call this query for each object I have on the system.