4 Replies - 709 Views - Last Post: 11 February 2009 - 03:20 PM Rate Topic: -----

#1 ninethousandfeet@msn.com  Icon User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

SQL to get user postings

Posted 11 February 2009 - 11:42 AM

hello,

my name is brad and i'm very new to this forum. i am also new to web development, so please excuse me if i use the incorrect terminology as i'm still learning. i am stuck on a problem and i'm pretty sure it has to do with the SQL that i'm using (... incorrectly). i have two tables that are relevant (i will list their contents below). i have a profile page set up for my users and there are two sections in particular that i'm concerned about with this query; "my buy list" and "my sell list." i currently have the page only accessible if the user is logged in, so it is strictly tied to a session variable (which i have a separate sql set up for... i'll list that too). i would like the user to have their posts to buy and share, if any, to be placed in the appropriate place on their profile page. for the posts that do show up on the users profile, i would like it to appear as the post_title and post_date.
i have my tables below and the current sql statement. i'm not sure if everything can somehow go in one sql statement or if i need more, but i'm definitely confused at this point.
any help would be greatly appreciated, thank you!

userTable: user_id, username, email, password, register_date, fun_fact

postingTable: post_id, user_id, post_title, post_date, product_name, category_name(ENUM), store_name, buy_or_share(ENUM)

current SQL (i deleted the many different WHERE clauses that i've tried to spare anyone the headache of my mistakes...)
SELECT user_id, username, register_date, email, fun_fact, DATE_FORMAT(register_date, '%b %e, %Y') AS us_format, userTable.post_id, postingTable.post_id, postingTable.post_title, postingTable.product_name, postingTable.post_date, postingTable.buy_or_share, postingTable.category_name, postingTable.store_name
FROM userTable, postingTable
WHERE username = colname (the colname is where i get the session variable, it is set to text, default -1, run-time value is $_SESSION['MM_Username'].

any recommendations, advice, help, etc. will make me better off than where i am now, thank you for the help!!

This post has been edited by ninethousandfeet@msn.com: 11 February 2009 - 11:46 AM


Is This A Good Question/Topic? 0
  • +

Replies To: SQL to get user postings

#2 itlee  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 55
  • Joined: 16-July 08

Re: SQL to get user postings

Posted 11 February 2009 - 01:02 PM

You didnt specify what problems you were having with the SQL you have already written?

Your SQL will return a Cartesian product of results (every combination of the rows from both tables) meaning you get all the rows from the posting table for each user row. this can lead to alot of data being returned.

To avoid this you need to specify a JOIN between the 2 tables, this involves specifying how the 2 tables relate to each other so that for each user his own postings are returned. e.g. FROM userTable LEFT INNER JOIN postingTable ON userTable.user_id = postingTable.user_id.

As for the data you will want to return for the 2 lists, you should create a SQL statement for each as it will be easier to bind the resultsets to the placement in the page without having to do further filtering on the data. You should also only return the fields you really need to, as this will speed up data access times.

itlee.
Was This Post Helpful? 0
  • +
  • -

#3 ninethousandfeet@msn.com  Icon User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

Re: SQL to get user postings

Posted 11 February 2009 - 02:10 PM

hello,

okay, so the problem i was having with the current SQL was that i kept showing all postings (it would not separate the postings of just that user)... which it sounds like you already guessed that issue would come up. i made the SQL with the LEFT JOIN as you recommended and i'm trying to make separate SQL statements for the 2 lists. the problem is that i still don't see how i relate a new SQL statement to that user's posts only. how do i make the SQL recognize that the user_id matches all of their post_id? they are obviously different numbers, so i don't know how this works. i tried to make the postingTable.user_id = userTable.user_id AND postingTable.post_id = var1(which was $_SESSION['Username'] because that is how the session variable is set up right now. this definitely didn't work :). any suggestions?

thanks again for the help!
brad
Was This Post Helpful? 0
  • +
  • -

#4 ninethousandfeet@msn.com  Icon User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

Re: SQL to get user postings

Posted 11 February 2009 - 03:11 PM

so i've been messing around with this and now i have the correct results displayed in my list, but the session variable is not displaying the right username and info.
the new SQL is:
SELECT userTable.user_id, username, register_date, email, fun_fact, DATE_FORMAT(register_date, '%b %e, %Y') AS us_format, postingTable.post_id, postingTable.post_title, postingTable.user_id
FROM userTable LEFT JOIN postingTable ON userTable.user_id = postingTable.user_id AND username = var1(var1=text, -1, $_SESSION['MM_Username'])

when i log in as userB, i am taking to the appropriate user profile page with all of the post that userB has made; however, userA's info is displayed in the greeting portion at the top of the page. not sure what happened, it's as if one issue was fixed only to disable something else that was working??

what do you recommend?
thank you!
Was This Post Helpful? 0
  • +
  • -

#5 ninethousandfeet@msn.com  Icon User is offline

  • D.I.C Regular

Reputation: 4
  • View blog
  • Posts: 310
  • Joined: 09-February 09

Re: SQL to get user postings

Posted 11 February 2009 - 03:20 PM

i got it!!

itlee, you definitely sent me in the right direction, thanks for your extremely useful advice!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1