homepage Welcome to WebmasterWorld Guest from 54.197.215.146
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
URGENT help with SQL statements
Join tables and repeat region
maxitee




msg:4369173
 3:41 pm on Sep 30, 2011 (gmt 0)

This probably should be the most impossible task to request from anyone on this website.. Any help will be appreciated..

See, i have two tables.... AGENT and USERS

Table AGENT:
id,agency (ID is the unique key, table contains more datas including name,address and so on)


Table USERS
id,agency (ID is the unique key, table contains more datas including name,address and so on)

What i want to do is i want to achieve an admin panel where i can view the following..

NORMAL HTML TABLE ===
name of agency| Number of clients |
result1 total
result2 total
result3 total (and so on)


The thing is... Each users(clients) has a field called AGENCY whose values are same with the referring AGENT's agency..

I was hoping to get an SQL statement to satisfy my need and how to use PHP's repeat region to populate the results.... I use Macromedia Dreamweaver and i am more of a google aided programmer(ROOKIE)

Any help at all will do..

 

maxitee




msg:4369174
 3:46 pm on Sep 30, 2011 (gmt 0)

HTML TABLE.............
Name of agency | Number of clients
result 1 total 1
result 2 total 2
result 3 total 3


It appears my first post did not allign clearly....
Please somebody help or give an idea on how to do this.. If you need more explanations kindly let me know..

Demaestro




msg:4369177
 4:09 pm on Sep 30, 2011 (gmt 0)

Hi Max,

You may want to do this with an SQL statement, then a loop with another SQL statement in it.

Something like this:

$agency_sql = 'select agent_id, agent_name from agency_table';
$agency_data = execute_sql($agency_sql);

for($i=0; $i<count($agency_data); $i=$i+1) {

$row = $agency_data[$i];

client_sql = 'select count(client_id) as client_count from client_table where agent_id = ' . $row['agent_id'];

client_data = execute_sql($agency_sql);

echo $row['agent_name'] . ' ' . client_data[0]['client_count'] . '<br /'>;

}

maxitee




msg:4369180
 4:28 pm on Sep 30, 2011 (gmt 0)

@DEMAESTRO...
Thanks for your prompt response.. i really appreciate...

BUT please could you make it more neater... Like look into it well and see where some $ signs are missing.. I am sure there ought to be $client_sql instead of the client_sql as posted... Perhaps there are some others missing which i am not familiar with..

Please i can see great sense in your technique.. Though it hasn't worked due to "Parse error: parse error, unexpected '[',"
Thanks.

maxitee




msg:4369191
 4:46 pm on Sep 30, 2011 (gmt 0)

JUST INCASE YOU REQUIRE MORE EXPLANATION... what i want to achieve is something like this..
Say,
You have a database with 2 tables.. Say table AGENTS stores data of John Doe(name) 123 advertisers(Agency) and so on

While, table USERS stores data of Mary Doe(name) 123 advertisers(Agency)

Mary Doe is a customer that was refered by 123 advertisers... The HTML TABLE i'm trying to achieve does not even care about John Doe's name.. All it cares about is his 123 advertisers company name..

So that my HTML table looks something like

NAME OF ADVERT COMPANY..................... NUMBER OF CLIENTS
123 ltd. ........................................ 32
abc ltd. ........................................ .. 12
xyz ltd. ........................................... 14


NOTE( total number of advert company is NOT static.. we continually get new entries from companies as well.)


The above is the basic concept.... Though i am also interested in knowing number of Clients with pets or with kids from each respective ADVERT COMPANY.... If someone can however help me with the basic concept above, i am sure i will be able to use it to obtain more data.

Demaestro




msg:4369331
 10:22 pm on Sep 30, 2011 (gmt 0)

Hi Max,

My code is more of a guide than a working solution. You will have to hammer it a bit to get it doing what you need.

for example sql_execute isn't going to work you need to replace it with your DB connection method. My code assumes that a list of rows is being returned.

You are correct about the missing $ as well.

Do a little massaging of the code and post back with what you have and any errors you can't get passed. I am more than happy to help you through this but I'm not willing to do all the heavy lifting myself.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved