I seem to be having great difficulty in getting the desired result a database query. I have two tables (ist_fugitives and ist_caught) populated with two rows of data. These are using the felonID field as their Key thingy. Information about each fugitive is stored in the ist_fugitives table, and once they are apprehended they also get a listing in the ist_caught table. The full structure and two example rows of data:
Code:
CREATE TABLE `ist_caught` (
`felonID` int(5) NOT NULL default '0',
`wantedBy` varchar(15) NOT NULL default '',
`caughtBy` varchar(15) NOT NULL default '',
`location` varchar(30) NOT NULL default '',
`date` varchar(12) NOT NULL default '',
PRIMARY KEY (`felonID`)
) TYPE=MyISAM;
INSERT INTO `ist_caught` VALUES (1, 'USA001', 'UK001', 'Germany', '23/07/2003');
INSERT INTO `ist_caught` VALUES (12, 'UK001', 'UK001', 'France', '25/07/2003');
INSERT INTO `ist_caught` VALUES (11, 'UK001', 'USA001', 'United States', '25/07/2003');
INSERT INTO `ist_caught` VALUES (15, 'USA001', 'USA001', 'Afghanistan', '25/07/2003');
INSERT INTO `ist_caught` VALUES (16, 'USA001', 'USA001', 'Yemen', '25/07/2003');
CREATE TABLE `ist_fugitives` (
`felonID` int(5) NOT NULL auto_increment,
`felonName` varchar(40) NOT NULL default '',
`felonDOB` varchar(10) NOT NULL default '',
`felonSex` char(1) NOT NULL default '',
`felonNationality` varchar(22) NOT NULL default '',
`felonLocation` varchar(22) NOT NULL default '',
`felonDescription` varchar(255) NOT NULL default '',
`felonWantedfor` varchar(255) NOT NULL default '',
`felonHunters` varchar(15) NOT NULL default '',
`felonImage` varchar(40) NOT NULL default '',
`felonStatus` varchar(12) NOT NULL default '',
PRIMARY KEY (`felonID`),
KEY `felonHunters` (`felonHunters`)
) TYPE=MyISAM AUTO_INCREMENT=17 ;
INSERT INTO `ist_fugitives` VALUES (15, 'Usama Bin Laden', '1957', 'M', 'Saudi Arabian', 'Afghanistan', 'Left-handed and walks with a cane. About 6\'4". Brown eyes and hair.', 'Usama Bin Laden is wanted in connection with the August 7, 1998, bombings of the United States Embassies in Dar es Salaam, Tanzania, and Nairobi, Kenya. These attacks killed over 200 people. In addition, Bin Laden is a suspect in other terrorist attacks t', 'USA001', 'laden.jpg', 'caught');
INSERT INTO `ist_fugitives` VALUES (16, 'Abdelkarin Al-Nasser', 'unknown', 'M', 'Saudi Arabian', 'Yemen', 'Brown eyes, black hair. 5\'8", about 170 pounds.', 'Abdelkarim Hussein Mohamed Al-Nasser has been indicted in the Eastern District of Virginia for the June 25, 1996, bombing of the Khobar Towers military housing complex in Dhahran, Kingdom of Saudi Arabia.', 'USA001', 'nasser.jpg', 'caught');
I have a page which shows all fugitives which have been apprehended and the details regarding their apprehension (as extracted from ist_caught). Personal information regarding the fugitive, such as their name and what they are wanted for, are taken from the ist_fugitives table.
The SQL I have constructed to do this (amongst various others I've tried) is:
Code:
SELECT ist_caught.felonID, ist_caught.wantedBy, ist_caught.caughtBy, ist_caught.date, ist_fugitives.felonName, ist_fugitives.felonImage FROM `ist_caught` INNER JOIN `ist_fugitives` ON ist_caught.felonID=ist_fugitives.felonID ORDER BY ist_caught.felonID DESC
The code I'm using to display this information is:
Code:
$DB->Query("SELECT ist_caught.felonID, ist_caught.wantedBy, ist_caught.caughtBy, ist_caught.date, ist_fugitives.felonName, ist_fugitives.felonImage FROM `ist_caught` INNER JOIN `ist_fugitives` ON ist_caught.felonID=ist_fugitives.felonID ORDER BY ist_caught.felonID DESC LIMIT 10");
if($DB->GetNumRows()!=0)
{
$rows = $DB->GetNumRows();
echo "<tr><td colspan=\"5\">$rows</td></tr>";
while($felonArray=$DB->GetArray())
{
$ID = &$felonArray[0];
$name = &$felonArray[4];
$wantedby = GetFullName($felonArray[1]);
$caughtby = GetFullName($felonArray[2]);
$image = &$felonArray[5];
$date = $felonArray[3];
echo "
<tr>
<td>
<font size=\"-1\">
<a href=\"./controlcentre.php?Session=$Session&ptl=viewfelon&felonID=$ID\">
$name
</a>
</font>
</td>
<td>
<font size=\"-1\">
$wantedby
</font>
</td>
<td>
<font size=\"-1\">
$caughtby
</font>
</td>
<td>
<font size=\"-1\">
$date
</font>
</td>
<td>
<font size=\"-1\">
<a href=\"$PREFIX/modules/viewphoto.mod.php?felonID='$ID'\" onclick=\"NewWindow(this.href,'name','300','300','yes');return false;\">Photograph</a>
</font>
</td>
</tr>
";
}
}
The data shown is correct, although only one row is there (should be two). An output of the number of rows also shows that there should be two.
This is the same code I'm using to display data from other database queries and I've had no other problem. The only significant difference is that this query contains a join.
I've looked through the MySQL documentation for the JOIN syntax, and after much trial and error this is as close to getting what I want as I can.
Does the problem lie in the SQL or the PHP? Any help appreciated
Linear Mode
