Inserting PHP multidimensional arrays into a mySQL database

Status
Not open for further replies.

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
 

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
 

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) "
 

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?
 

gary.b

New Member
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.
 

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);
   
?>
 

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_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:
<?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);
   
?>
 

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
 

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');
?>
 

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($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]
            $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!!!
   
   
?>
 

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.
 

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.
 

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>";
?>
 
Status
Not open for further replies.
Top