Basic insertion in the database

PHP
1

Hi,

i have a registration form (screenshot attached). I create two table to store them, one customer_master, send item_details. but i want to know how to get values of these (item) textboxes to insert into the database.
Here is the code by which these fields are generating.

<?php
require_once("config.php");
$query_for_result=mysql_query("SELECT * FROM mytable ORDER BY id ASC");
$num=mysql_numrows($query_for_result);


mysql_close();
?>
<table> <tr>
<td >Sl.No..</td>
<td >Item</td>
<td >Paper</td>
<td >Finish</td>
<td>Price</td>
<td >Vat</td>
<td >Total Amount</td>

</tr>

<?php
$i=0;
while ($i < $num)
{

$f1=mysql_result($query_for_result,$i,"id");
$f2=mysql_result($query_for_result,$i,"album_name");
$f3=mysql_result($query_for_result,$i,"paper_used");
$f4=mysql_result($query_for_result,$i,"finishing");
$f5=mysql_result($query_for_result,$i,"price");
$f6=mysql_result($query_for_result,$i,"vat");
$f7=mysql_result($query_for_result,$i,"total_amt");
$id=$i+1;
?>
<tr>
<td><input id="slno" name="security_code" type="text" value="<?php echo $f1; ?>" style="width:20px;"/></td>
<td ><input id="security_code" name="security_code" value="<?php echo $f2; ?>" type="text" style="width:150px;"/></td>
<td ><input id="security_code" name="security_code" type="text" value="<?php echo $f3; ?>" style="width:85px;"/></td>
<td ><input id="security_code" name="security_code" value="<?php echo $f4; ?>" type="text" style="width:90px;"/></td>
<td ><input id="security_code" value="<?php echo $f5; ?>" name="security_code" type="text" style="width:25px;"/></td>
<td ><input id="security_code" value="<?php echo $f6; ?>" name="security_code" type="text" style="width:35px;"/></td>
<td ><input id="security_code" name="security_code" value="<?php echo $f7; ?>" type="text" style="width:50px;"/></td>


</tr>
<?php
$i++;

}


?>
<tr>
<td ><input id="security_code" name="security_code" type="text" value="26" style="width:20px;"/></td>
<td ><input id="security_code" name="security_code" value="" type="text" style="width:150px;"/></td>
<td ><input id="security_code" name="security_code" type="text" value="" style="width:85px;"/></td>
<td ><input id="security_code" name="security_code" value="" type="text" style="width:90px;"/></td>
<td ><input id="security_code" value="" name="security_code" type="text" style="width:25px;"/></td>
<td ><input id="security_code" value="" name="security_code" type="text" style="width:35px;"/></td>
<td ><input id="security_code" name="security_code" value="" type="text" style="width:50px;"/></td>


</tr>
<tr>
<td ><input id="security_code" name="security_code" value="27" type="text" style="width:20px;"/></td>
<td ><input id="security_code" name="security_code" value="" type="text" style="width:150px;"/></td>
<td ><input id="security_code" name="security_code" type="text" value="" style="width:85px;"/></td>
<td ><input id="security_code" name="security_code" value="" type="text" style="width:90px;"/></td>
<td ><input id="security_code" value="" name="security_code" type="text" style="width:25px;"/></td>
<td ><input id="security_code" value="" name="security_code" type="text" style="width:35px;"/></td>
<td><input id="security_code" name="security_code" value="" type="text" style="width:50px;"/></td>


</tr>
<tr>
<td ><input id="security_code" name="security_code" value="28" type="text" style="width:20px;"/></td>
<td ><input id="security_code" name="security_code" value="" type="text" style="width:150px;"/></td>
<td><input id="security_code" name="security_code" type="text" value="" style="width:85px;"/></td>
<td ><input id="security_code" name="security_code" value="" type="text" style="width:90px;"/></td>
<td ><input id="security_code" value="" name="security_code" type="text" style="width:25px;"/></td>
<td><input id="security_code" value="" name="security_code" type="text" style="width:35px;"/></td>
<td ><input id="security_code" name="security_code" value="" type="text" style="width:50px;"/></td>


</tr>
<tr>
<td ><input id="security_code" name="security_code" value="29" type="text" style="width:20px;"/></td>
<td><input id="security_code" name="security_code" value="" type="text" style="width:150px;"/></td>
<td><input id="security_code" name="security_code" type="text" value="" style="width:85px;"/></td>
<td ><input id="security_code" name="security_code" value="" type="text" style="width:90px;"/></td>
<td ><input id="security_code" value="" name="security_code" type="text" style="width:25px;"/></td>
<td ><input id="security_code" value="" name="security_code" type="text" style="width:35px;"/></td>
<td><input id="security_code" name="security_code" value="" type="text" style="width:50px;"/></td>


</tr>
</table>

I want to store data of all 29 text boxes. but don’t know how to get values of these ?
Thanks

2

You need to give each field a unique name (like name=“field1”) then you can use PHP to grab that field by $fied = $_POST[‘field1’]; etc.

3

$f1=mysql_result($query_for_result,$i,“id”);
$f2=mysql_result($query_for_result,$i,“album_name”);
$f3=mysql_result($query_for_result,$i,“paper_used”);
$f4=mysql_result($query_for_result,$i,“finishing”);
$f5=mysql_result($query_for_result,$i,“price”);
$f6=mysql_result($query_for_result,$i,“vat”);
$f7=mysql_result($query_for_result,$i,“total_amt”);
$id=$i+1;
?>
friv 2

4

I would start again with something simple as you have quite a few problems and you should be using pdo or Mysqli not MySQL. MySQL is being depreciated and if you carry on with MySQL you will have to change it at some point so you might as well get it sorted now.

Your table wants to be in a form so you can submit the details to some php code; either on the same page or another page in the format of $field = $_POST[‘field1’]; or $field = $_GET[‘field1’]; depending on your form method as Patche said.

5 
<?php

// Firstly connect to the MySQL server using PDO (Replace the dbname, $user and $pass with the appropriate values

try {
    $db = $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    //$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::NULL_EMPTY_STRING);
}
catch (Exception $e) {
    echo 'Caught exception: ',  $e->getMessage(), "\
";
}

$sql="
    SELECT
          id
        , album_name
        , paper_used
        , finishing
        , price
        , vat
        , total_amt
    FROM
        mytable
    ORDER BY
        id ASC
";
$stmt = $db->prepare($sql);
$stmt->execute();
$albums = $stmt->fetchAll(PDO::FETCH_ASSOC);

?>
<form method='post' action=''>
    <fieldset><legend>Please enter your order details</legend>
<table>
    <tr>
        <td >Sl.No..</td>
        <td >Item</td>
        <td >Paper</td>
        <td >Finish</td>
        <td>Price</td>
        <td >Vat</td>
        <td >Total Amount</td>
    </tr>
<?php

foreach ($album AS $album ) {
    echo "
        <tr>
            <td><label for='Sl_No'>Sl.No:</label><input type='text' name='Sl_No[]' id='Sl_No' value='{$album['id']}'/></td>
            <td><label for='item'>Item:</label><input type='text' name='item[]' id='item' value='{$album['album_name']}'/></td>
            <td><label for='paper'>Paper:</label><input type='text' name='paper[]' id='paper' value='{$album['paper_used']}'/></td>
            <td><label for='finish'>Finish:</label><input type='text' name='finish[]' id='finishusername' value='{$album['finishng']}'/></td>
            <td><label for='price'>Price:</label><input type='text' name='price[]' id='price' value='{$album['price']}'/></td>
            <td><label for='vat'>VAT:</label><input type='text' name='vat[]' id='vat' value='{$album['vat']}'/></td>
            <td><label for='total_amount'>Total Amount:</label><input type='text' name='total_amount[]' id='username' value='{$album['total_amt']}'/></td>
        </tr>
    ";
}
?>
<input type='submit' value='Place Order'/>
</fieldset>
</form>

<?php

// to make the submitted date workable in the script dealing with the order

$ordered=count($_POST['Sl_No']);
$count=0;
$items_ordered = array();
while ( $count <> $ordered ) {
    $items_ordered[$count]['Sl_No'] = $_POST['Sl_No'][$count];
    $items_ordered[$count]['item'] = $_POST['item'][$count];
    $items_ordered[$count]['paper'] = $_POST['paper'][$count];
    $items_ordered[$count]['finish'] = $_POST['finish'][$count];
    $items_ordered[$count]['price'] = $_POST['price'][$count];
    $items_ordered[$count]['vat'] = $_POST['vat'][$count];
    $items_ordered[$count]['total_amount'] = $_POST['total_amount'][$count];
}

// When dealing with each item ordered, sanitize it and make use of prepared statements and bound parameters if using in a database query

?>

Code hasn’t been tested.

btw, what @Rubble; means is that there are three “PHP extensions” which can be used to access a MySQL server:

  • MySQL (mysql_*)
  • MySQL Improved (mysqli_*)
  • PDO

The mysql_* extension has been deprecated as of the current version of PHP and will very likely be removed from the next version of PHP, breaking any script which uses it. You should now be using either mysqli_* or PDO (PDO is more preferable as it’s more database server independent and allows the use of named parameters).

6
7

If you notice in SpacePhoenix’s example, the form field names are arrays, i.e. name=‘Sl_No’. See the brackets? You need to do the same or each will override the last. These empty will then become array KEYs when POST is made with values starting with ZERO, e.g. $_POST[‘Sl_No’][0], $_POST[‘Sl_No’][1] etc. You would then use a WHILE loop, as in his example or a foreach statement to grab info from these arrays. BTW, Just reposting your OP doesn’t cut it. If you don’t understand something, ask a specific question.

8

There is one typo in SpacePhoenix’s example in that it should be foreach ($albums AS $album ) { other than that his example should work fine. You also can deal with POST values directly within that while (or foreach loop) instead of building another array to deal with.

I guess the old mysql way of doing it would be something like this. (I like using foreach to get actual keys from post).

&lt;?php
foreach($_POST['Sl_No'] as $k =&gt; $Sl_No){
	if(!empty($Sl_No)){
	    $Sl_No        = mysql_real_escape_string(trim($_POST['Sl_No'][$k]));
	    $album_name   = mysql_real_escape_string(trim($_POST['item'][$k]));
	    $paper_used   = mysql_real_escape_string(trim($_POST['paper'][$k]));
	    $finishing    = mysql_real_escape_string(trim($_POST['finish'][$k]));
	    $price        = mysql_real_escape_string(trim($_POST['price'][$k]));
	    $vat          = mysql_real_escape_string(trim($_POST['vat'][$k]));
	    $total_amount = mysql_real_escape_string(trim($_POST['total_amount'][$k]));
	    
	    $values[] = "('$Sl_No','$album_name','$paper_used','$finishing','$price','$vat','$total_amount')";
	} 
}    
$sql = "INSERT INTO myordertable 
    (`Sl_No`,`album_name`,`paper_used`,`finishing`,`price`,`vat`,`total_amount`)
    values ";
    $sql .= implode(",",$values);
    mysql_query($sql) or die(mysql_error());    
?&gt;
9

hi,
I change my code accordingly but this show error, going to else part
here is my code…

<?php
require_once("config.php");
$query_for_result=mysqli_query($con,"SELECT * FROM items ORDER BY id ASC");
$num=mysqli_num_rows($query_for_result);
mysqli_close($con);

?>
<table width="50%" align="center" border="0" cellspacing="0" cellpadding="5" style="font-family:Verdana, Arial, Helvetica, sans-serif; background:#87bb2f;  -moz-border-radius: 14px; border-radius: 14px; font-size:12px; color:#000000; border:solid #d6d4d5; border-width:1px; margin-top:-1px;">  <tr>
<td valign="top" style="font-size:11px; background:#87bb2f; font-weight:bold;" align="center" >Sl.No..</td>
    <td valign="top" style="font-size:11px; background:#87bb2f; font-weight:bold;" align="center" >Item</td>
    <td valign="top" style="font-size:11px; background:#87bb2f; font-weight:bold;" align="center" >Paper</td>
    <td valign="top" style="font-size:11px; background:#87bb2f; font-weight:bold;" align="center" >Finish</td>
    <td valign="top" style="font-size:11px; background:#87bb2f; font-weight:bold;" align="center" >Price</td>
    <td valign="top" style="font-size:11px; background:#87bb2f; font-weight:bold;" align="center" >Vat</td>
    <td valign="top" style="font-size:11px; background:#87bb2f; font-weight:bold;" align="center" >Total Amount</td>
   
  </tr>

<?php

$i=0;

while($row = mysqli_fetch_assoc($query_for_result)) 
{
$f1=$row['id'];
$f2=$row['album_name'];
$f3=$row['paper_used'];
$f4=$row['finishing'];
$f5=$row['price'];
$f6=$row['vat'];
$f7=$row['total_amt'];
 
$id=$i+1;

?>
   <tr>
   <td valign="top" style="font-size:11px; " align="center" ><input id="slno" name="slno[]" type="text" value="<?php echo $f1; ?>" style="width:20px;"/></td>
   <td valign="top" style="font-size:11px; " align="center" ><input id="album_name" name="album_name[]" value="<?php echo $f2; ?>" type="text" style="width:150px;"/></td>
   <td valign="top" style="font-size:11px; " align="center" ><input id="paper_used" name="paper_used[]" type="text" value="<?php echo $f3; ?>" style="width:85px;"/></td>
   <td valign="top" style="font-size:11px; " align="center" ><input id="finishing" name="finishing[]"  value="<?php echo $f4; ?>" type="text" style="width:90px;"/></td>
   <td valign="top" style="font-size:11px; " align="center" ><input id="price"  value="<?php echo $f5; ?>" name="price[]" type="text" style="width:25px;"/></td>
   <td valign="top" style="font-size:11px; " align="center" ><input id="vat"  value="<?php echo $f6; ?>" name="vat[]" type="text" style="width:35px;"/></td>
   <td valign="top" style="font-size:11px; " align="center" ><input id="total_amt" name="total_amt[]"  value="<?php echo $f7; ?>" type="text" style="width:50px;"/></td>
    
   
  </tr>
  <?php
$i++;

}


?>

</table>

 <?php
  
   if(isset($_POST['submit']))
   {
  require_once("config.php");
foreach($_POST['slno'] as $k => $Sl_No)
{
    if(!empty($Sl_No))
    {
     $customer_id="1";
$customer_name="Mr.Rajesh";
$customer_user_name="rkr";
 $date=date('d-m-Y');
$time=date('h:m:i:A');
        $Sl_No        = mysqli_real_escape_string(trim($_POST['slno'][$k]));
        $album_name   = mysqli_real_escape_string(trim($_POST['album_name'][$k]));
        $paper_used   = mysqli_real_escape_string(trim($_POST['paper_used'][$k]));
        $finishing    = mysqli_real_escape_string(trim($_POST['finishing'][$k]));
        $price        = mysqli_real_escape_string(trim($_POST['price'][$k]));
        $vat          = mysqli_real_escape_string(trim($_POST['vat'][$k]));
        $total_amount = mysqli_real_escape_string(trim($_POST['total_amt'][$k]));
        
        $values[] = "('$customer_id','$customer_name','$customer_user_name','$Sl_No','$album_name','$paper_used','$finishing','$price','$vat','$total_amount','$date','$time')";
    } 
}  
$sql = "INSERT INTO mytable
    (customer_id, customer_name, customer_user_name, sl_no, album_name, paper, finish, price, vat, total_amt, date, time)
    values ";
    $sql .= implode(",",$values);
    mysqli_query($con,$sql) or die(mysqli_connect_error());    
}
else
{
echo "error";
}  



?>
10

I’m not seeing any UNMATCHED brackets but if you indent your code with a tab for every bracket set, you’ll see your “else” is with the if(isset($_POST[‘submit’])) line so if NOT if(isset($_POST[‘submit’])) it’s going to say “error”.

<?php

if(isset($_POST['submit']))
{
	require_once("config.php");
	foreach($_POST['slno'] as $k => $Sl_No)
	{
		if(!empty($Sl_No))
		{
			$customer_id="1";
			$customer_name="Mr.Rajesh";
			$customer_user_name="rkr";
			$date=date('d-m-Y');
			$time=date('h:m:i:A');
			$Sl_No = mysqli_real_escape_string(trim($_POST['slno'][$k]));
			$album_name = mysqli_real_escape_string(trim($_POST['album_name'][$k]));
			$paper_used = mysqli_real_escape_string(trim($_POST['paper_used'][$k]));
			$finishing = mysqli_real_escape_string(trim($_POST['finishing'][$k]));
			$price = mysqli_real_escape_string(trim($_POST['price'][$k]));
			$vat = mysqli_real_escape_string(trim($_POST['vat'][$k]));
			$total_amount = mysqli_real_escape_string(trim($_POST['total_amt'][$k]));
			
			$values[] = "('$customer_id','$customer_name','$customer_user_name','$Sl_No','$album_name','$paper_used','$finishing','$price','$vat','$total_amount','$date','$time')";
		}
	}
	$sql = "INSERT INTO mytable
	(customer_id, customer_name, customer_user_name, sl_no, album_name, paper, finish, price, vat, total_amt, date, time)
	values ";
	$sql .= implode(",",$values);
	mysqli_query($con,$sql) or die(mysqli_connect_error());
}
else
{
echo "error";
}
?>
11

Hi,
It still going into the else part.
{
echo “error”;
}
showing error.

thanks

12 

 $sql = "INSERT INTO mytable
    (customer_id, customer_name, customer_user_name, sl_no, album_name, paper, finish, price, vat, total_amt, date, time)
    values ";
    $sql .= implode(",",$values);

Can you please add after that:

echo "The query is: $sql";

and post the output of that?

13

I didn’t fix the “error” display, only pointed out why you seeing “error” display. What is not clear is why you want to show “error”. Is every loop in you form table required to be filled out? It was my understanding that only particular items in the form would be filled out and so in our processing foreach loop we only use the ones that are NOT empty so that whole "}else{ section should be removed like this.

 <?php

if(isset($_POST['submit']))
{
    require_once("config.php");
    foreach($_POST['slno'] as $k => $Sl_No)
    {
        if(!empty($Sl_No))
        {
            $customer_id="1";
            $customer_name="Mr.Rajesh";
            $customer_user_name="rkr";
            $date=date('d-m-Y');
            $time=date('h:m:i:A');
            $Sl_No = mysqli_real_escape_string(trim($_POST['slno'][$k]));
            $album_name = mysqli_real_escape_string(trim($_POST['album_name'][$k]));
            $paper_used = mysqli_real_escape_string(trim($_POST['paper_used'][$k]));
            $finishing = mysqli_real_escape_string(trim($_POST['finishing'][$k]));
            $price = mysqli_real_escape_string(trim($_POST['price'][$k]));
            $vat = mysqli_real_escape_string(trim($_POST['vat'][$k]));
            $total_amount = mysqli_real_escape_string(trim($_POST['total_amt'][$k]));

            $values[] = "('$customer_id','$customer_name','$customer_user_name','$Sl_No','$album_name','$paper_used','$finishing','$price','$vat','$total_amount','$date','$time')";
        }
    }
    $sql = "INSERT INTO mytable
    (customer_id, customer_name, customer_user_name, sl_no, album_name, paper, finish, price, vat, total_amt, date, time)
    values ";
    $sql .= implode(",",$values);
    mysqli_query($con,$sql) or die(mysqli_connect_error());
}
?>

If for some reason you are requiring all form fields, then you are going to need to set what I like to call a “flag” variable within the foreach loop and then add a separate IF statement to look for the flag. There are several way to do this but I will set a variable as empty and add “true” if missing data, then look for empty.

<?php
if(isset($_POST['submit']))
{
    require_once("config.php");	
	$error = "";
    foreach($_POST['slno'] as $k => $Sl_No)
    {
        if(!empty($Sl_No))
        {
            $customer_id="1";
            $customer_name="Mr.Rajesh";
            $customer_user_name="rkr";
            $date=date('d-m-Y');
            $time=date('h:m:i:A');
            $Sl_No = mysqli_real_escape_string(trim($_POST['slno'][$k]));
            $album_name = mysqli_real_escape_string(trim($_POST['album_name'][$k]));
            $paper_used = mysqli_real_escape_string(trim($_POST['paper_used'][$k]));
            $finishing = mysqli_real_escape_string(trim($_POST['finishing'][$k]));
            $price = mysqli_real_escape_string(trim($_POST['price'][$k]));
            $vat = mysqli_real_escape_string(trim($_POST['vat'][$k]));
            $total_amount = mysqli_real_escape_string(trim($_POST['total_amt'][$k]));

            $values[] = "('$customer_id','$customer_name','$customer_user_name','$Sl_No','$album_name','$paper_used','$finishing','$price','$vat','$total_amount','$date','$time')";
        }
		else
		{
		$error .= "true";
		}
    }
	
	if(empty($error)){
	    $sql = "INSERT INTO mytable
	    (customer_id, customer_name, customer_user_name, sl_no, album_name, paper, finish, price, vat, total_amt, date, time)
	    values ";
	    $sql .= implode(",",$values);
	    mysqli_query($con,$sql) or die(mysqli_connect_error());	
	}
	else
	{
	echo "error";
	}
}
?>