Web Analytics Made Easy -
StatCounter INSERT array into a database as seperate rows - CodingForum

Announcement

Collapse
No announcement yet.

INSERT array into a database as seperate rows

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • INSERT array into a database as seperate rows

    hello I have been working on breaking down a report into separate rows which I have done now,
    now I am left with this report broken into 2 separate array's,
    the starting report looks like this:
    Alberto 5
    Brs_Edu2 5
    CarminhaCCR 5
    Chessykatz 5
    da_terminator_x 5
    IFeelADouble6 5
    MRC_Melisa_ 5
    UBG_Angel_D_8 5
    victor54 5
    devinm21 60
    poppop 60
    TM7_CharrM5_ 90
    MC_Vicky 120
    so I used this code to break it down,

    Code:
    	//Get each record based on line breaks
    $records = explode(PHP_EOL, $_POST['points']);
     
    //Trim each array value
    $records = array_map('trim',$records);
     
    //loop thorough each record
    foreach($records as $record)
    {
        //Get position of last space
        $lastSpace = strrpos($record, ' ');
        //Get player name and points
        $player = trim(substr($record, 0, $lastSpace));
        $points = trim(substr($record, $lastSpace+1));
     
    
    
    	$sql = "INSERT INTO bg_points (player_name, points)
     VALUES ('$player', '$points')";
    
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    
    $conn->close();
    
    	  
       echo "Player Name: '{$player}', Points: {$points}<br>\n";
    	
    }
    I thought this would INSERT into my database as separate rows but I'm getting an error after the firs row inserts,
    New record created successfullyPlayer Name: 'Alberto', Points: 5
    Error: INSERT INTO bg_points (player_name, points) VALUES ('Brs_Edu2', '5')
    Player Name: 'Brs_Edu2', Points: 5
    Error: INSERT INTO bg_points (player_name, points) VALUES ('CarminhaCCR', '5')
    Player Name: 'CarminhaCCR', Points: 5
    Error: INSERT INTO bg_points (player_name, points) VALUES ('Chessykatz', '5')
    Player Name: 'Chessykatz', Points: 5
    Error: INSERT INTO bg_points (player_name, points) VALUES ('da_terminator_x', '5')
    Player Name: 'da_terminator_x', Points: 5
    Error: INSERT INTO bg_points (player_name, points) VALUES ('IFeelADouble6', '5')
    Player Name: 'IFeelADouble6', Points: 5
    Error: INSERT INTO bg_points (player_name, points) VALUES ('MRC_Melisa_', '5')
    Player Name: 'MRC_Melisa_', Points: 5
    Error: INSERT INTO bg_points (player_name, points) VALUES ('UBG_Angel_D_8', '5')
    Player Name: 'UBG_Angel_D_8', Points: 5
    Error: INSERT INTO bg_points (player_name, points) VALUES ('victor54', '5')
    Player Name: 'victor54', Points: 5
    Error: INSERT INTO bg_points (player_name, points) VALUES ('devinm21', '60')
    Player Name: 'devinm21', Points: 60
    Error: INSERT INTO bg_points (player_name, points) VALUES ('poppop', '60')
    Player Name: 'poppop', Points: 60
    Error: INSERT INTO bg_points (player_name, points) VALUES ('TM7_CharrM5_', '90')
    Player Name: 'TM7_CharrM5_', Points: 90
    Error: INSERT INTO bg_points (player_name, points) VALUES ('MC_Vicky', '120')
    Player Name: 'MC_Vicky', Points: 120
    do I need to use
    Code:
     for($i = 0; $i<$player_count; $i++)
    just before the sql insert statement and add [$I] to the $player and the $points to make it scroll threw this?

  • #2
    First off, are you using mysqli or PDO? Not entirely clear on that. Either way, you should be using what's called POEM to do this, prepare once, execute mostly. That means using prepared queries and NONE of that mouth-breathing ignorant "blindly dumping your variables into the query string" RUBBISH that really had NO business EVER being done with SQL in the first place. (despite it being the ONLY way to add values to a query in the now defunct mysql_ functions).

    mysqli:

    Code:
    $stmt = $sql->prepare('
    	INSERT INTO bg_points (
    		player_name, points
    	) VALUES (
    		?, ?
    	)
    ');
    
    $stmt->bindParam('si', $player, $points);
    
    $records = explode(PHP_EOL, $_POST['points']);
    foreach ($records as $record) {
    	list($player, $points) = explode(' ', $record);
    	$player = trim($player);
    	$points = trim($points);
    	$stmt->execute();
    }
    PDO:

    Code:
    $stmt = $sql->prepare('
    	INSERT INTO bg_points (
    		player_name, points
    	) VALUES (
    		:player, :points
    	)
    ');
    
    $stmt->bindParam(':player', $player, PDO::PARAM_STR);
    $stmt->bindParam(':points', $points, PDO::PARAM_INT);
    
    $records = explode(PHP_EOL, $_POST['points']);
    foreach ($records as $record) {
    	list($player, $points) = explode(' ', $record);
    	$player = trim($player);
    	$points = trim($points);
    	$stmt->execute();
    }
    Far, far simpler, no? POEM -- it's really brilliant and easy. No more screwing around with pasting values into the query string. Big bonus, it's also auto sanitized.

    I'd also consider doing a preg_replace of all whitespace after splitting it into separate lines of all whitespace to a single space, then you'd likely not need the extra trim -- might also be worth limiting the results from explode. That would go something like:

    Code:
    $records = explode(PHP_EOL, $_POST['points']);
    foreach ($records as $record) {
    	$record = trim(preg_replace('/\s+/', ' ', $record));
    	list($player, $points) = explode(' ', $record, 2);
    	$stmt->execute();
    }
    Last edited by deathshadow; Oct 4, 2016, 10:56 PM.
    Walk the dark path, sleep with angels, call the past for help.
    https://cutcodedown.com
    https://medium.com/@deathshadow

    Comment


    • #3
      I have updated this code since I last posted to a large degree.
      I have now added the needed STM PREPARE and such things,
      but for some reason now this code has stopped sending any results to my DB can anyone see what's going wrong as I am NOT getting no errors reported back,


      Code:
      <?php 
      
          error_reporting( E_ALL );
          ini_set("display_errors",1);
      
          $debugmode=true;
      
      
          require_once("includes/session.php");
          require_once("includes/functions.php");
      
      
          confirm_logged_in();
      
          $errors = array();
      
          include("includes/header.php");
      
      
          $servername = "localhost";
          $username = "*******";
          $password = "********";
          $dbname = "*************";
      
          $conn = new mysqli( $servername, $username, $password, $dbname );
          if( $conn->connect_error ) {
              die( $debugmode ? "Connection failed: " . $conn->connect_error : 'Unable to connect to database' );
          } 
      
          $records = explode( PHP_EOL, $_POST['points'] );
          $records = array_map( 'trim', $records );
          $status = array();
      
          foreach( $records as $record ) {
              $lastSpace = strrpos( $record, ' ' );
              $player = trim( substr( $record, 0, $lastSpace ) );
              $points = trim( substr( $record, $lastSpace+1 ) );
      
              $get_list = "select `player_name` from `bg_points`";
              $result = $conn->query($get_list);
      
      
      
      
      
              $sql = "INSERT into `bg_points` (`player_name`, `points`) values (?, ?, ? )
                      on duplicate key update player_name = ?, points = + ?";
              $stmt = $conn->prepare( $sql );
              if( $stmt ){
                  $stmt->bind_param( 'si', $player, $points );
                  $result = $stmt->execute();
      
                  /* keep track of sql operation status */
                  $status[ $player ]= $result ? 'New record created successfully' : 'Error: unable to execute insert';
              }
      
      
              echo "Player Name: '{$player}', Points: {$points}<br>\n";
          }
      
      
      /*
          if( $debugmode ){
              echo '<pre>',print_r($status,true),'</pre>';
          } */
      
      
         // include("includes/footer.php");
          $conn->close();
      ?>

      Comment

      Working...
      X