PHP/MySQL C.R.U.D. - How Would I Obtain the ID for a Specific Row?
Good afternoon Experts,
I am using a while loop to fetch the data from the MySQL database. I am not sure if the while loop is throwing me off a little, but I am stumped on how I would go about obtaining the ID for a specific row, so that particular row can be modified/updated/deleted.
Here is an image of my form, and the results it fetches from the database using a while loop.
Here is the corresponding code:
<?php
$sqlSuppTracker = "SELECT * FROM supplements
INNER JOIN users
ON users.id = supplements.usersID
WHERE users.id = '$userID'";
$querySuppTracker = mysqli_query($conn, $sqlSuppTracker);
$numberRowsSupps = mysqli_num_rows($querySuppTracker);
if($numberRowsSupps == 1){
echo "Currently, you have added $numberRowsSupps case to the supplement tracker.";
}elseif ($numberRowsSupps > 1) {
echo "Currently, you have added $numberRowsSupps cases to the supplement tracker.";
}else {
echo "You have not added any cases to the supplement tracker.";
require_once("forms/supplementTrackerForm.php");
<table class="tableBorder">
<th align="left">Case Number:</th>
<th align="left">Case Type:</th>
<th align="left">Date Added:</th>
<th align="left">Supplement:</th>
<th align="left">Next Supplement:</th>
<th align="left">Update/Delete:</th>
while ($row = mysqli_fetch_assoc($querySuppTracker)){
<td align="left" value="<?php echo $row['caseNum']; ?>"><?php echo htmlspecialchars($row['caseNum'], ENT_QUOTES, 'UTF-8'); ?></td>
<td align="left"><?php echo htmlspecialchars($row['caseType'], ENT_QUOTES, 'UTF-8'); ?></td>
<td align="left"><?php echo htmlspecialchars($row['dateCreated'], ENT_QUOTES, 'UTF-8'); ?></td>
<td align="left">
<?php echo htmlspecialchars($row['initialDate'], ENT_QUOTES, 'UTF-8'); ?>
<td align="left">
<?php echo htmlspecialchars($row['supplementDue'], ENT_QUOTES, 'UTF-8'); ?>
<td><br><br>
<input type="submit" name="updateFiveDaySupplement" value="Update 5 Day Supplement"><br><br>
<input type="submit" name="update14DaySupplement" value="Update 14 Day Supplement"><br><br>
<input type="submit" name="deleteSupplement" value="Delete Supplement">
<input type="hidden" name="hiddenID" value="<?php echo htmlspecialchars($row['supplements.id'], ENT_QUOTES, 'UTF-8'); ?>"><br><br>
</table>
if(isset($_POST['addCaseToTracker'])){
$caseNumber = "";
$caseNumber = strip_tags($_POST['caseNumber']);
$caseNumber = str_replace(' ', '', $caseNumber);
$caseType = "";
$caseType = strtoupper(strip_tags($_POST['caseType']));
$fiveDayRule = date("Y-m-d-", strtotime('+5 days'));
$sqlInsertSuppTracker = "INSERT INTO supplements (caseNum, caseType, dateCreated, initialDate, supplementDue, usersID)
VALUES ('$caseNumber', '$caseType', CURDATE(), CURDATE(), '$fiveDayRule', '$userID')";
// Perform a query, check for error
if (!mysqli_query($conn, $sqlInsertSuppTracker)) {
echo("Error description: " . mysqli_error($conn));
$caseNumber = "";
$caseType = "";
header('Refresh: 0');
the id is likely whatever makes the row specific. this can be a single or combination of columns. in you case i would guess the case_id is a primary key or unique index which would qualify as such
Here is the MySQL table structure. I need to be able to access the ID for each set of data that is fetched in the while loop, so the user can modify the data associated with that ID.
Your 'id' column is the key then. It has to be part of a "WHERE `id` = 'nnnn'" statement in your SELECT or UPDATE queries. I usually make it the first column in my display table.
So… I guess the issue is not so much what my SQL statement would be because I know how to hardcore the SQL statement to modify the record "WHERE id = 1," "WHERE id = 2," etc., but I do not know how to dynamically obtain the ID of the corresponding record. I thought I could set a variable equal to the row's ID, but that does not seem like it would work, either. If the variable is set during the WHILE loop, won't the variable be overwritten each time the loop executes, and only contain the ID of the last record of the loop...
Get a FREE t-shirt when you ask your first question.
We believe in
human
intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
statement along with all the other fields. You need to attach it to any actions in that row. I don't see a complete form there that would identify the target page or POST or GET action.
I'm sure this is easy to do, but I cannot wrap my brain around it...and it's frustrating to me...
I intentionally loaded all of the columns from the database and I know the ID is being loaded from the SQL query. Apart from the form being incomplete, I still don't know how to access a specific ID for a specific row when the data is being processed in a while loop...
So, for the first record ID = 1, and if I set that to a variable $suppRow then each time the while loop runs, won't it be overwritten...??
How do I set a variable to that specific ID in order access the information and update THAT specific row...?
Okay, I like the idea of each row containing its own form. However, I cannot get the id field to echo out. When I use this, the value field does not get populated with the records ID:
if you are unsure about the contents of $row, print_r , var_export , and var_dump are your friends.
--
separate forms will work. you can even simplify the code by sticing the id in the action property of the form element. that said beware that the user will not be able to make changes on multiple rows at the same time and validating any form will reload the page and undo user changes so this is probably not your best bet.
unless you want to go ajax, arrays are a much better option. the browsers are agnostic to arrays and php handles them properly.
you can use something like
fieldname[id]
or
data[id][fieldname]
So, I haven't been able to test the form handling out, yet. So, this is just me "thinking out loud," per se. When the user clicks one of the submit buttons, will the form process only the information for that specific form or will every form be processed, too.
For instance, the exact same submit button is created for each form with the same name, etc. So, how will the form be processed when form1 contains the same name for the submit buttons as form2 does?
Get a FREE t-shirt when you ask your first question.
We believe in
human
intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
