1

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.

David Velasquez
  • 2,346
  • 1
  • 26
  • 46
  • The `mysql_*` functions in PHP are deprecated and shouldn't be used. Please read [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for information on why and what to replace them with. – Matt Raines Apr 24 '16 at 18:13

1 Answers1

2

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I should've added this on the post, but I will not have a fixed size of teams to add, it will be changing. Unless I'm misunderstanding, I don't think I can specify the number of rows. Maybe it would be better to do a bunch of single-row inserts in a loop controlled by my Java program, but I'm not sure how to do that, so if you could post a code sample of how to use the `BEGIN TRANSACTION` and the `COMMIT` statements you made, that would be very helpful. – David Velasquez Apr 24 '16 at 16:55
  • a short note though: not "a few dozen kilobytes", upper limit is mysql's max_allowed_packet. – strangeqargo Apr 24 '16 at 17:04