Updating Multiple Records

PythonHelp · April 15, 2020

Hi Guys,

I am new to PHP/MYSQL. I am working on a web database project to record class attendance.
I have found this script which I have manipulated, however, I am not sure why it does not display the data. It may be something simple, I have checked over table names, field names, variable names to see that they match the database. It displays the textboxes with the php code rather than the data and all other fields display no data.

Can you help?

I will be focusing on Prepared Statements for SQL injections later.

<?php

$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


$sql="SELECT * FROM attendance";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);


?>


<html>
<head>
<title>Registers</title>
</head>
<body>


<?php include 'Navigation.php';?>

<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">

<table border="0" cellspacing="1" cellpadding="0" style="width: 1461px; height: 105px">

<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>First Name</strong></td>
<td align="center"><strong>Form</strong></td>
<td align="center"><strong>Year Group </strong></td>
<td align="center"><strong>Date</strong></td>
<td align="center"><strong>Attendance</strong></td>


</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td align="center"><? $Student_id[]=$rows['Student_id']; ?><? echo $rows['Student_id']; ?></td>
<td align="center"><? $firstname[]=$rows['firstname']; ?><? echo $rows['firstname']; ?></td>
<td align="center"><? $lastname[]=$rows['lastname']; ?><? echo $rows['lastname']; ?></td>
<td align="center"><? $Form_Group[]=$rows['Form_Group']; ?><? echo $rows['Form_Group']; ?></td>
<td align="center"><? $Year_Group[]=$rows['Year_Group']; ?><? echo $rows['Year_Group']; ?></td>
<td align="center"><? $Att_Date[]=$rows['Att_Date']; ?><? echo $rows['Att_Date']; ?></td>
<td align="center"><input name="Presence[]" type="text" id="Presence" value="<? echo $rows['Presence']; ?>"></td>
</tr>

<?php
}
?>

<tr>
<td colspan="7" align="center"><input type="submit" name="Submit" value="Submit Register"></td>
</tr>
</table>

</form>
</table>


<?php

// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE attendance SET Presence='$Presence[$i]' WHERE Student_id='$Student_id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:Registers.php");
}
mysql_close();
?>

PythonHelp · April 15, 2020

Ok the issue was <? so I added the <?php and it now displays the data from the table,.

The next issue is, I update the value in the text box, after clicking submit, it shows the original value and not the updated (which means the value is probably not being updated in the database?

Edited April 15, 2020 by PythonHelp

TrueMember · April 15, 2020

18 minutes ago, PythonHelp said:

Ok the issue was <? so I added the <?php and it now displays the data from the table,.

The next issue is, I update the value in the text box, after clicking submit, it shows the original value and not the updated (which means the value is probably not being updated in the database?

First of all, you can check if value was updated directly on your database.

Second, if you are using PHP version >= 5.5.0, the mysql_query was deprecated, you should use MySQLi or PDO_MySQL.

To find errors more easly i advice enable this (add to top of file):

ini_set('display_errors', 1); 
ini_set('display_startup_errors', 1); 
error_reporting(E_ALL);

PS: If you want use short tags like <? $count=0; ?> you should enable short_open_tag=On in your php.ini file.

Edited April 15, 2020 by TrueMember

chhorn · April 15, 2020

you are updating the record after displaying it. check https://en.wikipedia.org/wiki/IPO_model

also $Submit is never defined, check https://www.php.net/manual/en/security.globals.php

Edited April 15, 2020 by chhorn

PythonHelp · April 16, 2020

Thank you guys. I got the script to display the data and also edit and refresh the page.

I am now wanting to filter the data but it does not seem to work.

The form with the filter fields:

<body>
	<?php include 'Navigation.php';?>
	
	<form method="post" action="Registers_(1).php">
	
	
		<table frame="box" border="0"align="center" style="width: 38%; height: 63px;">
			<tr>
				<td class="auto-style2"><strong>Select Date</strong></td>
				<td class="auto-style3"><input name="Att_Date" type="date" />&nbsp;</td>
			</tr>
			<tr>
				<td class="auto-style2"><strong>Select Form Group</strong></td>
				<td class="auto-style3"><select name="Form_Group">
	<option value='SELECT' selected="selected">SELECT</option>
	<option value='Class A'>Class A</option>
	<option value='Class A'>Class B</option>

	</select>
	</td>
			</tr>
			<tr>
				<td class="auto-style1" colspan="2">
				<input name="Sub1" type="submit" value="Load Register" />&nbsp;</td>
			</tr>
		</table>
	
	
	</form>

The php script:

<?php

$host="shareddb1d.hosting.stackcp.net"; // Host name
$username="xxxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxxx"; // Database name
$tbl_name="xxxxxx"; /* Table name */

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


if(isset($_POST["Sub1"])){
$Att_Date=$_POST['Att_Date'];
$Form_Group=$_POST['Form_Group'];

}


$sql=("SELECT * FROM $tbl_name WHERE Form_Group ='$Form_Group' AND Att_Date = '$Att_Date'";;

$result=mysql_query($sql);

/* Count table rows */
$count=mysql_num_rows($result);
?>
<?php

/* Check if button name "Submit" is active, do this */
if(isset($_POST['Submit']))
{

header('Location: '.$_SERVER['REQUEST_URI']);
$count=count($_POST["Student_id"]);
	
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET firstname='" . $_POST['firstname'][$i] . "', lastname='" . $_POST['lastname'][$i] . "', Presence='" . $_POST['Presence'][$i] . "' WHERE Student_id='" . $_POST['Student_id'][$i] . "'";
$result1=mysql_query($sql1);
}
}

echo "";
mysql_close();
?>

<?php include 'Navigation.php';?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">

<tr>
<td align="center" class="auto-style1"><strong>Id</strong></td>
<td align="center" class="auto-style1"><strong>First Name</strong></td>
<td align="center" class="auto-style1"><strong>Lastname</strong></td>
<td align="center" class="auto-style1"><strong>Year Group</strong></td>
<td align="center" class="auto-style1"><strong>Form Group</strong></td>
<td align="center" class="auto-style1"><strong>Date</strong></td>
<td align="center" class="auto-style3"><strong>Attendance</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td align="center">
<input name="Student_id[]" type="text" value="<?php echo $rows['Student_id']; ?>" readonly class="auto-style1"></td>
<td align="center">
<input name="lastname[]" type="text"  value="<?php echo $rows['lastname']; ?>" readonly class="auto-style1"><span class="auto-style1">
</span>
</td>
<td align="center">
<input name="firstname[]" type="text" value="<?php echo $rows['firstname']; ?>" readonly class="auto-style1"><span class="auto-style1">
</span>
</td>
<td align="center">
<input name="Year_Group[]" type="text"  value="<?php echo $rows['Year_Group']; ?>" readonly class="auto-style1"><span class="auto-style1">
</span>
</td>
<td align="center">
<input name="Form_Group[]" type="text"  value="<?php echo $rows['Form_Group']; ?>" readonly class="auto-style1"><span class="auto-style1">
</span>
</td>
<td align="center">
<input name="Att_Date[]" type="text"  value="<?php echo $rows['Att_Date']; ?>" readonly class="auto-style1"><span class="auto-style1">
</span>
</td>

<td align="center" class="auto-style2">
<input name="Presence[]" type="text"  value="<?php echo $rows['Presence']; ?>" class="auto-style1"><span class="auto-style1">
</span>
</td>

</tr>

<?php
}
?>

<tr>
<td colspan="7" align="right">
<input type="submit" name="Submit" value="Submit" class="auto-style1"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>

Barand · April 16, 2020

2 hours ago, PythonHelp said:

it does not seem to work.

What is that supposed to tell us?

What is doing that it shouldn't?

What is it not doing that it should?

(And note that whatever group you select (A or B) it will always be "Class A" with that HTML code.)

PythonHelp · April 17, 2020

Sorry, I should have been clear

The records display without the Where clause, however, when I refer to the controls on the form to filter the data no records are displayed.

Why would only class A be displayed?

chhorn · April 17, 2020

And what's the sql statement then? What data do you expect to show up on that condition? Show an example of both.

Barand · April 17, 2020

2 hours ago, PythonHelp said:

Why would only class A be displayed?

Both options have the same value

16 hours ago, PythonHelp said:

<option value='Class A'>Class A</option> <option value='Class A'>Class B</option>

PythonHelp · April 17, 2020

16 hours ago, Barand said:

What is that supposed to tell us?

What is doing that it shouldn't?

What is it not doing that it should?

It is supposed to execute the query after sub1 button is pressed on the form which has two fields : Form_Group and Att_Date.

The query is: $sql=("SELECT * FROM $tbl_name WHERE Form_Group ='$Form_Group' AND Att_Date = '$Att_Date'");;

The query displays no data matching the filter fields, although it worked with $sql="SELECT * FROM attendance";

PythonHelp · April 17, 2020

2 hours ago, Barand said:

Both options have the same value

🙈

Barand · April 17, 2020

Have you turned on your error reporting, because this line should give a syntax error...

$sql=("SELECT * FROM $tbl_name WHERE Form_Group ='$Form_Group' AND Att_Date = '$Att_Date'";;
     ^

And does the date format in $Att_Date match that in the DB table (which should be yyyy-mm-dd)

Edited April 17, 2020 by Barand

PythonHelp · April 21, 2020

Hello,

Thank you, since learning how to turn on error reporting, I was able to resolve all the previous issues and I have used mysqli which has been new learning for me.

I am now able to display filtered data using variables posted from a form and I am able to update the data from multiple rows.

The problem I am having now is that after the update I need the table to display the data using the filter fields from the form that was originally submitted. But I am getting the errors:

Notice: Undefined index: Att_Date on line 33

Notice: Undefined index: Form_Group on line 34

I have tried to use session but with no luck. I feel stuck at this point and need some guidance.

<?php

ini_set('display_errors', 1); 
ini_set('display_startup_errors', 1); 
error_reporting(E_ALL);
?>



<?php

//Connection for database

$conn = mysqli_connect("xxxx", "xxxxxxxxx", "xxxxx", "xxxxxxx");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
}


$Att_Date=$_POST['Att_Date'];
$Form_Group=$_POST['Form_Group'];

session_start();
/*session is started if you don't write this line can't use $_Session  global variable*/

$_SESSION["newsession1"] = $Att_Date;
$_SESSION["newsession2"] = $Form_Group;

echo $_SESSION["newsession1"];
echo $_SESSION["newsession2"];


//Select Database
$sql = "SELECT * FROM attendance WHERE Form_Group ='$Form_Group' AND Att_Date = '$Att_Date'";
$result = $conn->query($sql);

?>


<?php

/* Check if button name "Submit" is active, do this */
if(isset($_POST['Submit1']))
{

header('Location: '.$_SERVER['REQUEST_URI']);
$count=count($_POST["Student_id"]);
	
for($i=0;$i<$count;$i++){
$sql1="UPDATE attendance SET Presence='" . $_POST['Presence'][$i] . "' WHERE Student_id='" . $_POST['Student_id'][$i] . "'";
$result = $conn->query($sql1);
}
}

echo "";

?>


<!doctype html>
<html>
<body>
<h1 align="center">Register</h1>
<table border="1" align="center" style="line-height:25px;">
<tr>
<th>Stduent ID</th>
<th>Name</th>
<th>Surname</th>
<th>Form</th>
<th>Year Group</th>
<th>Date</th>
<th>Presence</th>
</tr>
<?php
//Fetch Data form database
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
?>

<form method="post">
<tr>
<td><input name="Student_id" type="text" value="<?php echo $row['Student_id']; ?>"></td>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['Form_Group']; ?></td>
<td><?php echo $row['Year_Group']; ?></td>
<td><?php echo $row['Att_Date']; ?></td>
<td><input name="Presence" type="text" value="<?php echo $row['Presence']; ?>"></td>
</tr>

<?php
}
}
else
{
?>
<tr>
<th colspan="6">There's No data found!!!</th>
</tr>
<?php
}
?>
</table>
<p class="auto-style1"><input name="Submit1" type="submit" value="submit"></p>
</form>
</body>
</html>

Barand · April 21, 2020

For $_POST['Att_date'] to exist you need a form input field with that name. The only one I see is 'Presence'

Hard to tell with your complete lack of indentation, but it looks like you start a new form for every table row but only have one end-of-form tag at the end.

Sign In

Updating Multiple Records

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information