Top 10 Query
------=_NextPart_000_0004_01CBAF07.8ED4BE50
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
I am trying to write a mySQL query to determine if the current word =
being displayed in the game is one of the top 10 most popular. I am =
trying to achieve this by creating a table that tracks how many times =
each word was accessed. A new row is created for each access to the =
word. The table structure is as follows:
CREATE TABLE IF NOT EXISTS `bible_word_scramble_usage` (
`reference` int(25) NOT NULL AUTO_INCREMENT,
`bible_dictionary_reference` int(4) NOT NULL,
`ip_address` varchar(20) NOT NULL,
`date_accessed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE =
CURRENT_TIMESTAMP,
PRIMARY KEY (`reference`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D122 ;
The following is the SELECT query I need help tweaking. What I am =
trying to do is select the top 10 most popular words and then use a =
second select to see if the word being displayed is one of the top 10 =
(IE using the search results of the 10 top SELECT query).
The error this query is currently giving me is:
You have an error in your SQL syntax; check the manual that corresponds =
to your MySQL server version for the right syntax to use near '=3D =
`top_ten`.`bible_dictionary_reference` =3D 1 LIMIT 1'
This is the query:
SELECT `top_ten`.`bible_dictionary_reference`
FROM (
SELECT `bible_dictionary_reference` , COUNT( `reference` ) AS word_usage
FROM `bible_word_scramble_usage`
GROUP BY `bible_dictionary_reference`
ORDER BY word_usage DESC
LIMIT 10
) AS top_ten
WHERE =3D `top_ten`.`bible_dictionary_reference` =3D1
LIMIT 1
Thank you for helping me. Ron
The Verse of the Day
=E2=80=9CEncouragement from God=E2=80=99s Word=E2=80=9D
http://www.TheVerseOfTheDay.info
------=_NextPart_000_0004_01CBAF07.8ED4BE50--
Re: Top 10 Query
------=_NextPart_000_0016_01CBAF0E.799E94F0
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
I had an extra =E2=80=9C=3D=E2=80=9D sign by the WHERE, my mistake, I =
couldn=E2=80=99t see it originally. Ron
The Verse of the Day
=E2=80=9CEncouragement from God=E2=80=99s Word=E2=80=9D
http://www.TheVerseOfTheDay.info
From: Ron Piggott
Sent: Saturday, January 08, 2011 7:42 AM
To: php-db [at] lists.php.net
Subject: Top 10 Query
I am trying to write a mySQL query to determine if the current word =
being displayed in the game is one of the top 10 most popular. I am =
trying to achieve this by creating a table that tracks how many times =
each word was accessed. A new row is created for each access to the =
word. The table structure is as follows:
CREATE TABLE IF NOT EXISTS `bible_word_scramble_usage` (
`reference` int(25) NOT NULL AUTO_INCREMENT,
`bible_dictionary_reference` int(4) NOT NULL,
`ip_address` varchar(20) NOT NULL,
`date_accessed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE =
CURRENT_TIMESTAMP,
PRIMARY KEY (`reference`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D122 ;
The following is the SELECT query I need help tweaking. What I am =
trying to do is select the top 10 most popular words and then use a =
second select to see if the word being displayed is one of the top 10 =
(IE using the search results of the 10 top SELECT query).
The error this query is currently giving me is:
You have an error in your SQL syntax; check the manual that corresponds =
to your MySQL server version for the right syntax to use near '=3D =
`top_ten`.`bible_dictionary_reference` =3D 1 LIMIT 1'
This is the query:
SELECT `top_ten`.`bible_dictionary_reference`
FROM (
SELECT `bible_dictionary_reference` , COUNT( `reference` ) AS word_usage
FROM `bible_word_scramble_usage`
GROUP BY `bible_dictionary_reference`
ORDER BY word_usage DESC
LIMIT 10
) AS top_ten
WHERE =3D `top_ten`.`bible_dictionary_reference` =3D1
LIMIT 1
Thank you for helping me. Ron
The Verse of the Day
=E2=80=9CEncouragement from God=E2=80=99s Word=E2=80=9D
http://www.TheVerseOfTheDay.info
------=_NextPart_000_0016_01CBAF0E.799E94F0--
