How do I insert data from another website into mysql?

Question

I wanted to learn something new over the weekend and decided to teach myself PHP, DOM, and MySQL. As a starting project, I wanted to try adding the following table that I've extracted from another website into my own database.

http://i67.tinypic.com/fuot5g.jpg

However, I ran into a wall when I was trying to add the data (not including the header line like 'No.', 'Ticker', etc..) into my own database.

Here is an extract of my code:

<?php   

     $dom = new DOMDocument;
     $dom->loadHTML($document);

     $tbl = $dom->getElementByID('forex_performance');
     $trneeded = $tbl->getElementsByTagName('tr');

     foreach ($trneeded as $row) {
         foreach ($row->getElementsByTagName('td') as $cell) {
              $cellarray[] = $cell->nodeValue;
              $query = "INSERT INTO sampletable (no, ticker, price, perf5, perfhour, perfday, perfweek, perfmonth,perfquart, perfhalf, perfyear, perfytd, date, time) VALUES ('')
              mysql_query($query);
        } 
     }

?>

I am able to get specific data from the cell array, but I can't for the life of me figure out how to insert them into my db.

I'm still really new to this and hope my question made sense! I would really appreciate it if someone could help me by pointing me in the right direction!

Just a suggestion http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php so you can teach yourself better. — Alessandro Da Rugna, Mar 14 '16 at 11:33
wish I could say it didn't but it ended up taking that long and I am still struggling with it.. — Cuppy, Mar 14 '16 at 13:03

Indrajit · Accepted Answer · 2016-03-14T13:24:31.110

2

You can try this:

$userColumns = array(no, ticker, price, perf5, perfhour, 
perfday, perfweek, perfmonth,perfquart, perfhalf, perfyear, perfytd, date,time);

foreach ($trneeded as $row) {
 foreach ($row->getElementsByTagName('td') as $cell) {
    $userValue[] = $cell->nodeValue;        
 }
 $query = "INSERT INTO `sampletable` ( ".
      mysql_real_escape_string(implode(' , ', $userColumns)).
      ") VALUES ( '".
      mysql_real_escape_string(implode("' , '", $userValue)).
      "' )";
  mysql_query($query);
  $userValue = array();
}

edited Mar 14 '16 at 13:24

answered Mar 14 '16 at 11:39

Indrajit

405
4
12

Just for a suggestion you should use mysqli instead of mysql – Indrajit Mar 14 '16 at 11:40
Wow thanks! I'm trying to read your code and understand the logic before giving it a try. I've read about mysql being deprecated and all but it was what I learnt in school like a decade ago so I wanted to start with something familiar before learning additional things. ^^ – Cuppy Mar 14 '16 at 11:43
I'm getting the error "Column count doesn't match value count at row 1". However, I when I echoed $uservalue[0] - $uservalue[13], it seemed like the number of values matches the number of columns. Did I miss a comma or a value somewhere? – Cuppy Mar 14 '16 at 12:21
Just echo the $query and check the statement formed. You can copy that and execute in phpmyadmin to get the exact idea. – Indrajit Mar 14 '16 at 12:31
can't seem to edit my post above. So I decided to echo the $query to see what was being passed to mysql and the result was: **INSERT INTO `finviz_database` ( ) VALUES ( 'No.\' , \'Ticker\' , \'Price\' , \'Perf 5Min\' , \'Perf Hour\' , \'Perf Day\' , \'Perf Week\' , \'Perf Month\' , \'Perf Quart\' , \'Perf Half\' , \'Perf Year\' , \'Perf YTD' )** Seems like the column wasn't getting passed through and the values have the weird backslash added to it. Currently trying to read up on array_keys and implode functions to see if I can figure what went wrong – Cuppy Mar 14 '16 at 12:36
just remove array_keys I think than it should work fine. Sorry my bad. See the updated solution. Also make sure that userColumn array is set properly. – Indrajit Mar 14 '16 at 12:39
Oh great that worked! I just need to figure out a way to remove the backslashes and not insert the first header row of the table! Thank you so much for your help! – Cuppy Mar 14 '16 at 12:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/106240/discussion-between-cuppy-and-indrajit). – Cuppy Mar 14 '16 at 13:06

score 0 · Answer 2 · answered Mar 14 '16 at 11:33

0

Use INSERT INTO SELECT:

INSERT INTO sampletable (no, ticker, price, perf5, perfhour, perfday, perfweek, perfmonth,perfquart, perfhalf, perfyear, perfytd, date, time) 
SELECT ... ;

answered Mar 14 '16 at 11:33

Dylan Su

5,975
1
16
25

Hi, thanks for that! However, the table that I am getting the data from is extracted from another website. Can INSERT INTO SELECT still work in this case? – Cuppy Mar 14 '16 at 11:37

How do I insert data from another website into mysql?

2 Answers2