0

I have a Postings table (with data of people posting a service they offer) and a table of people that have corresponded (written mails) to these Posting authors thus starting a Transaction (inserted into a second table: Transactions). Each Posting can have many transactions. Each time a user Logs-in he/she (Transaction_Taker) can send mail to the author (Posting_Author) of his choice. Each first mail generates a new Transaction and its Transaction_Id (int) is appended to Postings table in the varchar, hyphen-separated Posting_Transaction_List field. The contents of each subsequent mail that same (logged-in) user (aka Transaction_Taker) sends, does not create/insert a new transaction (nothing inserted to field Posting_Transaction_List) but rather is appended (update) to the Transaction that was started initially by that user for that Posting.

For easy navigation and search, once a user has logged in, I show an ajax generated list of all these postings such that each Posting only shows once though it can have many transactions. In other words I need to show a list of all available Postings including the ones this (logged-in) user has started a Transaction for, but these postings (the ones in which this user has an ongoing transaction) should show, but we should not show that same Posting with OTHER user's transactions. That is, only the logged-in user should see his/her transactions.

Assuming I have table Postings with fields: Posting_Id (int), Posting_Author (varchar), Posting_Content (text), Posting_Transaction_List (varchar)

…and table Transactions with fields: Transaction_Id (int), Transaction_Posting_Id (int), Transaction_Taker_Id (int)

I am (almost) achieving my goal with the following SQL: 

$AlmostGoodSQL = "SELECT *, Posting_Id FROM Postings LEFT JOIN Transactions ON 
Postings.Posting_Id = Transactions.Transaction_Posting_Id WHERE Posting_Content 
LIKE '%"+$SomeSearchString+"%' GROUP BY Posting_Id";

The problem is this shows a distinct instance of each Posting, but not necessarily the ones that have to do with the logged-in user (in the case where there are many transactions -including hers- for a Posting). To do this, I would need to select ALL Postings without transactions attached PLUS those that have Transactions just for this user BEFORE doing the group by. This is what I cannot achieve. I believe that due to the way 'group by' works you could maybe select maximum or minimum values, but not an exact match, say for all the Postings that have Transactions with user (Transaction_Taker) '123456'. I think "group by" shows whichever instance it finds first. How to make it match my criteria?

It does not look like a subquery would do, but rather like something conditional, like: "Search for all Postings and if the Posting has a Transaction listed in the Posting_Transaction_List that points to a Transaction where the Transaction_Taker_Id is the one of the logged-in user ($UserId), then show it distinctly (just that one, once)"… and I don't know how to do all that in SQL: Can anybody please help?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Anonymouse
  • 19
  • 3
  • 1
    Your code is vulnerable to [sql injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Dan Aug 20 '14 at 18:37
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Aug 20 '14 at 19:11
  • Thank you @Dan (I hope I am writing the right way and in in the right place), but I prevent sql injection in the code, before the SQL, applying mysql_real_escape_string, to the values I get from $_POST: would you do it differently? – Anonymouse Aug 21 '14 at 00:55
  • @Strawberry, thank you for your help and I would have liked to Vote you for your useful help, but I can't vote as i do not have enough points (15?) yet :( – Anonymouse Aug 21 '14 at 02:05
  • Rather than voting on the advice provided, why not follow the advice provided. – Strawberry Aug 21 '14 at 07:32
  • @Strawberry, I will follow the advice, but it will take me sometime (had an accident and am typing with one hand). I just wanted to show appreciation in the meantime. – Anonymouse Aug 21 '14 at 14:12

0 Answers0