CODEWITHSUNDEEP
phpmysqli
SilenceIsGolden
SilenceIsGolden

Reputation: 59

Inserting 1000 results in my database. Automatically

I'm building a small application that will work like a phone agenda. I don't think that is really usefull for internet users, but I'm doing it just to learn a bit of php and mysql.

Right now, I'm on the part where I want to check and improve the performance of my data base. For that I would like to add ~ 1.000 contacts to be able to test some indexes and to get relevant results.

I would like to tell me if that is possible and how would you recommend me to do it. In my database I have: id (autoincremented) | email (pk) | name | sex | school

require_once("connection.php");
$sql = "INSERT INTO contacts(id,email, name, sex, school) VALUES ";
for ($i = 0; $i < 1000; $i++) {
    $sql .= "('".$id1[$i]."','".$email[$i].","male", "Computer Science"),";
}
rtrim($sql, ',');



$result = mysqli_query($con, $sql); 
if ( false===$sql ) { 
printf("error: %s\n", mysqli_error($con)); 
}

I have one problem with that! My email need to be unique since is a PK. I want something like [email protected], [email protected], [email protected]. Any ideea how to do it?

Upvotes: 0

Views: 101

Answers (5)

AeJey
AeJey

Reputation: 1457

Try this code. This will help you to have unique values for each rows and so you can perform various tests on this data without any confusion.

I have used mysqli_ object type querying. You can use the one you prefer instead.

for ($i = 0; $i < 1000; $i++) {
    $email = $db->escape_string('email'.$i.'@example.com');
    $name = $db->escape_string('Person Name '.$i);
    $gender = array("male","female");
    $sex = $db->escape_string($gender[array_rand($gender,1)]);
    $school = $db->escape_string('School Name '.$i);
    $sql = "INSERT INTO contacts(id, email, name, sex, school) VALUES ('', '$email', '$name', '$sex', '$school')";
    if(!$result = $db->query($sql))
    {
        die('There was an error running the query [' . $db->error . ']');
    }
}

Upvotes: 0

Phil
Phil

Reputation: 164811

A prepared statement would make this very easy

$stmt = $con->prepare("INSERT INTO contacts(email, name, sex, school) VALUES (?, ?, 'male', 'Computer Science')");
$stmt->bind_param('ss', $email, $name);

for ($i = 1; $i <= 1000; $i++) {
    $email = "user{$i}@example.com";
    $name = "Test Name $i";
    $stmt->execute();
}

I would also set the primary key on the id column and add a unique constraint to email.

Upvotes: 1

Jite
Jite

Reputation: 5847

First off, I would recommend using prepared statements instead of a mysqli_query call, prepared statements are awesome for this type of work.

When it comes to the email being unique, just concat a string with the index of the loop: 

$email = $i . "[email protected]";

And they will be:
[email protected]
[email protected]
===>
[email protected]

Upvotes: 3

Dan Sherwin
Dan Sherwin

Reputation: 723

$email = uniqid();
$sql .= "('".$id1[$i]."','".$email."@gmail.com','male', 'Computer Science'),";

Upvotes: -2

Manjeet Sharma
Manjeet Sharma

Reputation: 174

try

require_once("connection.php");
$sql = "INSERT INTO contacts(id,email, name, sex, school) VALUES ";
for ($i = 0; $i < 1000; $i++) {
    $sql .= "('".$id1[$i]."','".$i.$email[$i].","male", "Computer Science"),";
}
rtrim($sql, ',');



$result = mysqli_query($con, $sql); 
if ( false===$sql ) { 
printf("error: %s\n", mysqli_error($con)); 
}

Upvotes: 0

Related Questions