Inserting PHP multidimensional arrays into a mySQL database

Discussion in 'Coding Help' started by Ciarán Mc Cann, Jun 10, 2007.

  1. Ciarán Mc Cann

    Ciarán Mc Cann New Member

    Well as the tilte describes I need to know how to insert a multidimensional arrays into a mySQL database, the table I am inseting to has 14 columnes.
    So in every array there is 14 values which all need to be put into the right colunm. The multidimensional arrays has 58 arrays in it which means thats 58 rows of data to input into mysql.

    Yours Thankfully
    Ciaran Mc cann
     
  2. louie

    louie New Member

    use a while statement
    PHP:
    //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
     
  3. Ciarán Mc Cann

    Ciarán Mc Cann New Member

    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) "
     
  4. louie

    louie New Member

    where are those arrays coming from?
     
  5. Ciarán Mc Cann

    Ciarán Mc Cann New Member

    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?
     
  6. gary.b

    gary.b New Member

    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.
     
  7. Ciarán Mc Cann

    Ciarán Mc Cann New Member

    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);
       
    ?> 
     
  8. louie

    louie New Member

    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:
    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_10values('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 CoKildare,09/05/07,ONE OFF HOUSE,NULL,NO,NO')
    PHP:
    <?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($chCURLOPT_HEADER0);
         
    curl_setopt($chCURLOPT_RETURNTRANSFER1);
         
         
    $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);
       
    ?>
     
  9. Ciarán Mc Cann

    Ciarán Mc Cann New Member

  10. Ciarán Mc Cann

    Ciarán Mc Cann New Member

    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
     
  11. louie

    louie New Member

    you are welcome.
    Glad it worked out for you in the end.
     
  12. riddler

    riddler New Member

    I have a similar problem

    Not exactly sure where to start here.. I was asked as a favor to do this for a friend. I am doing a screen scrape w/class_http.php and its dumping the content to an array. I am trying to find a way to take that array content and dump it into a mysql db.

    Here's the next glitch/problem though.
    The way the screen scraper works it keys on a word in the HTML to start where it scrapes the table from.. So what infact I am getting is the field names in one array and the content in the next array.. I am really only interested in the content.

    ANY Help you can provide would be helpful. Basically I am trying to setup an IP Logging DB

    Here is the code/ printout of the array.

    Code:
    Array
    (
        [0] => Array
            (
                [0] =>  Kick 
                [1] =>  Ban 
                [2] =>  Name: 
                [3] =>  Team: 
                [4] =>  Ping: 
                [5] =>  Score: 
                [6] =>  IP 
                [7] =>  Global ID 
            )
     
        [1] => Array
            (
                [0] => 
                [1] => 
                [2] => Bozo(Spectator) 
                [3] =>   Blue
                [4] => 60
                [5] => 0
                [6] =>  25.25.25.20
                [7] =>  e3e6654a9232bf22218e5f2c39e72538
            )
     
    )
    
    (note, this is not a real ip address for security sake)
    =================
    table structure and db
    =================
    Code:
     
    <?php
    $mysqldate = date( ‘Y-m-d’, $phpdate );
    $phpdate = strtotime( $mysqldate );
    // Make a MySQL Connection
    mysql_connect("localhost", "user", "pass") or die(mysql_error());
    mysql_select_db("thedb") or die(mysql_error());
     
    // Create a MySQL table in the selected database
    mysql_query("CREATE TABLE playerdata (
    id int(11) NOT NULL AUTO_INCREMENT, 
    date varchar(30) NOT NULL default '', 
    name varchar(60) NOT NULL default '', 
    score varchar(30) NOT NULL default '', 
    IP varchar(30) NOT NULL default '', 
    GUID varchar(60) NOT NULL default '', 
    PRIMARY KEY  (id)
    );")or die(mysql_error()); 
     
    echo "Table Created!";
     
    ?>
    
    ==========================
    scrape and insert
    ==========================
    Code:
     
    <?php
    /*
    Include the http class. Modify path according to where you put the class
    file.
    */
    require_once(dirname(__FILE__).'/class_http.php');
    /* First, instantiate a new http object. */
    $h = new http();
    /*
    Where do you want to store your cache files?
    Default is current dir. You can set it here, or hard-code in the class.
    You must end this value with a "/".
    */
    $h->dir = "/cache/"; 
    $url = "[URL]http://127.20.25.2/admin/players[/URL]";
    if (!$h->fetch($url, 0, null, "scrapeuser", "scrapepass")) { 
      echo "<h2>There is a problem with the http request!</h2>";
      echo "Status: ".$h->status; 
      echo "<pre>".$h->header."</pre>"; 
      echo $h->log; 
      exit(); 
    } 
    $current_players = http::table_into_array($h->body, "Kick", 1, null); 
    /* Print out the array so you can see the stats data. */ 
    echo "<pre>"; 
    print_r($current_players); 
    echo "</pre>"; 
    $mysqldate = date("Y-m-d");
    echo $mysqldate;
    mysql_connect("localhost", "user", "pass") or die(mysql_error());
    mysql_select_db("thedb") or die(mysql_error());
    $playerdata=serialize($current_players);
     
    $query=INSERT INTO playerdata VALUES('$mysqldate', '$playerdata');
    ?>
    
     
  13. Ciarán Mc Cann

    Ciarán Mc Cann New Member

    My solution

    well the code below I didnt write, but it is how a solved my problem, baiscly what the code does is srcapes data from a table and then while's each row into a mysql_query, then just run them all at the one time, the system I use it for can run up to 200 insertion querys a day and it works find.

    PHP:
       <?php
       
       $myurl 
    $_POST[url];
       
       
    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($chCURLOPT_HEADER0);
         
    curl_setopt($chCURLOPT_RETURNTRANSFER1);
         
         
    $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]
                
    $cell preg_replace('/\'/'' '$cell);
          
         
    $data[$rowcount][$cellcount] = $cell
            
         
    $cell_value .= !empty($cell) ? "'".$cell."'~" "NULL~";//get the cell value
         
    if($cellcount == 0){$field_name "FILE_NUMBER_$cellcount";}//and so on 11 times
         
    if($cellcount == 1){$field_name "APPLICANT_NAME_$cellcount";}
         if(
    $cellcount == 2){$field_name "APPLICATION_TYPE_$cellcount";}
         if(
    $cellcount == 3){$field_name "APPLICATION_RECEIVED_$cellcount";}
         if(
    $cellcount == 4){$field_name "DEVELOPMENT_DESCRIPTION_$cellcount";}
         if(
    $cellcount == 5){$field_name "DEVELOPMENT_LOCATION_$cellcount";}
         if(
    $cellcount == 6){$field_name "DECISION_DATE_$cellcount";}
         if(
    $cellcount == 7){$field_name "DEVELOPMENT_TYPE_$cellcount";}
         if(
    $cellcount == 8){$field_name "PROTECTED_STRUCTURE_$cellcount";}
         if(
    $cellcount == 9){$field_name "WASTE_LICENCE_$cellcount";}
         if(
    $cellcount == 10){$field_name "RECEIVE_MARKETING_MATERIAL_$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).")replace<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 "$myurl";
       
       
    // 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!!!
       
       
    ?> 


     
     
  14. riddler

    riddler New Member

    Hmmm

    Thanks for the quick reply, I will try and run this. But I believe the hosting provider I am using doesn't allow curl for some reason. So I don't think I can use your code, also I haven't seen any curl based php code that will allow me to login to a secured or protected site.. Like my code does. I will see if maybe I can adapt some of your sql statements though..

    Unless of course anyone else has any ideas that would fit my current code.
     
  15. Ciarán Mc Cann

    Ciarán Mc Cann New Member

    Sorry I couldnt be of more help. Just look at the code again, as the sql was abit wrong frist, but I edited. then i get an output of somthing that is almost a corect mysql_query , and then I use a str_replace fuction to clean up the out put.
     
  16. riddler

    riddler New Member

    Ideas

    Actually your code gave me a couple ideas.. heres the WORKING end result.. w/my code.

    I have the page refresh every minute to give an updated push of current info into the DB.

    -Riddler

    Code:
    <META HTTP-EQUIV="refresh" content="60;URL=http://thehostedhost/php/dbPush.php"> 
    <?php
    /*Include the http class. Modify path according to where you put the class file.*/
    require_once(dirname(__FILE__).'/class_http.php');
    /* First, instantiate a new http object. */
    $h = new http();
    $h->dir = "/cache/"; //Cache directory for Screen Scrape
    $url = "[URL="http://126"]http://12[/URL]7.28.60.2/Admin"; // URL
    if (!$h->fetch($url, 0, null, "urluser", "urlpass")) {  //Do the scrape with the username/password at the end to authenticate
      echo "<h2>There is a problem with the http request!</h2>";
      echo "Status: ".$h->status; 
      echo "<pre>".$h->header."</pre>"; 
      echo $h->log; 
      exit(); 
    } 
    $current_players = http::table_into_array($h->body, "Kick", 1, null); 
    /* Print out the array so you can see the stats data. */ 
    //echo "<pre>"; 
    //print_r($current_players); 
    //echo "</pre>"; 
    $mysqldate = date("Y-m-d"); //DateStamp for entry into MySQL
    $size=sizeof($current_players); //Check the size of the array (See how many rows are returned by the scrape)
    $playernum=count($current_players)-1; //Scrub the Rows to remove the first Row of the Scrape.. Showing the Number of Players online.
    $dbh = mysql_connect("thehost", "thetableuser", "thepasswd") or die(mysql_error()); //Connect to your MySQL DB
    mysql_select_db("thedb") or die(mysql_error()); // Select the DB
    for($c = 1; $c <= sizeof($current_players); $c++) {  //Insert in a loop the players that are online to the DB
       if ($current_players[$c][2] != ''){
         $playername = html_entity_decode($current_players[$c][2]);
         $playerIP = html_entity_decode($current_players[$c][6]);
         $playerGUID = html_entity_decode($current_players[$c][7]);
     
     //check for duplicates 
     $query="SELECT * FROM playerdata WHERE IP='$playerIP' AND GUID='$playerGUID' "; 
     $result=mysql_query($query); 
     if(mysql_num_rows($result)==0) { 
     //end check for duplicate 
          $query = "INSERT INTO playerdata (date, name, IP, GUID) VALUES ('$mysqldate', '$playername', '$playerIP', '$playerGUID')";
               $result = mysql_query($query) or die(mysql_error());
          }     
       }
    }
    echo "<H2>Insert Complete</H2>";
    ?>
     

Share This Page