MySQL inserting thorough an array

Question

I have a MySQL table called season

I'm getting data from a PostgreSQL then store it inside my array and then i use the implode function to pass the data stored inside the array:

$season = array(
        'asset_id' => $asset_id,
        'number' => $season_number
    );
$season_col = implode(', ', array_keys($season));
$season_values = implode(', ', array_values($season));

Then I insert:

$insert_season = mysqli_query($conn, "INSERT INTO season(".$season_col.") VALUES(".$season_values.")");

This is one of 6 tables where i'm inserting data, and the problem is that when I run the code it does not insert all the data at once, I have to reload the page several times before it's all inserted. I also must add that the tables are all Interconnected with foreign keys, and also i tried disable the foreign key check and it did nothing for me.

I also do a check in php for repeated and nonexistent data:

$check_for_season = mysqli_query($conn, "SELECT asset_id, number FROM season WHERE asset_id = ".$asset_id." AND number = ".$season_number." LIMIT 1");
        if (mysqli_fetch_array($check_for_season)) {
            echo "Updates data";
        } else {
            $insert_season = mysqli_query($conn, "INSERT INTO season(".$season_col.") VALUES(".$season_values.")");
        }
    }

Your question is impossible to be answered with the informations given. `I have to reload the page several times before it's all inserted.` => That makes no sense `I also must add that the tables are all Interconnected with foreign keys, and also i tried disable the foreign key check and it did nothing for me.` => What does this have to do with anything? — Xatenev, May 30 '17 at 09:19
Is $season supposed to be a multidimensional array or something? Because other than that I don't see multiple values you'd be wanting to be inserting. — Enstage, May 30 '17 at 09:19
Print out the Statement and share it. Also share the db description — Jens, May 30 '17 at 09:20
@Jens `Your SQL Statement is open for SQL injection.` You don't know that. But possibly yes. — Xatenev, May 30 '17 at 09:21
@Mika If it makes no sense - step back and think about what else could play in here. In IT mostly things don't happen magically — Xatenev, May 30 '17 at 09:22
@Xatenev The only way to prevent SQL injection is to use prepared Statements and that is definivly not the case here — Jens, May 30 '17 at 09:22
@Jens To make SQL Injection possible, the information must come from a user or any service you don't control. You have no idea whether those variables inserted into the query have hardcoded values(come from server internal values) or not. — Xatenev, May 30 '17 at 09:24
Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — Masivuye Cokile, May 30 '17 at 09:29
@MasivuyeCokile because of the request of the company that i'm currently working for — Mika, May 30 '17 at 09:30
@Xatenev i thought that the error may occur because i insert values in 6 tables simultaneously and as they all relay on each other the foreign keys could be the problem — Mika, May 30 '17 at 09:44

score 0 · Answer 1 · answered May 30 '17 at 09:31

0

My educated guess would be that you encounter SQL error mid-execution could be due one of table's columns defined not to except certain values like 'NOT NULL' for instance. the execution would terminate if you try to pass $season['number'] = ''; Try to check SQL errors in your mysqli object. Also try to wrap your values in quotes.

$season_values = implode("','", array_values($season));
$insert_season = mysqli_query($conn, "INSERT INTO season(".$season_col.") VALUES('".$season_values."')");

to avoid errors when passing empty values.

answered May 30 '17 at 09:31

George Dryser

322
1
7

Should i use quotes even though the values i pass both are integers? – Mika May 30 '17 at 09:38
if there's no quotes and somehow NULL value is passed it will cause errors since final SQL will look like VALUES(1,2,,3) which is an error. – George Dryser May 30 '17 at 10:58

score 0 · Answer 2 · answered May 30 '17 at 09:31

You can use this code to dynamically create query.

$conn = new PDO('mysql:host=' . $HOST . ';dbname=' . $DATABASE, $USERNAME, $PASSWORD);
$conn->exec("set names utf8");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

$ins_query = 'INSERT INTO `' . $table_name . '` ';

$columns = array();
$columns_bindings = array();
foreach ($dataArray as $column_name => $data) {
    $columns[] = $column_name;
    $columns_bindings[] = ':' . $column_name;
}

$ins_query = $ins_query . '(' . implode(', ', $columns) . ') VALUES (' . implode(', ', $columns_bindings) . ')';

$stmt = $conn->prepare($ins_query);

foreach ($dataArray as $column_name => $data) {
    $stmt->bindValue(":" . $column_name, $data);
}
if (!$stmt->execute()) {
    print_r($stmt->errorInfo());

} else {
    echo "Insertd";
}

MySQL inserting thorough an array

2 Answers2