1.Consider the following entities and their relationships Company (c_no, c_name, c_city, c_share_value) Person (p_no, p_name, p_city, p_ph_no) Relationship between Company and Person is many-to-many with descriptive attribute no_of_shares. Using the above database, write a PHP script to display person wise share details in tabular format.

Assignment: Practice Set Q.1 Solution

Consider the following entities and their relationships Company (c_no, c_name, c_city, c_share_value) Person (p_no, p_name, p_city, p_ph_no) Relationship between Company and Person is many-to-many with descriptive attribute no_of_shares. Using the above database, write a PHP script to display person wise share details in tabular format.

          <?php
  $conn=mysql_connect("localhost","root","");

  if(!$conn)
  {
    echo"Not connected to PHP My Admin,please check details again <br>";
  }

  $cd=mysql_query("create database companyshare");

  
  if(!$cd)
  {
    echo"Cannoct create db or already exist <br>";
  }

  $sd=mysql_select_db("companyshare");
  
  if(!$sd)
  {
    echo"Cannoct connect db<br>";
  }

  /*
  
  $=mysql_query("");

  
  if(!$)
  {
    echo" <br>";
  }
  
*/
  

//create tables
$ct1=mysql_query(" create table company
(                cno int not null primary key,
                cname varchar(40),
                ccity varchar(40),
                cphone int                
)                    ");

  
if(!$ct1)
{
  echo" Cannot create table 1 or already exist <br>";
}


$ct2=mysql_query(" create table person
(                pno int not null primary key,
                pname varchar(40),
                pcity varchar(40),
                pphone int                
)                    ");

  
if(!$ct2)
{
  echo" Cannot create table 2 or already exist <br>";
}


$ct3=mysql_query(" create table company_person
(
                      cno int not null references company(cno),
                      pno int not null references person(pno),
                      no_of_shares int not null
 )                   ");

  
if(!$ct3)
{
  echo" Cannot create 3 table or already exist <br>";
}

//insert record in all tables

for($i=1;$i<=5;$i++)
{
  $iq1=mysql_query("insert into company values($i,'company$i','city$i',0123456789)");
  if(!$iq1)
  {
    echo"Not inserted the data company : $i<br>";
  }
}


for($i=1;$i<=5;$i++)
{
  $iq2=mysql_query("insert into person values($i,'person$i','city$i',0123456789)");
  if(!$iq2)
  {
    echo"Not inserted the data person : $i<br>";
  }
}

for($i=1;$i<=5;$i++)
{
  for ($j=5; $j>=1 ; $j--) 
  {
      $rn=rand(1,100);
  $iq3=mysql_query("insert into company_person values($i,$j,$rn)");
  if(!$iq3)
  {
    echo"Not inserted the data cp : $i<br>";
  }

  }
}

  
$res=mysql_query("select c.cno , p.pno , cp.no_of_shares 
                    from company c , person p , company_person cp
                      where c.cno=cp.cno and p.pno=cp.pno ;
                  ");

  
if(!$res)
{
  echo"cannot fetch result please che query <br>";
}

while($result=mysql_fetch_array($res))
{
  echo"Company number: $result[0]<br>";
  echo"person number: $result[1]<br>";
  echo"Number of shares: $result[2]<br><br><br>";
}
?>