Fetch multiple rows from one table and Store to another table in php Mysql

Question

I want to fetch multiple row data from one table and on users selection, store only the chosen row into another table.

The problem with this code is that data from table 'FLIGHTS' is fetching accurately from database but when I am trying to store it into another table 'bookFlight' it is storing only NULL values for all columns. Want help!

<?php

$username = "root"; 
$password = ""; 
$hostname = "localhost"; 
$dbhandle = mysql_connect($hostname, $username, $password)  
            or die("Unable to connect to MySQL");
$selected = mysql_select_db("dbtest",$dbhandle)
            or die("Could not select dbtest");

session_start();

         ////// STORING DATA INTO TABLE 'bookFlight' ////////

 if(isset($_POST['Submit']))
{
         $company_name   = mysql_real_escape_string($_POST['company_name']);
         $flight_category= mysql_real_escape_string($_POST['flight_category']);
         $rates           = mysql_real_escape_string($_POST['rates']);
         $qry    = "INSERT INTO bookFlight"."(company_name,flight_category,rates)". 
                  "VALUES('$company_name','$flight_category','$rates')"; 

    $retval = mysql_query( $qry, $dbhandle );
    if(! $retval ) {
           die('<br><br> Could not enter data: ' . mysql_error());
        }
      else  {  echo "Entered data successfully\n";   }               
}
              ////// FETCHING DATA FROM TABLE 'flights' ////////

$sql = "SELECT * FROM flights where type_id = 
                                      (SELECT type_id FROM tour WHERE city = 
                                     '{$_SESSION['destination_Address']}')";

$myData = mysql_query($sql,$dbhandle);
$num = mysql_num_rows($myData);

  echo "<table border=1>
  <tr>
  <th> COMPANY NAME :  </th>
  <th> FLIGHT CATEGORY : </th>
  <th> RATES :  </th>
  </tr>";

 for ($i=0; $i <$num; $i++)
{
$record = mysql_fetch_array($myData,MYSQL_ASSOC); 
echo "<form method=post>";
  echo "<tr>";
  echo "<td>" . $company_name[]= $record['company_name']      ."</td>";
  echo "<td>" . $flight_category[]= $record['flight_category']."</td>";
  echo "<td>" . $rates[]= $record['rates']                    ." </td>";
  echo "<td>" . "<input type='submit' name='Submit' value='SELECT'></td>";
  echo "</tr>";  
}
echo"</table>";
echo "</form>";

?>

**WARNING**: If you're just learning PHP, please, do not learn the obsolete [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface. It's awful and has been removed in PHP 7. A replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) and a guide like [PHP The Right Way](http://www.phptherightway.com/) helps explain best practices. Make **sure** your user parameters are [properly escaped](http://bobby-tables.com/php) or you will end up with severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Aug 31 '17 at 16:18
Defining your query outside of the function call is also a habit you need to break. It's all too easy to pass in the wrong string as an argument, especially if you're juggling things like `$sql1`, `$sql2` and so on. Supplying the query string directly to the function completely eliminates any ambiguity. — tadman, Aug 31 '17 at 16:19

Rajdeep Paul · Accepted Answer · 2017-08-31T16:37:53.133

That's because you are not sending anything except submit with your form, and that's why $_POST array has nothing except submit value.

You need to pass the row id(I'm assuming id column in this case) along with the form data so that you could insert that particular row in bookFlight table.

for ($i=0; $i <$num; $i++){
    $record = mysql_fetch_array($myData,MYSQL_ASSOC); 
    echo "<form action='?id=".$record['id']."' method='post'>";
    ...
}

And once the user hits the submit button, this is how you can INSERT the data.

if(isset($_POST['Submit'])){
    $qry = "INSERT INTO bookFlight (company_name, flight_category, rates) SELECT company_name, flight_category, rates FROM flights WHERE id = '".$_GET['id']."'";
    $retval = mysql_query( $qry, $dbhandle );
    if(! $retval ) {
        die('<br><br> Could not enter data: ' . mysql_error());
    }else {  
        echo "Entered data successfully\n";   
    }               
}

Sidenote: Don't use mysql_* functions, they are deprecated as of PHP 5.5 and are removed altogether in PHP 7.0. Use mysqli or pdo instead. And this is why you shouldn't use mysql_* functions.

alright thanks it is working.. but now the error arising is that it is storing for only WHERE id= 1 and not for other ids . what do i do now ? — Sabah, Sep 01 '17 at 04:37
@Sabah Can you please elaborate what do you mean by *it is storing for only WHERE id= 1 and not for other ids*? Also, you have to move this closing statement `echo "";` inside the `for` loop. — Rajdeep Paul, Sep 01 '17 at 05:22
@Sabah Welcome, glad I could help! :-) Please *accept* the answer if it resolved your issue. [How to accept answer on Stack Overflow?](https://meta.stackexchange.com/a/5235) — Rajdeep Paul, Sep 01 '17 at 06:17

Fetch multiple rows from one table and Store to another table in php Mysql

1 Answers1