CODEWITHSUNDEEP
phpmysql
Colo
Colo

Reputation: 87

insert to database in php mysql

I am doing an insert into a database in php so that it submits more than one row as the information to be submitted is dynamically generated.problem is that it only submits the last data and ignores the rest.how do i do it?here is my code:

<?php
include('includes/conn.php');
$row="SELECT name,refNo,department FROM profile WHERE     department='$getid'";
$query=mysqli_query($conn,$row)  or die(mysqli_error($conn));
echo "<div class='col-md-4 col-md-offset-1' >";
echo "<table class='table table-hover table-striped table-bordered'>
<tr class='info'>
</tr>";
while($row=mysqli_fetch_array($query))
{
    $name=$row['name'];
    $job=$row['refNo'];
    echo "<tr>";
    echo "<td>".$row['name']."</td>";
    echo "<td>".$row['refNo']."</td>";
    echo "<form method='post' action='selection.php'>";
    echo "<td>"."<input type='text' name='essential' class='form-control' width='100%' required>"."</td>";
    echo "<td>"."<input type='text' name='desirable' class='form-control' width='100%' required>"."</td>";
    echo "<input type='hidden' name='name' value='".$name."'>";
    echo "<input type='hidden' name='job' value='".$job."'>";
    echo "</tr>";
    if(isset($_POST['submit']))
    {
        $name=$_POST['name'];
        $job=$_POST['job'];
        $essential=$_POST['essential'];
        $desirable=$_POST['desirable'];
        $insert="INSERT INTO shortlist(name,job,points) VALUES('$name','$job','$essential' + '$desirable')";
        $query=mysqli_query($conn,$insert) or die(mysqli_error($conn));
        if($query)
        {
            header("location:index.php");
        }
    }
}
echo "</table>";
echo "<input type='submit' class='btn btn-success' name='submit' value='Submit'>";
"</form>";
echo "</div>";
mysqli_close($conn);
?>

Upvotes: 0

Views: 126

Answers (5)

Kickstart
Kickstart

Reputation: 21513

Merging the replies from @EasonLuo and @AnkitArjaria, putting the fields out as arrays and doing a single insert after looping through the input:-

<?php
include('includes/conn.php');
echo "<div class='col-md-4 col-md-offset-1' >";
echo "<form method='post' action='selection.php'>";
echo "<table class='table table-hover table-striped table-bordered'>
<tr class='info'>
</tr>";
$values = array();
if(isset($_POST['submit']))
{
    foreach($_POST['name'] AS $key=>$value)
    {
        $name = mysqli_real_escape_string($conn, $_POST['name'][$key]);
        $job = mysqli_real_escape_string($conn, $_POST['job'][$key]);
        $essential = (int)$_POST['essential'][$key];
        $desirable =(int)$_POST['desirable'][$key];
        $values[] = "('$name','$job',".($essential + $desirable).")";
    }
    if (count($values) > 0)
    {
        $insert = "INSERT INTO shortlist(name,job,points) VALUES ".implode(', ', $values);
        $query = mysqli_query($conn,$insert) or die(mysqli_error($conn));
        if($query)
        {
            header("location:index.php");
        }
    }
}

$row = "SELECT name,refNo,department FROM profile WHERE department='$getid'";
$query = mysqli_query($conn,$row)  or die(mysqli_error($conn));
$cnt = 0;

while($row=mysqli_fetch_array($query))
{
    $cnt++;
    $name = $row['name'];
    $job = $row['refNo'];
    echo "<tr>";
    echo "<td>".$row['name']."</td>";
    echo "<td>".$row['refNo']."</td>";
    echo "<td>"."<input type='text' name='essential[$cnt]' class='form-control' width='100%' required>"."</td>";
    echo "<td>"."<input type='text' name='desirable[$cnt]' class='form-control' width='100%' required>"."</td>";
    echo "<input type='hidden' name='name[$cnt]' value='".$name."'>";
    echo "<input type='hidden' name='job[$cnt]' value='".$job."'>";
    echo "</tr>";
}
echo "</table>";
echo "<input type='submit' class='btn btn-success' name='submit' value='Submit'>";
"</form>";
echo "</div>";
mysqli_close($conn);
?>

Upvotes: 0

Eason.Luo
Eason.Luo

Reputation: 134

You should insert outside the while loop with one statement only. Try this one.

$query = mysqli_query ( $conn, $row ) or die ( mysqli_error ( $conn ) );
echo "<div class='col-md-4 col-md-offset-1' >";
echo "<table class='table table-hover table-striped table-bordered'>

<tr class='info'>

</tr>";
//insert outside while loop
$insert = "INSERT INTO shortlist(name,job,points) VALUES ";
$values = array();
while ( $row = mysqli_fetch_array ( $query ) ) {
    $name = $row ['name'];
    $job = $row ['refNo'];
    echo "<tr>";
    echo "<td>" . $row ['name'] . "</td>";
    echo "<td>" . $row ['refNo'] . "</td>";
    echo "<form method='post' action='selection.php'>";
    echo "<td>" . "<input type='text' name='essential' class='form-control' width='100%' required>" . "</td>";
    echo "<td>" . "<input type='text' name='desirable' class='form-control' width='100%' required>" . "</td>";
    echo "<input type='hidden' name='name' value='" . $name . "'>";
    echo "<input type='hidden' name='job' value='" . $job . "'>";
    echo "</tr>";
    if (isset ( $_POST ['submit'] )) {
        $name = $_POST ['name'];
        $job = $_POST ['job'];
        $essential = $_POST ['essential'];
        $desirable = $_POST ['desirable'];
        //fetch values
        $values[] = "('" . $name . "','" . $job . "','" . ($essential + $desirable) . "')";
        if ($query) {
            header ( "location:index.php" );
        }
    }
}
echo "</table>";
echo "<input type='submit' class='btn btn-success' name='submit' value='Submit'>";
"</form>";

echo "</div>";
// implode and append all the values, will generate a sql like 'insert into table_name (column_group) values (val_group_1),(val_group_2)'
$insert .= implode(',',$values); 
//insert multiple rows at one time
$query = mysqli_query ( $conn, $insert ) or die ( mysqli_error ( $conn ) );
mysqli_close ( $conn );
?>

Upvotes: 2

Ankit Arjaria
Ankit Arjaria

Reputation: 111

You are using simple name not array in your form input Your generated form looks like

<form method='post' action='selection.php'>
<input type='text' name='essential' >
<input type='text' name='desirable' >
</form>

So only last value is saved in the form input You should use array of names in input form like That

<form method='post' action='selection.php'>
<input type='text' name='essential[]' >
<input type='text' name='desirable[]' >
</form>

in this way all values will be saved in name array like i am showing essential array output

// printed output

Array (

[0] => First input value

[1] => Second input value

[2] => Third input value

) you can print whole posted array by print_r() function this will help you more in storing multiple record

Upvotes: 1

Logan Wayne
Logan Wayne

Reputation: 5991

Note:

  • If you want to pursue the logic you want with your code, you can just remove the if(!$query) condition. If your query is true, it will go to index.php and will not be able to continue the loop. So only the first data will be inserted.
  • If you still want the user to be redirect to index.php after the insert query, you can try this:

Code:

...END OF YOUR INSERT AND WHILE LOOP HERE...

if(isset($_POST["submit"])){
  header("LOCATION:index.php");
}

Upvotes: 0

user4904106
user4904106

Reputation:

You are try to seperate two value of column using + . It is use as

  $essential=$_POST['essential'];
  $desirable=$_POST['desirable'];
  $val=$essential+$desirable;
  $insert="INSERT INTO shortlist(`name`,`job`,`points`) VALUES('".$name."','".$job."','".$val."')";

Upvotes: 0

Related Questions