do I need to use an array?

Lets see if I can explain this correctly...

I am building an administration area.  I am trying to display ALL items in a table.  Table 1 is called service_locations.  Table 2 is called doctors. Table 3 is called locations.  I have setup an INNER JOIN on all three tables.

I have a <?php do loop to display all data in service_locations such as

id          city          doctor
1          5               12
2          3                9
3          19              3

The values here are exactly as they are in the service_locations table, however, what I actually want displayed on the screen is the city Name and doctor Name instead of their id's.  My INNER JOIN works fine, meaning I can display the city and name but the problem is that it ONLY displays the last record.  So if the id values equal the following:

city table
5 = Georgetown
3 = Young
19 = Freshman

doctor table
12 = Hughes
9 = Smith
3 = Thompson

my display shows

id          city                doctor
1           Freshman     Thompson
2           Freshman     Thompson

What I need to do, is possibly a for each loop in addition to my do while loop?  I'm just a bit lost here on how to display the city name and doctor name for each result.
LVL 16
rbudjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ahmad2121Commented:
you may have an error in your inner join. can you please post it?
0
rbudjAuthor Commented:
SELECT locations.city, physicians.PhysicianFirstName, physicians.PhysicianLastName
FROM service_locations INNER JOIN locations ON locations.id = Service_LocationsServiceID INNER JOIN physicians ON PhysicianID = Service_LocationsDocID

0
ahmad2121Commented:
do you really need an inner join for that?
SELECT 
  locations.city, physicians.PhysicianFirstName, physicians.PhysicianLastName
FROM 
  service_locations,locations,physicians
WHERE
  Service_LocationsDocID = PhysicianID 
 AND 
  Service_LocationsServiceID = locations.id

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ahmad2121Commented:
i think there is a mistake in the above query.. hold on.
0
Ray PaseurCommented:
Is this by any chance a homework assignment?  We are prohibited from providing answers for homework by the TOS of EE, but we can provide guidance to learning resources.  Just checking, thanks, ~Ray
0
rbudjAuthor Commented:
no this is not a homework assignment :/
0
Ray PaseurCommented:
Great.  Please post the CREATE TABLE statements so we can see what the field names are.  Thanks.
0
rbudjAuthor Commented:
here is the part that matters.  Again, I want to display the city and doctor names instead of the id's
<?php do { ?>
            <tr class="<?php echo $WARRT_AltClass1->getClass(true); ?>">
              <td class="WADAResultsTableCell"><a href="service_locations_Detail.php?Service_LocationsID=<?php echo(rawurlencode($row_WADAservice_locations['Service_LocationsID'])); ?>" ><?php echo($row_WADAservice_locations['Service_LocationsID']); ?></a></td>
              <td class="WADAResultsTableCell"><a href="service_locations_Detail.php?Service_LocationsID=<?php echo(rawurlencode($row_WADAservice_locations['Service_LocationsID'])); ?>" ><?php echo $row_WADAservice_locations['Service_LocationsServiceID']; ?> - <?php echo $row_rsJoin['city']; ?></a></td>
              <td class="WADAResultsTableCell"><a href="service_locations_Detail.php?Service_LocationsID=<?php echo(rawurlencode($row_WADAservice_locations['Service_LocationsID'])); ?>" ><?php echo($row_WADAservice_locations['Service_LocationsDocID']); ?></a></td>

Open in new window

0
Ray PaseurCommented:
Please post the CREATE TABLE statements, thanks.
0
rbudjAuthor Commented:
i dont know what you mean by create table statements.
0
Ray PaseurCommented:
When these data base tables were created, you (or someone) used CREATE TABLE and in that, you laid out the names of the fields, their characteristics, whether they were indexed or NULL, etc.  That is what I am looking for.  It will help us show you the correct queries and the best way to iterate over the query results set.
0
rbudjAuthor Commented:
i used phpmyadmin
-- phpMyAdmin SQL Dump
-- version 2.11.9.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 10, 2009 at 02:04 PM
-- Server version: 4.1.22
-- PHP Version: 5.2.6
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Database: `mydatabase`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `locations`
--
 
CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(11) NOT NULL auto_increment,
  `cat_city_id` varchar(255) NOT NULL default '',
  `name_1` varchar(255) NOT NULL default '',
  `name_2` varchar(255) NOT NULL default '',
  `address` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `state` varchar(15) NOT NULL default '',
  `zip` varchar(10) NOT NULL default '',
  `phone_appt` varchar(15) NOT NULL default '',
  `phone` varchar(15) NOT NULL default '',
  `fax` varchar(15) NOT NULL default '',
  `hours` varchar(255) NOT NULL default '',
  `more_info` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `physicians`
--
 
CREATE TABLE IF NOT EXISTS `physicians` (
  `PhysicianID` int(11) NOT NULL auto_increment,
  `PhysicianFirstName` varchar(255) NOT NULL default '',
  `PhysicianLastName` varchar(255) NOT NULL default '',
  `PhysicianUndergraduate` longtext NOT NULL,
  `PhysicianMedical_School` longtext NOT NULL,
  `PhysicianInternship` longtext NOT NULL,
  `PhysicianFellowship` longtext NOT NULL,
  `PhysicianBoard` longtext NOT NULL,
  `PhysicianHonors` longtext NOT NULL,
  `PhysicianResearch` longtext NOT NULL,
  `PhysicianMembership` longtext NOT NULL,
  `PhysicianPersonal` longtext NOT NULL,
  `PhysicianPhoto` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`PhysicianID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `service_locations`
--
 
CREATE TABLE IF NOT EXISTS `service_locations` (
  `Service_LocationsID` int(11) NOT NULL auto_increment,
  `Service_LocationsServiceID` varchar(255) NOT NULL default '',
  `Service_LocationsDocID` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`Service_LocationsID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

Open in new window

0
ahmad2121Commented:
try this one please:
SELECT locations.city, physicians.PhysicianFirstName, physicians.PhysicianLastName
FROM physicians
INNER JOIN service_locations ON Service_LocationsDocID = PhysicianID
INNER JOIN locations ON Service_LocationsID = locations.id

Open in new window

0
rbudjAuthor Commented:
ahmad, that does not work.  My original statement pulls all the correct data, I just cannot get it to display properly.
0
ahmad2121Commented:
ah ok, that wasn't clear from your first post.

below is a sample display of the fields you want. that should work.
 $SQL = "YOUR SQL STATEMENT";
$result = mysql_query($SQL);
 
while ($db_field = mysql_fetch_assoc($result)) {
print "<td>" . $db_field['city'] . "</td>";
print "<td>" . $db_field['PhysicianFirstName'] . "</td>";
print "<td>" . $db_field['PhysicianLastName'] . "</td>";
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rbudjAuthor Commented:
im still working on this... i will report back
0
rbudjAuthor Commented:
still working on this, i will report back.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.