Query More Than One Input

[Guest]

Having trouble and I just cannot seem to make any progress.  I think that I have tried so many things that I have confused myself and now I am unsure of where to turn.

I have a simple query that should take a set of inputted words – run each word through the database – then based on the matching of each word – return a term and the total number of times that all the words match that particular term.

So far, all I have been able to do is run the words through the database – but, it seems to only count or return results based on the last word from the input.

Let’s say I input the following three words:

Time Test True

And have the following three terms:

Bandit, Results, False.

Now, if I had the following matches (i.e. time matches to Bandit 3 times, to Results 2 times and to False 1 time):

                        Time     Test      True

Bandit              3          2          1

Results             1          1          1

False                2          1          2

Given the inputs (all at one time) – I want the results to return:

Bandit First – as it has the most matches of all words combined,

False second – as it has the second most matches of all words combined,

And so on.

However, how I have it set up – it will run through all the words – but only return the results of the last word and then order the results based on that last word only.

In this case (above) – it will run all the words – but the results it returns would be:

False – because it has the most results for the last word,

Then Bandit, then Results.

Also, if the last word (True in this case) did not return a match of any kind – I would get a LIMIT error – even if the other words matched the terms in the db.

I have tried many, many things and just seem to get nowhere.  Please help – do I add more code to the query – like if statements or do I do it outside the query?

Code is attached.

[bregent]

>However, how I have it set up

I don't really know how you have set it up so I can't comment on what you did wrong. It looks to me as if you are trying to create a crosstab. You can do that using a few sql tricks:

http://dev.mysql.com/tech-resources/articles/wizard/index.html

To be of more help you need to provide your schema, sample data, sample user input and expected output.

[Guest]

Thanks I will look into it.

[Guest]

Cross tabs or pivit tables will not work here since I do not know how mant search terms will be entered.  Would work great if I just want to pull and manipulate information from a set of known tables.  But, that is not my case.

Thanks - I will try elsewhere.

As a side note - I did set up a scheme and desired results in my write up.

[bregent]

>Cross tabs or pivit tables will not work here

>since I do not know how mant search terms will be entered.

You can dynamically build the crosstab query based on the number of search terms entered.

>As a side note - I did set up a scheme and desired results in my write up.

OK, I wasn't sure if that crosstab you included was an example of the output or just an explanation of the data distribution. It's also still not clear how the data in arranged in the table(s).

[Guest]

Thanks - but will try a new approach.