Sunday, October 18, 2009

PHP MySQL Union Query

Union Query with PHP and MySQL

For deailed explanation of MySQL sample data and logic, please see my Jasper Reports Query post

-- --------------------------------------------------------

--
-- Table structure for table `BookList`
--

CREATE TABLE IF NOT EXISTS `BookList` (
`id` int(11) NOT NULL auto_increment,
`Author` varchar(50) default NULL,
`Title` varchar(50) default NULL,
`ISBN` varchar(50) default NULL,
`LocalFileNumber` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Data for table `BookList`
--

INSERT INTO `BookList` (`id`, `Author`, `Title`, `ISBN`, `LocalFileNumber`) VALUES
(1, 'Henry James', 'The Portrait of a Lady', '0141439637', NULL),
(2, 'T.S. Eliot', 'Collected Poems', '0151189781', NULL),
(3, 'William Gaddis', 'The Recognitions', '0844667404', NULL),
(4, 'John Smith', 'History of Our Library', NULL, 'JS12345678'),
(5, 'James Merrill', 'The Changing Light at Sandover', '0307263215', NULL),
(6, 'Wendy Jones', 'Library Policy Manual', NULL, 'WJ12345678');




<?php
$myconn = mysql_connect("localhost","username","password");
if (!$myconn)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("Books", $myconn);


$result = mysql_query
('select Author, Title, ISBN as "BookCode"
FROM BookList where ISBN is not null
UNION
select Author, Title, LocalFileNumber as "BookCode"
FROM BookList where ISBN is null')
or die(mysql_error());

while($row = mysql_fetch_array($result))
  {
  echo $row['Author'] . " " . $row['Title'] . " " .$row['BookCode'];
  echo "</br
>";
  }

mysql_close($myconn);
?>



(and with a bit formatting.........


<?php
$myconn = mysql_connect("localhost","username","password");
if (!$myconn)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("Books", $myconn);

$result = mysql_query('select Author, Title, ISBN as "BookCode"
FROM BookList where ISBN is not null
UNION
select Author, Title, LocalFileNumber as "BookCode"
FROM BookList where ISBN is null')
or die(mysql_error());

echo "<table border='1'>
<tr>
<th>Author</th>
<th>Title</th>
<th>BookCode</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Author'] . "</td>";
  echo "<td>" . $row['Title'] . "</td>";
  echo "<td>" . $row['BookCode'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($myconn);
?>

No comments: