Hi,

Sorry if I haven't explained this problem very well, but any help/suggestions would be great

If you need to get data from different tables and perform two separate count queries of records from another table is it possible to do it all in one query or do multiple queries need to be used?

I have one query that gets data from two tables (and the mapping table), then I get the results using a while loop which also prints out the table, within this while loop (and in the table) I want to output the total number of records in a spearate table that is not used in the orignial query that is used to output the results in the while loop (but it is joined by a mapping table:

1) Should a separate query be used?
2) Should it be used within the while loop?

Thanks,

Select Count(*) From Tables Where Expression

You would run one of those queries for every count you need.

Why are you using a loop?

Learning Newbie, I don't think I explained it correctly so I will try and explain it better. I have the following tables:

PHP Code:

tbl action
id
actionId
description
 
tbl event
id
eventId
description
 
tbl eventAction
id
eventId
actionId
 
tbl people
 
tbl organisation
 
tbl organisationPeople 


I currently have a query that gets events:

PHP Code:

$eventIds =& $dbcrm->query("SELECT * FROM events E, people P, peopleEvent PE, organisationPeople OP WHERE P.personId = PE.personId AND E.eventId = PE.eventId AND P.personId = OP.personId AND OP.organisationId = '$_GET[id]' ORDER BY E.date DESC");
checkDBError($eventIds);
 
while { // keep getting rows until there are no more rows to get
} 


The while loop is beling used to get all of the data and print out event records for people stored in the people table.

(An event can have multiple corresponding actions)

In the results printed out from the while loop I also want to include the number of actions that correspond to that event record: which means I need to use a COUNT query so I can display the number of actions for the event which means I need to query the eventAction table as well.

Therefore, my question is (sorry for the long post) since I am using a query already (with while loop) to get event records, where do I put the count query? Inside the while loop? for example:

QUERY1 (get events from tbl event, tbl people and tbl peopleEvent where organisation = $_GET[id])

while { print out records
I also want to display the number of actions that correspond to these events that have been retrieved from QUERY1
}

Where do I put the query (QUERY2) that COUNTS the number of actions for the events listed in the above while loop? The tables required for the COUNT query are tbl event, tbl action and tbl eventAction.

Thanks,

You should (or would benefit to) use a join in your query rather than a while loop. PHP is extremely slow. When you run a bit of code, there's a part of the web server called an interpreter that parses your text code, and runs it. Even MySQL is compiled, which means the code is already parsed and translated into machine language. Then there are massive inefficiencies wherever there's interprocess communication, so a round trip to the database is a hugely expensive thing. It can work fine when you have 20 rows in your table, and start timing out at a few hundred.

From what I can tell, the way you're using PHP to run a while loop over results coming out of the database, is reinventing the wheel. The database is already highly specialized at what you'd like to do. It's not only faster, but much easier.

Select A.ActionID, E.EventID, Count(*) From Action A Join ActionEvent AE On A.ActionID = AE.ActionID Join Event E On AE.EventID = E.EventID Group By A.ActionID, E.EventID

That will give you only the counts, and the IDs they correspond to. You could instead ask for all the data. This query will parse, execute, and stream the results back faster than the first 2 or 3 times a loop would run with a similar Select Count(*) Where X = ? query nested inside. It will put a substantially lower burden on your server. And it won't force you to write looping constructs, at least here.

If you want to get all the information from one of the tables, instead of just a count, use a sort instead of a grouping. That will keep all the records together that belong in a group, but without collapsing them down to an aggregate like a sum, average, or count.

Excellent comments once again Learning Newbie, thanks for your help. I changed the query and was able to use it to get the number of actions for specifc events based on different criteria.

I was wondering, if you want to get three different counts from the same table would you include it in one query rather than use three separate queries?

For example,

Where complete = 1, 2 and 3 would you need three separate queries to get the COUNT for each of them?

PHP Code:

$actionNum =& $dbcrm->getOne("Select COUNT(*) From eventAction EA Join action A On EA.actionId = A.actionId Join events E On EA.eventId = E.eventId WHERE EA.eventId = ".$eventId['eventId']." AND A.complete = '1' Group By E.eventId"); 


If I wanted the # of rows in a single table that have the given values in a particular field, I'd write a query like this

Select Complete, Count(*) As NumberOfRecords
From Table
Where Complete In (1, 2, 3)
Group By Complete

Which will return

Complete               NumberOfRecords
1                          132
2                          891
3                          17

Then, my application (be it PHP, ASP, ASP.NET, etc) has all the information it needs, but it's only sent 1 query to the database, and it's only had to wait on the results of 1 query instead of 3. That makes it run faster, it lets other things going on on the server run faster, because the database is less tied up. And most of all, it makes it easier for you to write.

That's a much more efficient way of getting the COUNT than using two separate queries. Is there any way to assign the totals for each (1,2,3) to a field name that you can then use?

for example, something like this:

PHP Code:

$query = "SELECT complete, COUNT(*) AS numRecords1, numRecords2, numRecords3 FROM table
WHERE complete IN (1, 2, 3)
GROUP BY complete"
 
mysql_fetch_array etc
 
echo $query['numRecords1'];
echo $query['numRecords2'];
echo $query['numRecords3']; 


Possibly with a pivot, but I'd be shocked if MySQL supports those. I suppose you could possibly write out the results of the query above into a temp table and then use a series of sub queries to format all the results onto the same row. I think getting the right value into the right column, predictably and reliably, will be a lot more difficult than walking 3 rows in the result set, tho. Especially when they're so well correlated - here's the ID value, here's the count that corresponds to it.

I think you mentioned referencial integrity in a different post in answer to a question I asked about how does information get updated in the mapping table to match corresponding records. Am I right in thinking that because foreign keys are defined in the mapping table they are automatically updated when data is inserted into the tables? For some reason I'm finding it difficult to understand this lol

If I have a three tables called organisation, people and organisationPeople, and there is already existing data about organisations stored in the organisations table and I want to insert new people into the people table and each person belongs to an organisation.

Is this correct: There is no personId field in the organisation table and no organisationId in the peope table because these are stored as foreign keys in the mapping table?

If I insert a new person into the people table how do I match that record with an organisation? Because to match it up you need the personId and if that's auto increment in the people table how do you insert the peopleId and the organisationId in the mapping table to show that person record corresponds to that organisation?

Am I right in thinking that because foreign keys are defined in the mapping table they are automatically updated when data is inserted into the tables?

It depends... It depends upon you say that you want to CASCADE or RESTRICT your updates.

You can specify to the DB that you want to cascade the updates.
In that case, if you update a field that is used as a foreign key, the depending field would be updated too.
Everything would be done in a transaction, meaning that if one of the sub-tables update fails, everything is rollbacked to the original state.

On contrario, if you don't cascade updates, the update will fail, because the sub-table integrity would not be kept after the update.

http://www.oreillynet.com/onlamp/blo...t_foreign.html
http://www.gunduz.org/seminer/pg/fkey.php
No, the update will be refused, because it would

Ok, so in terms of the tables I have:

PHP Code:

tbl organisation
organisationId
name
 
tbl organisationPeople
id
personId (FK)
organisationId (FK)
 
tbl people
personId
firstname
surname 


When I insert a new person into the people table, where do I specify what organisation they belong to? I can't insert it into the mapping table and if I inserted it into the people table there would be no point in having the organisationId in the mapping table? Unless I do include an organisationId field in the people table and make both of the FK's in the mapping table the foreign keys for the people table?

It depends on what constraints you want to enforce.
If you want every users to be incorporated into an organisation, then yes, I would move the organisation into the peoples table, like that (in postgresql):

create table organistations(
  organisation_id serial,
  name varchar(200) not null,
  contraint primary key (organistation_id)
);

create table peoples(
  people_id serial,
  firstname varchar(50) not null,
  surname  varchar(50) not null,
  lastname varchar(50) not null,
  organisation_fk integer not null references organisations(organisation_id),
  constraint primary key (people_id)
);

This will ensure that any user you create is attached to an existing organisation.
But it limits you on a 1 to 1 relation between a user and an organisation (meaning that a user MUST be part of an organisation).

If you want a person not to be referenced to an organisation, either drop the foreign key, or use a reference table:

create table organistations(
  organisation_id serial,
  name varchar(200) not null,
  contraint primary key (organistation_id)
);

create table peoples(
  people_id serial,
  firstname varchar(50) not null,
  surname  varchar(50) not null,
  lastname varchar(50) not null,
  constraint primary key (people_id)
);

create table organisations_peoples(
  people_fk integer not null references peoples(people_id),
  organisation_fk integer not null references organisations(organisation_id),
  constraint primary key (people_fk, organisation_fk)
);

That way, you can have a user not being in an organisation, but you can keep a foreign key constraint for those who are.
Note that the reference table have a multiple PK. This means that you still have a 1 to 1 relation between the peoples and the organisations.
Change it to

constraint primary key (people_fk)

To have a 1 to many relation between a user and organisations. That way, you could have several rows in the reference table who are linking 1 user to several organisations.

Thanks for taking the time to explain it in detail I think I will use a mapping table and not include organisationId in the people table.

But how do you macth the records in the mapping table when a new person is inserted?

for example,

if you insert a new person (when user has entered details into a form and clicked submit)

the person data will be inserted, but where in that insert query would you include the organisationId for the organisation? You can't insert it into the mapping table at the same time because the personId would not have been generated. The only way I can see how to do it is to use two queries, one for inserting the person into the people table and then a different query to get the newly inserted person from the people table and insert the personId and organisationId into the mapping table (organisationPeople)

Am I missing something here?

the person data will be inserted, but where in that insert query would you include the organisationId for the organisation?

You take it the wrong direction...

The foreign key is on the reference table.
That means that it's the datas in the reference table that must validate against peoples table. Not the other way.
You can insert as many peoples as you want, without linking them to any organisations, but you cannot declare a link between a non existing user and an organisation, nor between an existing user and a non existing organisation.

Like you said, you need 2 queries. Or, if you want to automate it, you need either to put a "on insert" trigger on the people table or to use a stored procedure that will take care of executing the 2 statements.

Stretching a bit what may come next, if you have a user in both table, the select is quite simple:

select *
from peoples p, organisations_peoples op
where p.people_id = op.people_fk;

You can write it that way too, I find it more easily readable:

select *
from peoples
inner join organisations_peoples op on p.people_id = op.people_fk;

That type of relation between the 2 tables is called an inner join. Only rows present in both table will be returned.

But, this won't give you peoples that are not associated to any organisations.
To do so, you have to use an outer join.
The outer join define a "base" table, whose every rows must be returned, and an "optionnal" query who will maybe return something...
You write it:

select * 
from peoples p
left outer join organisations_peoples op on p.people_id = op.people_fk;

The "left" outer join signifie that the table on the left of the query (or the previously declared, if you prefer) is the one to be taken as a base.
Note that when you have several joins, you cannot have an inner join after an outer join.
You must organize your query to have every inner joins before every outer joins, like this:

SELECT 
        ab.idsArtBasisID
        ,n.strLangerName
        ,tbm.intSum AS numBestellteMenge
        ,lagb.numBestandMenge
        ,ts.strStatus AS strBestellStatus
        ,ts.blnNichtBereit AS blnNichtBereitZuSenden
        ,talp.numAusloesMenge
        ,talp.numBestellMenge
        ,tvk.numVK
    FROM csaarl
        INNER JOIN dbo.funArtBasis(@intMdtUID) ab ON ab.idsArtBasisID = csaarl.intID
        INNER JOIN dbo.tblArtName n ON n.idsArtNameID = csaarl.intSubID
        INNER JOIN @tmpVK AS tvk ON tvk.idsArtBasisID = csaarl.intID
        LEFT OUTER JOIN dbo.fwtblArtLiefPrio(@intMdtUid) AS talp ON talp.intArtBasisFK = csaarl.intID
            AND talp.intBestellPrio=1
            AND talp.strLiefPrioTypCode='AV'
        LEFT OUTER JOIN dbo.fwtblLagBestand(@intMdtUID) lagb ON lagb.intArtBasisFK = csaarl.intID
        LEFT OUTER JOIN @tmptBestellteMenge tbm ON tbm.idsArtBasisID = ab.idsArtBasisID
        LEFT OUTER JOIN @tmpStatus ts ON ts.idsArtBasisID = ab.idsArtBasisID
    WHERE 1=1 
        AND csaarl.blnIsActive = 1
    ORDER BY csaarl.intRowNumber

This is a query I crafted this afternoon. It calls many user defined functions and stored procedures too, but you got the picture...

Originally Posted by drew22299

if you insert a new person (when user has entered details into a form and clicked submit)

the person data will be inserted, but where in that insert query would you include the organisationId for the organisation? You can't insert it into the mapping table at the same time because the personId would not have been generated. The only way I can see how to do it is to use two queries, one for inserting the person into the people table and then a different query to get the newly inserted person from the people table and insert the personId and organisationId into the mapping table (organisationPeople)

Am I missing something here?

You want to add a new person. Are they part of an organization? They don't need to be - you can insert just a person with no relationship. But if you do want to add the person as a member of an organization, you can do that, too. You just need to use 2+ queries, like you said.

First, you insert the person into your Person table. When you do this, the database generates a PersonID value. And, like you've noticed, you need to know what this number is so you can add it to your mapping table.

So, you can either use a function like @@Identity, or Scope_Identity() if you might possibly have triggers at some point. This returns the ID value that was generated for your new person. Or, you could get it by querying the table, but this method is faster.

Declare @idValue int
Insert Person Values ( blah blah blah )
Select @idValue = @@Identity
Insert PersonOrganization Values ( @idValue, @OrganizationID )

I found your tutorial on joins very useful tripy, because there may be people who don't belong to an organisation which I hadn't thought about making a query for

Learning newbie, I thought using 2 or more queries was a no go because it would slow the response times down but I have used three queries and it still exectutes the page quickly and inserts the person data into both the people and organisationPeople tables

Here are the three queries I am using, they might not be as efficient as you experts would make your queries but at least it gets the job done. The first query INSERTS the person data entered by the user into the people table, the second query gets the newly inserted person by selecting the organisationId (I chose to use this still but will prob remove it later), firstname, surname and status since the combination of these fields will probably nearly always be fairly unique, - is there a better way of getting the newly inserted personId using queries? Learning newbie, I know you suggested using DECLARE but is that the only way it can be done? The third query inserts the personId an organisationId in the mapping table.

PHP Code:

$query = "INSERT into people (title,first_name, surname, phoneNum, emailAddress, jobTitle, organisationId, status, client) VALUES ('$title','$forename','$surname','$phone','$email','$jobtitle','$organisationId','$status','$client')";
    $name =& $db->query($query);
    checkDBError($name);
 
$getPeopleIds =& $db->getOne("SELECT personId FROM people WHERE organisationId = '$_GET[id]' AND first_name = '$forename' AND surname = '$surname' AND status = '$status'");
    checkDBError($getPeopleIds);
 
$query2 = "INSERT into organisationPeople (organisationId, personId) VALUES ('$_GET['id','$getPeopleIds')";
    $name2 =& $db->query($query2);
    checkDBError($name2); 


tripy, I have tried to include a left outer join in this query but can't get it to work, I want to retrieve eventId's even if there are no personId's for the event but the part I'm finding difficult to understand is where to put the outer join and whether or not to use outer join on the other tables that are needed in the query?

SELECT *, date_FORMAT(date, '%M %e, %Y') as newdate FROM events E, people P, peopleEvent PE, organisationPeople OP OUTER JOIN WHERE P.personId = PE.personId AND E.eventId = PE.eventId AND P.personId = OP.personId AND OP.organisationId = '$_GET[id]' ORDER BY E.date DESC");

Originally Posted by drew22299

Learning newbie, I thought using 2 or more queries was a no go because it would slow the response times down but I have used three queries and it still exectutes the page quickly and inserts the person data into both the people and organisationPeople tables

Using 2 or more queries is almost always slower than using 1 query, so that's a good fact to know when you plan things. But if the only way to do what you need is with 2 queries, then getting the job done is always better than not getting it done. Usually, the reason people like me and Tripy point these things out is because there tend to be a lot of possible ways to do something, and the more you know about how to recognize a good or a bad idea, the better designs you'll come up with. Using 2 queries has drawbacks, but that doesn't mean you should never do it, just that if there's a way to do it with 1 query, that's usually better.

Originally Posted by drew22299

Learning newbie, I know you suggested using DECLARE but is that the only way it can be done? The third query inserts the personId an organisationId in the mapping table.

Well, it looks like you've found another way it can be done, using PHP and MySQL instead of pure SQL. Using a declare statement and identity function would have the same thing happen inside the database. You're telling your web server to ask the database a question right now, and then to tell the database to do something with the answer it gives for the last question. It's a bit more efficient to do it all in the DB, since the DB really has all the info it needs - you're only using PHP to coordinate the work, to say "do this, now do this".

you have malformed your query.
First, you should say if the outer join is a LEFT outer join or a RIGHT outer join.
Second, you don't say on which field your outer join should articulate.
Just a detail, but I would have articulate my query around the "peoples" table, as it seems to me that it's the "central" table of your query. It may sometime makes the query writing easier, if you can follow a logical path.
I have rewrote your query here, using the more verbose syntax. Try it, and tell me if something is wrong:

SELECT *, date_FORMAT(date, '%M %e, %Y') as newdate 
FROM events E, 
  INNER JOIN peopleEvent PE on E.eventId = PE.eventId,  --we link the events to the peoples via a reference table
  INNER JOIN people P on P.personId = PE.personId,  --we link the peoples to the events via the same reference table
  LEFT OUTER JOIN organisationPeople OP on op.personId = P.personId --we get every organisation a user might be part of
    and OP.organisationId = '$_GET[id]'
ORDER BY E.date DESC

tripy, the query worked great and seems to retrieve data from the database quicker, thanks for re-writing it