CODEWITHSUNDEEP
phpmysqljson
David Velasquez
David Velasquez

Reputation: 2366

How can I write my php script to insert multiple rows into database?

I'm still learning about the server side code which is why I have this question. So far I have a php script that inserts one row only. Here it is:

<?php

$response = array();

if(isset($_POST['team_full_name']) && isset($_POST['team_short_name'] && isset($_POST['league']))
{
    $team_fn = $_POST['team_full_name'];
    $team_sn = $_POST['team_short_name'];
    $league = $_POST['league'];

    require_once __DIR__ . '/db_connect.php';

    $db = new DB_CONNECT();
    $result = mysql_query("INSERT INTO teamData(team_full_name, team_short_name, league) 
        VALUES('$team_fn', '$team_sn' '$league')");

    if($result)
    {
        $response["success"] = 1;
        $response["message"] = "Team Data successfully added.";

        echo json_encode($response);
    }
    else
    {
        $response["success"] = 0;
        $response["message"] = "Error occurred!";

        echo json_encode($response);
    }
}
else
{
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing.";
    echo json_encode($response);
}

?>

But in this case, I will be POSTing a JSON string of the entire array holding multiple rows. How do I setup the php script to handle multiple rows?

The class that I'm converting to a JSON string is:

public class TeamData
{
    String mFullName;
    String mShortName;
    String mLeague;

    ....
}

Sorry if I'm forgetting some important information, I'm completely new to working with php and a webserver. I can edit the post if you state what I need to add to make the post clearer.

Upvotes: 1

Views: 48

Answers (1)

O. Jones
O. Jones

Reputation: 108641

You can write multirow INSERT queries like this:

INSERT INTO teamData(team_full_name, team_short_name, league) VALUES
      ('$val', '$val', '$val'),
      ('$val', '$val', '$val'),
      ('$val', '$val', '$val'),
      ('$val', '$val', '$val'),
      ('$val', '$val', '$val')

You can give as many rows' worth of values as you want, as long as you keep your query length to a few dozen kilobytes.

You can also do a bunch of single-row inserts (like you're doing) starting them with 

    BEGIN TRANSACTION;

and ending them with 

    COMMIT;

This will (if you're using InnoDB) give you most of the speed boost of the multirow insert. It also has the sweet characteristic that if your program crashes, the database is left unchanged.

Upvotes: 2

Related Questions