user3152011 user3152011 - 7 months ago 9
SQL Question

allowing users to edit in the mysql

I am having a problem with my code. This code is supposed to display a mysql database and let the user edit it so that their edits register in the mysql table. But for some reason the query is not working and I can't get it so that the user can edit into a mysql table.

<!DOCTYPE HTML>
<html>
<head>
<title><?php echo 'giggity'; ?></title>
</head>
<body>
<?php
$con = mysqli_connect('localhost', 'root', 'ankith12','Employees');
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "select * from Employ";
$query = mysqli_query($con,$sql);
echo "<table border ='1' style='height:90%;width:90%; position: absolute; top: 50; bottom:50; left: 0; right: 0;border:1px solid' align = 'center'>
<tr>
<th>Employee id</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Meetings Today</th>
<th>Sales</th>
<th>Comments</th>
</tr>";
?>
<form method = 'Post'>
<?php
$i = 1;
while( $row = mysqli_fetch_array($query) )
{
echo "<tr><td>". $row['employee_id'] . "<br><input type ='submit' name = 'Submit_$i' >". "</td>";
echo "<td>". $row['Firstname']. "<input type = 'textfield' name = 'first' >"."</td>";
echo "<td>". $row['Lastname']."<input type = 'textfield' name = 'second' >" . "</td>";
echo "<td>". $row['Meetings']."<input type = 'textfield' name = 'third' >". "</td>";
echo "<td>". $row['Sales']."<input type = 'textfield' name = 'fourth' >". "</td>";
echo "<td>". $row['Comments']."<input type = 'textfield' name = 'fifth' >". "</td></tr>";
$i++;
}
echo "</table>";
?>
<br>
<br>
<!-- Submit<br><input type ='submit' name = 'Submit' > -->
</form>
<?php

function alert($s){
echo "<script type = 'text/javascript'>alert(\"$s\");</script>";
}

// $i = 1
$con = mysqli_connect('localhost', 'root', 'ankith12','Employees');
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$query = "SELECT employee_id from Employ";
$qudey = mysqli_query($con,$query);
$rows= mysqli_fetch_assoc($qudey);
$dee = 1;
$easy = 0;
// $userfirst = $_POST['first'];
// $userlast = $_POST['second'];
// $usermeetings = $_POST['third'];
// $usersales = $_POST['fourth'];
// $usercomments = $_POST['fifth'];
foreach($rows as $i){
//alert($_POST["Submit_$dee"]);
if(isset($_POST["Submit_$dee"])) {
// alert("true");
$i = 1;
$userfirst = $_POST['first'];
$userlast = $_POST['second'];
$usermeetings = $_POST['third'];
$usersales = $_POST['fourth'];
$usercomments = $_POST['fifth'];
alert($userfirst);
if($userfirst !== ""){
$QueryA = "UPDATE Employ SET Firstname = $userfirst WHERE employee_id = $i";
mysqli_query($con,$QueryA);
alert($QueryA);
}
if($userlast !== "")
{
$QueryB = "UPDATE Employ SET Lastname = $userlast WHERE employee_id = $i";
mysqli_query($con,$QueryB);
}
if($usermeetings !== "")
{
$QueryC = "UPDATE Employ SET Meetings = $usermeetings WHERE employee_id = $i";
mysqli_query($con,$QueryC);
}
if($usersales !== "")
{
$QueryD = "UPDATE Employ SET Sales = $usersales WHERE employee_id = $i";
mysqli_query($con,$QueryD);
}
if($usersales !== "")
{
$QueryE = "UPDATE Employ SET Comments = $usercomments WHERE employee_id = $i";
mysqli_query($con,$QueryE);
}
//echo 'done';
}
// echo'done';
$easy++;
$dee = $dee + 1;
}
mysqli_close($con);
?>
</body>
</html>

Answer

@user3152011 Do you have more than 1 employee, if so your inputs are coming back as all blank unless you're trying to update the last employee's information because you're defining multiple inputs with the same name. Try var_dump($_POST)and see.

for example right now if you have 2 employees, you'll have 2 inputs both with same name like in <input type = 'textfield' name = 'first' > so when you submit if you submit the first employee your $_POST['first'] will be blank.

You can either put <form> inside your while loop so that each one is a separate form or look into using something like <input type = 'textfield' name = 'first[]' > so that they all come back as an array so you have $_POST['first'][0] or $_POST['first'][1] and so on.

Also, if you want users to edit field names (instead of printing out the value and then having a blank input with echo "<td>". $row['Firstname']. "<input type = 'textfield' name = 'first' >"."</td>) put that value right into the textfield by using echo "<td><input type = 'textfield' name = 'first' value='". $row['Firstname']."'>"."</td>, it'll be much friendlier. And since the values will be populated with values from the database you don't have to check to see if it's blank, you can always just run the UPDATE if it's submitted, if nothing changes it'll just update it with existing data which is no change.

And I am not sure why you're running the $query = "SELECT employee_id from Employ"; the second time.

Now, it looks like you're hardcoding to update WHERE employee_id = $i which is 1 in your case. You might want to pass the employee_id along with all your other fields using something like echo "<input type="hidden" name="employee_id" value = '".$row['employee_id']."'>"; This way when you submit the form, you'll have an employee_id available in $_POST['employee_id'] and just update that employee.

*** And don't forget to protect yourself from SQL Injections using http://ca1.php.net/mysqli_real_escape_string

You can try out the code below:

<!DOCTYPE HTML>
<html>
<head>
    <title><?php echo 'giggity'; ?></title>
</head>
<body>
<?php
function alert($s){
    echo "<script type = 'text/javascript'>alert(\"$s\");</script>";
}
    $con = mysqli_connect('localhost', 'root', 'ankith12','Employees');
        if (mysqli_connect_errno())
    {
         echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    //We'll try to update data first so that the query to display Employ is shown with fresh data
    if(isset($_POST["employee_id"])) {
        $useremployeeid = mysqli_real_escape_string($con,$_POST['employee_id']);
        $userfirst = mysqli_real_escape_string($con,$_POST['first']);
        $userlast =  mysqli_real_escape_string($con,$_POST['second']);
        $usermeetings =  mysqli_real_escape_string($con,$_POST['third']);
        $usersales =  mysqli_real_escape_string($con,$_POST['fourth']);
        $usercomments =  mysqli_real_escape_string($con,$_POST['fifth']);

        alert($userfirst);

        $QueryA = "UPDATE Employ SET Firstname = '$userfirst',
                                     Lastname = '$userlast',
                                     Meetings = '$usermeetings',
                                     Sales = '$usersales',
                                     Comments = '$usercomments'
                    WHERE employee_id = $useremployeeid";
        $query = mysqli_query($con,$QueryA);
        if (!$query){
             printf("Error: %s\n%s\n", mysqli_sqlstate($con),mysqli_error($con));
        }
    }

        $sql = "select * from Employ";
        $query = mysqli_query($con,$sql);
        if (!$query){
             printf("Error: %s\n%s\n", mysqli_sqlstate($con),mysqli_error($con));
        }
        echo "<table border ='1' style='height:90%;width:90%; position: absolute; top: 50; bottom:50; left: 0; right: 0;border:1px solid' align = 'center'>
            <tr>
            <th>Employee id</th>
            <th>Firstname</th>
            <th>Lastname</th>
            <th>Meetings Today</th>
            <th>Sales</th>
            <th>Comments</th> 
            </tr>";
$i = 1;
 while( $row = mysqli_fetch_array($query) )
{
    echo "<form method = 'Post'>";
    echo "<input type='hidden' name='employee_id' value='".$row['employee_id']."'>";
    echo "<tr><td>". $row['employee_id'] . "<br><input type ='submit' name = 'Submit_$i' >". "</td>";
    echo "<td><input type = 'textfield' name = 'first' value='". $row['Firstname']. "'>"."</td>";
    echo "<td><input type = 'textfield' name = 'second' value='". $row['Lastname']."'>" . "</td>";
    echo "<td><input type = 'textfield' name = 'third' value='". $row['Meetings']."'>". "</td>";
    echo "<td><input type = 'textfield' name = 'fourth' value='". $row['Sales']."'>". "</td>";
    echo "<td><input type = 'textfield' name = 'fifth' value='". $row['Comments']."'>". "</td></tr>";
    echo "</form>";
    $i++;
}
echo "</table>";
mysqli_close($con);
?>
</body>
</html>