I need help with very complex query

hi all,

I hope it is easier for you to answer than for me trying to explain
it...

In a database I have some tables , each one has some mandatory fields
at the beginning and a couple at the end.
In the middle each table can have some additional fields from 0 to n
depending on how many fields have been inserted by who created the
table.

Now, I need to set up a script which ,after receiving from a form the
table name, can print the first known fields,and all the additional
ones , but I don' t want it to show the last 2 columns of the table
because they store sensitive or useless contents.


any tips ?

TIA

johnny

re: I need help with very complex query

johnny wrote:[color=blue]
> hi all,
>
> I hope it is easier for you to answer than for me trying to explain
> it...
>
> In a database I have some tables , each one has some mandatory fields
> at the beginning and a couple at the end.
> In the middle each table can have some additional fields from 0 to n
> depending on how many fields have been inserted by who created the
> table.
>
> Now, I need to set up a script which ,after receiving from a form the
> table name, can print the first known fields,and all the additional
> ones , but I don' t want it to show the last 2 columns of the table
> because they store sensitive or useless contents.
>
>
> any tips ?
>
> TIA
>
> johnny
>[/color]
Sounds like you have a database design problem more than a query
problem. With the limited details you have provided, I would suggest
something like the following:

1. have a table that contains the mandatory fields and the private ones.
Let's call this one table 'A'.
2. make sure that each row in the table has a unique id (in mysql, look
at the auto_increment feature)
3. create a second table, 'B', that will contain the arbitrary data.
Each row in B will contain the argument value and the id from the row in
table A that is relates to. So, you have 0 to n rows in B referencing
the unique id of a row in A.

This lets you have an arbitrary number of fields in B for each entry in
A. Also, if you name the columns in A in your SQL query instead of
using '*', you can have SQL ignore the private fields on the end.

If this helps, I would suggest you spend a little time looking at
database design and the capabilities of the SQL language.

-david-

re: I need help with very complex query

David Haynes wrote:

hi David, thanks you made me realize that I already had a way to do
that simpler.

Ok , just to let you know, In my spare time I am making a sort of CRM
system.

So I have a script to create a contact list as a new table, the table
can have just an email field but also others such as name, address and
so on.

Anyway, when I create a new table I also add its name and a description
into another table which lists all the contact lists , so I could just
have to add a field to this "list of lists" and insert into that column
a comma separated string of all the fields of the new table .

Then , of course, I can print the fields I want to see the records,
delete, update one of them and so on...

It sound quite simple this way, isn't it ?

johnny

[color=blue]
> johnny wrote:[color=green]
> > hi all,
> >
> > I hope it is easier for you to answer than for me trying to explain
> > it...
> >
> > In a database I have some tables , each one has some mandatory fields
> > at the beginning and a couple at the end.
> > In the middle each table can have some additional fields from 0 to n
> > depending on how many fields have been inserted by who created the
> > table.
> >
> > Now, I need to set up a script which ,after receiving from a form the
> > table name, can print the first known fields,and all the additional
> > ones , but I don' t want it to show the last 2 columns of the table
> > because they store sensitive or useless contents.
> >
> >
> > any tips ?
> >
> > TIA
> >
> > johnny
> >[/color]
> Sounds like you have a database design problem more than a query
> problem. With the limited details you have provided, I would suggest
> something like the following:
>
> 1. have a table that contains the mandatory fields and the private ones.
> Let's call this one table 'A'.
> 2. make sure that each row in the table has a unique id (in mysql, look
> at the auto_increment feature)
> 3. create a second table, 'B', that will contain the arbitrary data.
> Each row in B will contain the argument value and the id from the row in
> table A that is relates to. So, you have 0 to n rows in B referencing
> the unique id of a row in A.
>
> This lets you have an arbitrary number of fields in B for each entry in
> A. Also, if you name the columns in A in your SQL query instead of
> using '*', you can have SQL ignore the private fields on the end.
>
> If this helps, I would suggest you spend a little time looking at
> database design and the capabilities of the SQL language.
>
> -david-[/color]

re: I need help with very complex query

johnny wrote:[color=blue]
>
> I need to set up a script which ,after receiving from a form the
> table name, can print the first known fields,and all the additional
> ones , but I don' t want it to show the last 2 columns of the table
> because they store sensitive or useless contents.[/color]

You can get a list of fields in a table by running a query like this:

SHOW FIELDS FROM [table_name];

or

SHOW COLUMNS FROM [table_name];

Then you can write all names into an array and simply skip the last two
when outputting the list into the browser...

Cheers,
NC

re: I need help with very complex query

Keep in mind, that these column and DB-structure operations are usualy
DB-specific, commands working in one DB-platform will not work in other
DB-platform.
Marek

NC wrote:[color=blue]
> johnny wrote:
>[color=green]
>>I need to set up a script which ,after receiving from a form the
>>table name, can print the first known fields,and all the additional
>>ones , but I don' t want it to show the last 2 columns of the table
>>because they store sensitive or useless contents.[/color]
>
>
> You can get a list of fields in a table by running a query like this:
>
> SHOW FIELDS FROM [table_name];
>
> or
>
> SHOW COLUMNS FROM [table_name];
>
> Then you can write all names into an array and simply skip the last two
> when outputting the list into the browser...
>
> Cheers,
> NC
>[/color]