[PHP-DB] SQL expert is needed. - MySQL database
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. (go here if the stuff below got
all muddle up http://www.ezigraphics.com/sql_explanation.txt)
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.
Thanks in advance.
Scott Mebberson.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Selection an id from a table where it doesn't exisit in another
Hi Guys, I have two tables, words and searchwords. words contains a list of words with an id number next to them and searchwords contains a page id with a word id (from the words table). I want to select all of the id's from the words table which does not exist in the word_id feild in the searchwords table. Make sense, it is for searching? Let me know if it doesn't. This is the SQL query I am running on MySQL (3.22) - SELECT id FROM words, searchwords WHERE words.id != searchwords.word_id; It isn't working. All it does it return a list of results which are totally incorrect. Thanks Scott. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] DISTINCT in SQL query
Hi Guys,
I am retriving information based across three tables, here is the SQL
statement
SELECT DISTINCT title, filelocation, filename, ranking, summary FROM pdf,
words, searchwords WHERE words.id = searchwords.word_id AND pdf.id =
searchwords.pdf_id AND LCASE(words.word) LIKE LCASE('%$searchstr%') ORDER BY
ranking DESC;
The only problem is, the return set has duplicated rows. But I think I may
have a problem in the words table. I am indexing pdf files and they each
have upto 15 search words, but some of the rows in the words table aren't
words, but instead three or four words, ie.
script (this is a word in one row)
server script (these words are in the second row)
So the query is picking up script in both cases due to the %% in the LIKE
clause, is there anyway to remove the duplicate rows or do I have to have
only one word in each row in the words table?
Hope this makes sense... let me know if it doesn't and I'll re-write it ;)\
Thanks
Scott.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Resource ID
Hi Guys, I am querying my MySQL database with this sql: $q-ezi_query($db, SELECT id FROM words WHERE word='$word'); ezi_query() is just a class - an database layer so the databases can be swapped. Then I check the result to see if it worked. But all I get in the result is Resource id #5 - this number can very sometimes as I am doing these query in a loop and it just counts up. to 8. But I am querying for $word which is a string which is not in that table in the database? Does anybody now what this means? Thanks. Scott -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Inseting info into MySQL and retrieving all in one SQL command
Hi guys, I am adding some information to my MySQL database. It is user's information. So it has things like email address, password, username, all that sort of stuff. What I need to do is once I have added it found out the id - a primary key which auto increments itself. At the moment I am doing two different MySQL queries an INSERT and a SELECT to first of all add the information and then find out the primary key. Is there anyway I can do this in one step? Thanks in advance Scott Mebberson [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Re: Inseting info into MySQL and retrieving all in one SQL command
Thanks, Beau. Great advice! Scott Mebberson [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Hi guys, I am adding some information to my MySQL database. It is user's information. So it has things like email address, password, username, all that sort of stuff. What I need to do is once I have added it found out the id - a primary key which auto increments itself. At the moment I am doing two different MySQL queries an INSERT and a SELECT to first of all add the information and then find out the primary key. Is there anyway I can do this in one step? Thanks in advance Scott Mebberson [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] uploading files
Hi Guys, From your experiences, please answer my questions relating to uploading files. Can you get php to upload an image if you have the direct location of the file (ie. c:\bar\foo\bar\foobar.jpg), but the only thing is that I am not getting this from a form file input with the ENCTYPE of the form set to "multipart/form-data". I am getting the location from a string that is open from a file. Is it still possbile? Also, is there anyway around this?? Thanks Scott Mebberson [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
