inserting into SQL with PHP

Question

Essentially I want to:

• pull info from mySQL server
• create a table of students with their name, phone number, and exam date
• I want a checkbox next to each student pulled from mySQL and when the checkbox is clicked and the user hits submit, it inserts a value into mySQL column 'contacted'under that specific student. The value will either be "yes" or "NULL"
• I used primary key (id) which auto increments to create unique checkbox names for each student

application: The user will retrieve a table of our students and their exam dates. The user will call (via phone) the students and ask about their exam. Once the user has contacted that student, they check the checkbox to show that that particular student has already been contacted. That information will be stored in mySQL for that particular student to show that student was contacted.

here is my code:

<?php

define('DB_NAME', 'Students');
define('DB_USER', 'admin');
define('DB_PASSWORD', 'password');
define('DB_HOST', 'localhost');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

$sql = sprintf("SELECT id,f_name,l_name,phone,exam_date FROM Student_data");

$result = mysql_query($sql);

$table_count = 0;

$student_id = array();

echo "<script>
function DoTheThing()
{
" .
for($x = 0; $student_id[$x] != NULL; $x++) 
  { 
$in = sprintf("INSERT INTO Student_data (contacted) VALUES ('". $_POST[$row['id']] ."') WHERE id = '" . $row['id'] . "';" );
$db_selected->mysql_query($in)
  }
. "
}
</script>";

echo "<table width= 400 border=1><form action=\"DoTheThing()\" method=\"POST\">
     <tr>
       <th width='175' scope='col'>Name</th>
       <th width='150' scope='col'>Phone</th>
       <th width='125' scope='col'>Exam Date</th>
     </tr>";
while($row = mysql_fetch_array($result))
  { 
       echo "<tr>";
       echo "<td><center>"  . $row['f_name'] . "&nbsp;". $row['l_name']. "</center></td>";
       echo "<td><center>". $row['phone'] ."</center></td>";
       echo "<td><center>". $row['exam_date'] ."<input type=\"checkbox\" name=\"" . $row['id'] . "\" value=\"yes\"></center></td>";
     echo "</tr>";
     $student_id[$table_count] = $row['id']
     $table_count = +1;

  }
   echo "</form></table>
   <br/><br/><input style = \"height:35px;width:95px;font-size:20px;\" type=\"submit\" name=\"submit\" value=\"Submit\">
   ";

 mysql_close($link);
?>

edit: Sorry, realized I never posted my question

It stopped working when I attempted to insert the "yes" or "NULL" value into mySQL. I am very new to mySQL and was wondering if any of my statements were wrong.

Answer 1

This should be a very big boost of help, basically a shell. All that is left to do is inserting the data into your SQL server.

I commented the code so you could see what was going on, when, and where.

Also, you should definitely stay AWAY from mysql_* as it's deprecated. My example was made using mysqli_*. Another options would be PDO.

    <?php

    //Set variables (Can be done on another file for more security)
    $Host = "Localhost";
    $User = "admin";
    $Pass = "password";
    $DB = "Students";

    //Connect to the databse using mysqli. NOT MYSQL WHICH IS DEPRECATED
    $con = mysqli_connect($Host, $User, $Pass, $DB);

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

    //if submitted
    if(isSet($_POST['submit'])) {

        //submit data using mysqli_query and then reload the page.


        //clear POST variables before you reload the page.
        unset($_POST);

       //reload the page
       echo "<META http-equiv='refresh' content='0'>";

    } else { //if not submitted

    //define search variable, and query it.
    $db_selected = mysqli_query($con, "SELECT id,f_name,l_name,phone,exam_date FROM Student_data");

    //Start table
        echo "<form method='POST'>";
        echo "  <table>";
        echo "      <tr>";
        echo "          <th>Name</th>";
        echo "          <th>Phone</th>";
        echo "          <th>Exam Date</th>";
        echo "          <th></th>";
        echo "      </tr>";

    //Loop through sql database
    while($row = mysqli_fetch_array($check)) {
        echo "      <tr>";
        echo "          <td>".$row['f_name']."&nbsp;".$row['l_name']."</td>";
        echo "          <td>".$row['phone']."</td>";
        echo "          <td>".$row['exam_date']."</td>";
        echo "          <td><input type='checkbox' name='checkbox['".$row['id']."']' value='1'></td>";
        echo "      </tr>";
    }

    //end table
        echo "  </table>";
        echo "<input type='submit' value='Submit' name='submit'>";
        echo "</form>";

    }

?>