Solved

Processing data from a multiple table join

Posted on 2006-07-12
22
204 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
  • 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

825 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