[RESOLVED] SQL / PHP code help

[snoopgreen]

Hello everyone

I was hoping someone may be able to help / advise me
Here is my problem
I have some code which is basically a search form
but say a user searches for a distance learning centre and say 5 different programmes run at that centre
my code brings the centre details back 5 times which i dont want i just want the centre to appear once and then say the programme titles to appear.

the code ive got at present is below:

$DLmq = "SELECT * FROM distance_learn_operations, prog_dis_learn WHERE distance_learn_operations.short_code=prog_dis_learn.short_code";

//if ($prog_title <> "") {
// $DLmq .= " AND programme.prog_title = '" . $prog_title . "'";
//}

if ($DLName <> "") {
$DLmq .= " AND distance_learn_operations.centre_name = '" . $DLName . "'";
}

if ($DLCountry <> "") {
$DLmq .= " AND distance_learn_operations.country = '" . $DLCountry . "'";
}

i know there is GROUP BY but you cant use that with the WHERE CLAUSE can you and im certain i need the WHERE clause
so was hoping there is some other way of grouping or just bringing the data once.

thanks
snoopgreen

[bodzan]

You should try adding something like this at the end of your query...

$DLmq .= " GROUP BY whatever_you.need";

...where whatever_you.need would be the table.field that you need result to be grouped by...

If I recall right you CAN use GROUP BY with WHERE clause, BUT again - not quite sure on this one...

[jmosterb]

"i know there is GROUP BY but you cant use that with the WHERE CLAUSE can you and im certain i need the WHERE clause
so was hoping there is some other way of grouping or just bringing the data once."

The problem is not with the WHERE clause. You CAN use a WHERE and an GROUP BY in the same query. However, all of the fields in tables distance_learn_operations and prog_dis_learn needs to be either in the GROUP BY clause or in an aggregate function (such as SUM or similar).

My suggestion would be for you to write out the wanted fieldnames instead of the * in the query, for example:

SELECT centre_detail_1, centre_detail_2 FROM distance_learn_operations, prog_dis_learn WHERE distance_learn_operations.short_code=prog_dis_learn.short_code GROUP BY centre_detail_1, centre_detail_2