save multidimensional array with foreach loop in database

Question

I warite script to extract price and name from website with foreach loop.

foreach ($table_rows as $tr) { // foreach row
    $row = $tr->childNodes;
    if ($row->item(0)->tagName != 'tblhead') { // avoid headers
        $data[] = array(
            $trip ['Name'] = trim($row->item(0)->nodeValue),
            $trip['LivePrice'] = trim($row->item(2)->nodeValue),
            $trip ['Changing'] = trim($row->item(4)->nodeValue),
            $trip ['Lowest'] = trim($row->item(6)->nodeValue),
            $trip['Topest'] = trim($row->item(8)->nodeValue),
            $trip['Time'] = trim($row->item(10)->nodeValue),
        );
    }
}

and then save they in to the database with mysql (no mysqli or pdo) . but only save one record ، If the 5 column provided .

and after each refresh the page save Previous record (i.e repetitive recod)

please see following .

in the database:

id | name | liveprice | changing | lowest | topest | time 
1  |  lg  |  25500    |   05     |  22500 |  22500 |  2014
2  |  lg  |  25500    |   05     |  22500 |  22500 |  2014
3  |  lg  |  25500    |   05     |  22500 |  22500 |  2014

database code and php for store data in to the database

$article = array();
mysql_select_db("coin", $con);

"CREATE TABLE `Dadoo`(id INT NOT NULL AUTO INCREMENT,PRIMARY KEY(id),`title` VARCHAR(255),`liveprice` VARCHAR(255),`changing` VARCHAR(255),`lowest` VARCHAR(255),`topest` VARCHAR(255) ENGINE=MyISAM";

$debugquery = mysql_query("INSERT INTO `Dadoo`(title,liveprice,changing,lowest,topest,time) VALUES ('" . $trip['Name'] . "','" . $trip['LivePrice'] . "','" . $trip['Changing'] . "','" . $trip['Lowest'] . "','" . $trip['Topest'] . "','" . $trip['Time'] . "')");

if (!$debugquery) {
    die(mysql_error());
}

now how can I save multidimensional array in the database?

You need to put the insert query inside the foreach loop, so it runs multiple times — Oleg Dubas
– Oleg Dubas, Commented Jan 22, 2015 at 7:01
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — tyteen4a03
– tyteen4a03, Commented Jan 22, 2015 at 7:20

aviemet · Accepted Answer · 2015-01-22 07:32:07Z

1

your array needs to be outside the loop or you'll just be overwriting each index every time you loop:

$data = [];
$i = 0;
foreach($table_rows as $tr) { // foreach row
    $row = $tr->childNodes;
    if($row->item(0)->tagName != 'tblhead') { // avoid headers
        $data[i]['Name' ] = trim($row->item(0)->nodeValue);
        $data[i]['LivePrice'] = trim($row->item(2)->nodeValue);
        $data[i]['Changing'] = trim($row->item(4)->nodeValue);
        $data[i]['Lowest'] = trim($row->item(6)->nodeValue);
        $data[i]['Topest'] = trim($row->item(8)->nodeValue);
        $data[i]['Time'] = trim($row->item(10)->nodeValue);

    }
}

you'll have to loop over your sql insert statement too. but the above code will at least leave you with an array containing more than one record.

-or-

the most efficient way might just be to generate the insert statement and perform the query inside your loop instead of storing the values in an array:

foreach($table_rows as $tr) { // foreach row
    $row = $tr->childNodes;
    if($row->item(0)->tagName != 'tblhead') { // avoid headers
        $sql = "INSERT INTO `Dadoo`(title,liveprice,changing,lowest,topest,time) VALUES ('" . trim($row->item(0)->nodeValue) . "','" . trim($row->item(2)->nodeValue) . "','" . trim($row->item(4)->nodeValue) . "','" . trim($row->item(6)->nodeValue) . "','" . trim($row->item(8)->nodeValue) . "','" . trim($row->item(10)->nodeValue) . "')");
        mysql_query($sql);
    }
}

answered Jan 22, 2015 at 7:32

aviemet

1,7132 gold badges16 silver badges20 bronze badges

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

2 Comments

amir rasabeh Over a year ago

Thanks,question : why don't save replace new records to Previous records in the database?

aviemet Over a year ago

An INSERT statement will always create a new row in your table. If you want to replace the records each time, you would need to UPDATE the row. http://www.tutorialspoint.com/sql/sql-update-query.htm

Weltschmerz · Accepted Answer · 2015-01-22 07:44:50Z

Can also be done with one query using the format:

INSERT INTO tablename (column1, column2, ...) 
VALUES (value01, value02, ...), (value11, value12, ...), ...

For example:

class InsertValues {

    private $values;
    private $row = array();

    public function endRow() {
        $this->values[] = '(' . implode(',', $this->row) . ')';
        $this->row = array();
    }

    public function addColumnValue($value, $quote = true) {
        $value = mysql_real_escape_string(trim($value)); //should use mysqli
        $this->row[] = $quote ? "'" . $value . "'" : $value;
    }

    public function render() {
        return implode(',', $this->values);
    }

}

mysql_select_db("coin", $con);

$inserValues = new InsertValues();

foreach ($table_rows as $tr) { // foreach row
    $row = $tr->childNodes;
    if ($row->item(0)->tagName != 'tblhead') { // avoid headers
        $inserValues->addColumnValue($row->item(0)->nodeValue);
        $inserValues->addColumnValue($row->item(2)->nodeValue);
        $inserValues->addColumnValue($row->item(4)->nodeValue);
        $inserValues->addColumnValue($row->item(6)->nodeValue);
        $inserValues->addColumnValue($row->item(8)->nodeValue);
        $inserValues->addColumnValue($row->item(10)->nodeValue);

        //above can be replaced with a for loop ;)

        $inserValues->endRow();
    }
}

$debugquery = mysql_query("INSERT INTO `Dadoo`(title,liveprice,changing,lowest,topest,time) "
        . "VALUES " . $inserValues->render());

if (!$debugquery) {
    die(mysql_error());
}

Collectives™ on Stack Overflow

save multidimensional array with foreach loop in database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related