SQL Query

Anyone have suggestions for either an SQL Query package or one that can build HTML based reports?

Thanks,
Robert


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

The universal standard for this is phpMySQL, which is usually
installed on any Apache/PHP/MySQL Web server anywhere. It’s a general-
purpose visual console, not a simplified report writer or client-
friendly tool at all, though. Anything you can dream up, you can do
within it. But you have to know and understand SQL to get anything of
even minor complexity out of it. For the basics, like listing all the
rows in a table, it does a fine job, and can even make PDF or HTML
reports based on your query, just by pointing and clicking.

Walter

On Jun 25, 2011, at 11:14 AM, Robert wrote:

Anyone have suggestions for either an SQL Query package or one that
can build HTML based reports?

Thanks,
Robert


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

As with other topics on which I’m not fully versed, I don’t think I’ve been entirely clear as I haven’t mastered the terminology.

What I believe I’m really looking for is a program that will allow me to put together some HTML based ‘views’ of a MySQL database. This would allow my client to simply check things in their browser rather than running a separate program (or having to fire up phpMySQL).

Robert


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

At 09:44 -0400 26/6/11, Robert wrote:

As with other topics on which I’m not fully versed, I don’t think
I’ve been entirely clear as I haven’t mastered the terminology.

What I believe I’m really looking for is a program that will allow
me to put together some HTML based ‘views’ of a MySQL database. This
would allow my client to simply check things in their browser rather
than running a separate program (or having to fire up phpMySQL).

phpMyAdmin (phpMySQL ?) is a thing that ‘simply check things in the
browser’. It’s general purpose. You can have something that’s easier
to use, but it will need to be customised for your particular
database. I have done just that for a group of tables I work with,
but it’s the fact that it’s customised that makes it easier than
phpMyAdmin. My script is written to be driven by the table structure
and could be useful for others with the same complexity of database
table, but it would be very easy to have a table that it would fail
with.

David


David Ledger - Freelance Unix Sysadmin in the UK.
HP-UX specialist of hpUG technical user group (www.hpug.org.uk)
email@hidden
www.ivdcs.co.uk


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

That’s fine. If you have the structure of the table somewhere (it’s
often referred to as a SQL schema) you might be able to build such a
tool fairly easily. What method do you currently use to look at the
database, and what did you use to build it and populate it with data?
Let’s start there, and see if we can figure out how to get where you
need to go.

Walter

On Jun 26, 2011, at 9:44 AM, Robert wrote:

As with other topics on which I’m not fully versed, I don’t think
I’ve been entirely clear as I haven’t mastered the terminology.


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

I should be able to dig up a SQL schema (will know more tomorrow). Currently using phpMyAdmin to view the database. Not sure what they used to originally build the system.

I know I’ll need to learn (and brush up on) some PHP/MySQL coding to pull all this off. I was hoping there might be a package or tool to accomplish “simple” browser views of the database. Would PHP Actions Suite do the trick? Are there any tutorials for this action?

Robert


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Those Actions are pretty old. You may be able to find something from
when they were written, but it’s not likely that they would help you
very much. Seeing the schema should help with advice about what to do
next. Generally, viewing a single table is pretty easy. But viewing a
single table only very rarely is worth anything at all, because a
properly designed database will relate one table to another, and only
by looking at multiple tables in combination will you have anything
useful and human-readable. Otherwise, you tend to have mostly foreign
key records, so then rather than knowing what ‘category’ something is,
you end up knowing that it’s ‘categories_id’ is 23.

Walter

On Jun 26, 2011, at 3:30 PM, Robert wrote:

I should be able to dig up a SQL schema (will know more tomorrow).
Currently using phpMyAdmin to view the database. Not sure what they
used to originally build the system.

I know I’ll need to learn (and brush up on) some PHP/MySQL coding to
pull all this off. I was hoping there might be a package or tool to
accomplish “simple” browser views of the database. Would PHP Actions
Suite do the trick? Are there any tutorials for this action?

Robert


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

You might want to have a look at phpMyEdit http://www.phpMyEdit.org

With this you can easily build a webpage to interrogate/update an existing MySQL database.

David


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Walter — I have the SQL schema (on all the tables). I should be able to figure out the table relationships in short order. Still not sure how the original system was built. Suggestions on next steps / tools to use to interrogate the database?

Dave — “easily build” is a relative phrase : ) I’m sure once I get into phpMyEdit, some things will start clicking.

Thanks,
Robert


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Exactly how it was built isn’t important. I think we’ve established
that you inherited it, rather than building it yourself. So the next
step I would take would be to use phpMyAdmin (which should be on your
hosting server, accessible from whatever passes for a control panel
there) to browse the layout of the table you’re interested in.

Things to look for:

  1. What field (column) is set to be an auto-incrementing key, also
    known as a Primary Key. Any table you want to list must have one of
    these. There are special classes of tables without them, but they are
    rare.

  2. Are there any fields in the table that seem to refer to another
    table? You can spot these because they will have a name that seems
    like it means something, but the content of that column in any row
    will be a simple integer. This is known as a foreign key, and it means
    that your database has been “normalized” so that repetitive data is
    only ever entered once.

Sidebar: If your database reflects current thinking in database
design, you will have a separate table for each class of thing that
you might want to track. For example, you might have a ‘clients’ table
and an ‘orders’ table. Clients stores a one row-per-client model of
each of your clients. Each client in turn may have multiple orders,
both current and complete. So looking in the orders table, you would
see a reference to which client ordered this order, by convention
named clients_id, and you would have a date that the order was started
on, probably called created_on or created_at. What this does is make
sure that you don’t have to have all the same rows, in duplicate (and
potentially in error) referring to the client name, address, etc.
stored in the same table as the orders. Your clients table holds that,
and your orders table is smaller and less likely to suffer from
errors. (The client moved since this order was made, but if you need
to send mail related to the old order, you still want it to go where
the client lives now.)

Walter

On Jun 27, 2011, at 4:49 PM, Robert wrote:

Still not sure how the original system was built. Suggestions on
next steps / tools to use to interrogate the database?


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Walter,

I’ve located all of the Primary and Foreign keys in the tables of interest. The design does seem to reflect “current thinking” as you describe it.

Next step?

Robert

On 27 Jun 2011, 10:17 pm, waltd wrote:

So the next step I would take would be to use phpMyAdmin to browse the layout of the table you’re interested in.


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

You’re going to need to choose a toolkit to build the report table. If
you look at the system that David mentioned, that might be the
quickest (least code) way to go, because you will need to put in your
database username and password and the name of the table you want to
list, press a button, and that kit will generate a blob of code that
knows how to list the rows in the table. I haven’t used it myself, but
it seems like a fairly mature system and it’s likely to get you pretty
far done.

Another option would be to use my Generate script (there’s a
screencast for it at http://freewaycasts.com to create a complete
“CRUD” (Create Read Update Delete) interface for the tables. That
system understands the Rails/MAR/CakePHP naming conventions, so if it
encounters a column named orders_id and knows that there’s an orders
table in the database, it will create the related lookup for you
automatically. Watch the screencast and see if it will fit your needs.

Walter

On Jun 27, 2011, at 6:44 PM, Robert wrote:

Walter,

I’ve located all of the Primary and Foreign keys in the tables of
interest. The design does seem to reflect “current thinking” as you
describe it.

Next step?

Robert

On 27 Jun 2011, 10:17 pm, waltd wrote:

So the next step I would take would be to use phpMyAdmin to browse
the layout of the table you’re interested in.


offtopic mailing list
email@hidden
Update your subscriptions at:
List Options | FreewayTalk


offtopic mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options