need help on table design in DB :)
Quleczka
I have to make custom reports solution in PHP + MySQL and I'm looking
for the best way to design part of my database. I will appreciate any
help.
I have to store option to display custom reports on selected table.
There has to be an option to easily modify report criteria at any
time.
For example let's say I have table called 'ExampleTable' in DB which
has 20 different columns. Just few column names for example 'ID',
'Name' , 'Desc', 'Date' , 'ValueA', 'ValueB'.
User on PHP site can choose that he wants to have report for
'ExampleTable' with selected columns: 'Name', 'Date' and 'ValueB'
sorted by ID asc and he wants only rows where 'ValueB'= 4 and Date >
'29/07/2008'. Basically almost any kind of criteria. Could be also
something like : ('ValueB'=4 OR ValueB' = 6 OR 'ValueB'>30) AND
('Date' = > '29/07/2008' OR Name = 'special' ).
Let's say everything which has form 'something AND/OR something' plus
possible parenthesis but to make it simpler only one level of
parenthesis ( not something like (A OR (B AND C) OR D) OR E ).
Has anyone good idea how to store it in database for easy editing
later on? I can store whole WHERE clause as text but then I have to
parse everything in PHP to let user modify it :/
For sure I can store conditions as 'Name' = 'special' in separate
table (probably as 3 separate fields - column/condition/value ) and
then just store condition in form '(1 OR 2) AND (3 OR 4) ' with
numbers of entries instead of basic conditions but I have no better
idea how to divide the whole query to store it more easily :/
Any suggestions?
All ideas appreciated :)
Quleczka
Philipp Post
package can be bought for this. A quick web search for "report
generator php mysql" brought up some pages. As I have not used them,
neither PHP, I can not recommend a specific one to you. Possibly a PHP
newsgroup could give more input for this.
brgds
Philipp Post
Quleczka
> Prior re-inventing the wheel it might be better to see if a commercial
> package can be bought for this.
Unfortunately I can't. It's part of bigger company intranet system and
really customised solution is necessary in this case.I have to write
it from scratch.
> Possibly a PHP newsgroup could give more input for this.
I asked here because I don't need help with PHP. I know how to write
code for this engine in PHP. I just don't have good solution for clean
db design in this case.
My question is how to store nicely in DB expressions like this '(A OR
B OR C) AND (D OR E)' for easy editing of each part later on.
For example:
-changing middle AND to OR
- changing D to G
- removing AND (D OR E) part
etc.
Any other ideas than text string and PHP parsing of it?
Regards,
Quleczka
Ed Prochak
I'm still not clear on the feature you want.
Does the user get a list of options or do they enter the search
criteria?
(Do they enter the actual query where clause? I smell possible SQL
injection.)
Ed
Quleczka
> criteria?
User have a page where he/she can select criteria but can't enter it
directly.
For example can select one of the fields from drop down list, choose
between <, <=, >, >=, = (drop down as well) and enter value to
compare. Then he can press button with AND or OR and add another
criteria and so on. There is option to add one level of ( ) as well.
I can easily generate SQL query from user selection and store in the
DB. The only problem is that I have to have option to easily populate
all the fields again and display page for user to edit criteria.
I can do this of course with parsing this string in PHP to divide it
in small parts but I hoped to find some nice idea to store it already
divided with all pieces separately.
> (Do they enter the actual query where clause? I smell possible SQL
> injection.)
Don't worry. I know how to avoid SQL injections and to make it even
safer user can't type anything else than value to compare plus it's
internal website for small number of non-IT people :)
Quleczka
Thomas Kellerer
Quleczka wrote on 28.08.2009 15:17:
> For example let's say I have table called 'ExampleTable' in DB which
> has 20 different columns. Just few column names for example 'ID',
> 'Name' , 'Desc', 'Date' , 'ValueA', 'ValueB'.
A table design with columns "ValueA", "ValueB", "ValueC", ... is most definitely wrong.
This smells like a de-normalized model.
Thomas
Quleczka
It was just example. I could write 'ColumnA', 'ColumnB' etc.
Instead of 'ValueA' can be everything - 'IsAdmin' or 'StartDate' or
'ProjectDescription' or 'ID' or 'Creator' or something else.
Just table with some columns and user can select which columns should
be visible in the report he/she is creating.
I don't want to discuss design of this table. This is table which is
existing and I can't change it.
I have to create custom reports based on this table and store criteria
for these reports in another table.
Quleczka
Quleczka
example".
It looks similar to the page I have to enter criteria but mine is even
more limited and user can't type anything except value to compare.
I just need some smart way to store this data in the DB for easy
access later on. If I don't find one I'll just write function to parse
the whole string and extract data from it.
Quleczka
Gene Wirchenko
>> A table design with columns "ValueA", "ValueB", "ValueC", ... is most definitely wrong.
>
>It was just example. I could write 'ColumnA', 'ColumnB' etc.
A difference in column names does not change whether the table
design is denormalised.
>Instead of 'ValueA' can be everything - 'IsAdmin' or 'StartDate' or
>'ProjectDescription' or 'ID' or 'Creator' or something else.
>Just table with some columns and user can select which columns should
>be visible in the report he/she is creating.
Get a report writer.
>I don't want to discuss design of this table. This is table which is
>existing and I can't change it.
Then why are you asking about table design if there is nothing
that can be done?
>I have to create custom reports based on this table and store criteria
>for these reports in another table.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
Walter Mitty
"Quleczka" <qule...@gazeta.pl> wrote in message
news:ab005391-3489-412d...@s31g2000yqs.googlegroups.com...
In general, a "smart way to store this data in the DB" implies some kind of
new table design. In another response, you indicate that you're stuck with
the existing table design.
Which is it?
> Quleczka
Quleczka
> design is denormalised.
How you can tell if table is denormalised or not if only info you have
about it is that it does contain some columns?
> >I don't want to discuss design of this table. This is table which is
> >existing and I can't change it.
>
> Then why are you asking about table design if there is nothing
> that can be done?
I don't want to discuss design of existing table which report is based
on. Basically this can be any table. Just some random table with some
columns. Also column names are not important or the fact if existing
table is denormalised or not. I can't change this existing data
structure.
I was asking about advice how to create new tables (or tables) to
store the report criteria in easy to access way.
Best regards,
Quleczka
Quleczka
> new table design. In another response, you indicate that you're stuck with
> the existing table design.
>
> Which is it?
I'm stuck with tables from which data for these custom reports comes
from. I don't want to discuss if design of these tables is
denormalised or not cause only fact which matters is that there is a
table with some columns and I have to create reports based on data
from this table.
What I I want to design is smart way is a new table (or few tables) to
store custom report criteria in it.
I hope now it's clear enough :))
Quleczka
Nilone
Take a look at http://en.wikipedia.org/wiki/Category:Normal_forms_(logic)
Philipp Post
Just some thoughts on this - you will need to define
- report names and descriptions
- filters (where clauses)
- grouping - do you need subtotals?
- sorting
- columns to display and their layout on paper (column width, font,
colour)
- page layout (header, footer, subtitles)
- export formats (pdf, xls, csv, xml and whatever else and some
special settings - e. g. charset)
If you wish to put that in SQL, most of them would have a separate
table (after you defined your exact requirements). However storing
these things is more or less some kind of text processing, where you
need to maintain the order of the lines. SQL basically is founded on
unordered sets. You could put a sequence into such tables to maintain
ordering but this requires some additional overhead and maintenance in
case you re-order or remove a row. A question would be if that all
needs to go into SQL tables at all or if you could store this in some
kind of settings file on your server.
I knew a company who has rolled their own web based report creator
similar to what you describe, but it took them years to become a
usable tool for their customers. However I have no details about how
this was technically implemented.
brgds
Philipp Post
Gene Wirchenko
>> � � �A difference in column names does not change whether the table
>> design is denormalised.
>
>How you can tell if table is denormalised or not if only info you have
>about it is that it does contain some columns?
In general, you can not. With the exception of repeating columns
within a table, denormalisation is a property of multiple tables
considered together.
>> >I don't want to discuss design of this table. This is table which is
>> >existing and I can't change it.
>>
>> � � �Then why are you asking about table design if there is nothing
>> that can be done?
>
>I don't want to discuss design of existing table which report is based
>on. Basically this can be any table. Just some random table with some
>columns. Also column names are not important or the fact if existing
>table is denormalised or not. I can't change this existing data
>structure.
>
>I was asking about advice how to create new tables (or tables) to
>store the report criteria in easy to access way.
OK. Be aware that the denormalisation may bite in some cases.