CODEWITHSUNDEEP
phphtmlmysqledit
user3152011
user3152011

Reputation: 61

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>

Upvotes: 3

Views: 481

Answers (4)

Tin Tran
Tin Tran

Reputation: 6202

@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 https://www.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>

Upvotes: 2

Elliptical view
Elliptical view

Reputation: 3782

How to help yourself:

There is way too much going on in your code to make a simple test, or frankly to have anyone here take the time to help you with it.

Make a copy of your code above and set it a side for now. Then take an ax to your code and take out everything that absolutely isn't needed. Take it down to about 3 lines of code.

1) Just a connect and display any error message.  
2) Create a simple query string with one field and one table.
3) And then run that one line and get the error message back.

BTW, can you edit your MySQL using MySQL Query Browser? If not sort that out first.

You must learn to break problems in half and to simplify things when they aren't working. You can't really expect other people to do that work for you. It is your job as a programmer to learn to tease out details by testing little things at a time. Then as you get the little things working, you can add them together to build big things. I've taken programs that were 1000 lines of code long and reduced them to 10 lines to figure out a problem at times (not often, thankfully, but sometimes). For example, I had a bug many years ago where a random byte in memory was getting changed, intermittently. It was in a fairly big project and I at first didn't have any idea of where to look for it. But by a process of removing things to simplify the system I was able to little by little sort out where it was coming from. It turned out to be a register that was getting stomped on by a timer once a second in a random way.

Tip: I often use the undo/redo feature in Notepad++ for this cutting because then once I've found my problem it's easy to restore the text to what it was. First I mark and copy the fix. Then undo. Then paste the fix in at the end. It's easy to cut 1000 lines into 10 and then bring it back to 1000 when you are done. Sometimes I go forward or back as much as 30 or 100 edits. It's just easier than commenting things out to delete them, and later undo the delete.

Upvotes: 0

rohit john
rohit john

Reputation: 3

hi you can use the below code instead of repeating the same query for each field

$query=mysql_query("UPDATE Employ SET Firstname = '$userfirst',Lastname = '$userlast',Meetings = '$usermeetings',Sales = '$usersales',Comments = '$usercomments'WHERE employee_id ='$i'") or die(mysql_error)

hope this will work for you

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You have update statements like this:

UPDATE Employ SET Firstname = $userfirst WHERE employee_id = $i

Most people's names are strings of letters. Consider, for instance, if employee 1 had the name "Gordon":

UPDATE Employ SET Firstname = Gordon WHERE employee_id = 1

Can you see what is wrong with this query? Hint: SQL does not recognize "Gordon" as being anything. You need single quotes to delimit the value:

UPDATE Employ SET Firstname = '$userfirst' WHERE employee_id = $i

When you are writing code that does variable substituion and you have a problem, the first step should always be to print out the string after variable substitution.

Upvotes: 2

Related Questions