General suggestions:
- Try to avoid creating html code from php whenever possible.
- Don't mix up db access statements with html creation codes (like you did with the
while loop and mysqli_fetch_assoc function). Better save the db fetching results into an array and use only this array to iterate through its items later, in the html codes part.
- You are trying to avoid sql injection by applying escaping provided by
mysqli_real_escape_string, followed by mysqli_query. I would strongly suggest you to forget both functions from now on. Make yourself a habit from using prepared statements instead. With the prepared statements you'll achieve the complete avoidance of sql injection - if correctly applied. See also this post if you wish.
- Try to use the object-oriented MySQLi library instead of the procedural one (see my code). In the php.net docs of MySQLi, to each procedural function corresponds an object-oriented styled method.
- You should avoid validations like
or die(...). You can catch and handle any kind of errors/exceptions/warnings/notices/etc (like db connection failure, bad sql syntax, undefined index, failed data fetching etc) by using a more elegant solution. And here, in regard of MySQLi.
Back to your code:
- I wrote the solution below in the hope that it will help you gain another perspective about the whole code structure and involved "operations". It has a lot of comments indeed, but I think you'll find them useful.
- The HTTP GET method is not a viable solution in the context of your task (not even for testing purposes). You should stick with the POST method from the beginning till the end and find some other testing ways.
- In general, if you need an "ID" column in tables, then don't show it to the users of your website. Hide it. Even then you'll still be able to access the id value of each record.
- @Mahesh and @Tenflex already provided you good solutions regarding the values of the
attending comboboxes. Personally, I used a slightly different approach: hidden inputs for the values of the "ID" column and the attribute name="attending[]" for each attending combobox. This way, upon submission, you will catch two arrays (see $ids and $attendings), in which each item corresponds to the posted user id, resp. to the user attending value. You can see on screen the resulting arrays upon submission as well, because I implemented 2-3 testing code lines (search for @todo in my codes and take action accordingly).
- Avoid using deprecated or not anymore supported attributes in the html code (like
border='1', which is not supported in HTML5) and avoid using inline css styles. In both situations assign css classes to the html elements instead and customize them correspondingly in css.
- You can copy/paste/run my code as it is. Just create the two pages and run it to see how it works. Don't forget to change the values of the db connection parameters.
- I used
$connection instead of $link.
- Again: read the articles regarding the error/exception reporting and the prepared statements.
Good luck.
P.S: If you care about code elegance and a better data access, then don't hesitate to use PDO instead of MySQLi. They are in a lot of regards very similar, though PDO is the perfect choice.
connection.php
<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'yourdb');
define('USERNAME', 'youruser');
define('PASSWORD', 'yourpass');
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception).
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* @link http://php.net/manual/en/class.mysqli-driver.php
* @link http://php.net/manual/en/mysqli-driver.report-mode.php
* @link http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
/*
* Create a new db connection.
*
* @see http://php.net/manual/en/mysqli.construct.php
*/
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);
users.php
<?php
require 'connection.php';
/*
* Perform operations upon form submission.
*/
if (isset($_POST['submit'])) {
$ids = $_POST['ids'];
$attendings = $_POST['attending'];
/*
* Just for testing the results.
* @todo Delete the two lines below.
*/
echo '<pre>User ids: ' . print_r($ids, TRUE) . '</pre>';
echo '<pre>Attendings: ' . print_r($attendings, TRUE) . '</pre>';
$messages[] = 'The third user has the user id ' . $ids[2] . ' and the attending ' . $attendings[2] . '.';
}
/*
* Just for testing.
* @todo Delete the line below.
*/
$_SESSION['pin'] = 12;
// Get the pin.
$pin = $_SESSION['pin'];
/*
* The SQL statement to be prepared. Notice the so-called markers,
* e.g. the "?" signs. They will be replaced later with the
* corresponding values when using mysqli_stmt::bind_param.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'SELECT *
FROM users
WHERE pin = ?';
/*
* Prepare the SQL statement for execution.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
/*
* Bind variables for the parameter markers (?) in the
* SQL statement that was passed to prepare(). The first
* argument of bind_param() is a string that contains one
* or more characters which specify the types for the
* corresponding bind variables.
*
* @link http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$statement->bind_param('i', $pin);
/*
* Execute the prepared SQL statement.
* When executed any parameter markers in the sql statement will
* automatically be replaced with the appropriate data.
*
* @link http://php.net/manual/en/mysqli-stmt.execute.php
*/
$statement->execute();
/*
* Get the result set from the prepared statement.
*
* NOTA BENE:
* Available only with mysqlnd ("MySQL Native Driver")! If this
* is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
* PHP config file (php.ini) and restart web server (I assume Apache) and
* mysql service. Or use the following functions instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* @link http://php.net/manual/en/mysqli-stmt.get-result.php
* @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = $statement->get_result();
/*
* Fetch the data and save it into an array.
*
* @link http://php.net/manual/en/mysqli-result.fetch-all.php
*/
$users = $result->fetch_all(MYSQLI_ASSOC);
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* @link http://php.net/manual/en/mysqli-result.free.php
*/
$result->close();
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*
* @link http://php.net/manual/en/mysqli-stmt.close.php
*/
$statement->close();
/*
* Close the previously opened database connection.
* Not really needed, because php automatically closes all connections
* when the script processing finishes.
*
* @link http://php.net/manual/en/mysqli.close.php
*/
$connection->close();
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
<meta charset="UTF-8" />
<!-- The above 3 meta tags must come first in the head -->
<title>Demo</title>
<style type="text/css">
body { padding: 30px; }
button { margin-top: 20px; padding: 7px 12px; background-color: #8daf15; color: #fff; border: none; }
.messages { margin-bottom: 20px; }
.users { border-collapse: separate; border: 1px solid #ccc; }
.users thead th { padding: 10px; background-color: #f3f3f3; }
.users tbody td { padding: 5px; }
.idColumn { display: none; }
</style>
</head>
<body>
<h4>Users list</h4>
<div class="messages">
<?php
if (isset($messages)) {
echo implode('<br/>', $messages);
}
?>
</div>
<form name="confirm-attending-form" action="" method="post">
<table class="users">
<thead>
<tr>
<th class="idColumn">ID</th>
<th>First Name</th>
<th>Surname</th>
<th>Invite Type</th>
<th>Attend?</th>
</tr>
</thead>
<tbody>
<?php
if ($users) {
foreach ($users as $user) {
$id = $user['id'];
$firstName = $user['forename'];
$surname = $user['surname'];
$inviteType = $user['invite_type'];
?>
<tr class="user">
<td class="idColumn">
<input type="hidden" id="userId<?php echo $id; ?>" name="ids[]" value="<?php echo $id; ?>" />
</td>
<td>
<a href="javascript:alert('Do something with this row. User id: <?php echo $id; ?>');">
<?php echo $firstName; ?>
</a>
</td>
<td>
<?php echo $surname; ?>
</td>
<td>
<?php echo $inviteType; ?>
</td>
<td>
<select name="attending[]">
<option value="0">No</option>
<option value="1">Yes</option>
</select>
</td>
</tr>
<?php
}
} else {
?>
<tr>
<td colspan="5">
<?php echo 'No users found'; ?>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
<button type="submit" id="submit" name="submit" value="submit">
Submit
</button>
</form>
</body>
</html>
Used table structure:
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`pin` int(11) DEFAULT NULL,
`forename` varchar(100) DEFAULT NULL,
`surname` varchar(100) DEFAULT NULL,
`invite_type` int(11) DEFAULT NULL,
`attending` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
