Thread: Inserting PHP multidimensional arrays into a mySQL database

use a while statement

PHP Code:

//start loop 
 
// Field name
 $theValue = (!get_magic_quotes_gpc()) ? addslashes(array_value) : array_value; 
 $theValue = ($theValue != "") ? " '" . $theValue . "'" : "NULL";
 $fieldList["`field_name`"] = $theValue;
//and so on for each field
 
//then you execute the query
// Insert
  $sSql = "INSERT INTO `table_name` (";
  $sSql .= implode(",", array_keys($fieldList));
  $sSql .= ") VALUES (";
  $sSql .= implode(",", array_values($fieldList));
  $sSql .= ")"; 
  mysql_query($sSql, $conn) or die("Failed to execute the insert " . __LINE__ . ": " . mysql_error($conn) . '<br>SQL: ' . $sSql);
 
//end loop 


ok right ya I still not to sure what I have to sub in there. could you do the example again using this array data.Note this isnt the actaull array.

Kind Regards
Ciaran Mc Cann

Code:

array(2) {
  [0]=>
array(11) {
    [0]=>
    string(7) "05/2146"
    [1]=>
    string(1) "M
    [2]=>
    string(1) "P"
    [3]=>
    string(8) "08/09/05"
    [4]=>
    string(5) "house"
    [5]=>
    string(7) "Kildare"
    [6]=>
    string(8) "09/05/07"
    [7]=>
    string(3) "ONE"
    [8]=>
    string(0) ""
    [9]=>
    string(2) "NO"
    [10]=>
    string(2) "NO"
  }
  [1]=>
  array(11) {
    [0]=>
    string(7) "05/2957"
    [1]=>
    string(11) "B"
    [2]=>
    string(10) "P"
    [3]=>
    string(8) "16/12/05"
    [4]=>
    string(3) "bun"
    [5]=>
    string(4) "wood"
    [6]=>
    string(8) "10/05/07"
    [7]=>
    string(3) "ONE"
    [8]=>
    string(0) ""
    [9]=>
    string(2) "NO"
    [10]=>
    string(2) "NO"
  }
[2]=>
  array(11) {
    [0]=>
    string(7) "05/2957"
    [1]=>
    string(11) "B"
    [2]=>
    string(10) "P"
    [3]=>
    string(8) "16/12/05"
    [4]=>
    string(3) "bun"
    [5]=>
    string(4) "wood"
    [6]=>
    string(8) "10/05/07"
    [7]=>
    string(3) "ONE"
    [8]=>
    string(0) ""
    [9]=>
    string(2) "NO"
    [10]=>
    string(2) "

where are those arrays coming from?

A data scraper which scraped the information from a table and now I am wishing to insert it into my mySQL database with the exact same col headings and that. if you would like to see the data scraper script I will post it?

if you would like to see the data scraper script I will post it?

Yeah go for it. Might get a better idea of what you need.

I think your solution is a simple nested While loop (if i'm understanding it right.. maybe not - Im' crap at articulating these things.)

You'd take each element from the "array(2) {..." array, then loop throught each value inserting it into the appropriate row. and so on.

just to let yea know i didnt write this.

Code:

<?php

   error_reporting(E_ALL);
   //
   // BIG FILE, we need some extra time to load it!
   //
   ini_set("max_execution_time", 120);

   function getTable($url) {
   
     //
     // 1. Fetch content of page
     //
     $ch = curl_init($url);
     curl_setopt($ch, CURLOPT_HEADER, 0);
     curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
     
     $content = curl_exec($ch);
     
     curl_close($ch);
     
     //
     // 2. Process content of page
     //
     
     // Remove everything before the table
     $temp = preg_split("/section.34.*?<table[^>]*>/si", $content);
     $content = $temp[1];
     
     // Remove everything after table
     $content = preg_replace("/<\/table>.*$/si", "", $content);
     
     // Spit rows
     $rows = preg_split("/<\s*tr[^>]*>/i", $content);
       
     // Remove the first element, which doesn't contain a row
     // and the second element which contains the header
     array_shift($rows);
     array_shift($rows);
     
     //
     // Process each row
     //
     
     $data = array();
     $rowcount = 0;
     
     foreach ($rows as $row) {
        
        $data[$rowcount] = array();
        
        // Remove HTML comments
        $row = preg_replace("/<!--.*?-->/s", "", $row);
        
        // Convert &nbsp; to spaces
        $row = preg_replace("/&nbsp;/i", " ", $row);
        
        // Split row to cells
        $cells = preg_split("/<\s*td[^>]*>/i", $row);
        
        // Remove first element, which doesn't contain a cell
        array_shift($cells);
        
        // Process each cell
        $cellcount = 0;
        foreach ($cells as $cell) {
           // Convert <BR>'s to spaces
           $cell = preg_replace("/<br>/i", " ", $cell);
           // Remove HTML-tags
           $cell = preg_replace("/<.*?>/s", "", $cell);
           // Remove starting and trailing whitespace
           $cell = ltrim(rtrim($cell));
           // Remove multiple spaces
           $cell = preg_replace('/\s\s+/', ' ', $cell);
           // Store cell contents in $data[row][column]
           $data[$rowcount][$cellcount] = $cell;
           $cellcount++;
        }
        $rowcount++;
     }
        
     return $data;   
        
   }
   
   // The URL of the page which contains the table to get the data from
   $url = 'http://webiplan.kildarecoco.ie/publiciplan/Email/week.asp?SD=9/5/07&ED=16/5/07&AC=K';
   
   // getTable($url) returns an array containing all data from the table:
   //   $data[row][column] (both start at zero)
   //   
   // for example:
   //  $data[0][0] the data from the first cell from the first row
   //  $data[2][4] the fifth cell from the third row
   //
   // to get the last cell from the last row:
   //  $rows = count($data);
   //  $cols = count($data[0]);
   //  $lastcell = $data[$rows-1][$cols-1];
   //
   $data = getTable($url);
   
   // Lets dump all the contents of $data to see what's in it
   // Look at the source of the page!!!
   var_dump($data);
   
?>

try this but change the field_name to suit your database and the insert statement plus you need to prepare the fields for the database.
Just a rough example

it will give something like this

PHP Code:

insert into(name_of_the field_0,name_of_the field_1,name_of_the field_2,name_of_the field_3,name_of_the field_4,name_of_the field_5,name_of_the field_6,name_of_the field_7,name_of_the field_8,name_of_the field_9,name_of_the field_10) values('05/2146,Mandy Murphy,PERMISSION,08/09/05,2 storey dwelling with garage attached, new entrance, bored well, waste water treatment and all ancillary site works,St. John's Castledermot Co. Kildare,09/05/07,ONE OFF HOUSE,NULL,NO,NO') 


PHP Code:

<?php
   error_reporting(E_ALL);
   //
   // BIG FILE, we need some extra time to load it!
   //
   ini_set("max_execution_time", 120);
   function getTable($url) {
   
     //
     // 1. Fetch content of page
     //
     $ch = curl_init($url);
     curl_setopt($ch, CURLOPT_HEADER, 0);
     curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
     
     $content = curl_exec($ch);
     
     curl_close($ch);
     
     //
     // 2. Process content of page
     //
     
     // Remove everything before the table
     $temp = preg_split("/section.34.*?<table[^>]*>/si", $content);
     $content = $temp[1];
     
     // Remove everything after table
     $content = preg_replace("/<\/table>.*$/si", "", $content);
     
     // Spit rows
     $rows = preg_split("/<\s*tr[^>]*>/i", $content);
       
     // Remove the first element, which doesn't contain a row
     // and the second element which contains the header
     array_shift($rows);
     array_shift($rows);
     
     //
     // Process each row
     //
     
     $data = array();
     $rowcount = 0;
     
     foreach ($rows as $row) {
        
        $data[$rowcount] = array();
        
        // Remove HTML comments
        $row = preg_replace("/<!--.*?-->/s", "", $row);
        
        // Convert &nbsp; to spaces
        $row = preg_replace("/&nbsp;/i", " ", $row);
        
        // Split row to cells
        $cells = preg_split("/<\s*td[^>]*>/i", $row);
        
        // Remove first element, which doesn't contain a cell
        array_shift($cells);
        
        // Process each cell
        $cellcount = 0;
  $cell_value = "";
  $field_count = "";
        foreach ($cells as $cell) {
           // Convert <BR>'s to spaces
           $cell = preg_replace("/<br>/i", " ", $cell);
           // Remove HTML-tags
           $cell = preg_replace("/<.*?>/s", "", $cell);
           // Remove starting and trailing whitespace
           $cell = ltrim(rtrim($cell));
           // Remove multiple spaces
           $cell = preg_replace('/\s\s+/', ' ', $cell);
           // Store cell contents in $data[row][column]
           $data[$rowcount][$cellcount] = $cell; 
     $cell_value .= !empty($cell) ? "'".$cell."'~" : "NULL~";//get the cell value
     if($cellcount == 0){$field_name = "name_of_the field_$cellcount";}//and so on 11 times
     if($cellcount == 1){$field_name = "name_of_the field_$cellcount";}
     if($cellcount == 2){$field_name = "name_of_the field_$cellcount";}
     if($cellcount == 3){$field_name = "name_of_the field_$cellcount";}
     if($cellcount == 4){$field_name = "name_of_the field_$cellcount";}
     if($cellcount == 5){$field_name = "name_of_the field_$cellcount";}
     if($cellcount == 6){$field_name = "name_of_the field_$cellcount";}
     if($cellcount == 7){$field_name = "name_of_the field_$cellcount";}
     if($cellcount == 8){$field_name = "name_of_the field_$cellcount";}
     if($cellcount == 9){$field_name = "name_of_the field_$cellcount";}
     if($cellcount == 10){$field_name = "name_of_the field_$cellcount";}
     $field_count .= $field_name."~";//and so on 11 times
     $field_name = ""; //reset value
           $cellcount++;    
        }
   
     $field_count = rtrim($field_count,"~");//trim last ~
     $cell_value = rtrim($cell_value,"~");//trim last ~
     
     //here prepare the fields for insert and run mysql_query
     echo "insert into(".str_replace("~",",",$field_count).") values(".str_replace("~",",",$cell_value).")<br />";
     $cell_value = "";//clear values after insert
   $field_count = "";//clear values after insert
        $rowcount++;
     }
        
     return $data;   
        
   }
   
   // The URL of the page which contains the table to get the data from
   $url = 'http://webiplan.kildarecoco.ie/publiciplan/Email/week.asp?SD=9/5/07&ED=16/5/07&AC=K';
   
   // getTable($url) returns an array containing all data from the table:
   //   $data[row][column] (both start at zero)
   //   
   // for example:
   //  $data[0][0] the data from the first cell from the first row
   //  $data[2][4] the fifth cell from the third row
   //
   // to get the last cell from the last row:
   //  $rows = count($data);
   //  $cols = count($data[0]);
   //  $lastcell = $data[$rows-1][$cols-1];
   //
   $data = getTable($url);
   
   // Lets dump all the contents of $data to see what's in it
   // Look at the source of the page!!!
   var_dump($data);
   
?>

edit: oops

Thank you so much, I did have to change the code abit to escape a few things and that but all works fine now. Your a life saver, I have about 5000+ records to log into the database and I will have 100 or so every week so I need a nice fast method to do so. Thanks a million.

Yours Thankfully
Ciaran Mc Cann