Help with Mysql Select

[whelpton]

Hey guys, thanks for reading my post.

 

I'm banging my head against a brick wall with this one, I have very limited knowledge of Mysql statements unfortunately.

 

I'm trying to select data from a table that contains product purchases and find out which customers have purchased two seperate products, the code I have for this so far is:

 

$brand = $_GET["brand"];
$brand2 = $_GET["brand2"];

$result = mysql_query('SELECT * FROM sample WHERE `nice_date` >= "'.$year.'-01-01" and `nice_date` < "'.$year.'-01-31"  AND `product_name` LIKE "%'.$brand.'%" AND `product_name` LIKE "%'.$brand2.'%" ');

 

However, this simply finds products that contain $brand and $brand2, whereas I'm trying to look for results where both brands were purchased.

 

Once again, thanks for reading and any pointers would be brilliant.

[jazzman1]

Any errors?

 

Can you echo the query before to send it to db?

 

Example:

 

$query = 'SELECT * FROM sample WHERE `nice_date` >= "'.$year.'-01-01" and `nice_date` < "'.$year.'-01-31"  AND `product_name` LIKE "%'.$brand.'%" AND `product_name` LIKE "%'.$brand2.'%" ';
echo $query; exit;

[whelpton]

Hey Jazzman1, thanks for replying.

 

There are no errors occuring and echoing the statement comes back with:

 

SELECT * FROM sample WHERE `nice_date` >= "2012-01-01" and `nice_date` < "2012-01-31" AND `product_name` LIKE "%bread%" AND `product_name` LIKE "%butter%"

 

Where $brand1=bread & brand2=butter

[jazzman1]

What type of structure is nice_date in your DB, is it varchar?

[Barand]

Find those who bought brand1 and match against those who bought brand2

 

SELECT * FROM sample
WHERE `nice_date` >= '{$year}-01-01' and `nice_date` < '{$year}-01-31'
AND `product_name` LIKE '%$brand1%'
INNER JOIN
(
SELECT custID FROM sample
WHERE `nice_date` >= '{$year}-01-01' and `nice_date` < '{$year}-01-31'
AND `product_name` LIKE '%$brand2%'
) as brand2
USING (custID)

Edited January 6, 2013 by Barand

[whelpton]

Barand,

 

Unfortunately I don't seem to be able to run that Query, I've modified it to fit the characteristics of my DB and it dies:

 

SELECT * FROM sample WHERE `nice_date` >= "2012-01-01" and `nice_date` < "2012-01-31" AND `product_name` LIKE "%bread%" INNER JOIN ( SELECT user_id FROM sample WHERE `nice_date` >= "2012-01-01" and `nice_date` < "2012-01-31" AND `product_name` LIKE "%butter%" ) as product_name USING (user_id)

[Barand]

I cleaned it up a bit and changed the subquery alias name back to "brand2". Don't give it one of your column names.

 

SELECT * FROM sample
WHERE `nice_date` >= '2012-01-01' and `nice_date` < '2012-01-31'
AND `product_name` LIKE '%bread%'
INNER JOIN
   (
   SELECT user_id FROM sample
   WHERE `nice_date` >= '2012-01-01' AND `nice_date` < '2012-01-31'
   AND `product_name` LIKE '%butter%'
   ) as brand2
   USING (user_id)

[whelpton]

I'm still not getting any data or errors from that statement :/

 

 

$result = mysql_query("SELECT * FROM sample
WHERE `nice_date` >= '2010-01-01' and `nice_date` < '2012-01-31'
AND `product_name` LIKE '%a%'
INNER JOIN
 (
 SELECT user_id FROM sample
 WHERE `nice_date` >= '2010-01-01' AND `nice_date` < '2012-01-31'
 AND `product_name` LIKE '%b%'
 ) as brand2
 USING (user_id)");
while($row=mysql_fetch_array($result))
{
echo "<tr>";
echo "<td align='center' width='200'>" . $row['user_id'] . "</td>";
}
echo "</table>";

I've opened the query up to just A & B so it theoretically should match most products, but I'm not getting anything at all.

 

Thanks for your help so far though, Like I said earlier on, I don't have much experience with sql statements such as these.

 

 

EDIT- on second thoughts, it looks like its bugging out with the query.

 

ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN ( SELECT user_id FROM sample WHERE `nice_d' at line 4

Edited January 6, 2013 by whelpton

[Barand]

Oops! Try

 

$sql = "SELECT sample.*
    FROM sample
    INNER JOIN
	    (
	    SELECT user_id FROM sample
	    WHERE `nice_date` >= '2012-01-01' AND `nice_date` < '2012-01-31'
	    AND `product_name` LIKE '%butter%'
	    ) as brand2
	    USING (user_id)
    WHERE `nice_date` >= '2012-01-01' and `nice_date` < '2012-01-31'
    AND `product_name` LIKE '%bread%'  ";

[whelpton]

Thank you very much, that worked beautifully! One final question, as it stands right now, this sql will echo product names that are like bread, is there any way to also echo the butter products?

 

Thanks once again! =D

[Jessica]

Use OR.

[whelpton]

Instead of AND?

[Jessica]

What string has both bread and butter in it? A string like "bread and butter". If you want to find strings which have EITHER of those terms, which do you think you should use, AND or OR?

[Barand]

A little more complicated:

 

$sql = "SELECT sample.*
    FROM sample
    INNER JOIN
	    (
	    SELECT user_id, product_name FROM sample
	    WHERE `nice_date` >= '2012-01-01' AND `nice_date` < '2012-01-31'
	    AND ((`product_name` LIKE '%butter%') OR (`product_name` LIKE '%bread%'))
	    ) as brand2
	    ON sample.user_id = brand2.user_id AND sample.product_name <> brand2.product_name
    WHERE `nice_date` >= '2012-01-01' and `nice_date` < '2012-01-31'
    AND ((sample.`product_name` LIKE '%butter%') OR (sample.`product_name` LIKE '%bread%'))
    ORDER BY sample.user_id, sample.nice_date   ";