Solved

do I need to use an array?

Posted on 2009-07-10
17
198 Views
Last Modified: 2012-05-07
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.
0
Comment
Question by:rbudj
  • 8
  • 5
  • 4
17 Comments
 
LVL 6

Expert Comment

by:ahmad2121
ID: 24825942
you may have an error in your inner join. can you please post it?
0
 
LVL 16

Author Comment

by:rbudj
ID: 24826015
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
 
LVL 6

Expert Comment

by:ahmad2121
ID: 24826073
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
 
LVL 6

Expert Comment

by:ahmad2121
ID: 24826080
i think there is a mistake in the above query.. hold on.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 24826158
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
 
LVL 16

Author Comment

by:rbudj
ID: 24826175
no this is not a homework assignment :/
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 24826183
Great.  Please post the CREATE TABLE statements so we can see what the field names are.  Thanks.
0
 
LVL 16

Author Comment

by:rbudj
ID: 24826247
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 24826268
Please post the CREATE TABLE statements, thanks.
0
 
LVL 16

Author Comment

by:rbudj
ID: 24826309
i dont know what you mean by create table statements.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 24826322
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
 
LVL 16

Author Comment

by:rbudj
ID: 24826429
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
 
LVL 6

Expert Comment

by:ahmad2121
ID: 24826469
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
 
LVL 16

Author Comment

by:rbudj
ID: 24826807
ahmad, that does not work.  My original statement pulls all the correct data, I just cannot get it to display properly.
0
 
LVL 6

Accepted Solution

by:
ahmad2121 earned 500 total points
ID: 24826862
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
 
LVL 16

Author Comment

by:rbudj
ID: 24883469
im still working on this... i will report back
0
 
LVL 16

Author Comment

by:rbudj
ID: 24940107
still working on this, i will report back.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now