stubarny

thanks I'll try that code step 1 is to just find which submissions I currently "like". so in steps 2-4 I'll probably be joining tables (and asking you for help ) Thanks, Stu

thanks but I need to find my last vote for each submission that I've voted on... I think the code below will group by submission_vote_submission_number and then order by timestamp, but how do I ignore all but the most recent record for each value of submission_vote_submission_number? Step1: "SELECT submission_vote_submission_number WHERE submission_vote_account_number = '1' GROUP BY submission_vote_submission_number, submission_vote_timestamp DESC" Thanks, Stu

Assuming my account number is 1: Step1: " SELECT submission_vote_submission_number WHERE submission_vote_account_number = '1' " but this is finding all votes where I "liked" a submission, how do I change this so that it only looks at my last vote for each submission?

Thanks Barand

Hi All, Thanks for your help on the other collaberative filtering thread. I'd just like to post a similar problem I had a while back that I gave up on - but I think in hindsight it may be possible to solve? Let's say we have a website where users post "submissions" and then can vote whether they like or dislike each other's submissions by submitting "submission_votes". I'd like to create a list of recommended posts by following the below logic: 1. find all the submissions that I like 2. find all the users who like at least 1 submission that I like 3. find all the submissions that are liked by users who like at least 1 submission that I like 4. order the returned submissions by the number users who like them (by "users" I mean "users who like at least 1 submission that I like") The tricky bit(?) is that users can vote on a submission more than once (maybe they disliked it at first, but now like it) and therefore only the latest vote should be used (for each combination of submission_vote_account_number and submission_vote_submission_number). Please find the table structures below: submissions: submission_number (unique index, autoincrementing) submission_content submission_account_number (foreign key --> field "account_number" in table "accounts") submission_timestamp submission_votes: submission_vote_index (unique index, autoincrementing) submission_vote_account_number (foreign key --> field "account_number" in table "accounts") submission_vote_submission_number (foreign key --> field "submission_number" in table "submissions") submission_vote_content (1 or 0 - 1 being like, 0 being dislike) submission_vote_timestamp accounts: account_number (unique index, autoincrementing) account_username Thanks, Stu

Thank you

Thanks everyone, Please may I ask what these 2 bold parts of the SQL query below do?: SELECT purchases.purchase_productID, COUNT(*) as recommendationSubtotal FROM purchases INNER JOIN ( SELECT purchase_UserID FROM purchases INNER JOIN ( SELECT purchase_productID FROM purchases WHERE purchase_userID = 1 ) as X using (purchase_productID) WHERE purchase_userID <> 1 ) as Y USING (purchase_userID) GROUP BY purchase_productID ORDER BY recommendationSubtotal DESC; I assume they create aliased tables called X and Y (which I guess I don't technically need in my code?)? But please could you tell me what the "using (purchase_productID)" and "using (purchase_userID)" does? Thanks, Stu

Ok thanks Barand, please could you tell me if this is correct (I think I just needed to remove the first line of the sql query to stop it trying to insert into a table?): SELECT products.product_id As recommendation_productID, COUNT(*) As recommendationSubtotal FROM purchases INNER JOIN ( SELECT purchaseUserID FROM purchases INNER JOIN ( SELECT purchase_productID FROM purchases WHERE purchase_userId = 1 ) as X using (product_id) WHERE purchase_UserID <> 1 ) as Y USING (purchase_userID) GROUP BY productID ORDER BY recommendationSubtotal DESC

Hello, thank you for your patience with me I promise I'm not being deliberately inept :-) I'm afraid I'm getting confused by your solutions because I've used the same field names in different tables, please could you allow me to have another try where I have the table name at the start of each field name? I've also put an underscore in any field name that is a foreign key, and just to clarify the output doesn't have to be a table - just a normal SQL results array: users userID | userUsername 1 | David 2 | Sam 3 | Geoff 4 | Peter 5 | Tim products productID | productName 1 | Lawn Mower 2 | Greenhouse 3 | Paving slab 4 | BBQ 5 | Chainsaw purchases purchaseID | purchase_userID | purchase_productID 1 | 1 | 1 2 | 1 | 2 3 | 2 | 2 4 | 2 | 3 5 | 3 | 1 6 | 3 | 4 7 | 4 | 2 8 | 4 | 4 9 | 5 | 4 10 | 5 | 5 SQL query output: recommendationID | recommendation_productID | recommendationSubtotal 1 | 2 | 2 2 | 4 | 2 3 | 3 | 1 4 | 1 | 1 Please could you tell me if the below code is correct (sorry I've removed aliases because I find it hard to remember which tables they relate to): SELECT 'products'.'productID' As recommendation_productID, COUNT(*) AS recommendationSubtotal FROM 'products' WHERE 'products'.'productID' IN (SELECT 'purchases'.'purchaseID' FROM 'purchases' WHERE 'purchases'.'purchase_UserID' <> 5 ) GROUP BY 'products'.'productName' ORDER BY recommendationSubtotal DESC Many thanks, Stu

Thank you, I see your point about the foreign ID's - how would I code for the below structure? User_ID | Username 1 | David 2 | Sam 3 | Geoff 4 | Peter 5 | Tim Product_ID | ProductName 1 | Lawn Mower 2 | Greenhouse 3 | Paving slab 4 | BBQ 5 | Chainsaw Purchase_ID | User_ID | Product_ID 1 | 1 | 1 2 | 1 | 2 3 | 2 | 2 4 | 2 | 3 5 | 3 | 1 6 | 3 | 4 7 | 4 | 2 8 | 4 | 4 9 | 5 | 4 10 | 5 | 5 Output table: Subtotal_ID | Product_ID | Count 1 | 2 | 2 2 | 4 | 2 3 | 3 | 1 4 | 1 | 1 Thanks, Stu

Hi, I'd like to create a "people who liked the products you bought also bought..." tool For example please see below a list of users and purchased products, User | Product David | Lawn Mower David | Greenhouse Sam | Greenhouse Sam | Paving slab Geoff | Lawn Mower Geoff | BBQ Peter | Greenhouse Peter | BBQ Tim | BBQ Tim | Chainsaw Say my user is "David" - I'd like to identify the people who bought at least 1 identical item as David (i.e. everyone except Tim), and return a count of the products these people bought (sorted by the count field): Product | Count Greenhouse | 2 BBQ | 2 Paving slab | 1 Lawn Mower | 1 Please could you tell me how to do this? Can it be done in one SQL query? Thanks, Stu

awesome, thanks guys - that worked The reason for needing 2 loops: first loop is for a 'history' list of votes a user has made, the second loop is within a list of posts so that only posts the user has not voted on yet are displayed. Would the below code be better? ------------------------ $record = mysql_fetch_array($result_user_votes, MYSQL_BOTH); and then two loops of: while($record) -------------------------- Stu

Hello, I have 2 loops that use the following code: while($record = mysql_fetch_array($result_user_votes, MYSQL_BOTH) The first time loop works, the second loop doesn't work at all - does this mean that I can't use the SQL results array more than once? or am I going about this incorrectly? Thanks, Stu

Hi, I've designed a simple tool for identifying the quality of posts on my social network by forcing people to state whether they like a post or not before allowing them to see the next post. So for each post I have a running total of the number of people who have seen the post and the proportion or people who have liked/disliked the post. Table of 'granular votes': user_account, post_number, vote (like/dislike), timestamp, ip_address I then have some running totals in the "posts" table: post_likes (number of likes), post_dislikes (number of dislikes), post_views (number of post views) What I'm trying to do now is personalise this functionality for each user by, identifying what posts people are likely to like based on their history of 'likes'. i.e. by finding which posts people liked who also like the same posts as the user. This should show posts that are not only high quality but also relevent to the user's interests. However I'm really struggling with it - in my experience any solution that uses joined tables using lots of data is going to be really slow (hence me using running totals in the submissions table). Please could someone nudge me in the right direction for finding out an effective way of doing this? Thanks, Stu

Thanks Pikachu2000 :-) Regarding the quotations in the indices please could you tell me what are the practical implications of not using quotations are? I noticed that my arrays worked fine without the quotations so I stopped using them to improve the readabililty of my code. Thanks, Stu

Hi All, I have the code below which returns "<br>line 82, dalmonfishing<br>". Please could you explain why it doesn't return "<br>line 82, salmonfishing<br>"? (i.e. why is the s getting replaced with a d?) $_SESSION[process_sign_up][sign_up_email]="salmonfishing"; $_SESSION[process_sign_up][sign_up_email][test_result_detail]="duplicate"; echo "<br>line 82, " . $_SESSION[process_sign_up][sign_up_email] . "<br>"; Thanks, Stu

Hello, I have a table of responses to an event invite (note people can resond multiple times if they change their mind): guest_name, guest_status, timestamp David, accepted, 2012-05-12 Peter, accepted, 2012-05-12 David, declined, 2012-06-02 Sam, accepted, 2012-06-02 Peter, tentative, 2012-06-12 So to display the latest statuses of invitees' responses I would want to display the following information: Accepted: Sam Tentative: Peter Declined: David Please could you tell me how to write an sql command that will do this? I'm struggling to write a command that only uses the most recent entry for each invitee. Thanks, Stu

Hi ignace, I don't like UPDATE queries because they effectively DELETE and INSERT. The deleted data is lost without trace, along with any audit trail of "which users did what and when" (along with ip addresses and timestamps). Is there another way of doing this that maintains a record of all user actions? :-)

Hello, I am trying to code a meeting functionality whereby users can create meetings, invite people to meetings and respond (accept/decline) meeting invites. I am coding a page view that shows a meeting invitation along with a list of of users' (latest) responses (Accept/Decline). To do this I was simply going to join the "meetings" table (which has a list of meetings) with the "responses" table (which has a list of users' responses). However it's occured to me that users may respond several times to an invite (e.g. accept at first and then decline later on), which would cause several entries in the "responses table" and so causing me to need to find the most recent response for each user for a given meeting). I know this can be done with a complicated SQL query but it strikes me as an inelegant solution, slow and maybe not scaleable? Please could someone suggest a better approach? Thanks for your help, Stu

Hello, I have the following code which is trying to insert data into a table. However, the field "website_page_name" has a unique field therefore if a value already exists the code below fails. Please could you tell me how to check that the value for "website_page_name" has not already been entered into the table? Many thanks, Stu

Hello, I am trying to run the following SQL code which replaces the string "Warehouse-Workers" with the string "Warehouse" in the website_page_name field of the website_page_names table: The problem is that website_page_name is a unique field and the query falls over whenever a duplicate is found. Please could you tell me how to workaround? (I want to not update any records that would cause a duplicate if updated) Thanks, Stu

Many thanks, that's great!

Hello, I am trying to copy a single sql table from a mysql database on one server to another mysql database on another server. I need to do this twice a day so really would like an automated php/sql cronjob solution - please could you tell me if this is possible? Thanks for your help, Stu

Oh wow I didn't know php could retrieve to much data so quickly. I added this to the top of your code to stop a memory overload error, and it works in about 6 seconds which is cool. Thanks Jesie

Hi Jesie, Thanks but I need to find the first and last record in 40 ranges, so I'm not sure I can avoid a loop? Stu