Not getting ANY results

[Guest]

For some reason I am not getting query results – cannot figure out why.

The idea here is to search a table with multiple words inputted from a form.

Depending on how many words, the code will creates additional WHERE conditions to find matches for each word.

Thus, if three would are inputted, it will create three WHERE statements and query the table for all three words then return the results.  If four words – then it will create four WHERE statement and query all four words and so on.

But, for some reason it is not returning any results and I can’t figure out why.

[bregent]

Insteading of submitting the query to the database, spit it out to the screen so you can see where the problem is. Post the output here if you can't find the problem in the sql.

[David_Powers]

Bregent's advice is spot on. Also, I don't know if you have seen the article I wrote about doing this a few months ago? See http://cookbooks.adobe.com/post_Create_search_query_with_optional_fields-16245.html.

[Guest]

Thanks - I tried printing out the results - but for some reason I am not getting any results - so nothing prints.  If I print out the input - it prints outs fine - just no results. I also wanted to echo any errors - but, no errors and no results - thus I have nothing to print out.

I will try to print the query.

Will also check out your article.

[Guest]

I printed the query as follows and all seems wells – it appears to doing what I want it to.  I used the following inputs: time test true – but, it still did not return any results

There was one small error that I fixed –

$query_rsSearch = $query_rsSearch."$arrayField[$a] = $arraySearch[$b]";

$arrayField[$a] – was missing a letter – should have been $arrayFields[$a]

But, after this fix - still no results and I can't figure out why.

SELECT p.page_name AS name, p.page_title AS title, p.page_text AS text, COUNT(*) AS occurrences FROM page p, word w, occurrence o WHERE p.page_id = o.page_id AND w.word_id = o.word_id AND w.word_word = time OR w.word_word = test OR w.word_word = true GROUP BY p.page_id ORDER BY occurrences DESC

I did get the following errors:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource on line 80

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource on line 153

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource on line 191

I did some research on possible causes and got as many different answers as there are stars in the night sky.

It has to have something to do with the code outside the query – I have tried many things only to get the same no results.

Please help.

[David_Powers]

SELECT p.page_name AS name, p.page_title AS title, p.page_text AS text, COUNT(*) AS occurrences FROM page p, word w, occurrence o WHERE p.page_id = o.page_id AND w.word_id = o.word_id AND w.word_word = time OR w.word_word = test OR w.word_word = true GROUP BY p.page_id ORDER BY occurrences DESC

Printing out the query like that makes the problem clear. Your WHERE clause is looking for values in w.word_word, which is presumably a VARCHAR column. The values should be in quotes.

WHERE p.page_id = o.page_id AND w.word_id = o.word_id 
AND w.word_word = 'time' OR w.word_word = 'test' OR w.word_word = 'true'

When testing, it's useful to use mysql_error() to display any error messages from MySQL. It would have highlighted a syntax error in your query.

[Guest]

Mr. Powers – Thanks.  I tried printing out the errors – but never got anything other than the mysql_num_row error.  So, I must have done something wrong in setting this up – shows how new I am to this. Where would I set up the mysql_errror()?  I think this is my problem not knowing where to get the program to print errors.  Would it go after the query or when the db is selected?

I also tried to put quotes around the input terms and still could not get any results – but will try again – I think I tried double quotes.

Again, thanks for your help.

[Guest]

Thanks for the tip on putting the array value in ‘’ – got that set up OK.

Now I am having the following issue.  Not sure why I am having such a hard time with this – think that I just got myself turned around too many times.

Now, when I run the query – I get the following.

Results: Resource id #3

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 'Resource id #3 LIMIT 0, 10' at line 1

I tried to research this a bit and found that I might need to include the following:

$query_results = mysql_fetch_assoc($query_results);

But, I get the following

Results: Array

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 'Array LIMIT 0, 10' at line 1

Now I have fixed errors like this before by increasing the key by 1 – but, that is not how I have this set up – using a while loop not a for loop.

Can you tell me if I am on the right track here? Should I switch this to a foreach loop?

One side note:  It is pretty cool that you can break out of the query and insert nearly anything you want, how many times you want to, right back into the query.

[David_Powers]

brywilson88 wrote:

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 'Resource id #3 LIMIT 0, 10' at line 1

I tried to research this a bit and found that I might need to include the following:

$query_results = mysql_fetch_assoc($query_results);

But, I get the following

Results: Array

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 'Array LIMIT 0, 10' at line 1

Resource id#3 means you're trying to use the result of a database query directly in another query. You can't do it.

When you use $result = mysql_query($sql);, $result is a database result resource. To do anything with it, you need to use a function, such as mysql_fetch_assoc() to extract the results. However, mysql_fetch_assoc() gets one row at a time, so you need to loop through the database result resource extracting each row (unless you know there's only one row in the result).

mysql_fetch_assoc() produces an array of results from that row. So, in your example, the value of a column called word would be in $query_results['word'].

[Guest]

Thanks for the information - I will see what I can do to get the results to come out right.  Again, thanks.

[Guest]

Thanks - I am still just too confused here to get this to work.  I think I understand what you are saying about mysql_fetch_assoc() - but, just cannot get it to work here.

I think I will just scrap what I have and try to approach this a different way.

Again, thanks for your invaluable insight and information.