Issues with Getting data from DB and Saving it back

[dapcigar]

Am loading data from the database to a dropdown list. but when i want to save it back to the DB, it is saving the ID and not the name.

here is the HTML form

 

<form class="form-horizontal" method="post" action="rec_proc.php?process=true">
                  <fieldset>
                    <legend>Requisition Form</legend>
                    <div class="control-group">
                      <label class="control-label" for="focusedInput">First Name:</label>
                      <div class="controls">
                                  <span class="input-xlarge uneditable-input"><?php echo $firstname ?></span>
                      </div>
                    </div>
                    <div class="control-group">
                      <label class="control-label" for="focusedInput">Department :</label>
                      <div class="controls">
                                  <span class="input-xlarge uneditable-input"><?php echo $department ?></span>
                      </div>
                    </div>
                   
                    <div class="control-group">
                                <label class="control-label" for="selectError">Category :</label>
                                <div class="controls">
                                  <select id="selectError" data-rel="chosen" name="cat_name">
                                    <?php
                while($row =  mysql_fetch_array($select)){
                extract($row);?>
              <option value="<?php echo $id;?>"><?php echo $cat_name; ?></option>
                <?php }?>
                                  </select>
                                </div>
                    </div>
                    <div class="control-group">
                      <label class="control-label" for="focusedInput">Description :</label>
                      <div class="controls">
                        <input class="input-xlarge focused" id="focusedInput" type="text" name="description" value="">
                      </div>
                    </div>
                    <div class="control-group">
                                <label class="control-label" for="appendedInput">Amount :</label>
                                <div class="controls">
                                  <div class="input-append">
                                    <input id="appendedInput" size="16" type="text" name="amount"><span class="add-on">.00</span>
                                  </div>
                                  <span class="help-inline">Enter Amount</span>
                                </div>
                              </div>
                    <div class="control-group">
                      <div class="controls"></div>
                    </div>
                    <div class="form-actions">
                      <button type="submit" class="btn btn-primary">Send Requisition</button>
                      <button type="reset" class="btn">Cancel</button>
                    </div>
                  </fieldset>
                </form>

 

......................................................................................................................

 

Here is the code

 

 <?php
 
if(isset($_GET['process'])){

$cat_name = $_POST['cat_name'];

}

$description = $_POST['description'];
$amount= $_POST['amount'];

$date = date('Y-m-d');
$status = "Pending ";

include ('mysql_connect.php');


//insert into DB
$sql = mysql_query("INSERT INTO requisition ( id, department, category, description, amount, date, username, firstname, lastname, status) VALUES ( NULL, '$department', '".$cat_name."', '$description', '$amount','$date', '$user', '$firstname', '$lastname', '$status' )") or die(mysql_error());

header ( 'location: staff_dash.php');

?>

 

i want it to save the category and not the ID.. how can i correct this?

 

 

Thanks in advance

[cyberRobot]

In the code which processes the form submission, you could add another query to get the category name based on the passed ID. Note that you'll want to make sure the ID is a number before running the query to prevent SQL injections. You could use ctype_digit() to check for numbers:

http://www.php.net/manual/en/function.ctype-digit.php

 

 

Alternatively, the form could be modified to pass the category name:

<option value="<?php echo $cat_name;?>"><?php echo $cat_name; ?></option>

Side note: perhaps you're already aware of this, but form information can be tampered with by the user. To prevent SQL injections, you can utilize mysql_real_escape_string():

http://www.php.net/mysql_real_escape_string

[cyberRobot]

Have you considered modifying you database? The script could keep saving the category ID to the database. You could then modify the scripts which utilize the "requisition" database table so they use a JOIN query to get the category name based on the ID.

http://www.techonthenet.com/mysql/joins.php