RE: My SELECT is still returning the same rows
Hmm, the words table is just a table with an id field and a word field, the word field only contains one word in it and then the id field is that words id. There is only one word per row. The searchwords table is a long list of keywords that relate to that field, i.e. it contains the pdf id, the words, id and it's ranking. They row duplication problem is occuring when a pdf has both the words that the query is searching for. Does that make sense? Thanks Scott. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Carl Troein Sent: Tuesday, 25 September 2001 3:34 AM To: [EMAIL PROTECTED] Subject: Re: My SELECT is still returning the same rows Scott Mebberson writes: AND (words.word LIKE '%award%' OR words.word LIKE '%section%') ## - difference between 1 and 2 word search What do you mean by that comment? I can't make any real sense out of it, and it doesn't seem to have much to do with the actual query (which returns rows that contain 'award' or 'section'). AND (words.word LIKE '%award%') The only problem is, it is still returning the same rows. As in, returning rows that are exactly the same? Well, do you have any rows that contain 'award' but not 'section' and that match the other criteria? Could it be that you're confusing OR with some other operator, like XOR or AND NOT? //C -- Carl Troein - Cmrdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
My SELECT is still returning the same rows
Hi Guys, The following query that I am using is returning duplicate rows. For a two word search: SELECT DISTINCT pdf.title, pdf.filename, searchwords.ranking, pdf.summary FROM pdf, words, searchwords WHERE words.id = searchwords.word_id AND pdf.id = searchwords.pdf_id AND (words.word LIKE '%award%' OR words.word LIKE '%section%') ## - difference between 1 and 2 word search ORDER BY ranking ASC; For a one word search: SELECT DISTINCT pdf.title, pdf.filename, searchwords.ranking, pdf.summary FROM pdf, words, searchwords WHERE words.id = searchwords.word_id AND pdf.id = searchwords.pdf_id AND (words.word LIKE '%award%') ORDER BY ranking ASC; The only problem is, it is still returning the same rows. As in, returning rows that are exactly the same? Any ideas? Thanks Scott Mebberson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Count(*) with zero rows
Hi Raymond, I have been having the same problem using the GROUP BY statement, even without the COUNT(*) function. I'm working with PHP and the way I got around this is to use arrays. I retrieved all rows from the table and then sorted them into seperate arrays according to, in your case, ClientName. Then do a count() on each of the arrays to find out how many items each client has bought. I created a main array, named - in your case, $clients and then created other arrays according to clients names. So $clients[John] would contain an array with all of the itemName's that John bought. Does that make sense? If anyone else has a lead on the GROUP BY statement, it would be very much appreciated. Scott. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Raymond Prisament Sent: Wednesday, 19 September 2001 9:10 AM To: [EMAIL PROTECTED] Subject: Count(*) with zero rows In the course of using MySQL the following issue has come up a few times; I am unaware of the solution for it, but I am sure one exists, since it seems fairly elementary. It has to do with the COUNT(*) and GROUP BY functions. Let's say I have clients, and clients buy items. table clients: - ClientID | ClientName - 1 | John 2 | Mary table items: --- ClientIDWhoBoughtIt | ItemName --- 1 | Dishwasher 1 | Light Bulb 1 | Grill --- Notice that John has bought three things, and Mary did not buy anything. Now, I want a report on how many items each client has bought. The way you are supposed to do this is: SELECT ClientName, COUNT(*) AS itemcount FROM clients, items WHERE clients.ClientID = items.ClientIDWhoBoughtIt GROUP BY clients.ClientID This will return: -- ClientName | itemcount -- John | 3 -- Which is nice, but is not what I want, because I don't get any information about Mary. What I want, obviously, is: -- ClientName | itemcount -- John | 3 Mary | 0 -- Is there any way to get that result? I would think there must be. Such functionality is vital if, say, you want to build a report with an alphabetical list of everyone who's signed up for your web site, and the number of things they've bought. It doesn't make sense to not list their names simply because they signed up and didn't buy anything yet - that may be valid information. I would greatly appreciate anyone's input into this. Sincerely, Ray Prisament - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Expert Needed
Hi Guys, here goes..
SELECT count(page_searchwords.word_id) as score, page_data.id, filename,
title FROM page_data, pagewords, page_searchwords WHERE
(page_searchwords.media_type='static' AND pagewords.id =
page_searchwords.word_id AND page_data.id = page_searchwords.id AND
LCASE(pagewords.word) LIKE LCASE('%hit%')) OR
(page_searchwords.media_type='static' AND pagewords.id =
page_searchwords.word_id AND page_data.id = page_searchwords.id AND
LCASE(pagewords.word) LIKE LCASE('%two%')) GROUP BY page_data.id ORDER BY
score DESC;
Hi Guys, above is one of the SQL queries my PHP codes makes up to search my
database. That is what it looks like for a two words search, i.e. hit two.
It looks like the following when it does a one word search, i.e. hit
SELECT count(page_searchwords.word_id) as score, page_data.id, filename,
title FROM page_data, pagewords, page_searchwords WHERE
(page_searchwords.media_type='static' AND pagewords.id =
page_searchwords.word_id AND page_data.id = page_searchwords.id AND
LCASE(pagewords.word) LIKE LCASE('%hit%')) GROUP BY page_data.id ORDER BY
score DESC;
I'll now explain the tables to you.
I have the follwing tables in my database.
table: page_data table: pagewordstable:
page_searchwords
++--+---+-+ ++-+
++-++
| id | filename | title | contents | | id | word|| id |
word_id | media_type |
++--+---+-+ ++-+
++-++
| 1 | 01_title | ABC | information | | 1 | wtc || 2 |
1| static|
| 2 | fileAA | foo | wtc | ++-+
++-++
| 3 | foobar | bar | content | | 2 | information || 1 +
2| static|
++--+---+-+ ++-+
++-++
It works like this.
page_data has the webpage information in it, when the page get's update the
SQL I have written get's all of the keywords from the page. It then get's
the id's from the pagewords table (i.e. pagewords.id), adding the words if
it is not already there. Then once it has all of the id's from pagewords
(i.e. pagewords.id) it adds the information to the page_searchwords table.
The page_searchwords.id column contains the equivalent of the page_data.id
column. page_searchwords.word_id is the equivalent of the pagewords.id
column and the media_type is any of the following three strings; static,
pdf, news.
The search works like this it. It sorts through the page_words table and
get's the id of the words the user is searching. In the example above if the
user search's for wtc and information, the id's it would retrieve are 1 and
2. Once it has these id's it then searches the page_searchwords table and
retrieves any row which has a word_id value of either 1 or 2 (being wtc or
information respectively). Once it has this it then write the search results
out to the browser using the following columns from the page_data table; id,
filename, title.
I then also have another table:
table: news
++--+-+--+
| id | headline | content | date |
++--+-+--+
| 1 | newHead | contentH| 11.01|
++--+-+--+
| 2 | header | bulding | 12.01|
++--+-+--+
This is much the same as the page_data title. I also have the contents of
the news pages being update into the pagewords and page_searchword tables.
What I need to know is how to look through these at the same time, and get
the new id, headline and content out of the database.
Does anybody have any idea? This does make sense doesn't it? Let me know if
it doesn't thanks.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
