Populate a form based on primary key

I have set up a page where I can update an entry in my database.

For now, I have set it up to populate the form with the plantID '2' but this only updates this single entry.

I need it to select any plant (based on the primary key 'plantID') and then have it populate the form with the selected record.

What would be the best way to achieve this?

I have read a few questions related to this, but they use other programs that I'm not familiar with.

Thanks.

<?php

// MySQL Database Connect
require_once("connect.php");

// Call the file to check if the user is logged in
require_once("code/check_login.php");
 
// read the values from the form and store in variables

$categoryID = $_POST['categoryID'];

$botanicName = $_POST['botanicName'];

$commonName = $_POST['commonName'];

$plantDescription = $_POST['plantDescription'];

$commonUse = $_POST['commonUse'];

$plantType = $_POST['plantType'];

$maxHeight = $_POST['maxHeight'];

$maxWidth = $_POST['maxWidth'];

$popular = $_POST['popular'];

 
// escape variables for security

$categoryID = mysqli_real_escape_string($conn, $categoryID);

$botanicName = mysqli_real_escape_string($conn, $botanicName);

$commonName = mysqli_real_escape_string($conn, $commonName);

$plantDescription = mysqli_real_escape_string($conn, $plantDescription);

$commonUse = mysqli_real_escape_string($conn, $commonUse);

$plantType = mysqli_real_escape_string($conn, $plantType);

$maxHeight = mysqli_real_escape_string($conn, $maxHeight);

$maxWidth = mysqli_real_escape_string($conn, $maxWidth);

$popular = mysqli_real_escape_string($conn, $popular);
 
// create the UPDATE query
$query="UPDATE plant SET categoryID='$categoryID', botanicName='$botanicName', commonName='$commonName', plantDescription='$plantDescription', commonUse='$commonUse', plantType='$plantType', maxHeight='$maxHeight', maxWidth='$maxWidth', popular='$popular'WHERE plantID='2'";
 
//execute the query

$results = mysqli_query($conn, $query );

// check for errors
if(!$results) {
    
echo ("Query error: " . mysqli_error($conn));
    
exit;
    
}

else {
    
// Redirect the browser window back to the edit_plant page if there are no errors
    
header("location: ../edit_plant.php");

}

?>
    <h2>Edit a Plant</h2>
    
<?php

// run a select query to return the existing data for the record
$query = "SELECT * FROM plant WHERE plantID='2'"; 
    
$results = mysqli_query($conn, $query );

// capture any errors    
if(!$results) { 
    
echo ("Query error: " . mysqli_error($conn));
    
}
    
else {
    
// fetch and store the results for later use if no errors
while ($row = mysqli_fetch_array($results)) {
    
$cat_ID = $row['categoryID'];

$bot_name = $row['botanicName'];

$comm_name = $row['commonName'];

$pl_desc = $row['plantDescription'];

$comm_use = $row['commonUse'];
    
$pl_type = $row['plantType'];

$m_height = $row['maxHeight'];

$m_width = $row['maxWidth'];

$pop = $row['popular'];
       
}
    
}
    
?>

<form method="post" action="code/update_plant.php">

<p>Category ID: <input type="text" name="categoryID" value="<?=$cat_ID?>" required></p>

<p>Botanic Name: <input type="text" name="botanicName" value="<?=$bot_name?>" required></p>

<p>Common Name: <input type="text" name="commonName" value="<?=$comm_name?>"required></p>

<p>Plant Description: <input type="text" name="plantDescription" value="<?=$pl_desc?>" required></p>

<p>Common Use: <input type="text" name="commonUse" value="<?=$comm_use?>" required></p>

<p>Plant Type: <input type="text" name="plantType" value="<?=$pl_type?>" required></p>

<p>Max. Height (m): <input type="text" name="maxHeight" value="<?=$m_height?>" required></p>

<p>Max. Width (m): <input type="text" name="maxWidth" value="<?=$m_width?>" required></p>

<p>Popular? (Y/N): <input type="text" name="popular" value="<?=$pop?>"required></p>

<input type="submit" name="submit" value= "Update">

</form>

1 answer

  • answered 2018-02-13 04:39 1000Nettles

    In your second PHP snippet, you need to be able to load a row based on a dynamic plantID. My recommendation, if you're writing this with raw PHP and no framework, is to construct your page's URL to be similar to http://example.com/plantid=[id] where [id] is the dynamic ID of the plant you're looking to load.

    Then, in your second PHP snippet, you could load the plant based on the GET variable like so:

    <?php
    
    // run a select query to return the existing data for the record
    $plantID = (int) $_GET['plantid'];
    $query = "SELECT * FROM plant WHERE plantID={$plantID}"; 
    
    $results = mysqli_query($conn, $query);
    

    Note that we're typecasting the plant ID so we don't inject any user input directly into a MySQL statement. (See How can I prevent SQL injection in PHP?)

    When you get back a row from the plants table, ensure you assign the ID:

    $plant_ID = $row['id'];
    

    In your form which posts to update the plant, you will need a hidden input which contains the plant ID:

    <form method="post" action="code/update_plant.php">
    ...
    <input type="hidden" name="plantID" value="<?php echo $plant_ID ?>">
    ...
    </form>
    

    The above assumes that id is the primary key column name for your plants table.

    In your first PHP snippet which updates the plant row, you will need to grab the plantID which was POSTed from the hidden input which has a name of plantID.