Member Avatar for SimonIoa

Hello the same issue has troubled me in the past.
I have a autocomplete script that the user chooses people to send them something.
I use to store multiple values in one row. That is the id of the selected people that the user chooses.
Thats not the proper thing to do and i changed it.
I have created another table that stores each value with the same id.

Here is an example of what i am trying to store.

* a_id = 1       to_user_id = 103
* a_id = 1       to_user_id = 107
* a_id = 1       to_user_id = 115

i mean that the a_id stays the same but the to_user_id will change upon the INSERT statement.

How do i do that?

Edited by SimonIoa because: edit
  • 4 Contributors
  • 7 Replies
  • 275 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by SimonIoa

Dani AI

Generated

Short answer: insert the main row into the first table, get its AUTO_INCREMENT value, then insert one row per recipient into the second table. The second table should allow repeated a_id values (use a composite primary key of (a_id, to_user_id) or a unique constraint to prevent duplicates). This is the separate-table approach mentioned by and is the correct pattern for one-to-many relationships.

Example table definition (second table):

CREATE TABLE ad_recipients (
  a_id INT NOT NULL,
  to_user_id INT NOT NULL,
  PRIMARY KEY (a_id, to_user_id),
  FOREIGN KEY (a_id) REFERENCES table_one(a_id) ON DELETE CASCADE
) ENGINE=InnoDB;

Example insertion pattern using PDO (transaction + prepared statements):

$pdo->beginTransaction();

$stmtMain = $pdo->prepare("INSERT INTO ads (title, description, user_id) VALUES (:t, :d, :u)");
$stmtMain->execute([':t'=>$title, ':d'=>$desc, ':u'=>$uid]);
$aId = $pdo->lastInsertId();

$stmtRecip = $pdo->prepare("INSERT IGNORE INTO ad_recipients (a_id, to_user_id) VALUES (:a, :r)");
foreach ($recipients as $r) {
  $r = (int)$r;
  if ($r <= 0) continue;
  $stmtRecip->execute([':a' => $aId, ':r' => $r]);
}

$pdo->commit();

For higher throughput build a single multi-row INSERT instead of looping (build placeholders and one execute). Validate and sanitize recipient IDs (array_map('intval', ...), array_filter), optionally check that each recipient exists in the users table, and wrap everything in a transaction so a failure rolls back both the main row and the recipient inserts. Use INSERT IGNORE or the composite primary key to avoid exact-duplicate rows, or ON DUPLICATE KEY UPDATE to handle updates.

Notes on form data: the form must submit an array of IDs (e.g., multiple inputs with array-style names or JSON/AJAX). The earlier form-control discussion in the thread is orthogonal to the database logic; ensure the server receives a clean array of integer IDs before inserting.

Recommended Answers

All 7 Replies

Member Avatar for SimonIoa

here is the code

html
<input type="hidden" class="selected_ids" value="selected_ids" name="selected_ids[]" multiple="yes" id="selected_ids" />

php

    $s = array($_POST['selected_ids']);
    foreach($s as $sa)
    {
        $toUser = $sa;
    }
Member Avatar for jstfsklh211

name="name[]"
helps with multiple selects or multiple fields with the same name

in your case i'd use a comma separated list and parse the posted value

Member Avatar for SimonIoa

I dont have a problem retrieving the data, i dont know to put them in the second table.

the second table has two columns
a_id
to_user_id

Whats the mysql syntax to store the same id but different to_user_id

Member Avatar for rubberman

It depends upon the table definition. If a_id is unique, then you need to alter the table structure so that a_id is no longer unique and the primary key is composed of a_id and to_user_id. Then, you can have multiple rows to the same a_id, but each a_id can only have one copy of to_user_id. SQL does not deal well with multiple values like you want unless you do this, or create a secondary table that is linked (joined) to the first one via a_id, and the first table has no to_user_id. Unless your table has other data associated with a_id (such as name, address, whatever), then the first suggestion I made would be optimal. If it does have such info, then my second suggestion would be appropriate.

Member Avatar for SimonIoa

Yes i did already did your second suggestion. What would be the script to insert the values?

Member Avatar for SimonIoa

Insert_Ads($title,$desc,$uid,$toUser)
This is the script to put data on table one
"INSERT INTO table_one(a_title,a_desc,user_id)VALUES('$title','$desc','$uid')"

how should the script will be to insert to table two the AUTO_INCREMENT a_id from table_one and the multiple values of $toUser?

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.