Member Avatar for flames1

Hello ppl...

How to insert a list of lines to sql through php and create a id for each line

example....i have

flames
flintones
flames1
....
....
so on

after submitting these lines i need a short id for these lines like (short id can be anything)

1232
1546
1578
..
..
so on

hope i m clear to u...

  • 3 Contributors
  • 2 Replies
  • 132 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by seslie

Dani AI

Generated

A clear, reliable approach is to (a) clean and split the textarea into distinct lines, (b) insert each line safely from PHP, and (c) generate a short, unique token for every row either in the database or in the application. described the basic need; pointed toward a table-based solution and mentioned DB triggers — both are valid starting points but they have different trade-offs (see notes below).

Practical workflow to follow:

  1. Split the submitted text by newline, trim entries, drop empty lines and duplicates, and limit length.
  2. Use prepared statements (PDO or mysqli) and a transaction for atomicity and safety.
  3. Choose how to create the short id:
    • Derive a compact string from the numeric DB id (no collisions, reversible).
    • Generate an opaque token in the app (Hashids or random string) and enforce a UNIQUE index.
    • Use a DB trigger if you want DB-side enforcement (harder to change later).
  4. Store both the original text and the short id (indexed) so lookups are fast.

Small PHP pattern (conceptual, not tied to an existing reply): start a transaction, insert the line, get the numeric id, convert it to a short string (for example via base62 or Hashids), then update the row or insert both values if you precompute the token. That keeps ids short and avoid collisions while remaining fast for moderate batches.

Troubleshooting and tips: prefer prepared statements over deprecated mysql_* calls; for very large imports do batched multi-row inserts and then compute/assign short ids in a second pass; add a UNIQUE index on the short token column to catch collisions; test concurrency if using triggers. For implementation details and libraries see the PHP PDO prepared statements documentation [https://www.php.net/manual/en/pdo.prepared-statements.php], the Hashids project [https://hashids.org/], and the Base62 concept [https://en.wikipedia.org/wiki/Base62].

Recommended Answers

All 2 Replies

Member Avatar for Member #120589

You can create the table with this SQL:

CREATE TABLE `lines` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `line` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Start the auto_increment at any value or delete that parameter to start at 1

Change the varchar and int lengths to whatever you need.

then just add the lines - assume that your lines of text are in an array called $lines:

$values = "('" . implode("'),(NULL,'",$lines) . "')";
$q = mysql_query("INSERT INTO `lines` (`id`,`line`) VALUES $values");
Edited by Member #120589
Member Avatar for seslie

Hello Flames1,

I assume you are trying to insert into a database table through php. Am i right?

First you need to create a trigger. That trigger is where you specify the form of the short ids. You can make it random, or decide to increment by 1 or whatever you prefer.

And write ur code in a way that includes the trigger.

Regards,

Seslie.

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.