Attendance form help part 4

Question

SpottyBlue 0 Junior Poster in Training

8 Years Ago

Hi,

I need help with the attendance form. I’ve changed the coding in the attendance form (index.php). When I tried to update the first row, nothing happened. For the last row, the message says, "We couldn’t update the attendance form at this time."

Please check lines 96 and 101 to see if got any error.

Download the attendance form system in zip file: https://drive.google.com/open?id=0B07_pOHhTox3TGRNWVZlckVuT3c
Inside there is the database table called attendance.sql. You need xampp and you must import the table (attendance.sql) in phpmyadmin.

mysql php

3 Contributors
8 Replies
245 Views
2 Days Discussion Span
Latest Post 8 Years Ago Latest Post by SpottyBlue

RudyM

8 Years Ago

In your index.php, you do the following check:

if (!empty($id) && !empty($present) && !empty($late))

But I'm not seeing where $id is being set.

Edited 8 Years Ago by RudyM because: Correction.

cereal 1,524 Nearly a Senior Poster

8 Years Ago

In addition to RudyM's comment, you're mixing mysql_ functions with mysqli_ functions. The formers are deprecated in PHP 5.* and removed in PHP 7.*, which is the latest stable PHP version.

So, you have to fix your queries at lines 99 and 102 and you have to fix the escaping, consider using prepared statements. Also, the return statement at line 17 of the connect file, will prevent the execution of the following code in the file, in practice mysqli_close($link) at line 19 is not executed, which in this case is fine, otherwise the queries on the main file would not work.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

SpottyBlue 0 Junior Poster in Training · Answer 1 · 2016-05-10T15:06:07+00:00

SpottyBlue 0 Junior Poster in Training

8 Years Ago

Hello? Anyone can help me?

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 2 · 2016-05-10T20:59:25+00:00

Hi, can you paste just the relevant code? People here can answer also from mobile and cannot setup a database and run your scripts. Also, if the download link becomes unavailable, the thread becomes useless for the readers.

SpottyBlue 0 Junior Poster in Training · Answer 3 · 2016-05-11T02:07:38+00:00

Here's the code (index.php):

<!DOCTYPE html>

<?php
require 'connect2.inc.php';
?>

<html>
<head>
    <title>Attendance Form</title>
    <link rel="stylesheet" href="http://bootswatch.com/paper/bootstrap.min.css">
    <style type="text/css">

    </style>
</head>

<body>
    <nav class="navbar navbar-inverse">
    <div class="container-fluid">
        <div class="navbar-header">
          <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-2">
            <span class="sr-only">Toggle navigation</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          </button>
          <a class="navbar-brand" href="index.php">Mindspace Attendance form</a>
        </div>

        <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-2">
            <ul class="nav navbar-nav">
                <li><a href="register.php">Register student</a></li>
                </li>
            </ul>
        </div>
    </div>
    </nav>

    <div class="row vertical-center-row" id="form-container">
        <div class="col-md-10 col-md-offset-1" style="background:white">
            <form action="index.php" id="Stu_ATT" class="form-horizontal" method="POST" action="$_SERVER['PHP_SELF']">
                <fieldset>
                <legend>Attendance</legend>

                <table class="table table-striped table-hover">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Student name</th>
                            <th>Present</th>
                            <th>Late</th>
                            <th>Time in</th>
                            <th>Time out</th>
                            <th>Remarks</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php while($row = mysqli_fetch_array($result))
                    {
                    ?>
                        <tr>
                            <td><?php echo $row['id']; ?></td>
                            <td><?php echo $row['Sname']; ?></td>
                            <td><input type="checkbox" name="present" id="present"<?php echo $row['present'] > 0 ? ' checked':'';?>></td>
                            <td><input type="checkbox" name="late" id="late"<?php echo $row['late'] > 0 ? ' checked':'';?>></td>
                            <td><input type="time" class="form-control" name="timeIn" id="timeIn" value="<?php echo $row['timeIn']; ?>"></td>
                            <td><input type="time" class="form-control" name="timeOut" id="timeOut" value="<?php echo $row['timeOut']; ?>"></td>
                            <td><input type="text" class="form-control" id="remarks" name="remarks" value="<?php echo $row['remarks']; ?>"></td>
                        </tr>
                    <?php } ?>
                    </tbody>
                </table>

                <div class="form-group">
                    <input type="submit" class="btn btn-primary" value="Submit">
                </div>

                </fieldset>
            </form>
        </div>
    </div>
</nav>
</body>
</html>

<?php

if (isset($_POST['present']) && isset($_POST['late']) && isset($_POST['timeIn']) && isset($_POST['timeOut']) && isset($_POST['remarks']))
{
    $present = $_POST['present'];
    $late = $_POST['late'];
    $remarks = $_POST['remarks'];
    $timeIn = $_POST['timeIn'];
    $timeOut = $_POST['timeOut'];

    // Start updating process
    if (!empty($id))
    {
        $query = "SELECT * FROM `attendance` WHERE `id`='$id'";
        $query_run = mysql_query($query);

        $query = "UPDATE `attendance` SET `present`= '".mysql_real_escape_string($present)."',`late`= '".mysql_real_escape_string($late)."', `timeIn`= '".mysql_real_escape_string($timeIn).";, `timeOut`= '".mysql_real_escape_string($timeOut).";, `remarks`= '".mysql_real_escape_string($remarks)."' WHERE `id` = '$id' ";
        if ($query_run = mysql_query($query))
        {
            echo 'Updated successfully.';
        }
        else
        {
            echo 'Sorry, we couldn\'t update at this time. Try again later.';
        }
    }
    else
    {
        echo 'We couldn\'t update the attendance form at this time.';
    }
}
?>

connect2.inc.php

<?php
$conn_error = 'Could not connect.';

$mysql_host = 'localhost';
$mysql_user = 'root';
$mysql_pass = '';

$mysql_db = 'attendance';

$link = mysqli_connect($mysql_host, $mysql_user, $mysql_pass);

if ($link) 
{
    mysqli_select_db($link, $mysql_db);
    $result = mysqli_query($link,"SELECT * FROM `attendance`");

    return $result;

    mysqli_close($link);
}
else
{
    die($conn_error);
    echo 'Failed';
}
?>

SpottyBlue 0 Junior Poster in Training · Answer 4 · 2016-05-12T02:32:11+00:00

It's no use to fix by myself. Can help me fix the code?

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 5 · 2016-05-12T05:58:32+00:00

Ok, first of all: your UPDATE query had ; after timeIn and timeOut columns, due I think, to a copy & paste:

`timeIn`= '".mysql_real_escape_string($timeIn).";,
`timeOut`= '".mysql_real_escape_string($timeOut).";,

It would raise an error, even by using mysql_ functions.
To convert your UPDATE query to prepared statements you can do like this:

$query = "UPDATE `attendance` SET `present`= ?,`late`= ?, `timeIn`= ?, `timeOut`= ?, `remarks`= ? WHERE `id` = ?";

$stmt = mysqli_prepare($link, $query);
mysqli_stmt_bind_param($stmt, 'sssssi', $present, $late, $timeIn, $timeOut, $remarks, $id);

if(mysqli_stmt_affected_rows($stmt) > 0)
    echo "success";

else
    echo "failure";

mysqli_stmt_close($stmt);

BUT you have to consider that if you run the same query again, sending the same input that is already in the table row, then mysqli_stmt_affected_rows() will return 0. In particular read:

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE/DELETE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query has returned an error. NULL indicates an invalid argument was supplied to the function.

And:

If the number of affected rows is greater than maximal PHP int value, the number of affected rows will be returned as a string value.

Source: http://php.net/manual/en/mysqli-stmt.affected-rows.php

What is the purpouse of the SELECT query at line 99?

$query = "SELECT * FROM `attendance` WHERE `id`='$id'";
$query_run = mysql_query($query);

It seems useless as the variables are rewritten by the following update query.

Docs:

Please, take time to learn how MySQLi works, read the documentation carefully as the comments that you find in the documentation, which a lot of times, give good advices about some functions. Give a chance to PDO too, I prefer it beacuse is not limited to the MySQL database.

SpottyBlue 0 Junior Poster in Training · Answer 6 · 2016-05-13T03:20:04+00:00

Should i place the code above in between lines 96 and 114?
And which line number should i set the variable id?