I'm using PHP and need to Insert into sql using a while loop

Question

I'm after a little help. I have a page for a user to input upto 10 different rows of information. Dispatch details. I have created a page with my form using a loop..

<?php
  session_start();
  require("config.php");
  require("header.php");
  $db = mysql_connect($dbhost, $dbuser, $dbpassword);
  mysql_select_db($dbdatabase, $db);    
?>
<br><br><br></br>
<form action="insertdispatch.php"  method="post">
  <body>
    <center>
      <table>
        <tr>
          <td><center><b>Ref</td>
          <td><b><center>Date</td>
          <td><b><center>Service</td>
          <td><b>   <center>Tracking</td>
        </tr>
<?php 
  $index = 1;
  $name = 1;
  while($index <= 10){
?>
         <td><input type="text" 
                   name="transno<?php echo $index;?>" 
                   id="transno<?php echo     $index;?>" />
         </td>
     <td><input type="text" name="date<?php echo $index;?>" 
               id="date<?php echo $index;?> "/>
         </td>
     <td><select name = "service<?php echo $index;?>"><?php
    $viewsql = "SELECT * FROM dispatch_service ORDER BY service ASC";
            $viewresult = mysql_query($viewsql);
            while($row = mysql_fetch_assoc($viewresult)){
        ?> <option value=<?php echo $row['service'] ;?>> 
                    <?php echo  $row['service'] ;?></option>
        <?php
            }
              echo "</select>";?>
      <td><input type="text" 
                     name="tracking<?php echo $index;?>" 
                     id="tracking<?php echo  $index;?>"/>
          </td>
      </tr>
      <?php $index ++;
    }?>
         <center>
       <td><input type="submit" value="Add Product" />
     </form>
     </center>
   </td>
 </tr>
 </table>
 </center>
 <center><a href='javascript:history.back(1);'>Back</a>
 </body>
 </html>`

I have 10 of each text box, the name of the text box adds the value of index to the end. (with my limited coding experience I am very pleased with myself) so I go to the insertdispatch.php page and the plan is to insert each of these values into my table... now...I have no clue... and I cannot seem to figure out how I am going to do this...

I think I will need to use a loop again.. but I can't seem to figure out how I am going to call each of the $_POST values. I don't really want to use 10 different insert statements, as the form may increase in size. here is what I have so far..

<?php

session_start();
require("config.php");

$db = mysql_connect("localhost","root","");
if (!$db)
{
do_error("Could not connect to the server");
}


mysql_select_db("hbt",$db)or do_error("Could not connect to the database");

$index = 1;
while($index <= 10){

$insertsql = "INSERT into dispatch (trans_no, date, service, tracking) values     ()";
mysql_query($insertsql);

 $index ++;
  }
//header("Location: " . $config_basedir . "home.php");
?>

I am not looking for anyone to finish the coding for me, but any tips would be grateful! :)

the mysql_* functions are depricated, switch to mysqli_* or PDO — NDM
– NDM, Commented Sep 12, 2013 at 13:40
If you want a hint - var_dump($_POST) in your second file will show you all the fields that your form is sending. — andrewsi
– andrewsi, Commented Sep 12, 2013 at 13:41

NDM · Accepted Answer · 2013-09-12 13:43:39Z

3

you can build 1 insert statement that inserts multiple rows:

INSERT into dispatch (trans_no, date, service, tracking) values
   (1, '2013-09-12', 'myService1', 'on'),
   (1, '2013-09-12', 'myService2', 'on'),
   (1, '2013-09-12', 'myService3', 'on'),
   (1, '2013-09-12', 'myService4', 'on'),
   (1, '2013-09-12', 'myService5', 'on');

Just build this inside your the while, and execute it after the while has finished.

To build this query, you will need to perform the exact same loop as when you are generating the HTML, but now just fetch the values from $_POST instead of create a html field for them...

note while building your HTML, you are firing a static query inside your for loop. since this query is static, the results will also not change, and it is best to execute that query outside of the outer while loop.

answered Sep 12, 2013 at 13:43

NDM

6,8483 gold badges42 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bdf · Accepted Answer · 2013-09-12 14:23:32Z

1

You've already set up your while loop with $index - you could simply use that to iterate through the POST values, since you set their name attribute with an index. Consider:

$index = 1;
while($index <= 10){
    $trans_no = $_POST["transno$index"];
    $service = $_POST["service$index"];
    $date = $_POST["date$index"];
    $tracking = $_POST["tracking$index"];

    $insertsql = "INSERT into dispatch (trans_no, date, service, tracking)
                  VALUES($trans_no, $date, $service, $tracking)";
    mysql_query($insertsql);

 $index++;}

Though it would be much cleaner to set up your form inputs as arrays, as noted by others here.

Also, please read up on SQL injection. You need to sanitize any user input before it's inserted into a database - otherwise a malign user could wipe your whole database.

edited Sep 12, 2013 at 14:23

answered Sep 12, 2013 at 13:59

bdf

2471 silver badge8 bronze badges

2 Comments

bdf Over a year ago

One more thing: Don't use mysql_connect. It's deprecated as of PHP 5.5. Check the docs at the PHP docs site and use mysqli_connect instead.

user2772806 Over a year ago

Thank you for your help. I have heard about mysqli_connect. I have just finished a very basic 'beginners' to php/sql course so I am looking to improve where I can. So many thanks

symcbean · Accepted Answer · 2013-09-12 13:51:01Z

1

(you really should read up more on basic HTML - tehre are lots of mistakes there even before considering the PHP code).

name="transno<?php echo $index;?>"

This is really messy too - you are creating extra work and complication for yourself. Use arrays:

name="transno[]"

If you do exlpicitly want to reference the item again then set the index:

id="transno[<?php echo $index; ?>]"

And at the receiving end....use a single insert statement to add the rows - not 10 seperate ones (it will be much faster).

answered Sep 12, 2013 at 13:51

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

1 Comment

user2772806 Over a year ago

Thanks for the input. I will look into arrays, I know they are much cleaner to use, but don't really know much about them.

Collectives™ on Stack Overflow

I'm using PHP and need to Insert into sql using a while loop

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related