Display data from relational MySQL on web page

[sheeksav]

I am new to PHP/MySQL so please forgive my inexperience.

 

I am building a site in which users will be able to access data held in a MySQL database, which is hosted on a remote server by a web host. I have been using phpMyAdmin to create database tables.

 

For standalone tables, I have had no trouble accessing the database and displaying the data (formatting in HTML/CSS). Here is an example of a page that works:

 

(following login credentials)

$query = "SELECT * FROM bars ORDER BY name";
$result = mysql_query($query);
            
               $num = mysql_num_rows($result);

                mysql_close();
                                                
               
               $i = 0;
               while ($i < $num) {
                  
                  $name = mysql_result($result, $i, "name");
                  $address = mysql_result($result, $i, "address");
                  $district = mysql_result($result, $i, "district");
                  $phone = mysql_result($result, $i, "phone");
                  $web = mysql_result($result, $i, "web");
                  
                  ?>
                  
                           
                            
                         <div class="post">
                            
                            <div class="info">
                              <h3><? echo $name ?></h3>                            
                        <? echo $address ?><br />
                                <? echo $district ?><br />
                               <? echo $phone ?><br />
                               <a href="http://<? echo $web ?>" target="_blank"><? echo $web ?></a><br />                          
                              <>
                                
                                <br class="clear" />
                                
                        <><!--post-->
                        
               <?php
               $i++;
               }                        
            ?>

 

 

For another page, I want to access anther table in the database. This table, however is not standalone. I have 4 tables related to one another. I have created these tables with engine type InnoDB.

 

 

The first table called "Beers" has the following fields:

 

 

id - primary key

brewery_id (foreign key)

style_id (foreign key)

cat_id (foreign key)

 

 

I then used phpMyAdmin to create a link between the ID field of each of the child tables and the corresponding parent table. I received no errors so I assume up to this point everything is set up correctly.

 

 

I am ultimately trying to get the all of the information from the related tables to display on my web page with HTML/CSS formatting. Here is the code I am using:

 

    <? 
				$query = "SELECT * FROM beers ORDER BY name";
				$result = mysql_query($query);
				$num = mysql_num_rows($result);





				mysql_close();


				$i = 0;
				while ($i < $num) {

					$name = mysql_result($result, $i, "name");
					$brewery_id = mysql_result($result, $i, "brewery_id");
					$cat_id = mysql_result($result, $i, "cat_id");
					$style_id = mysql_result($result, $i, "style_id");
					$abv = mysql_result($result, $i, "abv");



					?>

                        	
                            
                         <div class="post">
                                                      
                            <div class="info">
                        		<h2><? echo $name ?></h2>
                                <h3><? echo $brewery_id ?></h3>
                                 <br />
                                Category: <b><? echo $cat_id ?></b>  
                                Style: <b><? echo $style_id ?></b>  
                                ABV: <b><? echo $abv ?></b>                          	                      
                        		<>
                                
                                <br class="clear" />
                                
                        <><!--post-->
                        
				<?php
				$i++;
				}								
			?>

The names of each beer from the "Beer" parent table displays, but then it where I would like to see $brewery_id, $cat_id and $style_id , it is outputting the integer value from the parent field, not the corresponding value from the child fields.

 

 

Any assistance on this matter is greatly appreciated.

[fenway]

You're new -- so I've added the code tags for you -- but this is required as per the ToS.

 

In terms of your actual question, you need a join