mysqli中每行的唯一ID

Question

Here is the code that I've used

html:

<form action = 'insert.php' method='POST'>
<input type = "text" name = "name">
<input type = "submit" name = "submit">
</form>

php:

<?php
$servername='localhost';
$username='noob';
$password='password';
    $conn = mysqli_connect($servername, $username, $password);
    if (!$conn) 
        die("Connection failed: " . mysqli_connect_error());
$a=$_POST['name'];
mysqli_query("INSERT INTO table1 (name) VALUES ('$a')");
?>

Right now, the code above inserts a name into a table called table 1. My question now is, say I input many names into the table, but I want each name to have a unique 6 character/digit id. How would I change this code? For example, after 3 inserts, the table should look like this:

[table 1]
name               unique_id
NAME1              994323
NAME2              832344
NAME3              332123

Each value of unique_id must be unique. Is there any efficient way to do this?

Answer 1

For the random code generation you can use RAND() as To obtain a random integer N in the range a <= N < b, just use the expression FLOOR(a + RAND() * (b – a)) as described in mysql docs

Your query is vulnerable to SQL Injection, fix it with mysqli_real_escape_string !

$name = mysqli_real_escape_string($_POST['name']);
$result = mysqli_query("SELECT FLOOR(100000 + RAND() * 990000) AS random_num
    FROM table1 
    WHERE random_num NOT IN (SELECT table1.code FROM table1)
    LIMIT 1");
$row = mysqli_fetch_row($result);
$code = $row[0];

mysqli_query("INSERT INTO table1 (name, code) VALUES ('$name', '$code')");

This is the working query for random digit generation: http://sqlfiddle.com/#!9/2acd3/1

EDIT:

Personally I prefer PDO instead of mysqli functions, anyway,

someone asked my why I suggest the PDO instead of MySQLi function. This is the way that I suggested, You can use whatever you want

• Database Support

  • PDO has 12 different drivers
  • MySQLi has only Mysql driver

• API

  • PDO: only OOP (avoiding bad practice)
  • MySQLi OOP or Procedural

• Named parameters

  • PDO: yes
  • MySQLi no

• Prepared statements (client side)

  • PDO: yes
  • MySQLi: no