Jump to content

Updating Multiple Records


PythonHelp
 Share

Recommended Posts

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();
?>

 

Link to comment
Share on other sites

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 by PythonHelp
Link to comment
Share on other sites

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 by TrueMember
Link to comment
Share on other sites

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>

 

 

 

Link to comment
Share on other sites

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";

 

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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>

 

 

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.