i have three tables (link provided below) and I have a main index page where the user chooses the category. and what i want is that all the advertisers links and images based on that category appear. its simple. but at the same time difficult.
difficult because they way i have my database setup, i can have one link for many different images. and thus i would like to use some sort of random function that picks out of all the images associated with that single link, one particular image. If you look at my db struture you will realize this
CREATE TABLE category (
category_name varchar(100) NOT NULL default '',
PRIMARY KEY (category_name)
) TYPE=MyISAM;
CREATE TABLE imageinfo (
imageinfo_name varchar(60) NOT NULL default '',
imageinfo_location varchar(60) NOT NULL default 'images/',
imageinfo_title varchar(60) NOT NULL default '',
linkinfo_url varchar(100) NOT NULL default '',
PRIMARY KEY (imageinfo_name)
) TYPE=MyISAM;
CREATE TABLE linkinfo (
linkinfo_url varchar(100) NOT NULL default '',
category_name varchar(100) NOT NULL default '0',
linkinfo_description text NOT NULL,
PRIMARY KEY (linkinfo_url)
) TYPE=MyISAM;
<?php
//database related informaiton.
require ('configure.php');
require ('database.php');
require ('database_tables.php');
$link=db_connect() or die ('Some error occured in connecting to the database');
$catname = $HTTP_GET_VARS['category'] ;
$query= "SELECT *
FROM " . TABLE_CATEGORY . ", " . TABLE_LINKINFO . ", " . TABLE_IMAGEINFO.
" WHERE ". TABLE_CATEGORY . ".category_name = " . TABLE_LINKINFO. ".category_name AND " .
TABLE_LINKINFO . ".linkinfo_url = " . TABLE_IMAGEINFO . ".linkinfo_url AND " .
TABLE_CATEGORY . ".category_name = '$catname'
order by " . TABLE_CATEGORY . ".category_name, " . TABLE_LINKINFO . ".linkinfo_url";
$result = db_query($query);
?>
<TABLE cellSpacing=0 cellPadding=0 width="98%" border=0>
<TBODY>
<TR>
<TD vAlign=top width="74%"> <br>
<?php // here we start using the recordset $result
$row = mysql_fetch_array( $result );
while ( $row ) { // start of outer while
$this_linkinfo = array();
$linkinfo = $row['linkinfo_url'];
while ( $linkinfo == $row['linkinfo_url'] ) { // start inner while
$this_linkinfo[] = $row;
$row = mysql_fetch_array( $result );
} // end of inner while
$chosen_row = array_rand( $this_linkinfo ); // randomly chose one of the rows
?>
<TABLE class=default cellSpacing=0 cellPadding=1 width="97%" align=center bgColor=white border=0>
<TBODY>
<TR>
<TD width="470" height="67" align=left vAlign=top class=middleCellText>
<a href="<?php echo $this_linkinfo; ?>" >
<img src="<?php echo $chosen_row['imageinfo_location'].$row['imageinfo_name']; ?>"></a>
</TD>
<TD class=middleCellText vAlign=top width="246">
<?php echo $chosen_row['linkinfo_description']; ?>
</td>
</TR>
</TBODY>
</TABLE>
<?php
} // end of the outer while
?>
</TD>
</TR></TBODY></TABLE></FIELDSET>
</DIV>
</BODY>
</HTML>
and that produced :
<TABLE class=default cellSpacing=0 cellPadding=1 width="97%" align=center bgColor=white border=0>
<TBODY>
<TR>
<TD width="470" height="67" align=left vAlign=top class=middleCellText>
<a href="Array" >
<img src=""></a>
<TD class=middleCellText vAlign=top width="246">
</td>
</TR>
</TBODY>
</TABLE>
How can i fix this ?
to reiterate waht im trying to do.
based on a category name that i have (catname), pull all the results from the category table that match this catname (only one row will be pulled).
now pull all the links from the linkinfo table that match this catname.
for each link that was just pulled, pull every image associated with this link.
if more than one image is pulled for any one link, than randomly choose one of the images.
now display the results as shown above.
thanks.
