pumaf1 Posted November 28, 2008 Share Posted November 28, 2008 Hi, I wonder if someone could help me. I have two tables within the same database. one called infobase and one called flags. I am displaying data from the infobase table on a page ( http://www.pumaf1.org.uk/drivera.php ) and I would like to have an image for the drivers nationality shown. I know how to actually get the image from the database from within one single table. I have fields in infobase and flags both called "nat". for example both contain a value of "Great Britain". What i want to do is on http://www.pumaf1.org.uk/drivera.php is to show the flag by the drivers name, but to do this I am unsure of how to do it. (I am doing this to purely save on disc space and a field on each record filled with <img src = "http://www.pumaf1.org.uk/images/flagico/britain.png" alt="Great Britain"> ) any help greatly taken regards dave Quote Link to comment Share on other sites More sharing options...
fenway Posted November 28, 2008 Share Posted November 28, 2008 Sounds like you need a join... post your table structure. Quote Link to comment Share on other sites More sharing options...
pumaf1 Posted November 28, 2008 Author Share Posted November 28, 2008 Heres the table structures: Server: localhost Database: web16-results -- phpMyAdmin SQL Dump -- version 2.11.4 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Nov 28, 2008 at 03:23 PM -- Server version: 5.0.67 -- PHP Version: 4.4.8 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `web16-results` -- -- -------------------------------------------------------- -- -- Table structure for table `flags` -- CREATE TABLE `flags` ( `id` int(11) NOT NULL auto_increment, `Country` text NOT NULL, `nat` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -------------------------------------------------------- -- -- Table structure for table `infobase` -- CREATE TABLE `infobase` ( `id` int(7) NOT NULL, `year` text NOT NULL, `date` text NOT NULL, `event` text NOT NULL, `round` text NOT NULL, `country` text NOT NULL, `track` text NOT NULL, `pos` text NOT NULL, `points` int(2) NOT NULL, `name` text NOT NULL, `nat` text NOT NULL, `team` text NOT NULL, `engine` text NOT NULL, `Chassis` text NOT NULL, `tyres` text NOT NULL, `laps` text NOT NULL, `racetime` text NOT NULL, `class` text NOT NULL, `reason` text NOT NULL, `grid` text NOT NULL, `weather` text NOT NULL, `inputted` text NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Quote Link to comment Share on other sites More sharing options...
fenway Posted November 28, 2008 Share Posted November 28, 2008 Yikes! Why is everything of type TEXT? And why don't you store the flag_id in the infobase table? Quote Link to comment Share on other sites More sharing options...
pumaf1 Posted November 29, 2008 Author Share Posted November 29, 2008 because i am a newbie and its not being stored in the infobase table due to the fact i would have <img src = "http://www.pumaf1.org.uk/images/flagico/britain.png" alt="Great Britain"> or simalr over 1000 times in the table. :-\ Quote Link to comment Share on other sites More sharing options...
fenway Posted December 1, 2008 Share Posted December 1, 2008 because i am a newbie and its not being stored in the infobase table due to the fact i would have <img src = "http://www.pumaf1.org.uk/images/flagico/britain.png" alt="Great Britain"> or simalr over 1000 times in the table. :-\ No, you'd have flag_id = 1 a thousand times in your table... Quote Link to comment Share on other sites More sharing options...
pumaf1 Posted December 3, 2008 Author Share Posted December 3, 2008 ahh ok i misunderstood you, how do i get the page which pulls the infobase data out to connect to the flags table? Quote Link to comment Share on other sites More sharing options...
fenway Posted December 4, 2008 Share Posted December 4, 2008 With a join on flag_id. Quote Link to comment Share on other sites More sharing options...
pumaf1 Posted December 11, 2008 Author Share Posted December 11, 2008 Hi, Sorry about this but I am having problems getting this to work - can I have some further assistance please? I have attached my page code below: you can see the page at http://www.pumaf1.org.uk/drivera.php knid regards <html> <?php // listing script // connect to the server mysql_connect( 'localhost', 'web16-results', '*******' ) or die( "Error! Could not connect to database: " . mysql_error() ); // select the database mysql_select_db( 'web16-results' ) or die( "Error! Could not select the database: " . mysql_error() ); // retrieve all the rows from the database $query = "SELECT * FROM infobase JOIN `flags` ON infobase.nat = flags.nat"; $query = "SELECT DISTINCT name, nat FROM infobase WHERE name LIKE 'a%' ORDER BY name asc"; $results = mysql_query( $query ); // print out the results if( $results ) { ?> <body bgcolor="#FFFFFF" text="#000000" link="#0000FF" vlink="#0000FF" alink="#0000FF" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0"><div align="center"> </div> <div align="center"><font size="2" face="verdana"> <? include 'header.php' ?> </font> </div> <div align="center"> <table width="770" border="0" align="center" cellpadding="0" cellspacing="0"> <tr> <td height="57" valign="top"> <p> </p> <table width="516" border="0" align="center" cellpadding="0" cellspacing="0"> <tr> <td bgcolor="#FFFFFF"><div align="center"><font color="#0000FF" face="verdana"><strong>"A"</strong></font></div></td> </tr> <tr> <td height="1" bgcolor="#CCCCCC"></td> </tr> </table> <table width="516" border="0" align="center" cellpadding="1" cellspacing="1"> <?php while( $contact = mysql_fetch_object( $results ) ) { // print out the info $id = $contact -> id; $name = $contact -> name; $nat = $contact -> nat; $year = $contact -> year; $flag = $contact -> flag; ?> <tr> <td width="31%"><div align="center"> <p><font size="1" face="verdana"><? echo( "$name" ) ;?></font></p> </div></td> <td width="33%"><font size="1" face="verdana"><? echo( "$nat" ) ;?><? echo( "$flag" ) ;?></font></td> <td width="18%"><font size="1" face="verdana"><?php echo( "<a HREF='driveralld.php?name=$name'>Results<br></a>" );?></font></td> <td width="18%"><font size="1" face="verdana"><?php echo( "<a HREF='driverallp.php?name=$name'>Print Version<br></a>" );?></font></td> </tr> <tr> <td colspan="4" height="1" bgcolor="#CCCCCC"></td> </tr> <? } } else { die( "Sorry we are having a database problem, please contact admin and report the following error : " . mysql_error() ); } ?> </table> <p> </p> <div align="center"><a href="javascript:history.go(-1)" class="style1"><font size="2" face="verdana">Back</font></a> </div></td> </tr> </table> <font size="2" face="verdana"> <? include 'footer.php' ?> </font></div> </body> </html> Quote Link to comment Share on other sites More sharing options...
fenway Posted December 11, 2008 Share Posted December 11, 2008 I don't need to see the page or the code... just the query that you're currently running. Quote Link to comment Share on other sites More sharing options...
pumaf1 Posted December 11, 2008 Author Share Posted December 11, 2008 // retrieve all the rows from the database $query = "SELECT * FROM infobase JOIN `flags` ON infobase.nat = flags.nat"; $query = "SELECT DISTINCT name, nat FROM infobase WHERE name LIKE 'a%' ORDER BY name asc"; $results = mysql_query( $query ); thats what gets the code from the database <?php while( $contact = mysql_fetch_object( $results ) ) { // print out the info $id = $contact -> id; $name = $contact -> name; $nat = $contact -> nat; $year = $contact -> year; $flag = $contact -> flag; ?> <tr> <td width="31%"><div align="center"> <p><font size="1" face="verdana"><? echo( "$name" ) ;?></font></p> </div></td> <td width="33%"><font size="1" face="verdana"><? echo( "$nat" ) ;?><? echo( "$flag" ) ;?></font></td> <td width="18%"><font size="1" face="verdana"><?php echo( "<a HREF='driveralld.php?name=$name'>Results<br></a>" );?></font></td> <td width="18%"><font size="1" face="verdana"><?php echo( "<a HREF='driverallp.php?name=$name'>Print Version<br></a>" );?></font></td> thats how its printed to the page. thanks Quote Link to comment Share on other sites More sharing options...
fenway Posted December 11, 2008 Share Posted December 11, 2008 Why two queries? Same variable? What are you trying to do again? Quote Link to comment Share on other sites More sharing options...
pumaf1 Posted December 11, 2008 Author Share Posted December 11, 2008 i am trying to store all of the images for some flags in a table called "flags" eg: id - 1 nat - Great Britian flag - <img src = "http://www.pumaf1.org.uk/img/flagico/britain.png" alt="Great Britain"> what i have on the screen at the minute is the words "great britain" (taken from the infobase table) printed what I would like to to link the two database tables via the one page so that the image for the drivers nationality is shown instead of the words. in the case this page http://www.pumaf1.org.uk/drivera.php, taking the united states driver - i would want it to pick out the following record from the flags table (and only display the flag img) id - 2 nat - united states flag - <img src = "http://www.pumaf1.org.uk/img/flagico/united_states.png" alt="United States"> does that help? thanks Quote Link to comment Share on other sites More sharing options...
fenway Posted December 12, 2008 Share Posted December 12, 2008 I don't see a flag field anywhere in your tables... and storing full paths and html like that is going to get you into trouble. Quote Link to comment Share on other sites More sharing options...
pumaf1 Posted December 12, 2008 Author Share Posted December 12, 2008 ojk should be read as country not flag - wghoops also why am i going to get into trouble storing that data? sorry for all of the questions Quote Link to comment Share on other sites More sharing options...
fenway Posted December 15, 2008 Share Posted December 15, 2008 I don't understand your clarification. And you don't store full paths because they can change ... same with html. Store the important piece -- filename -- and deal with everything else in template code. Quote Link to comment Share on other sites More sharing options...
pumaf1 Posted December 15, 2008 Author Share Posted December 15, 2008 wellt he lcioations of the files wont change - i know that much the two tables both have a field called country. on the main table - infobase - it displays text. what i want to be able to do is to connect the two tables by that field (country) and instead of the text being shown i would like it to show the countries flag d Quote Link to comment Share on other sites More sharing options...
fenway Posted December 15, 2008 Share Posted December 15, 2008 Even if you think the files locations won't change, don't force your application into that decision... and the html has to come out regardless. You'll thank me later. As for the JOIN, it sounds like you'll need to join on the country field... but it's very bad form to use a text field for this... you really need to include a foriegn key to the other table. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.