生成随机数并保存在数据库中

Question

On my PHP/MYSQL BASED website sellers can post 10 items of the same kind and nature and value.

I want users to be able to post qty = 10, value = 100. (values are preset in a dropdown and selected by sellers).

At the moment I am able to post a single item only for a preset value and a code is generated as shown in the code given below:

 $sql_query = "INSERT INTO 
 eg_posts(seller_id,value_id,quantity,cdate,egcode) 
 VALUES
 ('$a','$bval','$cqty',now(),CONCAT( CHAR(FLOOR(RAND()*26)+65),FLOOR(100+RAND()*(5000-100))))"; 

The above post is saved with one item, with one alphanumeric code with a preset value chosen by the seller.

I want the user to be able to post more than one items (say 10) of the same kind and nature and value and I want 10 random alphanumeric codes to be generated for these 10 items. And I want all 10 random codes to be save in the database under the post ID.

Answer 1

Using PDO (it will work even if you want to change the values for different values, quantities because it binds each variable separately):

try {
    //Make your connection handler to your database
    $conn = new PDO("mysql:host=".$servername.";dbname=".$database, $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
    $a = 1;
    $bval= 1;
    $cqty = 10;

    $values = array();
    $binds = array();

    $temp = "INSERT INTO eg_posts (seller_id, value_id, quantity, cdate, egcode) VALUES ";

    for($i = 0; $i < 10; $i ++) {
        $values[] = ' (seller_id'.$i.' = :seller_id'.$i.', value_id'.$i.' = :value_id'.$i.', quantity'.$i.' = :quantity'.$i.', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) )))';
        $binds[':seller_id'.$i] = $a;
        $binds[':value_id'.$i] = $bval;
        $binds[':quantity'.$i] = $cqty;
    }
    $sql_query = $temp.implode(', ', $values).';';

    $stmt = $conn->prepare($sql_query);
    $stmt->execute($binds);

} catch(PDOException $e) {
    echo $e->getMessage();
    die();
}

Result:

INSERT INTO eg_posts (seller_id, value_id, quantity, cdate, egcode) 
VALUES 
(seller_id0 = :seller_id0, value_id0 = :value_id0, quantity0 = :quantity0, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
(seller_id1 = :seller_id1, value_id1 = :value_id1, quantity1 = :quantity1, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
(seller_id2 = :seller_id2, value_id2 = :value_id2, quantity2 = :quantity2, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
(seller_id3 = :seller_id3, value_id3 = :value_id3, quantity3 = :quantity3, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
(seller_id4 = :seller_id4, value_id4 = :value_id4, quantity4 = :quantity4, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
(seller_id5 = :seller_id5, value_id5 = :value_id5, quantity5 = :quantity5, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
(seller_id6 = :seller_id6, value_id6 = :value_id6, quantity6 = :quantity6, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
(seller_id7 = :seller_id7, value_id7 = :value_id7, quantity7 = :quantity7, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
(seller_id8 = :seller_id8, value_id8 = :value_id8, quantity8 = :quantity8, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
(seller_id9 = :seller_id9, value_id9 = :value_id9, quantity9 = :quantity9, NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) )));

Plain SQL:

$a = 1;
$bval= 1;
$cqty = 10;

$temp = "INSERT INTO eg_posts (seller_id, value_id, quantity, cdate, egcode) VALUES ";
$values = array();
for($i = 0; $i < 10; $i ++) {
    $values[] = " ('$a', '$bval', '$cqty', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) )))";
}
$sql_query = $temp.implode(', ', $values).';';

echo $sql_query;

Result:

INSERT INTO eg_posts (seller_id, value_id, quantity, cdate, egcode) 
VALUES 
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) ))),
('1', '1', '10', NOW(), CONCAT( CHAR(FLOOR(RAND()*26) + 65), FLOOR(100 + RAND() * (5000-100) )));