[PHP][MySQL]Selecting multiple records based on multiple conditions into a single one

[akhileshbc]

I have the following table design:

Table1
---------------
uID        | Email
---------------
1 | [email protected]
2 | [email protected]
3 | [email protected]
4 | [email protected]


Table2
---------------
cID     |uID     | Content_title
---------------
1 | 1 | abc1
2 | 1 | abc2
3 | 2 | abc3
4 | 3 | abc4
5 | 3 | abc5
6 | 3 | abc6
etc..

What I want to do is, I need to select the email of two uIDs(which I'll pass in the query, say uID1 and uID2) and also, the content_title and cID which belongs to one of the uIDs that I pass(here, I'll pass the cID also).

Did I made it complicated ?

What I'll pass in the query is:

• uID of first user (uID1)
• uID of second user (uID2)
• cID of second user (ie. of uID2)

This should return:

• email of first user (uID1)
• email of second user (uID2)
• content_title of the second user from Table2 (ie. of uID2. The cID that I pass will be validated in this part. I mean, the cID that I passed in the query, should belongs exactly to the uID2. Otherwise, empty field is returned.)

What I have tried so far (and working):

SELECT * FROM 
(
   (SELECT uID FROM Table1 WHERE uID = '1' LIMIT 1) 
      AS email1, 
   (SELECT uID FROM Table1 WHERE uID = '3' LIMIT 1) 
      AS email2
)

Example:
I'll pass uID1 = '1' , uID2 = '3' and cID = '4'
Then it should return:

[email protected]  |  [email protected]  |  4  |  abc4

Any ideas ?

Thanks

[GaryMazzone]

I don't know MySQL well enough. For SQL Server I would use a cross apply

[akhileshbc]

I don't know MySQL well enough. For SQL Server I would use a cross apply

Thanks

I don't think CROSS APPLY is there in mySQL. But will google it.

[TheBigB]

First of all, what is the scenario here more or less?
Secondly, where do you get the two user ID's from?
Thirdly, in your example the uID 1 is not related to cID 4; why do you want to retrieve it?

[akhileshbc]

What I'm trying here is, when a registered user wants to contact the author of a particular content, he could click on the "contact via email" button and a form appears. After entering the message he wants to deliver, he would sent it. One id will be the recipient's and the other one will be the sender's.
It is via jQuery(I'm a mere beginner and don't even know the basics of jQuery, but following tutorials and tricks from other sites, found on Googling)

So, when the form is submitted to a PHP page, it would check whether the current session is a valid user(registered user) or not. If so, it will query the database to find the email ids of both the sender and the author(recipient). Also, it should validate that the content id being passed is belongs to the particular author(which the id is being passed, ie. the second ID). If so, get the email addresses of both of them and also, the content title(which I would be including in the email).

I know how to do it in separate queries. But what I'm trying to do is, I'm trying to combine into a single query to achieve a better performance.

Hope it's clear.

Thanks

[Hack]

It looks like something called GROUP_CONCAT would be a MySQL equivalent to a T-SQL CROSS APPLY.

[akhileshbc]

It looks like something called GROUP_CONCAT would be a MySQL equivalent to a T-SQL CROSS APPLY.

Thanks

But I'm not sure how it can be used to solve my issue ! After reading about it, it seems to be to concatenate a group into a single record(if I'm correct).

A brief idea on how to use it will be helpful (no need to give me the query string or code, but just the basic idea of how to use it with my query to solve the issue)

I'll try to read other tutorials regarding this, so that the bulb in my head may lightens up after that.

[TheBigB]

I don't think putting it all in one query will improve performance and/or readability.

One thing you could pull together is retrieval of the recipients email address and content title.
It would look something like this:

$recipientId = 3;
$contentId = 4;

$q = "SELECT t2.`Content_title`, t1.`Email`
      FROM `Table2` t2
      LEFT JOIN `Table1` t1 ON t1.`uID` = t2.`uID`
      WHERE t1.`Email` IS NOT NULL
      AND t2.`cID` = '$contentId'
      AND t2.`uID` = '$recipientId'";
$result = mysql_query($q);
if(($row = mysql_fetch_assoc($result)))
{
    // We have a result!
    // That means we have verified that the author of $contentId is $recipientId and we found
    // the email address for $recipientId
    $recipientEmail = $row['Email'];
    $contentTitle = $row['Content_title'];
}
else
{
    // Something about the input data is invalid.
}

Not tested, but should work

[akhileshbc]

Thanks. Will try to experiment with that