Irish SEO,  Marketing & Webmaster Discussion

 

Inserting PHP multidimensional arrays into a mySQL database

This is a discussion on Inserting PHP multidimensional arrays into a mySQL database within the Coding Help forums, part of the Webmaster Help category; Well as the tilte describes I need to know how to insert a multidimensional arrays into a mySQL database, the ...


Go Back   Irish SEO, Marketing & Webmaster Discussion > Webmaster Help > Coding Help

Register Forum Rules FAQDonate Calendar Search Today's Posts Mark Forums Read

  #1 (permalink)  
Old 10-06-2007, 12:55 PM
Coder
Recent Blog:
 
Join Date: Mar 2007
Location: Kildare Ireland
Posts: 59
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
Ciarán Mc Cann will become famous soon enough
Send a message via MSN to Ciarán Mc Cann
Default Inserting PHP multidimensional arrays into a mySQL database

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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #2 (permalink)  
Old 10-06-2007, 04:26 PM
louie's Avatar
Senior Member
 
Join Date: Jan 2006
Location: Dublin, Ireland
Posts: 2,204
Nominated 6 Times in 4 Posts
Nominated TOTW/F/M Award(s): 1
louie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud of
Send a message via Yahoo to louie Send a message via Skype™ to louie
Default

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 
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #3 (permalink)  
Old 10-06-2007, 06:14 PM
Coder
Recent Blog:
 
Join Date: Mar 2007
Location: Kildare Ireland
Posts: 59
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
Ciarán Mc Cann will become famous soon enough
Send a message via MSN to Ciarán Mc Cann
Default

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) "
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #4 (permalink)  
Old 10-06-2007, 08:37 PM
louie's Avatar
Senior Member
 
Join Date: Jan 2006
Location: Dublin, Ireland
Posts: 2,204
Nominated 6 Times in 4 Posts
Nominated TOTW/F/M Award(s): 1
louie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud of
Send a message via Yahoo to louie Send a message via Skype™ to louie
Default

where are those arrays coming from?
__________________
:. Web Design & Development Web Design Ireland
:. Search Engines Optimization Search Engines Optimization
:. Car Parts & Accessories Car Parts
:. Cars Ireland Cars Ireland
:. I Have 2 Find It Directory SEF Directory
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #5 (permalink)  
Old 10-06-2007, 09:38 PM
Coder
Recent Blog:
 
Join Date: Mar 2007
Location: Kildare Ireland
Posts: 59
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
Ciarán Mc Cann will become famous soon enough
Send a message via MSN to Ciarán Mc Cann
Default

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?
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #6 (permalink)  
Old 10-06-2007, 10:02 PM
insert-witty-remark-here
 
Join Date: Oct 2006
Location: Dundalk
Posts: 167
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
gary.b will become famous soon enough
Default

Quote:
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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #7 (permalink)  
Old 10-06-2007, 10:06 PM
Coder
Recent Blog:
 
Join Date: Mar 2007
Location: Kildare Ireland
Posts: 59
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
Ciarán Mc Cann will become famous soon enough
Send a message via MSN to Ciarán Mc Cann
Default

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);
   
?>
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #8 (permalink)  
Old 10-06-2007, 11:10 PM
louie's Avatar
Senior Member
 
Join Date: Jan 2006
Location: Dublin, Ireland
Posts: 2,204
Nominated 6 Times in 4 Posts
Nominated TOTW/F/M Award(s): 1
louie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud oflouie has much to be proud of
Send a message via Yahoo to louie Send a message via Skype™ to louie
Default

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

Last edited by louie; 10-06-2007 at 11:17 PM.. Reason: revised code
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #9 (permalink)  
Old 10-06-2007, 11:22 PM
Coder
Recent Blog:
 
Join Date: Mar 2007
Location: Kildare Ireland
Posts: 59
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
Ciarán Mc Cann will become famous soon enough
Send a message via MSN to Ciarán Mc Cann
Default

edit: oops

Last edited by Ciarán Mc Cann; 10-06-2007 at 11:25 PM..
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
  #10 (permalink)  
Old 11-06-2007, 12:32 PM
Coder
Recent Blog:
 
Join Date: Mar 2007
Location: Kildare Ireland
Posts: 59
Nominated 0 Times in 0 Posts
TOTW/F/M Award(s): 0
Ciarán Mc Cann will become famous soon enough
Send a message via MSN to Ciarán Mc Cann
Default

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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!Spurl this Post!Reddit! Wong this Post!
Reply With Quote
Reply

Tags
arrays, database, inserting, multidimensional, mysql, php

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads

Thread Thread Starter Forum Replies Last Post
calender script (php, mysql) 7aken Coding Help 3 20-10-2006 06:57 AM
php, mysql & .asp from same laptop alex Server / Technical Administration Tips and Queries 11 16-10-2006 06:01 PM


Sponsored links

Pepperjam Network
Paid On Results www.zanox.com


All times are GMT +1. The time now is 06:28 PM.


Powered by: vBulletin Version 3.8.2, Copyright ©2000 - 2009, Jelsoft Enterprises Limited.
Hosted in Ireland by Blacknight - Test your ISP |Irish Hosting Directory| Armchair.ie|Logo by Eden Web Design|Avatars by Afterglow |Latest Blog Entries | VPS HostingAd Management by RedTyger

Search Engine Friendly URLs by vBSEO 3.3.0

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51