PythonHelp Posted April 15, 2020 Share Posted 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(); ?> Quote Link to comment Share on other sites More sharing options...
PythonHelp Posted April 15, 2020 Author Share Posted April 15, 2020 (edited) 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 Quote Link to comment Share on other sites More sharing options...
TrueMember Posted April 15, 2020 Share Posted April 15, 2020 (edited) 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 Quote Link to comment Share on other sites More sharing options...
chhorn Posted April 15, 2020 Share Posted April 15, 2020 (edited) 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 Quote Link to comment Share on other sites More sharing options...
PythonHelp Posted April 16, 2020 Author Share Posted 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" /> </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" /> </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> Quote Link to comment Share on other sites More sharing options...
Barand Posted April 16, 2020 Share Posted 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.) Quote Link to comment Share on other sites More sharing options...
PythonHelp Posted April 17, 2020 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
chhorn Posted April 17, 2020 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted April 17, 2020 Share Posted 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> Quote Link to comment Share on other sites More sharing options...
PythonHelp Posted April 17, 2020 Author Share Posted 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"; Quote Link to comment Share on other sites More sharing options...
PythonHelp Posted April 17, 2020 Author Share Posted April 17, 2020 2 hours ago, Barand said: Both options have the same value 🙈 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 17, 2020 Share Posted April 17, 2020 (edited) 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 Quote Link to comment Share on other sites More sharing options...
PythonHelp Posted April 21, 2020 Author Share Posted 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> Quote Link to comment Share on other sites More sharing options...
Barand Posted April 21, 2020 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.