Need script to access several tables having 1 common field, all other fields have different names and number of fields per table is different

Question

I am using several tables that have 1 common field ($cust_id) all the other fields have different names and the number of fields per table varies.

example:

table 1   id, cust_id,     t_car,    d_car,   t_fuel,   d_fuel
table 2   id, cust_id,     t_door,  d_door, t_floor, d_floor, t_window, d_window, t_wall, d_wall 

I want to use a single script to access the tables individually (no join capability). Also I want to take the t_ and use it to perform other code and at the same time take the remaining field name and using it as a title.

//$s_table = "table 1";
$s_table = "table 2";

// ------------------------------------------------------------- get table field names
$sql = "
     SHOW COLUMNS 
     FROM $s_table
     ";

$res= $conn->query($sql);   
$x=1;
while($row = $res->fetch_assoc()){
    $columns[$x] = $row['Field'];
    
    $headings = ($columns);
    $head .=  $x . "- " .$headings[$x] . '<br />';
     $field_name[$x] = $headings[$x] ;
    $x++;
}

$x=$x-1;



for ($cnt = 1; $cnt <= $x; $cnt++) {
     $heading = $headings[$cnt] ;

     $get_fields .= "&#36;field$cnt =  &#36;row['$heading']; ";

} // close for loop



$sql= "
     SELECT *
    FROM  $s_table
     WHERE cust_id='$cust_id'
     ";

$result = $conn->query($sql);   

while($row = $result->fetch_assoc()) {
     $get_fields;
     
// if I echo $get_fields it looks good but then if I echo $field3 I get nothing.  I believe this is where I am having problems.  
     

     for ($cnt = 1; $cnt <= $x; $cnt++) {

          $heading = $headings[$cnt];
          $field=   $field_name[$cnt];
     }
     
$field=   $field_name[$cnt];
               $field_length    = strlen($heading);
               $field_prefix     =  substr($heading, 0, 2);
               $field_name_a = substr($heading, 2, $field_length);
               $category         =  ucfirst($field_name_a);


          $heading_sub = substr($heading, 0, 1);


          if($field_prefix = "t_" ){     // only the last "t_" is shown.
               if(!$heading) {
                    $field_name_a .= "<button class='ok' name='buy' value='$heading '>$category </button>";
               } else  if($heading >= 85) {
                   $field_name_a .= "already bought - $category"; 
               } else { 
                    $field_name_a  .= "&#10060; <button class='red' name='buy' value='$heading '>$category
                     </button>";
               }
     }

//} // close for loop

} // close while loop



$page_content = "
$field_name_a
";
?>

Answer 1

As long as the tables are in the same database you can use join statements. If the tables are in separate databases, you will need to establish separate connections and then use join statements.