Solved

Processing data from a multiple table join

Posted on 2006-07-12
22
205 Views
Last Modified: 2013-12-12
OK, I have the following query - I want to add some phone and e-mail contact information to it.  I have

      $query = "SELECT CONCAT(k.last_name, ', ', k.first_name, ' ', IF(k.middle_name IS NULL, '', k.middle_name), ' ', IF(k.nickname IS NULL, '', CONCAT('\"', k.nickname, '\"'))) AS name, k.jersey_name, k.year_pledged, CONCAT(a.street, '<BR>', a.city, ', ', states.abbrev, '  ', a.zip) AS fulladdress FROM kappas k
                        LEFT JOIN kappas_address ka
                        ON ka.kappa_id = k.kappa_id
                        LEFT JOIN address a
                        ON a.address_id = ka.address_id
                        LEFT JOIN states
                        ON a.state_id=states.state_id
                        WHERE k.year_pledged BETWEEN " . $beginYear . " AND " . $endYear
                        . " ORDER BY k." . $orderBy;

The table I want to add from is:
Communication_Info
        communication_info_id        smallint(6) PK                
      communication_info       varchar(60)             
      kappa_id       smallint(6) FK
        communication_type_id        char(3) FK

Which will contain values like:
1   myemail@g.com   34    'HEM"  (for home e-mail)
2   555-555-5555      34    'HPH'   (for home phone)

I want to be able to grab the contact info by communication type so that I know which is a home phone, e-mail, cell phone, etc. -- can I do this through the select statement?
0
Comment
Question by:PurpleSlade
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 7
22 Comments
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17088594
The rows then get processed like                   

while($row=mysql_fetch_array($result, MYSQL_ASSOC))                  
{
      echo '<tr><td>' . $row['name'] . '</td><td>' . $row['jersey_name'] . '</td><td>' . $row['year_pledged'] . '</td><td>' . $row['fulladdress'] . '</td></tr>';
}

so I would want to add in there something like
<td>$row['homephone']</td><td>$row['cellphone']</td> etc.

Is this possible?

0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17088606
Also, the other table is
communication_info_type
     communication_info_type_id  char(3) PK
     description varchar(20)

'HPH'  'Home Phone'
'WPH' 'Work Phone'
'HEM' 'Home E-mail'
'WEM' 'Work E-mail'
'CPH' 'Cell Phone'

and that's all that's in that table.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17089230
You need to define what the relationship between the tables in your query and the communication type table is -
Then it is as simple as doing a sub query in your select statement to pull the information from the communication_info table
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:PurpleSlade
ID: 17092294
Well, the three tables are

CREATE TABLE `kappas` (
 `kappa_id` smallint(6) NOT NULL auto_increment,
 `first_name` varchar(20) NOT NULL default '',
 `middle_name` varchar(20) default NULL,
 `last_name` varchar(20) NOT NULL default '',
 `nickname` varchar(20) default NULL,
 `jersey_name` varchar(20) default NULL,
 `birthday` date default '0000-00-00',
 `year_pledged` year(4) NOT NULL default '0000',
 `year_graduated` year(4) default NULL,
 PRIMARY KEY  (`kappa_id`)
) TYPE=InnoDB

CREATE TABLE `communication_info` (
 `communication_info_id` smallint(6) NOT NULL auto_increment,
 `communication_info` varchar(60) NOT NULL default '',
 `kappa_id` smallint(6) NOT NULL default '0',
 `communication_type_id` char(3) NOT NULL default '',
 PRIMARY KEY  (`communication_info_id`),
 KEY `kappa_id` (`kappa_id`),
 KEY `communication_type_id` (`communication_type_id`),
 CONSTRAINT `communication_info_ibfk_1` FOREIGN KEY (`kappa_id`) REFERENCES `kappas` (`kappa_id`),
 CONSTRAINT `communication_info_ibfk_2` FOREIGN KEY (`communication_type_id`) REFERENCES `communication_type` (`communication_type_id`)
) TYPE=InnoDB

CREATE TABLE `communication_type` (
 `communication_type_id` char(3) NOT NULL default '',
 `communication_type` varchar(20) default NULL,
 PRIMARY KEY  (`communication_type_id`)
) TYPE=InnoDB
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17094778
Please Try

$query = "
      SELECT
            CONCAT(k.last_name, ', ', k.first_name, ' ', IF(k.middle_name IS NULL, '', k.middle_name), ' ', IF(k.nickname IS NULL, '', CONCAT('\"', k.nickname, '\"'))) AS name,
            k.jersey_name,
            k.year_pledged,
            CONCAT(a.street, '<BR>', a.city, ', ', states.abbrev, '  ', a.zip) AS fulladdress,
            ifnull(
                  (select
                        ci.communication_info
                   from
                        communication_info ci,
                        communication_type ct
                  where
                        ci.kappa_id = k.kappa_id
                        and ct.communication_type_id = ci.communication_type_id
                        and ct.communication_type = 'HPH'
                  limit
                        1)
                  , 'N/A') as Home_Phone,
            ifnull(
                  (select
                        ci.communication_info
                   from
                        communication_info ci,
                        communication_type ct
                  where
                        ci.kappa_id = k.kappa_id
                        and ct.communication_type_id = ci.communication_type_id
                        and ct.communication_type = 'WPH'
                  limit
                        1)
                  , 'N/A') as Work_Phone,
            ifnull(
                  (select
                        ci.communication_info
                   from
                        communication_info ci,
                        communication_type ct
                  where
                        ci.kappa_id = k.kappa_id
                        and ct.communication_type_id = ci.communication_type_id
                        and ct.communication_type = 'HEM'
                  limit
                        1)
                  , 'N/A') as Home_EMail,
            ifnull(
                  (select
                        ci.communication_info
                   from
                        communication_info ci,
                        communication_type ct
                  where
                        ci.kappa_id = k.kappa_id
                        and ct.communication_type_id = ci.communication_type_id
                        and ct.communication_type = 'WEM'
                  limit
                        1)
                  , 'N/A') as Work_Email,
            ifnull(
                  (select
                        ci.communication_info
                   from
                        communication_info ci,
                        communication_type ct
                  where
                        ci.kappa_id = k.kappa_id
                        and ct.communication_type_id = ci.communication_type_id
                        and ct.communication_type = 'CPH'
                  limit
                        1)
                  , 'N/A') as Cell_Phone
       FROM
            kappas k
            LEFT JOIN kappas_address ka ON ka.kappa_id = k.kappa_id
            LEFT JOIN address a ON a.address_id = ka.address_id
            LEFT JOIN states ON a.state_id=states.state_id
      WHERE
            k.year_pledged BETWEEN $beginYear AND $endYear
      ORDER BY
            k.$orderBy
      ;";
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17095022
When I run that I get:

MySQL said:  
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select
                    ci.communication_info

I am trying to find out the version of mysql running at the web hosting facility
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17095196
Raynard, should "as Home_Phone" follow the "select ci.communication" ?
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17095356
No, this is a sub query that only returns one value  it does not need to be aliased - however it does not hurt to put it in
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17095361
Raynard - sorry, it is running after all - when I try and just grab cellphone however, they all come out as N/A.  I was stupidly running the query through phpMyAdmin and not through the php program which converts the variables.

Anways. With this for the results - all $row['Cell_Phone'] come out as 'N/A' -
echo '<tr><td>' . $row['name'] . '</td><td>' . $row['jersey_name'] . '</td><td>' . $row['year_pledged'] . '</td><td>' . $row['fulladdress'] . '</td><td>' . $row['Cell_Phone'] . '</td></tr>';
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17095371
are there values in the communication table for the cell phone??
ifnull says if there is nothing returned by the sub query then return 'N/A'
do any of the other sub queries work?
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17095427
Hi, I tried it for $row['Home_Phone'] also and still the N/A

There are definitely values in there (communication info) - these are the first 10 (changed slightly to protect the innocent)

INSERT INTO `communication_info` (`communication_info_id`, `communication_info`, `kappa_id`, `communication_type_id`) VALUES
(1, '899-000-0000', 89, 'HPH'),
(2, '582-000-0000', 89, 'WPH'),
(3, 'hot@hoffmail.com', 89, 'HEM'),
(4, '472-000-0000', 121, 'HPH'),
(5, '437-000-0000', 121, 'WPH'),
(6, '437-000-0000', 121, 'CPH'),
(7, '051-000-0000', 125, 'HPH'),
(8, '487-000-0000', 125, 'WPH'),
(9, '631-000-0000', 125, 'CPH'),
(10, 'mike@yahoo.com', 125, 'HEM'),
0
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 17095520
Try running

               select
                    ci.communication_info as cell_phone
                from
                    communication_info ci
               where
                    ci.kappa_id = k.kappa_id
                    and ci.communication_type = 'CPH'
               limit
                    1

instead;
You do not need to reference ct
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17095559
when I try and enter the query directly into phpMyAdmin on the web hosted version (as it is being passed through)  -  or via the command line here on my local version of SQL - I get the following:

Error

SQL query: Documentation

SELECT CONCAT( k.last_name, ', ', k.first_name, ' ', IF( k.middle_name IS NULL , '', k.middle_name ) , ' ', IF( k.nickname IS NULL , '', CONCAT( '"', k.nickname, '"' ) ) ) AS name, k.jersey_name, k.year_pledged, CONCAT( a.street, ' ', a.city, ', ', states.abbrev, ' ', a.zip ) AS fulladdress, ifnull( (

SELECT ci.communication_info
FROM communication_info ci, communication_type ct
WHERE ci.kappa_id = k.kappa_id
AND ci.communication_type_id = ct.communication_type_id
AND ct.communication_type = 'HPH'
LIMIT 1
), 'N/A' ) AS Home_Phone, ifnull( (

SELECT ci.communication_info
FROM communication_info ci, communication_type ct
WHERE ci.kappa_id = k.kappa_id
AND ct.communication_type_id = ci.communication_type_id
AND ct.communication_type = 'WPH'
LIMIT 1
), 'N/A' ) AS Work_Phone, ifnull( (

SELECT ci.communication_info
FROM communication_info ci, communication_type ct
WHERE ci.kappa_id = k.kappa_id
AND ct.communication_type_id = ci.communication_type_id
AND ct.communication_type = 'HEM'
LIMIT 1
), 'N/A' ) AS Home_EMail, ifnull( (

SELECT ci.communication_info
FROM communication_info ci, communication_type ct
WHERE ci.kappa_id = k.kappa_id
AND ct.communication_type_id = ci.communication_type_id
AND ct.communication_type = 'WEM'
LIMIT 1
), 'N/A' ) AS Work_Email, ifnull( (

SELECT ci.communication_info
FROM communication_info ci, communication_type ct
WHERE ci.kappa_id = k.kappa_id
AND ct.communication_type_id = ci.communication_type_id
AND ct.communication_type = 'CPH'
LIMIT 1
), 'N/A' ) AS Cell_Phone
FROM kappas k
LEFT JOIN kappas_address ka ON ka.kappa_id = k.kappa_id
LEFT JOIN address a ON a.address_id = ka.address_id
LEFT JOIN states ON a.state_id = states.state_id
WHERE k.year_pledged
BETWEEN 1987
AND 2006
ORDER BY k.last_name;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select ci.communication_info from communication_info ci, commun
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17095618
So it would look like this?

          ifnull(
               (select
                    ci.communication_info AS Home_Phone
                from
                    communication_info ci,
               where
                    ci.kappa_id = k.kappa_id
                    and ci.communication_type = 'HPH'
               limit
                    1)
               , 'N/A'),

...
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17095620
Earlier you said it was working - but it was returning 'N/A'
the na is solved if you change the sub queries for cell_phone etc to remove all references to ct and change ct.communication_type to ci.communication_type.
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17095774
OK I modified the table to reflect the following - I am getting a error: mysql_num_rows(): supplied argument is not a valid MySQL result resource:

$query = "
     SELECT
          CONCAT(k.last_name, ', ', k.first_name, ' ', IF(k.middle_name IS NULL, '', k.middle_name), ' ', IF(k.nickname IS NULL, '', CONCAT('\"', k.nickname, '\"'))) AS name,
          k.jersey_name,
          k.year_pledged,
          CONCAT(a.street, '<BR>', a.city, ', ', states.abbrev, '  ', a.zip) AS fulladdress,
          ifnull(
               (select
                    ci.communication_info AS home_phone
                from
                    communication_info ci
               where
                    ci.kappa_id = k.kappa_id
                    and ci.communication_type = 'HPH'
               limit
                    1)
               , 'N/A'),
          ifnull(
               (select
                    ci.communication_info AS work_phone
                from
                    communication_info ci
               where
                           ci.kappa_id = k.kappa_id
                    and ci.communication_type = 'WPH'
               limit
                    1)
               , 'N/A'),
          ifnull(
               (select
                    ci.communication_info AS email
                from
                    communication_info ci
               where
                    ci.kappa_id = k.kappa_id
                    and ci.communication_type = 'HEM'
               limit
                    1)
               , 'N/A'),
          ifnull(
               (select
                    ci.communication_info AS cell_phone
                from
                    communication_info ci,
               where
                    ci.kappa_id = k.kappa_id
                    and ci.communication_type = 'CPH'
               limit
                    1)
               , 'N/A')
      FROM
          kappas k
          LEFT JOIN kappas_address ka ON ka.kappa_id = k.kappa_id
          LEFT JOIN address a ON a.address_id = ka.address_id
          LEFT JOIN states ON a.state_id=states.state_id
     WHERE
          k.year_pledged BETWEEN $beginYear AND $endYear
     ORDER BY
          k.$orderBy
     ;";
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17096205
OK, this is driving me crazy: I cleaned up the query

$query = "
      SELECT
            CONCAT(k.last_name, ', ', k.first_name, ' ', IF(k.middle_name IS NULL, '', k.middle_name), ' ', IF(k.nickname IS NULL, '', CONCAT('\"', k.nickname, '\"'))) AS name,
            k.jersey_name,
                k.year_pledged,
                CONCAT(a.street, '<BR>', a.city, ', ', states.abbrev, '  ', a.zip) AS fulladdress,
                IFNULL( (SELECT ci.communication_info FROM communication_info ci WHERE ci.kappa_id = k.kappa_id AND ci.communication_type = 'HPH' LIMIT 1), 'N/A') AS home_phone,
                IFNULL( (SELECT ci.communication_info FROM communication_info ci WHERE ci.kappa_id = k.kappa_id AND ci.communication_type = 'WPH' LIMIT 1), 'N/A') AS work_phone,
                IFNULL( (SELECT ci.communication_info FROM communication_info ci WHERE ci.kappa_id = k.kappa_id AND ci.communication_type = 'HEM' LIMIT 1), 'N/A') AS email,
                IFNULL( (SELECT ci.communication_info FROM communication_info ci WHERE ci.kappa_id = k.kappa_id AND ci.communication_type = 'CPH' LIMIT 1), 'N/A') AS cell_phone
      FROM
            kappas k
            LEFT JOIN kappas_address ka ON ka.kappa_id = k.kappa_id
            LEFT JOIN address a ON a.address_id = ka.address_id
            LEFT JOIN states ON a.state_id=states.state_id
      WHERE
            k.year_pledged BETWEEN $beginYear AND $endYear
      ORDER BY
            k.$orderBy
      ;";

      echo $query . "</BR>";

It definitely does not like any of the subqueries - if I remove them, the query runs.  Including them I get teh Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource Message
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17096321
I fixed the error message by changing mysql_num_rows() to @mysql_num_rows()

I tried this simple subquery and I get no results - if I remove it I get the full 283 results - can anyone help?  Shouldn't it just insert 1 into the home_phone?

$query = "
      SELECT
            CONCAT(k.last_name, ', ', k.first_name, ' ', IF(k.middle_name IS NULL, '', k.middle_name), ' ', IF(k.nickname IS NULL, '', CONCAT('\"', k.nickname, '\"'))) AS name,
            k.jersey_name,
                k.year_pledged,
                CONCAT(a.street, '<BR>', a.city, ', ', states.abbrev, '  ', a.zip) AS fulladdress,
                (SELECT 1) AS home_phone
      FROM
            kappas k
            LEFT JOIN kappas_address ka ON ka.kappa_id = k.kappa_id
            LEFT JOIN address a ON a.address_id = ka.address_id
            LEFT JOIN states ON a.state_id=states.state_id
      WHERE
            k.year_pledged BETWEEN $beginYear AND $endYear
      ORDER BY
            k.$orderBy
      ;";      
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17096381
OK - when this query (the above one with (SELECT 1) passes through php I get - No Results

However, when I run it by pasting it into mysql command line, I get 283 results, which is what I would want, and the 1s are populated - it is hitting the same database, so there is something wrong with the way I am retrieving the results through php.
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17096429
OK, I think I am getting closer

$query = "
      SELECT
            CONCAT(k.last_name, ', ', k.first_name, ' ', IF(k.middle_name IS NULL, '', k.middle_name), ' ', IF(k.nickname IS NULL, '', CONCAT('\"', k.nickname, '\"'))) AS name,
            k.jersey_name,
                k.year_pledged,
                CONCAT(a.street, '<BR>', a.city, ', ', states.abbrev, '  ', a.zip) AS fulladdress,
                (SELECT 1) AS home_phone
      FROM
            kappas k
            LEFT JOIN kappas_address ka ON ka.kappa_id = k.kappa_id
            LEFT JOIN address a ON a.address_id = ka.address_id
            LEFT JOIN states ON a.state_id=states.state_id
      WHERE
            k.year_pledged BETWEEN $beginYear AND $endYear
      ORDER BY
            k.$orderBy
      ;";      

      echo $query . "</BR>";

      $result = @mysql_query($query);

      $num = @mysql_num_rows($result);   <-- this line seems to be null - I changed it to include an '@' because I was reading that it was a solution to error: mysql_num_rows(): supplied argument is not a valid MySQL result resource but now it is null
      
if($num > 0)  <-- so this fails
      {
                        while($row=mysql_fetch_array($result, MYSQL_ASSOC))  <-- so this never gets processed
         }
else
   {  echo "No results"; }

it looks like someone else had a problem with this but I have yet to find a solution, unless there is none:
http://www.osticket.com/forums/showthread.php?t=689
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17096530
I'm not sure what the error is - I have not experienced subqueries not working.

all the @ symbol in front of the functions does is supress any errors which is not a good thing.

If it is running through the command line I am guessing that it is a problem with how you are passing the data through php.

I would suggest that you get your php script to output the exact query you are submitting and the run that exact query through the command line.  If it does not work you may be able to analyse what exactly is being submitted and see the problem.

The problem may relate to the mysql_Num_rows - is it required for your script? if you omit it - is there data?
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 17096788
Hey raynard - thanks for the explanation on the @

I got it to work at home - I resolved some configuration issues that were not set properly and it works now.  Not sure if it will translate over to the web hosting database, but I guess I will find out soon enough.  Thanks for all of your help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

756 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