Thanks, laptop alias (and Barry), here is the SHOW CREATE TABLE
Posted by:
Kurt Wayne
Date: March 07, 2012 08:16PM
...results for the two tables:
brasil_newspapers CREATE TABLE `brasil_newspapers` (
`key` int(4) NOT NULL auto_increment,
`state` text NOT NULL,
`city` text NOT NULL,
`newspaper` text NOT NULL,
`newspaper_site` text NOT NULL,
`facebook` text NOT NULL,
`twitter` text NOT NULL,
PRIMARY KEY (`key`)
) ENGINE=MyISAM AUTO_INCREMENT=1027 DEFAULT CHARSET=utf8
and;
brasil_radio CREATE TABLE `brasil_radio` (
`key` int(11) NOT NULL auto_increment,
`state` text NOT NULL,
`city` text NOT NULL,
`radio` text NOT NULL,
`radio_site` text NOT NULL,
`rfacebook` text NOT NULL,
`rtwitter` text NOT NULL,
PRIMARY KEY (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
(I've inputted the records, so far, manually in my mySQL client.)
The code I'm using in PHP:
$query = "SELECT * FROM brasil_newspapers, brasil_radio
WHERE state='Alagoas'
AND brasil_newspapers.city=brasil_radio.city
ORDER BY city
LIMIT $offset, $rowsPerPage";
$result = mysql_query($query);
if($result)
{
while($row = mysql_fetch_array($result))
{
echo "<table border='1'>
<tr>
<th>City</th>
<th>Newspaper web site</th>
<th>Facebook</th>
<th>Twitter</th>
<th>Radio web site</th>
<th>Twitter</th>
<th>Twitter</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['city'] . "</td>";
echo "<td>";
echo "<a target=\"_new\" href=\"".$row['newspaper_site']."\">".$row['newspaper'] . "</a>";
echo "</td>";
echo "<td>";
//if(mysql_num_rows(mysql_query("SELECT facebook FROM brasil_newspapers WHERE facebook != '' and id_brasil_newspapers ='id_brasil_newspapers'"))){
if(strlen($row["facebook"]) > 0)
echo "<a target=\"_new\" href=\"".$row['facebook']."\">"."<IMG SRC=\"images/facebook.gif\">" . "</a>";
echo "</td>";
echo "<td>";
//if(mysql_num_rows(mysql_query("SELECT twitter FROM brasil_newspapers WHERE twitter != '' and id_brasil_newspapers ='id_brasil_newspapers'"))){
if(strlen($row["twitter"]) > 0)
echo "<a target=\"_new\" href=\"".$row['twitter']."\">"."<IMG SRC=\"images/twitter.gif\">" . "</a>";
echo "</td>";
echo "<td>";
echo "<a target=\"_new\" href=\"".$row['radio_site']."\">".$row['radio'] . "</a>";
echo "</td>";
echo "<td>";
//if(mysql_num_rows(mysql_query("SELECT facebook FROM brasil_newspapers WHERE facebook != '' and id_brasil_newspapers ='id_brasil_newspapers'"))){
if(strlen($row["facebook"]) > 0)
echo "<a target=\"_new\" href=\"".$row['rfacebook']."\">"."<IMG SRC=\"images/facebook.gif\">" . "</a>";
echo "</td>";
echo "<td>";
//if(mysql_num_rows(mysql_query("SELECT twitter FROM brasil_newspapers WHERE twitter != '' and id_brasil_newspapers ='id_brasil_newspapers'"))){
if(strlen($row["twitter"]) > 0)
echo "<a target=\"_new\" href=\"".$row['rtwitter']."\">"."<IMG SRC=\"images/twitter.gif\">" . "</a>";
echo "</td>";
echo "</tr>";
}
echo "</table>";