bowenbowen

Yeah you are right, don't need the distinct --> tired mind here as well.....I am now lost on what you want though, I thought you needed the top ten search terms? Surely that SQL achieves this with the example you provided? Perhaps I getting wrong end of the stick or I am incredibly dum.....sorry if I am, because I have been wasting your time.

My bad sorry This worked for me SELECT DISTINCT searchterm, COUNT(*) AS `Count` FROM search GROUP BY searchterm ORDER BY `Count` DESC LIMIT 10

SELECT COUNT(DISTINCT searchterm) AS `searchterm_count`, searchterm FROM table GROUP BY searchterm ORDER BY 'searchterm_count' DESC LIMIT 10 Give that a go, that should give you your top ten search terms descending

Has anyone got any tips on how I could optimize this query? [code] SELECT s.first_name AS 'First Name', s.last_name AS 'Last Name', s.year AS 'Year', s.form_group AS 'Form Group', SUM(b.re = '1') AS RE, SUM(b.rw = '1') AS RW, SUM(b.rc = '1') AS RC, SUM(b.rb = '1') AS RB, SUM(b.hw = '1') AS Homework, SUM(b.late = '1') AS late, SUM(b.d1 != '') AS D1, SUM(b.d2 != '') AS D2, SUM(b.d3 != '') AS D3, SUM(b.d4 != '') AS D4 FROM students s INNER JOIN behaviour_record_sheets_archive b ON s.student_id = b.student_id WHERE s.year = 8 GROUP BY s.student_id HAVING SUM(b.d1 != '') = 0 AND SUM(b.d2 != '') = 0 AND SUM(b.d3 != '') = 0 AND SUM(b.d4 != '') = 0 ORDER BY s.last_name ASC;[/code] Here is the result of my EXPLAIN statement [table] [tr] [td]ID[/td] [td]Select Type[/td] [td]Table[/td] [td]Type[/td] [td]Possible Keys[/td] [td]Key[/td] [td]Key Len[/td] [td]Ref[/td] [td]Rows[/td] [td]Extra[/td] [/tr] [tr] [td]1[/td] [td]Simple[/td] [td]s[/td] [td]index[/td] [td]PRIMARY[/td] [td]PRIMARY[/td] [td]30[/td] [td][/td] [td]1596[/td] [td]Using where; Using temporary; Using filesort[/td] [/tr] [tr] [td]1[/td] [td]Simple[/td] [td]b[/td] [td]ref[/td] [td]student_id[/td] [td]student_id[/td] [td]30[/td] [td]behaviour.s.student_id[/td] [td]162[/td] [td][/td] [/tr] [/table] As you may guess, I have an index setup in my behaviour_record_sheets_archive (excuse the name) on the column "student_id". I would just be interested to see if this query could be sped up at all, it is currently taking around 12 seconds to run.

[!--quoteo(post=387105:date=Jun 23 2006, 10:20 AM:name=bowenbowen)--][div class=\'quotetop\']QUOTE(bowenbowen @ Jun 23 2006, 10:20 AM) [snapback]387105[/snapback][/div][div class=\'quotemain\'][!--quotec--] [code] SELECT s.first_name AS 'First Name',        s.last_name AS 'Last Name',        s.year AS 'Year',        s.form_group AS 'Form Group',        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.re = '1'        AND s.student_id = b.student_id) AS RE,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rw = '1'        AND s.student_id = b.student_id) AS RW,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rc = '1'        AND s.student_id = b.student_id) AS RC,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rb = '1'        AND s.student_id = b.student_id) AS RB,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d1 != ''        AND s.student_id = b.student_id) AS D1,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d2 != ''        AND s.student_id = b.student_id) AS D2,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d3 != ''        AND s.student_id = b.student_id) AS D3,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d4 != ''        AND s.student_id = b.student_id) AS D4 FROM students s      INNER JOIN behaviour_record_sheets_archive b ON s.student_id =      b.student_id WHERE s.year = '7' GROUP BY s.student_id ORDER BY s.last_name ASC; [/code] This query counts columns in the behaviour_record_sheets_archive table to see if there is anything in that column, then relates the student_id in the behaviour_record_sheets_archive table (stupid name I know, I didn't name it!) to the student_id in the student table . Basically I need to use the result of of the count statements in the WHERE clause. I only want to display information where the count statements = 0. Not sure if any of you will understand what I mean, but any help would be much appreciated. [/quote] Found a solution for it, me being dumb. Although I would be interested to see if there is a better way. [code] SELECT s.first_name AS 'First Name',        s.last_name AS 'Last Name',        s.year AS 'Year',        s.form_group AS 'Form Group',        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.re = '1'        AND s.student_id = b.student_id) AS RE,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rw = '1'        AND s.student_id = b.student_id) AS RW,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rc = '1'        AND s.student_id = b.student_id) AS RC,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rb = '1'        AND s.student_id = b.student_id) AS RB,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d1 != ''        AND s.student_id = b.student_id) AS D1,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d2 != ''        AND s.student_id = b.student_id) AS D2,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d3 != ''        AND s.student_id = b.student_id) AS D3,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d4 != ''        AND s.student_id = b.student_id) AS D4 FROM students s      INNER JOIN behaviour_record_sheets_archive b ON s.student_id =      b.student_id WHERE      (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d1 != '' AND s.student_id = b.student_id) = 0 AND      (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d2 != '' AND s.student_id = b.student_id) = 0 AND      (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d3 != '' AND s.student_id = b.student_id) = 0 AND      (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d4 != '' AND s.student_id = b.student_id) = 0 AND      s.year = 7 GROUP BY s.student_id ORDER BY s.last_name ASC;[/code]

[code] SELECT s.first_name AS 'First Name',        s.last_name AS 'Last Name',        s.year AS 'Year',        s.form_group AS 'Form Group',        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.re = '1'        AND s.student_id = b.student_id) AS RE,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rw = '1'        AND s.student_id = b.student_id) AS RW,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rc = '1'        AND s.student_id = b.student_id) AS RC,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.rb = '1'        AND s.student_id = b.student_id) AS RB,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d1 != ''        AND s.student_id = b.student_id) AS D1,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d2 != ''        AND s.student_id = b.student_id) AS D2,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d3 != ''        AND s.student_id = b.student_id) AS D3,        (SELECT COUNT(*) FROM behaviour_record_sheets_archive b WHERE b.d4 != ''        AND s.student_id = b.student_id) AS D4 FROM students s      INNER JOIN behaviour_record_sheets_archive b ON s.student_id =      b.student_id WHERE s.year = '7' GROUP BY s.student_id ORDER BY s.last_name ASC; [/code] This query counts columns in the behaviour_record_sheets_archive table to see if there is anything in that column, then relates the student_id in the behaviour_record_sheets_archive table (stupid name I know, I didn't name it!) to the student_id in the student table . Basically I need to use the result of of the count statements in the WHERE clause. I only want to display information where the count statements = 0. Not sure if any of you will understand what I mean, but any help would be much appreciated.

[!--quoteo(post=362127:date=Apr 6 2006, 04:14 AM:name=Chris Coin)--][div class=\'quotetop\']QUOTE(Chris Coin @ Apr 6 2006, 04:14 AM) [snapback]362127[/snapback][/div][div class=\'quotemain\'][!--quotec--] so ther isnt a certain "tag" i could use. hmmm, i guess the only way is to color the code myself. [/quote] Look into using highlight_string or highlight_file on the php.net website.

[!--quoteo(post=361137:date=Apr 3 2006, 08:44 AM:name=bowenbowen)--][div class=\'quotetop\']QUOTE(bowenbowen @ Apr 3 2006, 08:44 AM) [snapback]361137[/snapback][/div][div class=\'quotemain\'][!--quotec--] I need to match a string in my field in my table, which would look something like this 8m/Ma1#ZI A26 All the fields have the same format. I face problems when I need to extract every field with the ZI letters in after the hash. I am guessing I need a regular expression to only match anything after the hash, can someone help me out with this. Any help would be appreciated. [/quote] Forget this, I was being stupid, I can just use LIKE '%#ZI%" that seems to work fine.