-1

i have a small question on how to create 1k rows as in sample below format in mysql using php

 for ($i = 0;$i < 1000;$i++) {
    $sql = 'INSERT INTO `tt_test` (`id`, `name` ,`year`,`payment`,`address`) VALUES (null,"asdasdsad'.$i.'",rand(2010,2015),rand('pending','success','failed','refunded'),"abcd'.$i.'")';
    $preparesql = $DBH->prepare($sql);
    $executesql = $preparesql->execute(array());
}

can any one help me out.... thank you -kanth

Shankar Narayana Damodaran
  • 68,075
  • 43
  • 96
  • 126
kanth
  • 7
  • 3
  • What exactly is the problem? Your code is not very performant, but the idea is correct - loop and add a row. – kero May 07 '14 at 10:53
  • 1
    I think you forgot to escape the quote. – Kien Truong May 07 '14 at 10:53
  • `$executesql = $preparesql->execute(array());` What's this? Also, your example is PHP and mysql, not only SQL. You're quite confused, aren't you? – briosheje May 07 '14 at 10:54
  • 2
    You can also just concatenate all queries into one string (INSERT can add mutiple value sets with one call) and safe much on performance. – ToBe May 07 '14 at 11:07
  • Anyways, next time please tell us what your actual problem is. Your question is VERY unclear. – ToBe May 07 '14 at 11:07
  • 1
    And you have mixed usage of single and double quotes for values in your resulting sql string. I think MySQL uses single quotes for it's values. – ToBe May 07 '14 at 11:09
  • @ToBe You're right. If perfomance is a problem, kanth has to read this answer: http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql/6889087#6889087 – Stephan Vierkant May 07 '14 at 11:16

4 Answers4

2

You are using prepared statements. You're preparing the query 1000 times, but you only have to prepare it once.

Here's an example using a PDO:

$sql = "INSERT INTO `tt_test` (`name` ,`year`,`payment`,`address`) VALUES (:name,:year,:payment,:address)";
$preparesql = $DBH->prepare($sql);

for ($i = 0;$i < 1000;$i++) {
    $params = array();
    $params[':name'] = 'asdasdsad'.$i;
    $params[':year'] = rand(2010,2015);
    $params[':payment'] = rand('pending','success','failed','refunded');
    $params[':address'] = 'abcd'.$i;
    $executesql = $preparesql->execute($params);
}

It's easier to prepare a query (once!) and then add the parameters to it. Your code is easier to read (and maintain) and you'll prevent SQL injections as well.

Stephan Vierkant
  • 9,674
  • 8
  • 61
  • 97
  • Not to mention it's faster. injection in this case is not the biggest worry though because no outside variables are used. – Stephan May 07 '14 at 11:12
0

Definately look at the link provided by @Mithredate because MySQL gets unwieldy at a certain point but this should make your code work:

 for ($i = 0;$i < 1000;$i++) {
    $sql = 'INSERT INTO `tt_test` (`id`, `name` ,`year`,`payment`,`address`) VALUES (null,"asdasdsad'.$i.'",rand(2010,2015),rand("pending","success","failed","refunded"),"abcd'.$i.'")';
    $preparesql = $DBH->prepare($sql);
    $executesql = $preparesql->execute(array()); }

EDIT: I stuck to what you provided to show you how close you were to getting it work, Stephan Vierkant's solution is a nicer solution once you get what you did wrong in your first attempt.

Stephan
  • 426
  • 5
  • 13
0

Here is how, without PHP: 

create table num(n int not null primary key) engine=InnoDB;

insert num(n) 
values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);


INSERT INTO `tt_test` (`id`, `name`, `year`, `payment`, `address`) 
select
    null, 
    concat('asdasdsad', n1.n + n2.n * 10 + n3.n * 100), 
    floor(2010 + 5 * rand()), 
    elt(floor(1 + 4 * rand()), 'pending', 'success', 'failed', 'refunded'),
    concat('abcd', n1.n + n2.n * 10 + n3.n * 100)
from num n1
join num n2 on 1=1
join num n3 on 1=1;

Pros: you have a "service table" which you can use to generate sequences.

Cons: no PHP :)

Andy W
  • 2,082
  • 1
  • 13
  • 9
-1

Because of the size of your queries, You might ran into execution time problems. This might help you.

Mehrdad Hedayati
  • 1,434
  • 13
  • 14