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?
$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?
ASKER
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.
communication_info_type
communication_info_type_id
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
Then it is as simple as doing a sub query in your select statement to pull the information from the communication_info table
ASKER
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
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
CONSTRAINT `communication_info_ibfk_2
) 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
;";
$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
;";
ASKER
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
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
ASKER
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
ASKER
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>';
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?
ifnull says if there is nothing returned by the sub query then return 'N/A'
do any of the other sub queries work?
ASKER
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'),
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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'),
...
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.
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.
ASKER
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
;";
$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
;";
ASKER
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
$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
ASKER
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
;";
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
;";
ASKER
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.
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.
ASKER
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_arr ay($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
$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_arr
}
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?
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?
ASKER
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.
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.
ASKER
while($row=mysql_fetch_arr
{
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>
Is this possible?