Control Search in all database tables

Applications Control
#1

Hi,

I’m trying to create a search control, just like on the helpdesk example on scriptcase. Below is the link.

http://helpdesk.scriptcase.net/menu_non_subscriber/menu_non_subscriber.php

Now, I would want to create the same thing search option, wherein when a user searches for something, the system would search that particular keywords in all tables then display it. I don’t know where to start.

Please help. I need this on my project…

Thanks in advance… Godbless

ishey

#2

Re: Control Search in all database tables

This is more of a SQL issue.

Google: search multiple tables

The keywords are JOIN/OUTER JOIN
if your tables are identical in structure that you are searching, then also have a look at UNION

You may want revisit your schema if you are searching many tables to see about creating a relational approach for this if possible.
Good DB design is key to creating a good app.

Regards,
Scott.

#3

Re: Control Search in all database tables

Hi ScottMartin,

Thanks for replying, I really appreciate it!!

Yes, it’s more of an SQL statement… I’m still working on it… THanks for the help!

ishey

#4

Re: Control Search in all database tables

Maybe you can try to use a View or Stored Procedure.

V?tor Jamil

#5

Re: Control Search in all database tables

Vitor,
Can a stored procedure be used in a Search application?

If so, do you have an example as that is what I’m needing to do.

Thanks,
Alan

#6

Re: Control Search in all database tables

Per a discussion with support …

You can:
Execute a SQL directory using sc_select(dataset,‘sql command’);
In SQL preperation, you can use ‘CALL PROCEDURE_NAME’ to execute any proc to be executed before the SQL statement is executed

Regards,
Scott.

#7

Re: Control Search in all database tables

I want the stored procedure to replace the query that’s being run, so I’m not sure if that will help or not.

In other words, the stored procedure actually returns the rows based on the parameters that I want to pass it.

#8

Re: Control Search in all database tables

So the sc_select would not work?
sc_select(dataset,“CALL MYPROC()”);

Regards,
Scott.

#9

Re: Control Search in all database tables

Let’s say I have a Grid application. It has a search on it.
To keep it simple we’ll just say that the search field is customers zipcode.
so that could be set up for sure, and would query up and show all the customers with that zipcode.

However I can only access this data via a stored procedure, I can’t query the table directly.

Is there a way to do that?

I could for sure use a stored procedure to insert, update, and delete from the customers data, but I can’t see a way where I can use a stored procedure for querying the customers data.

Maybe I’m just missing it, or not understanding.

#10

Re: Control Search in all database tables

I guess where we are crossing wires is that a SELECT command in your SP does not return data for the grid?

I have not tried using this feature, so I am making assumptions.

Regards,
Scott.

#11

Re: Control Search in all database tables

To execute a SP in mysql, you do a call, for example.

Call GetCust(‘AZ’)

might be a SP called GetCust, where you pass it a state code and it retrieves all the records for that State.

In MS SQL Server, it’s an Exec statement, but basically the same thing.

So it will retrieve the records, so it’s basically a form a select, but you can have the SP do a lot of other things for you.

It’s very common to only be able to get to tables thru SP’s. This is done for various reasons, but basically I just need to get my parameter values from my Search form, and use them to do a Call GetCust(my parms), instead of having SC just do a normal Select statement.

SC can handle using SP’s for updates, deletes, and inserts into the tables, but I think they don’t yet support queries by using SP’s. They may not see the value in doing it, I’m not sure.

If I could just intercept the complete SQL Statemetn that SC was going to execute to query my data and replace it with my own statement that would work, though I don’t know of a way to just replace the one that SC creates for me.

Does that make it clearer? If not, just let me know and I’ll see if I can explain it better.

#12

Re: Control Search in all database tables

I would like to learn more about Stored Procedure - when to use stored procedure
I would like to see an example.

When I want to use 3 tables and join them to create a set data that I can use - It takes a long time before the results are displayed .

I am an xbase (clipper) programmer - linking 3 tables together is a snap away .

What about some more details on this subject on the forum
Thanks

#13

Re: Control Search in all database tables

As far as I know you cannot retrieve data in an application using a stored procedure. Why must you use stored procedures?