wrong again, forgot the grouping,
this is the right sql:
$sql = "SELECT p.id,
p.$town,
p.$zip,
p.$phone,
p.$description
count(m.*) praxdocs
FROM $praxTable p,
$medTable m
WHERE p.id = m.prax
GROUP BY p.id,
p.$town,
p.$zip,
p.$phone,
p.$description";
"Michael Virnstein" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> i rethought the sql and found, that the column parxdocs wont contain the
> number of doctors per praxis.
> this would only be done if there are more than one doctor with the same
> name, surename and title in one praxis.
>
> but you can do this instead
> // now you'll get the number of doctors per praxis
> $sql = "SELECT p.id,
> p.$town,
> p.$zip,
> p.$phone,
> p.$description
> count(m.*) praxdocs
> FROM $praxTable p,
> $medTable m
> WHERE p.id = m.prax";
>
> $result = mysql_query($sql, $conn);
> while ($row = mysql_fetch_array($result)) {
> for ($i = 0; $i < $row["praxdocs"]; $i++) {
> $docsql = "SELECT $sureName,
> $preName,
> $title
> FROM $medTable
> WHERE prax = {$row["id"]}";
> $docresult = mysql_query($docsql, $conn);
> while ($docrow = mysql_fetch_array($docresult)) {
> // print names of docs here using $docrow array
> }
> //print address of praxis here using $row array
> }
>
> }
> "Michael Virnstein" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
> [EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> > typo in the querystring, this should work, i suppose
> >
> > $queryString = "SELECT count(m.*) parxdocs
> > m.$sureName,
> > m.$preName,
> > m.$title,
> > m.prax,
> > p.$town,
> > p.$zip,
> > p.$phone,
> > p.$description
> > FROM $medTable m,
> > $praxTable p
> > WHERE m.$prax = p.$id
> > GROUP BY m.prax, m.$preName, m.$sureName,
> > m.$title, p.$town, p.$zip, p.$phone, p.$description
> > ORDER BY m.$prax, m.$preName";
> >
> > "Michael Virnstein" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
> > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> > > ok, here we go.
> > >
> > > you normaly say this i suppose:
> > >
> > > while ($row = mysql_fetch_array($result) {
> > > //your html inserts here
> > > }
> > >
> > > if you'd use oracle, i'd suggest using a cursor, but you're using
MySql,
> > so
> > > you probably have to do it a bit different:
> > > (Not tested, could contain some errors!!!)
> > >
> > > // you'll now have the number of doctors in one praxis in praxdocs
> > > $queryString = "SELECT count(m.*) parxdocs
> > > m.$sureName,
> > > m.$preName,
> > > m.$title,
> > > p.$town,
> > > p.$zip,
> > > p.$phone,
> > > p.$description
> > > FROM $medTable m,
> > > $praxTable p
> > > WHERE m.$prax = p.$id
> > > GROUP BY m.prax, m.$preName, m.$sureName,
> > > m.$title, p.$town, p.$zip, p.$phone, p.$description
> > > ORDER BY m.$prax, m.$preName";
> > >
> > > // then output the html
> > > while ($row = mysql_fetch_array($result)) {
> > > // we don't need the first one, because we already have it.
> > > echo "{$row["title"]} {$row["preName"]} {$row["sureName"]}<br>";
> > > for ($i = 1; $i < $row["praxdocs"]; $i++) {
> > > $doctor = mysql_fetch_array($result);
> > > echo "{$doctor["title"]} {$doctor["preName"]}
> > > {$doctor["sureName"]}<br>";
> > > }
> > > // rest of the output using $row here
> > > }
> > >
> > > hope that helps
> > >
> > > "Christoph Starkmann" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
> > > B120D7EC8868D411A63D0050040EDA77111BE9@XCHANGE">news:B120D7EC8868D411A63D0050040EDA77111BE9@XCHANGE...
> > > > Hi folks!
> > > >
> > > > The following problem:
> > > >
> > > > I got a db (mysql) with information about doctors.
> > > > Name, adress, phone etc.
> > > >
> > > > Now I'm reading these information with a simple
> > > > mysql-query:
> > > >
> > > > $queryString = "SELECT DISTINCT m.$sureName, m.$preName, m.$prax,
> > > m.$title,
> > > > ";
> > > > $queryString .= "p.$town, p.$zip, p.$phone, p.$description ";
> > > > $queryString .= "FROM $medTable m, $praxTable p WHERE ";
> > > > $queryString .= "m.$prax = p.$id";
> > > >
> > > > Normally, I print out the information like this:
> > > >
> > > > Dr. med. John Doe // $title, $preName, $sureName
> > > > (shared practice) // description
> > > > Elmstreet 13 // $street
> > > > 666 Amityville 23 // $zip, $town
> > > > phone: 0049 - 815 - 4711 // $phone
> > > >
> > > > Okay. Now some of these folks are sharing a practice
> > > > ($description in the above code == "shared practice").
> > > >
> > > > I would like to have these grouped together like this:
> > > >
> > > > Dr. med. John Doe // $title, $preName, $sureName
> > > > Dr. med. Allan Smithee
> > > > (shared practice) // description
> > > > Elmstreet 13 // $street
> > > > 666 Amityville 23 // $zip, $town
> > > > phone: 0049 - 815 - 4711 // $phone
> > > >
> > > > I am starting to get a little confused right here and right now.
> > > > This is the reason for being THIS detailed, too ;) Don't want to
> > > > mix anything up.
> > > >
> > > > How would you achieve this goal fastest and "best"?
> > > > Creating a temp array and checking for double $description-s
> > > > which I store in the temp array and delete from the original one?
> > > > Or check this with the original array? How?
> > > > I found functions to get the value for one key in a hash, but not
> > > > for several values with the same key...
> > > >
> > > > Sorry for the confusion, starting to get fuzzy...
> > > >
> > > > Any ideas, hints?
> > > >
> > > > Thanx alot,
> > > >
> > > > Kiko
> > > >
> > > > --
> > > > It's not a bug, it's a feature.
> > > > christoph starkmann
> > > > mailto:[EMAIL PROTECTED]
> > > > http://www.gruppe-69.com/
> > > > ICQ: 100601600
> > > > --
> > >
> > >
> >
> >
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
