Member Avatar for luap599

Hi my first insert statment works ok, My problem is that I wish to insert all the selected options passed from the form. into my user_req_opt table. Some items only have 1 option others may have 10 options.

$sql = "insert into user_requests(rq_id, user_id, code_ref, code_id, cat_id, opt_yn, rq_qty, rq_unit, rq_type, rq_date) values(null, '$user_id', '".$_POST[code_ref]."', '".$_POST[code_id]."', '".$_POST[cat_id]."', '".$_POST[opt_yn]."', '".$_POST[rq_qty]."', 'ea', 'code', now())";
$res = mysqli_query($mysqli, $sql) or die (mysqli_error($mysqli));

$qid = mysqli_insert_id($mysqli);

if($_POST[opt_yn] != 'n'){

$opt = "insert into user_req_opt(rq_id, opt_ref, opt_value) values('".$qid."', '".$_POST[opt_ref]."', '".$_POST[opt_value]."')";
$result = mysqli_query($mysqli, $opt) or die (mysqli_error($mysqli));
  • 3 Contributors
  • 4 Replies
  • 182 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by luap599

Dani AI

Generated

Short answer: stop hard-coding numbered POST fields and let the form submit option groups as arrays, then loop server-side with a single prepared statement. That handles any number of option groups, is far easier to maintain than repeating index-based inserts (as suggested), and avoids SQL injection problems. asked for the schema and provided it, so the guidance below assumes you already have per-request option rows to insert.

Use associative names for each radio group so PHP receives an array keyed by the option reference. For example:

<!-- server-side generate one radio group per opt_ref -->
<input type="radio" name="opts[<?= $optRef ?>]" value="<?= htmlspecialchars($optValue) ?>">

On the PHP side, check the posted array and reuse a prepared statement to insert every selection. This example uses mysqli and shows the pattern (error handling simplified):

$requestId = (int)$mysqli->insert_id; // id of main request already inserted

if (!empty($_POST['opts']) && is_array($_POST['opts'])) {
    $stmt = $mysqli->prepare("INSERT INTO user_req_opt (rq_id, opt_ref, opt_value) VALUES (?, ?, ?)");
    $stmt->bind_param("iss", $requestId, $optRef, $optValue);

    foreach ($_POST['opts'] as $optRef => $optValue) {
        if ($optValue === '' || $optValue === null) continue;
        $optRef = (string)$optRef;
        $optValue = (string)$optValue;
        $stmt->execute() or die($stmt->error);
    }
    $stmt->close();
}

Extra tips: validate each submitted opt_ref/opt_value against your options table before inserting to prevent tampering; use transactions (InnoDB) so a failed option insert can roll back the whole request; cast IDs to integers and always use prepared statements instead of interpolating POST data. This pattern keeps code simple, secure, and able to handle one option or many without changing server logic.

Recommended Answers

All 4 Replies

Member Avatar for urtrivedi

You may continue inserting using following statement

if($_POST[opt_yn1] != 'n'){ $opt = "insert into user_req_opt(rq_id, opt_ref, opt_value) values('".$qid."', '".$_POST[opt_ref1]."', '".$_POST[opt_value1]."')";$result = mysqli_query($mysqli, $opt) OR die (mysqli_error($mysqli));
}

if($_POST[opt_yn2] != 'n'){ $opt = "insert into user_req_opt(rq_id, opt_ref, opt_value) values('".$qid."', '".$_POST[opt_ref2]."', '".$_POST[opt_value2]."')";$result = mysqli_query($mysqli, $opt) OR die (mysqli_error($mysqli));
}
.
.
.
.
.
.
.

I have assumed your post variables. You should change it according to your form.

Edited by urtrivedi because: n/a
Member Avatar for tyson.crouch

could you please post your table structure so we can get a better understanding of your requirements.

Member Avatar for luap599

Thank you for your response, and yes your code dose work if I were using hard coded form to submit the data.

However; my option forms are dynamically generated via PHP script with information pulled from the product options table.

I am using a set of radio boxes to allow viewers to select the options the wish to have!

Again Thank you for your reply

Paul

Member Avatar for luap599 
CREATE TABLE `user_req_opt` (
  `rq_id` int(11) NOT NULL,
  `opt_ref` char(2) NOT NULL,
  `opt_value` varchar(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The option users select from ane stored in the table below

CREATE TABLE `codes_opt` (
`code_id` int(11) NOT NULL,
`opt_ref` char(2) NOT NULL,
`opt_name` varchar(30) NOT NULL,
`opt_value` varchar(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Edited by luap599 because: to show more detail
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.