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

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-

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]

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

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]