CODEWITHSUNDEEP
phpmysqlarraysmultidimensional-array
amir rasabeh
amir rasabeh

Reputation: 437

save multidimensional array with foreach loop in database

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?

Upvotes: 1

Views: 4468

Answers (2)

Weltschmerz
Weltschmerz

Reputation: 2186

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());
}

Upvotes: 1

aviemet
aviemet

Reputation: 1653

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

Upvotes: 1

Related Questions