使用php创建表并插入数据

jv4diomz 于 2024-01-05 发布在 PHP

关注(0)|答案(2)|浏览(124)

我试图使用php在mysql上创建一个表。我发送一个包含列名和实际数据的数组。第一个位置是列名，其余的是实际数据。我设法创建了表和列。虽然我不能用数据填充表。这是我使用的PHP：

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

字符串
输入

["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"]

型
尝试插入原始字符串而不是$placeholder没有成功。

php

来源：https://stackoverflow.com/questions/77735978/creata-table-and-insert-data-using-php

2条答案

按热度按时间

92vpleto1#

您的代码将生成如下SQL：

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

字符串
这是不起作用的，因为给定的列名和值的数量不匹配。要插入多行，你需要这样做：

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

型
您也可以多次运行这样的单个语句，尽管这会导致比使用单个import语句更差的性能。

INSERT INTO tmp_transactions (some_column) VALUES (?);

型

赞(0）回复(0）举报 2024-01-05

b09cbbtk2#

我修改了$placeholders，使其为RISK语句中的每个值提供单独的占位符，并根据数据元素的数量使用call_user_func_array添加了参数的动态绑定。这些更改应该有助于成功地将数据插入表中。

// ...

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

    // ...

字符串

赞(0）回复(0）举报 2024-01-05

我来回答

使用php创建表并插入数据

2条答案

相关问题

热门标签

最新问答