Challenge

Poofman

I have a challenge for anyone that's good at PHP and Databases. The program uses formula's of some kind, maybe offsets, not sure, to store the info in my database. The problem I have is I need to create reports from this info, but cannot pull the info directly from the database, because let's say a number is supposed to be 150, and when I run the program that created the database it shows as 150. But the actual value in the database is 110. I need to find a way to pull this info out into it's true number so I can create my reports.

If you are up for the task, or have alot of time on your hands, email me. poofman@squadinferno.com

fahrvergnuugen

use whatever formula that is used to store the value to extract it as well.

lets say it was created using:

insert into table TABLE_NAME (field) values (150 - 40);

use

select field + 40 as field from TABLE_NAME

to retrieve it

Sxooter

Simple, just create a view that does the math for you:

create view super as select field11.2 as field1, field22.3 as field2 from tablename;

Poofman

2 problems here. The reason I posted here is because I've spent a couple weeks on this, and can't figure it out. So your answers are greek to me. Secondly, if I could figure out what is used to store the info, I could have figured out how to retrieve it.

fahrvergnuugen

Originally posted by Sxooter
Simple, just create a view that does the math for you:

create view super as select field11.2 as field1, field22.3 as field2 from tablename;

create view is not a standard sql command. sounds like some sort of proprietary microsoft jargon.

poofman, good luck finding someone who can figure it out for you

Sxooter

Originally posted by fahrvergnuugen
create view is not a standard sql command. sounds like some sort of proprietary microsoft jargon.

Horse puckies.

It's SQL-92.

Here's the chapter on it:

11.19 <view definition>

     11.19  <view definition>

     Function

     Define a viewed table.

     Format

     <view definition> ::=
          CREATE VIEW <table name> [ <left paren> <view column list>
          <right paren> ]
            AS <query expression>
            [ WITH [ <levels clause> ] CHECK OPTION ]

     <levels clause> ::=
          CASCADED | LOCAL

     <view column list> ::= <column name list>


     Syntax Rules

     1) The <query expression> shall not contain a <target specifica-
        tion> or a <dynamic parameter specification>.

     2) If a <view definition> is contained in a <schema definition>
        and the <table name> contains a <schema name>, then that <schema
        name> shall be the same as the specified or implicit <schema
        name> of the containing <schema definition>.

     3) The schema identified by the explicit or implicit schema name
        of the <table name> shall not include a table descriptor whose
        table name is <table name>.

     4) The viewed table defined by <view definition> shall not be
        identified by any <table reference> generally contained in the
        <query expression>.

     5) Any <table name> that is specified in the <query expression>
        shall be different from the <table name> of any <temporary table
        declaration>.

     6) If the <query expression> is updatable, then the viewed table is
        an updatable table. Otherwise, it is a read-only table.

     7) If the <query expression> is a <query specification> that con-
        tains a <group by clause> or a <having clause> that is not con-
        tained in a <subquery>, then the viewed table defined by the
        <view definition> is a grouped view.

     8) If any two columns in the table specified by the <query ex-
        pression> have the same <column name>, or if any column of that
        table has an implementation-dependent name, then a <view column
        list> shall be specified.

     296  Database Language SQL







                                                X3H2-92-154/DBL CBR-002
                                                 11.19 <view definition>


     9) The same <column name> shall not be specified more than once in
        the <view column list>.

     10)The number of <column name>s in the <view column list> shall
        be the same as the degree of the table specified by the <query
        expression>.

     11)No column in the table specified by <query expression> shall
        have a coercibility attribute of No collating sequence.

        Note: The coercibility attribute is described in Subclause 4.2.3,
        "Rules determining collating sequence usage".

        Note: The coercibility attribute for references to the column is
        defined in Subclause 6.4, "<column reference>".

     12)If WITH CHECK OPTION is specified, then the viewed table shall
        be updatable.

     13)If WITH CHECK OPTION is specified with no <levels clause>, then
        a <levels clause> of CASCADED is implicit.

     14)Let V be the view defined by the <view definition>. The un-
        derlying columns of every i-th column of V are the underlying
        columns of the i-th column of the <query expression> and the
        underlying columns of V are the underlying columns of the <query
        expression>.

Maybe you should pick a database that doesn't suck so hard next time. 😉

You'll note the original poster didn't mention using an inferior product and having to work around it. In fact, he stated the challenge was to people who are "good with PHP and databases."

If he needs a "mysql" answer he should mention he's using it. If he needs a "database" answer, he got one, and one that works on just about any database currently available:

db2, oracle, postgresql, firebird, mssql, etc. etc.

Sxooter

Originally posted by Poofman
2 problems here. The reason I posted here is because I've spent a couple weeks on this, and can't figure it out. So your answers are greek to me. Secondly, if I could figure out what is used to store the info, I could have figured out how to retrieve it.

Look, the problem here is that you've got some code between the data in the database and the output the user sees. You're gonna either have to post the portions doing the conversion and see if we can help, or figure it out on your own.

ONCE you've done that, formalize it in views (assuming you aren't using a toy database server) or write the code more cleanly, or store the correct values in the database.

Poofman

Ok, I agree that the code is needed here, but there are lots of code, so gonna give you the site that the program is on, if you download it and look at the install.php, you'll see how database is created. If from there you can find the formula to convert the data back using any type of routine, that's what I'm looking for. And once you show me how you found 1 of the database items, figured out how to convert it back, I'm willing to take the time to go through and do all the rest. Just point me in right direction, like where the formula's are and how to interpret them.

http://www.ai-hq.com/babstats/dloads.php?action=BAB.stats%20Chronos

download the server build.

Ive already taken the pains to learn how to pull the text strings back using base64, thought I was on right direction for the non-text fields too, but they don't convert right using base64, at least on what I've tried.

My ultimate end and goal are to figure out how to access the data, so I can set up a page with top 5 players in each stat, for example. I know how to read through the database fields and sort them, but need the right data for the sort to work right. Etc.

If you want to see the program running live, go to my site, and click on stats on top.