Member Avatar for elamigosam

hello, I am having issues inserting comma separated values to my database, this is what I have:

$invoiceProducts = "14:12,"; // eventualy the 14:12, will multiply to other values 43:2,34:1,45:1, etc
$invoiceProducts = addslashes($invoiceProducts); //stripslashes() to remove the slashes tried with and without stripslashes. 

$stmt = $conn->prepare("INSERT INTO invoices (invoiceActive, invoiceProducts) VALUES (?, ?)");
$stmt->bind_param("is", 1, $invoiceProducts);
$stmt->execute();

Error:
Fatal error: Cannot pass parameter 2 by reference

is this the best way to create this row of data? is there a better way?

why wont this work?
thanks :)

Edited by Dani because: Formatting fixed
  • 3 Contributors
  • 3 Replies
  • 350 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by elamigosam

Dani AI

Generated

Quick diagnosis: mysqli_stmt::bind_param requires real variables for the placeholder values because those arguments are passed by reference. Passing a literal (for example 1) causes the "Cannot pass parameter ... by reference" error. was pointing toward the parameter issue and 's suggestion to search was useful background.

A minimal fix is to put each value into a variable and let the prepared statement handle escaping (don’t use addslashes before binding). Example:

$active = 1;
$products = "14:12,"; // incoming CSV string

$stmt = $conn->prepare("INSERT INTO invoices (invoiceActive, invoiceProducts) VALUES (?, ?)");
$stmt->bind_param("is", $active, $products);

if (! $stmt->execute()) {
    error_log("Insert failed: " . $stmt->error);
}

Better approach (recommended): don’t store CSV in one column. Create a normalized invoice_items table (invoice_id, product_id, qty) and insert one row per product. That makes queries and integrity checks far easier. Example workflow:

// parse CSV, start a transaction
$items = array_filter(explode(',', rtrim($products, ',')));

$conn->begin_transaction();
// insert invoice, get insert_id
// prepare item insert once and loop
foreach ($items as $pair) {
    list($pid, $qty) = explode(':', $pair);
    $itemStmt->bind_param("iii", $invoiceId, $pid, $qty);
    $itemStmt->execute();
}
$conn->commit();

Troubleshooting tips: check prepare() return and inspect $stmt->error / $conn->error; use rtrim + array_filter to avoid empty pairs from trailing commas; avoid addslashes() when using prepared statements; consider JSON column (MySQL 5.7+) only if you genuinely need a single-column payload and can accept the tradeoffs.

Recommended Answers

All 3 Replies

Member Avatar for elamigosam

Thanks

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.