Query Help/Suggestions [56 K WARNING - < 15k of images]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Query Help/Suggestions [56 K WARNING - < 15k of images]

Post by wtf »

Greets,

What would be the easiest way to achive an output like this one.
Image

From the db structured as shown here.
Image


Here's the query I have at the moment

Code: Select all

select 
        client.name, 
        client_score.start_score,
        client_score.update_score,
        client_score.target_score,
        bureau.name
from client
join client_score on( client.id = client_score.client_id_fk )
join bureau on (client_score.bureau_id_fk = bureau.id )
where
client.id = 3;
... and this is the output

Code: Select all

name     | start_score | update_score | target_score |    name
-------------+-------------+--------------+--------------+------------
 Jon Doe III |         300 |          300 |          620 | Experian
 Jon Doe III |         300 |          333 |          620 | Equifax
 Jon Doe III |         300 |          300 |          620 | Transunion
Producing something like this would be ideal. However I'm not sure if it's possible with current table structure.

Code: Select all

name     | start_score | update_score | target_score | start_score | update_score | target_score | start_score | update_score | target_score |
-------------+-------------+--------------+--------------+-------------+-------------+--------------+--------------+-----------------------------+
 Jon Doe III |         300 |          300 |          620 |         300 |          333 |          620 |         300 |          300 |          620 |

I guess, i'm looking for a query that'll require minimal hacking on the php end. I'm looking to handle the query in the view or stored procedure. DB is postgresql.

Greatly appreciated.[/img]
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Can you post the create table statements and some test data to populate them with so I can have a play?

I think a lot might be achievable by altering the order in which you join the tables.
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

GM,

Thank you for taking the time to look at this. Here's table structure

Code: Select all

CREATE TABLE bureau
(
  id                    SERIAL NOT NULL PRIMARY KEY,
  name                  VARCHAR NOT NULL
) WITHOUT OIDS;

INSERT INTO bureau VALUES( 1, 'Experian' );
INSERT INTO bureau VALUES( 2, 'Equifax' );
INSERT INTO bureau VALUES( 3, 'Transunion' );

Code: Select all

CREATE TABLE client
(
  id                    SERIAL NOT NULL PRIMARY KEY,
  entity_id_fk          INT NOT NULL REFERENCES entity( "id" ),
  agent_id_fk           INT NOT NULL REFERENCES agent("id"),
  partner_user_id_fk    INT NOT NULL REFERENCES partner_user("id"),
  author_id_fk          INT NOT NULL REFERENCES entity( "id" ),
  type_id_fk            INT NOT NULL REFERENCES client_type( "id" ),

  active                BOOLEAN NOT NULL DEFAULT 't',

  name                  VARCHAR NOT NULL,

  email                 VARCHAR NULL,
  password              VARCHAR NULL,

  street1               VARCHAR NULL,
  street2               VARCHAR NULL,
  city                  VARCHAR NULL,
  state                 CHAR(2) NULL,
  zipcode               CHAR(10) NULL,
  home_phone            CHAR(15) NULL,
  work_phone            CHAR(15) NULL,
  cell_phone            CHAR(15) NULL,
  datetime              TIMESTAMP NOT NULL DEFAULT NOW(),
  
  UNIQUE( id, entity_id_fk, email )
) WITHOUT OIDS;

INSERT INTO client VALUES (3, 68, 1, 1, 1, 1, true, 'Jon Doe III', 'jd@client.com', 'password', '123 Some Street', 'Suite 432', 'Phoenix', 'AZ', '85258     \
', '602-555-4567   ', '623-667-8989   ', '480-999-8789   ', '2006-08-31 17:12:25.188714');
INSERT INTO client VALUES (6, 70, 2, 1, 1, 1, true, 'charles', '', '', '', '', '', 'AL', '          ', '               ', '               ', '              \
 ', '2006-09-11 11:48:58.920275');
INSERT INTO client VALUES (7, 71, 2, 1, 1, 1, true, 'charles', '', '', '', '', '', 'AL', '          ', '               ', '               ', '              \
 ', '2006-09-11 11:50:11.787509');
INSERT INTO client VALUES (8, 72, 2, 1, 1, 1, true, 'James Jones', '', '', '', '', '', 'AL', '          ', '               ', '               ', '          \
     ', '2006-09-11 11:50:29.864063');
INSERT INTO client VALUES (9, 73, 2, 1, 1, 1, true, 'Steve Nash', '', '', '', '', '', 'AL', '          ', '               ', '               ', '           \
    ', '2006-09-11 11:50:35.074011');
INSERT INTO client VALUES (11, 75, 2, 3, 1, 1, true, 'Charles Barkley', '', '', '', '', '', 'AL', '          ', '               ', '               ', '     \
          ', '2006-09-11 11:51:02.103744');
INSERT INTO client VALUES (10, 74, 2, 3, 1, 1, true, 'Shaquil O\\''Neal', '', '', '', '', '', 'AL', '          ', '               ', '               ', '   \
            ', '2006-09-11 11:50:49.823933');
INSERT INTO client VALUES (4, 32, 1, 3, 1, 1, true, 'New Client', 'newclient@client.com', 'client', '123 Some Street', 'Suite 400', 'Phoenix', 'AZ', '85015 \
    ', '480-555-5555   ', '602-555-5555   ', '623-555-5555   ', '2006-09-05 08:51:02.629335');
INSERT INTO client VALUES (12, 87, 2, 1, 1, 1, true, 'Client Name', '', '', '', '', '', '-1', '          ', '               ', '               ', '         \
      ', '2006-09-18 12:23:49.57459');

and finally

Code: Select all

CREATE TABLE client_score
(
  id                    SERIAL NOT NULL PRIMARY KEY,

  client_id_fk          INT NOT NULL REFERENCES client("id"),
  bureau_id_fk          INT NOT NULL REFERENCES bureau("id"),

  start_score           CREDITSCORE NOT NULL,
  start_date            TIMESTAMP NOT NULL DEFAULT NOW(),  
  update_score          CREDITSCORE NOT NULL,
  update_date           TIMESTAMP NOT NULL DEFAULT NOW(),  
  target_score          CREDITSCORE NOT NULL

) WITHOUT OIDS;

INSERT INTO client VALUES (3, 68, 1, 1, 1, 1, true, 'Jon Doe III', 'jd@client.com', 'password', '123 Some Street', 'Suite 432', 'Phoenix', 'AZ', '85258     \
', '602-555-4567   ', '623-667-8989   ', '480-999-8789   ', '2006-08-31 17:12:25.188714');
INSERT INTO client VALUES (6, 70, 2, 1, 1, 1, true, 'charles', '', '', '', '', '', 'AL', '          ', '               ', '               ', '              \
 ', '2006-09-11 11:48:58.920275');
INSERT INTO client VALUES (7, 71, 2, 1, 1, 1, true, 'charles', '', '', '', '', '', 'AL', '          ', '               ', '               ', '              \
 ', '2006-09-11 11:50:11.787509');
INSERT INTO client VALUES (8, 72, 2, 1, 1, 1, true, 'James Jones', '', '', '', '', '', 'AL', '          ', '               ', '               ', '          \
     ', '2006-09-11 11:50:29.864063');
INSERT INTO client VALUES (9, 73, 2, 1, 1, 1, true, 'Steve Nash', '', '', '', '', '', 'AL', '          ', '               ', '               ', '           \
    ', '2006-09-11 11:50:35.074011');
INSERT INTO client VALUES (11, 75, 2, 3, 1, 1, true, 'Charles Barkley', '', '', '', '', '', 'AL', '          ', '               ', '               ', '     \
          ', '2006-09-11 11:51:02.103744');
INSERT INTO client VALUES (10, 74, 2, 3, 1, 1, true, 'Shaquil O\\''Neal', '', '', '', '', '', 'AL', '          ', '               ', '               ', '   \
            ', '2006-09-11 11:50:49.823933');
INSERT INTO client VALUES (4, 32, 1, 3, 1, 1, true, 'New Client', 'newclient@client.com', 'client', '123 Some Street', 'Suite 400', 'Phoenix', 'AZ', '85015 \
    ', '480-555-5555   ', '602-555-5555   ', '623-555-5555   ', '2006-09-05 08:51:02.629335');
INSERT INTO client VALUES (12, 87, 2, 1, 1, 1, true, 'Client Name', '', '', '', '', '', '-1', '          ', '               ', '               ', '         \
      ', '2006-09-18 12:23:49.57459');

Notice that tables bureau and client have entity_id_fk which I am removing out of current db structure therefore thos fields can be taken out.


Thanks.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Hi wtf - you've given me the data for the client table twice, instead of the data for the client_score table.

I'd make up some of my own, but I'm not sure what the hell all the fields actually mean... :)

(Also, I'm not too hot with PostgreSQL, so I've done a conversion to MySQL for now)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Assuming you manage to achieve this, what will you do if you're later asked to add another credit checking company to the database? Wouldn't it be a lot easy to maintain if you're just looping through all the scores returned with one record per company rather than dealing with a single row made up of all the scores?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

You're query seems to be working. If I were in your position, I would now transfer the result data to an array:

Code: Select all

['John Doe III']['Eperian']['start']=300
                           ['update']=300
                           ['target']=620
                ['Equifax']['start']=300
                           ['update']=300
                           ['target']=620
                ['Transunion']['start']=300
                              ['update']=300
                              ['target']=620
Then it's just a matter of looping through the array.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

Onion:

Great question. To answer it, I simply don't know. I believe it was assumed that only three major credit bureaus will be supported. I'm not even sure if there are more then three anyway.


Pickle:
Query works good. Problem is that with the results that I get, I can't format it the way you suggest.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

wtf wrote:Great question. To answer it, I simply don't know. I believe it was assumed that only three major credit bureaus will be supported. I'm not even sure if there are more then three anyway
Another one could start up tomorrow. Always plan for the unforeseen.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

wtf wrote:Problem is that with the results that I get, I can't format it the way you suggest.
If your data is returned like this:

Code: Select all

name     | start_score | update_score | target_score |    name
-------------+-------------+--------------+--------------+------------
 Jon Doe III |         300 |          300 |          620 | Experian
 Jon Doe III |         300 |          333 |          620 | Equifax
 Jon Doe III |         300 |          300 |          620 | Transunion
You can iterate through it like this:

Code: Select all

while($row = mysql_fetch_assoc($results))
{
  $array[$row['name']][$row['name']] = array('start'=>$row['start_score'],'update'=>$row['update_score'],'target'=>$row['target_score'];
}
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply