Honza D. Malásek Honza D. Malásek - 2 months ago 6
MySQL Question

PHP - trying to insert data into database through cycle but only one row is filled

I am trying to insert data into database using for cycle. Here is my code:

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "objednavky";
//******************************************************
include 'simple_html_dom.php';
$html = file_get_html('http://www.quickbistro.cz/cs/rozvoz');
$count = substr_count($html,'<li class="item">');
$jidloA = array();
$cenaA = array();
//********************************************************
$conn = mysqli_connect($servername, $username, $password, $dbname);

if ($conn->connect_error)
{
$message = "Connection failed: " . $conn->connect_error;
} else {
for($x = 0; $x <= $count; $x++) {
$cenaA[$x] = $html->find("span[class=price]", $x);
$jidloA[$x] = $html->find("div[class=article]", $x);
$jidloSQL = strip_tags($jidloA[$x]);
$cenaSQL = strip_tags($cenaA[$x]);
$id = $x +1;
$array = array(
"id" => $id,
"popis" => "$jidloSQL",
"cena" => "$cenaSQL"
);

$sql = "INSERT INTO jidla";
$sql .= " (`".implode("`, `", array_keys($array))."`)";
$sql .= " VALUES ('".implode("', '", $array)."') ";
}
}
if($conn->query($sql) === TRUE) {
echo "Data inserted succesfully";
} else {
echo "error creating table" . $conn->error;
}
$conn->close();
?>


When I run the script it fills table only with one row where only ID is filled with number 81 ( number of items I am trying to extract). When I tried to insert just one row with without cycle it was alright and row inserted item corectly so I am guessing the problem is somewhere in my for cycle.

Answer

Your problem is that your $conn->query($sql) which runs the insert is not inside your for loop, so it is not being run on every loop, it's only being run after your for loop is finished and therefore it is only inserting the results of the final loop.

try doing it this way:

    for($x = 0; $x <= $count; $x++) {
            $cenaA[$x] = $html->find("span[class=price]", $x);
            $jidloA[$x] = $html->find("div[class=article]", $x);
            $jidloSQL = strip_tags($jidloA[$x]);
            $cenaSQL = strip_tags($cenaA[$x]);
            $id = $x +1;    
            $array = array(
               "id" => $id,
               "popis" => "$jidloSQL",
               "cena" => "$cenaSQL"
            );

            $sql  = "INSERT INTO jidla";             
            $sql .= " (`".implode("`, `", array_keys($array))."`)";
            $sql .= " VALUES ('".implode("', '", $array)."') ";

            if($conn->query($sql) === TRUE) {
                echo "Data inserted succesfully";
            } else {
                echo "error creating record" . $conn->error;
            }  

        }