how to display data from two tables in php

Question

I have two tables in a database, sight_country and sightseeing. I am inserting the ID of the country field from the sight_country table to s_country field of the table sightseeing. In php I am showing country field values from sight_country in a CSS drop-down menubar.

the code is

<li class="menu-item-has-children"><a href="#">Sightseeing</a>
         <ul class="sub-menu">
            <?php

                 $qry_st = "select * from `sight_country` limit 5";
                 $rec_st = mysql_query($qry_st );
                 if( mysql_num_rows($rec_st) > 0)
                     {
                     while($res_st = mysql_fetch_array($rec_st))
                    {
                   echo "<li><a href='sightseeing.php?id=$res_st[id]'>".$res_st['country']."</a></li>";
                  }
                   } 
                    ?> 
          </ul>
        </li>

When click on link of county value then I am showing all sightseeing data from the table sightseeing in php page.

the code is

$sql = "select * from `sightseeing` where `s_country` ='$id'";
$res = mysql_query($sql);
$rec = mysql_fetch_array($res);

the country may have two or more related sightseeing data, so I am displaying sightseeing titles from the sightseeing table in a sidebar menu in my PHP page.

the code is

<ul class="st_lnks">
          <?php

                 $qry_st = "select * from `sightseeing` where s_country = '$id'";

                 $rec_st = mysql_query($qry_st );
                 if( mysql_num_rows($rec_st) > 0)
                     {
                     while($res_st = mysql_fetch_array($rec_st))
                    {
                   echo "<li><a href='sightseeing.php?id=$res_st[s_country]'>".$res_st['stitle']."</a></li>";
                  }
                   } 
                    ?> 
      </ul>

when I click link of stitle I want to show it's related sightseeing data in same page. How it can be done?

`related sightseeing data` what is this? – u_mulder Dec 20 '15 at 15:28 — u_mulder, Dec 20 '15 at 15:28

score 0 · Answer 1 · answered Dec 20 '15 at 15:53

You could give your second link a second parameter. Give the first parameter a unique name

<ul class="sub-menu">
<?php
$qry_st = "select * from `sight_country` limit 5";
$rec_st = mysql_query($qry_st );
if ( mysql_num_rows($rec_st) > 0) {
    while ($res_st = mysql_fetch_array($rec_st)) {
        echo "<li><a href='sightseeing.php?idCountry=$res_st[id]'>".$res_st['country']."</a></li>";
    }
} 
?> 
</ul>

Then add a second parameter when generating the second link:

<ul class="st_lnks">
<?php
$qry_st = "select * from `sightseeing` where s_country = '$id'";
$rec_st = mysql_query($qry_st );
if ( mysql_num_rows($rec_st) > 0) {
    while($res_st = mysql_fetch_array($rec_st)) {
        echo "<li><a href='sightseeing.php?idCountry=$idCountry&idSightseeing=res_st['id']'>".$res_st['stitle']."</a></li>";
    }
}
?> 
</ul>

score 0 · Accepted Answer · edited May 23 '17 at 12:07

I am assuming that;

The whole script is on one page (sightseeing.php), which varies depending on any GET variables (variables in the URL).
Originally the page just displays the first menu. Then when u click a country, you are sent again to sightseeing.php. Now also with ?id=* which shows also a second list, containing the list of sightseeing relevant to the country selected.
You have a field called 'id' in your sightseeing table that has the unique sightseeing id.

To now additionally show details of the sightseeing selected (clicked by user); Modify the links in the second list. rather than:

echo "<li><a href='sightseeing.php?id=$res_st[s_country]'>".$res_st['stitle']."</a></li>";

Write:

echo "<li><a href='sightseeing.php?id=$res_st[s_country]&ss_id=$res_st[id]'>".$res_st['stitle']."</a></li>";

Now when u click one of the links and are sent back to to sightseeing.php you will also have another get variable GET['ss_id'] (which has the id of the sightseeing that you want to view). You can use this variable to pull the relevant details of the sightseeing.

$sightSeeingId = $_GET['ss_id'];
$sql3 = "select * from `sightseeing` where `id` ='$sightSeeingId' LIMIT 1";
$res3 = mysql_query($sql3);
$sightSeeingData = mysql_fetch_array($res3);

check that it has data and print it out

if(!$res3) die(mysql_error());              
if(mysql_num_rows($res3) > 0){
echo "Sight Seeing id:" . $sightSeeingData['id'];
}

As a side note you should be aware that mysql_* functions are outdated and your code is vunerable to sql injection, see here;

GET parameters vulnerable to SQL Injection - PHP

how to display data from two tables in php

2 Answers2