Solved

Processing data from a multiple table join

Posted on 2006-07-12
22
200 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Author Comment

by:PurpleSlade
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Raynard, should "as Home_Phone" follow the "select ci.communication" ?
0
 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

728 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

10 Experts available now in Live!

Get 1:1 Help Now