Link to home
Start Free TrialLog in
Avatar of PurpleSlade
PurpleSlade

asked on

Processing data from a multiple table join

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?
Avatar of PurpleSlade
PurpleSlade

ASKER

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?

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.
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
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
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
      ;";
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
Raynard, should "as Home_Phone" follow the "select ci.communication" ?
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
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>';
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?
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'),
ASKER CERTIFIED SOLUTION
Avatar of Raynard7
Raynard7

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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'),

...
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.
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
     ;";
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
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
      ;";      
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.
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
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?
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.