mysql - how to display data from two tables in php

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

Sightseeing

         

            
                 $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 "
".$res_st['country']."
";

                  }
                   } 
                    ?> 
          
        

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

          
                 $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 "
".$res_st['stitle']."
";
                  }
                   } 
                    ?> 
      

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

Answer

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 "
".$res_st['stitle']."
";

Write:

echo "
".$res_st['stitle']."
";

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