insert array into database syntax error

Question

I'm parsing article links from a friend's basketball website using DOM Parser. I want to store these values in my database but I'm getting a syntax error Here's the code:

<?php
include_once ('connect_to_mysql.php');
    include_once ('simple_html_dom.php');
    $html = file_get_html('http://basket-planet.com/ru/');
    $main = $html->find('div[class=mainBlock]', 0);
    $items = array();
        foreach ($main->find('a') as $m){
            $items[] = "$m->plaintext, $m->href";
        }
    //print_r($items);
    $reverse = array_reverse($items);
    print_r($reverse);

    $sql = mysql_query ("INSERT INTO basket_news (article, link) VALUES ".(implode(',', $reverse))."") or die (mysql_error());
?>

This is the output from the reverse array (sorry, it's in a nother language):

Array (
  [0] => 07:43 Видео. Дэвид Стерн и арбитры вручают "Лейкерс" победу над "Миннесотой" (1) , /ru/news/9234
  [1] => 07:51 "Чикаго" прервал победную серию "Майами" на отметке 27 (0) , /ru/news/9235
  [2] => 15:02 Кабмин выделил 200 млн грн на подготовку к Евробаскету (0) , /ru/news/9243
  [3] => 20:42 Евролига: ЦСКА ломает мадридский «Реал» (0) , /ru/news/9246
  [4] => 21:45 «Уникаха» побеждает в Стамбуле и молится на «Бамберг» (0) , /ru/news/9248 )

And here's the error:

You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 
'07:43 Видео. Дэвид Стерн и ' at line 1

What am I doing wrong here? Please advise...

Michael · Accepted Answer · 2013-03-28 22:01:23Z

2

Change to:

foreach ($main->find('a') as $m)
{
  $items[] = "'$m->plaintext', '$m->href'";
}

And

"INSERT INTO basket_news (article, link) VALUES (" . implode('), (', $reverse) . ")"

Also, ensure you escape your input (or ideally use prepared statements).

answered Mar 28, 2013 at 22:01

Michael

12.9k6 gold badges56 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kovge · Accepted Answer · 2013-03-28 22:14:24Z

0

The string is not delimited with quotes and not escaped in your code. The Values string should contained by ( and ) So the right way if you use mysql:

  $items[] = "('".mysql_real_escape_string($m->plaintext)."','".
                  mysql_real_escape_string($m->href)."')";

And as the other commenters said, you should try out mysqli, or PDO in php. The query building is easier and safer than mysql_* functions. (And mysql_* functions will be deprecated in the next version of PHP)

edited Mar 28, 2013 at 22:14

answered Mar 28, 2013 at 22:01

Kovge

2,0191 gold badge14 silver badges13 bronze badges

5 Comments

denikov Over a year ago

Ok, this worked but I got a whole bunch of weird characters in my articles column because the data was in Ukrainian...is there something I need to change for the article column? I tried UTF-8bin but that didn't work. Using XAMPP

Kovge Over a year ago

You should do after conenct to mysql server: mysql_query("SET NAMES utf8"), and set all field in yor database with utf8_general_ci collation. And your PHP code, and the source where you get data should be encoded with utf-8 And in phpmyadmin (at the 'home page'), set the connection to utf8 too. To see the data correct.

denikov Over a year ago

One more quick question...I'm having trouble putting in LIMIT 1 into the query. $sql = mysql_query ("INSERT INTO basket_news (article, link) VALUES ".(implode(',', $reverse))."") or die (mysql_error()); Gives me an error...why?

Kovge Over a year ago

Into this query ? You could use LIMIT only with SELECT query, If you want to limit the inserted rows you should break the foreach loop if a counter reaches the maximum number of insertions what you want to insert in a query.

Kovge Over a year ago

stackoverflow.com/questions/7582508/…, here is a way (in this topic the table scheme is different, but this could help you) to delete duplicated rows from your table after insertion.

Collectives™ on Stack Overflow

insert array into database syntax error

2 Answers 2

Comments

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Linked

Related