Page 1 of 1

query issue

Posted: Sat Feb 22, 2003 6:14 am
by cgildenhuys
I have 3 tables, the first is joined to the second and the second to the third. I pass an id thru the url and according get info from table 1, that is joined to table 2 and so on. Table 2 store imageIds in an array since each entry can have one or more images. Image info is in table 3. I somehow managed to make the code work but not all my rows in the second table shows up, only 4(there are 6). All the fields in the db is filled out correctly. The code :

<?php
// display event, this is table one
$sql = "SELECT * FROM broadkast,broadkastLoc WHERE broadkast.broadkast_id = broadkastLoc.event_id AND broadkast.broadkast_id ='$id' ORDER BY broadkast.broadkast_id ASC";
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
$myrow = mysql_fetch_array($result);
?>
<span class='medium'><b><?php echo $myrow['event']; ?></b></span>
<p><?php echo $myrow['introEng']; ?></p>

<table width='600' border='0' cellspacing='0' cellpadding='0'>
<?php
// display all locations for the event, this is table 2
$sql = "SELECT * FROM broadkastLoc,images WHERE broadkastLoc.imageId = images.id_images AND broadkastLoc.event_id ='$id' ORDER BY broadkastLoc.broadkastLoc_id ASC";
$result2 = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
while($myrow2 = mysql_fetch_array($result2)) {
echo "<tr><td valign='top'>";
?>
<b><?php echo $myrow2['name']; ?></b><br />
<?php echo $myrow2['descripEng']; ?>
</td>
<td>

<?php
$images = explode(",",$myrow2['imageId']);
foreach($images as $value) {
$myrow2['id_images'] = $value;
// pull id info from images after getting ind ids from array
// table 3
$sql = "SELECT * FROM images WHERE id_images ='$value'";

$result3 = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
while($myrow3 = mysql_fetch_array($result3)) {
?>
<img src="../images/<?php echo $myrow3['thumbUrl']; ?>" alt="<?php echo $myrow3['alt']; ?>" width="85" height="85" border="0" / ><br /><br />

<?php } }
?>
<?php echo "</td></tr>";
} ?>
</table>

thanks

Posted: Sun Feb 23, 2003 3:35 am
by josa
It's hard to tell if it's the SQL queries or the PHP code that causes the problem since I don't know anything about the data in the database. You could try adding...

Code: Select all

echo mysql_num_rows($result2)
...after the second query. If this prints 4 then the query returns only four rows which indicate that there is something wrong with the query or the data.

/josa

query issue

Posted: Sun Feb 23, 2003 4:44 am
by cgildenhuys
I tried "echo mysql_num_rows($result2);" and it still returned 4 rows. So therefore the problem with the query or the db data.
The datatype for the broadkastLoc.imageId field is SET because in the future there might be more than one image per location. For the moment there will only be one image so I tried changing the datatype to INT in the db, the result is that it changed all the imageId's I had plus giving ones to the first and last entry that does not yet excist in the image table. Which might explain the problem. It seems the query is treating the SET field as INT in the first and last row and therefore the imageIds don't excist and since I'm not using a left join, not showing up. Strange though that once I changed the datatype and of course all imageIds changed but before at least the 4 rows that were coming up had the correct imageIds.