[Finding "most searched for" in mysql table.]

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.

[Finding "most searched for" in mysql table.]

My bad sorry

This worked for me

SELECT DISTINCT searchterm, COUNT(*) AS `Count` FROM search GROUP BY searchterm ORDER BY `Count` DESC LIMIT 10

[Finding "most searched for" in mysql table.]

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

[Optimize Query]

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.

[Dodgy MySQL Query]

[!--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]

[Dodgy MySQL 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',
       (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.

[display raw code]

[!--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.

[*SOLVED* MySQL and Regular Expressions]

[!--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.