Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Creata table and insert data using php

Im trying to create a table on mysql using php. i send an array containing the columnname and the actual data. the first position is the columnname and the rest is the actual data. Ive managed to create the table and also the columns. although im not able to populate the table with the data. This is the PHP im using:

<?php
// Assuming your MySQL database connection details
$DATABASE_HOST = "xxxx";
$DATABASE_USER = "xxxx";
$DATABASE_PASS = "xxxx";
$DATABASE_NAME = "xxxx";

$conn = mysqli_connect($DATABASE_HOST, $DATABASE_USER, $DATABASE_PASS, $DATABASE_NAME);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$data = json_decode($_POST['data'], true);

$columnName = array_shift($data); // Remove and return the first element
$columnData = $data; // The rest of the array is the data to be inserted

// Build the SQL query dynamically based on the number of data elements
$placeholders = implode("', '", array_fill(0, count($columnData), "?"));

$query = "CREATE TABLE IF NOT EXISTS tmp_transactions (rownumber INT AUTO_INCREMENT PRIMARY KEY);";
$result = $conn->query($query);

if ($result) {
    echo "Table created successfully";
} else {
    echo "Error creating table: " . $conn->error;
    $queryDrop = "DROP TABLE tmp_transactions";
    $result = $conn->query($queryDrop);
}

$queryAlter = "ALTER TABLE tmp_transactions ADD COLUMN $columnName VARCHAR(255);";
$result = $conn->query($queryAlter);


$sqlInsert = "INSERT INTO tmp_transactions ($columnName) VALUES ($placeholders);";
$stmtInsert = $conn->prepare($sqlInsert);

$stmtInsert->bind_param(str_repeat('s', count($columnData)), ...$columnData);

$resultInsert = $stmtInsert->execute();

if ($resultInsert) {
    echo "Data inserted successfully";
} else {
    echo "Error inserting data: " . $stmtInsert->error;
}

$stmtInsert->close();
$conn->close();
?>

The input

["Amount","-43.85","200.00","4925.00","-210.00","-62.00","1638.00","25435.00","-12.50","-74.00"] 
or 
["transactiondate","2023-12-22","2023-12-22","2023-12-22","2023-12-21","2023-12-21","2023-12-22","2023-12-22","2023-12-18","2023-12-18"]

tried inserting raw string instead of $placeholder without success.

Tags:

2

Answers


  1. 0

    Your code will generate SQL like:

    INSERT INTO tmp_transactions (some_column) VALUES (?, ?, ?, ?);

    which won’t work, since the number of given column names and values does not match. To insert multiple rows you would need this:

    INSERT INTO tmp_transactions (some_column) VALUES (?), (?), (?), (?);

    You could also run a single statement like this multiple times, although that’s going to result in worse performance than doing it with a single import statement.

    INSERT INTO tmp_transactions (some_column) VALUES (?);
    Login or Signup to reply.
  2. 0

    I modified the $placeholders to have separate placeholders for each value in the INSERT statement, and I added a dynamic binding of parameters using call_user_func_array based on the number of data elements. These changes should help with inserting the data into the table successfully.

    

    

        // ...

    // Build the SQL query dynamically based on the number of data elements
    $placeholders = implode(", ", array_fill(0, count($columnData), "?"));

    // ...

    $queryAlter = "ALTER TABLE tmp_transactions ADD COLUMN $columnName VARCHAR(255);";
    $result = $conn->query($queryAlter);

    // ...

    $sqlInsert = "INSERT INTO tmp_transactions ($columnName) VALUES ($placeholders);";
    $stmtInsert = $conn->prepare($sqlInsert);

    // Dynamically bind parameters based on the number of data elements
    $bindParams = array(str_repeat('s', count($columnData)));
    foreach ($columnData as &$value) {
        $bindParams[] = &$value;
    }

    call_user_func_array(array($stmtInsert, 'bind_param'), $bindParams);

    // ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search