Forums

SQL Filter before display

jmack15 04 Feb, 2010
Hello all,

I was wondering if anyone could help me with this. It may be quit simple but I really can't seem to piece it together.

I wish to do a filter for certain table entries before displaying the entire lot on the front end.
So for example, I have a table called "something" with the columns id, col1 and col2.
I wish to output the contents of this table using chronoconnectivity. However, I do now wish to display entries with id numbers say..15 and 19. How or where would I enter the SQL code?

Ordinarily I would place the code in the SQL/WHERE tab under the General tab. But since I wish to execute this before outputting it to the frontend..I seem to be lost.

Thanks,
-JohnnyMack
GreyHead 04 Feb, 2010
Hi JohnnyMack,

I'm not understand the question clearly. You want to pre-filter the output but allow the user to 'undo' the prefilter??

Bob
jmack15 04 Feb, 2010
Hi GreyHead,

I appreciate your prompt reply.

I do no want to display these tuples from the table at all.

ChronoConnectivity displays every entry in the table that is selected. I wish to hide several entries.

Hope this helps explain my dilemma a bit more clearly.

Thanks,
-JohnnyMack
GreyHead 04 Feb, 2010
Hi JohnnyMack,

Ah, that makes sense to me.

You can include AND `id` NOT IN ('15, '19') to the WHERE clause.

Bob
jmack15 04 Feb, 2010
Hi GreyHead,

Thank you so much for your patience and help.

I tried as you suggested however I got the following error....


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 'NOT IN ('15','16')' at line 1 SQL=SELECT count(*) FROM jos_crimson `id` NOT IN ('15','16');


The code I entered was in the WHERE/SQL area under the General Tab is as follows.


`id` NOT IN ('64','66');


I get a similar error even if I prefix AND to the statement.

Once again, I remain much obliged to you for your help.

Thanks,
-JohnnyMack
jmack15 04 Feb, 2010
Wait a sec...I got rid of that error by adding WHERE before the statement..like

WHERE `id` NOT IN ('15','16');


However, I now get the following error and no entry is displayed.

Warning: Invalid argument supplied for foreach() in \..\components\com_chronoconnectivity\libraries\connection.php on line 300


Your help would be greatly appreciated.

Thanks
-JohnnyMack
GreyHead 04 Feb, 2010
Hi JohnnyMack,

Sorry about the missing WHERE, I'm never sure if it's needed or not.

At a guess no results are being returned. Try turning on Site Debug temporarily to grab the MySQL query generated here - look for the one with the WHERE clause in it.

Bob
jmack15 05 Feb, 2010
Hi GreyHead,

Many thanks for your kind help. I do sincerely appreciate it.

The problem was that I added a ";" after the statement hence abruptly ending the entire SQL statement.

The solution was to only add...


WHERE `id` NOT IN ('15','16')


Once again thank you so much for your help. I would never have figured it out otherwise.

Cheers,
-JohnnyMack
GreyHead 05 Feb, 2010
Hi JohnnyMack,

Good catch I wouldn't have spotted that extra ; in a hurry!

Bob
This topic is locked and no more replies can be posted.