[SOLVED] query help

[tauchai83]

i trying to create a [color=red]new function to track the "good potential" customers who have purchase things more than 10 times through online.[/color] How to do a good sql statement tat will suit that need?

my code is here:

$strsql1 = "SELECT DISTINCT A.Cust_id, A.First_name, A.Last_name, A.Phone, A.Email, A.Address, B.Cust_id FROM customer A, orders B WHERE A.Cust_id=B.Cust_id";
$result1 = mysql_query($strsql1);

1 table contain customer particulars, another 1 is order table which has Cust_id as its Foreign Key...

[worldworld]

I understand that your query is correct.  :)

[fenway]

I'm not sure I understand what you mean... but a proper JOIN, and no distinct would be better.

[tauchai83]

the query retrive what i want but how to echo/print only the customer's list that have purchased 10 times with my company? example...use count??? >10?? can give me idea?

[fenway]

[code]
SELECT A.Cust_id, A.First_name, A.Last_name, A.Phone, A.Email, A.Address, COUNT(B.Cust_id) AS cnt
FROM customer A
LEFT JOIN orders B ON A.Cust_id=B.Cust_id
HAVING cnt >10
[/code]

[tauchai83]

fenway,

the sql tat u wrote does not work at all...nothing come out and error msg display no record found!....i have revised my table and it seem logic...just nit a complete or maybe i would say advanced sql statement to retrieve it from DB....any more idea? i understood what u wrote....is good...but does not work....

anymore idea?? :).. thanks a lot!

-chai-

[fenway]

Sorry, I missed a group by...

[tauchai83]

GROUP By what? the cust_id???

[tauchai83]

Ok..solve...anyhow...i did not use Left Join...just customer A, orders B and add Group By Cust_id...thanks a lot fenway...u are really mysql super guru who can give idea...thanks ya...i have one new query problem which not sure u can help...how to select an product that have expire?

I have one table called product: pd_id, pd_qty, pd_name, pd_description,[color=red]Expire_Date [/color] field.

this expire date i put as [color=red]VarChar[/color] in the format of dd-mm-yyyy..eg...27-01-2007

i want to retrieve ALL expired item from this table..the following is my code tat did not work...

$day  = date("d");
$month = date("m");
$year  = date("Y");
$today= $year.'-'.$month.'-'.$day;

$sql = "SELECT * from product WHERE 'strtotime(Expire_Date)<strtotime($today)'";
$result1 = mysql_query($sql);

i have try many ways....how to solve this? should i put the Expire_Date as date format instead Varchar?
plz help..thank you for ur time...

i try use strtotime to compare it....i think that's wrong..but how can i solve this prob?

[fenway]

I simply suggested LEFT JOIN in case you wanted to every get a count() of zero.

First, regarding your new query, you don't need any of that fancy PHP date stuff... use CURRENT_DATE(), a MySQL date function.  Then, just use normal comparison operators:

SELECT * FROM product WHERE ExpireDate < CURRENT_DATE()

[tauchai83]

so the Expire_Date i should put as "date" or "varchar" to compare?

[tauchai83]

hehe..done...fenway..thanks a lot! u know what is si fu? superguru!...thanks....