通过 PHP 批量插入 MySQL 数据库会跳过名称 [英] Mass inserting into an MySQL database via PHP skips names
问题描述
我目前正在开发一个可以同时跟踪数百名用户的跟踪程序，但我遇到了一个有点烦人的问题.我让用户插入数据库的方式是通过 html textarea，然后将它们作为参数发送.总有大约 60 人中有 7 人(也总是同名)被插入到数据库中，但它没有插入正确的数据.我试过只插入这些用户，一切都很好，所以一次插入所有的人会不会有问题?
I'm currently working on a tracking program which tracks hundreds of users at once, but I've came across a somewhat annoying problem. The way I get the users to insert into the database is via an html textarea and then I send them as a parameter. There's always about 7 out of 60 people (it's always the same names, too) that are inserted into the database, but it doesn't insert the proper data. I've tried to just insert those users and it does it all fine, so could there be a problem inserting all of the people at once?
这是我正在使用的一些代码:
index.html:
Here's some of the code that I'm using:
index.html:
<head>
<title>Test</title>
</head>
<body>
<form name="input" action="tracker.php" method="get">
<textarea name="names" rows="20" cols="20"></textarea><br>
<input type="submit" value="Submit" />
</form>
</body>
tracker.php:
$database = mysql_connect("mysql.alwaysdata.com", "stacktest", "stackoverflow");
if (!$database) {
die('Could not connect to database: ' . mysql_error());
}
mysql_select_db("stacktest_1", $database);
mysql_query("CREATE TABLE stats (username varchar(12), start text, UNIQUE (username))");
$names = explode("\n", $_GET['names']);
if (isset($_GET['track'])) {
for ($i = 0; $i < count($names); $i++) {
startTracker($names[$i]);
}
}
displayData();
function startTracker($username) {
$stats = getStats($username);
mysql_query("INSERT IGNORE INTO stats (username, start) VALUES ('" . trim($username) . "', '$stats')");
}
function grabStats($username) {
$query = mysql_query("SELECT * FROM stats WHERE username LIKE '$username'");
while ($row = mysql_fetch_array($query)) {
return $row['start'];
}
}
function displayData() {
global $names;
for ($i = 0; $i < count($names); $i++) {
$stats = getStats($names[$i]);
$starting = getStat(grabStats($names[$i]), 2, 0);
$current = getStat($stats, 2, 0);
$gained = $currentExperience - $startingExperience;
echo "Stats for <b>" . $names[$i] . "</b>:<br>";
echo "Starting experience: " . number_format($starting) . "<br>";
echo "Current experience: " . number_format($current) . "<br>";
echo "Gained experience: " . number_format($gained) . "<br><br>";
}
}
function getStats($username) {
$curl = curl_init("http://hiscore.runescape.com/index_lite.ws?player=" . $username);
curl_setopt ($curl, CURLOPT_CONNECTTIMEOUT, $timeout);
curl_setopt ($curl, CURLOPT_USERAGENT, sprintf("Mozilla/%d.0", rand(4, 5)));
curl_setopt ($curl, CURLOPT_HEADER, (int) $header);
curl_setopt ($curl, CURLOPT_RETURNTRANSFER, 1);
curl_setopt ($curl, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt ($curl, CURLOPT_VERBOSE, 1);
$httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
$output = curl_exec($curl);
curl_close ($curl);
if (strstr($output, "<html><head><title>")) {
return false;
}
return $output;
}
function getStat($stats, $row, $skill) {
$stats = explode("\n", $stats);
$levels = explode(",", $stats[$skill]);
return $levels[$row];
}
mysql_close($database);
您会注意到在数据库中 funkymunky11 没有数据，但显然有来自我阅读的网站的数据，如您所见.
You'll notice inside the database that funkymunky11 has no data, yet there's clearly data from the site that I read from, as you can see.
我还创建了一个数据库，这样您就可以看到发生了什么:http://phpmyadmin.alwaysdata.com/
I've also created a database just so you can see what's happening: http://phpmyadmin.alwaysdata.com/
用户名:stacktest
密码:stackoverflow
Username: stacktest
Password: stackoverflow
以下是我用来测试的名称(我把它放在文本区域内):
The follow are the names that I used to test (I put this inside the text area):
永恒
quux
funkymunky11
ts丹妮
aeterna
quuxx
funkymunky11
ts danne
如果有一些不需要的代码，我很抱歉，我仔细检查并清理了几乎所有我认为不需要的东西.
I'm sorry if there's some unneeded code, I went through and cleaned almost everything that I didn't think was needed.
提前致谢，罗恩
推荐答案
我认为您需要查看插入命令中出现了哪些错误.将以下代码复制到 tracker.php 页面，然后更改对 startTracker 的调用，使其调用 startTrackerDebug.然后看看你得到了什么.请注意，在我的函数中，我使用 sprintf() 和 mysql_real_escape_string() 来确保数据在插入时与 mysql 兼容.
I'm thinking you need to see which errors are cropping up on the insert command. Copy the following code to your tracker.php page and then change the call to startTracker so that it calls startTrackerDebug. Then see what you get. Note that in my function I use sprintf() and mysql_real_escape_string() to make sure the data plays nice with mysql when inserted.
function startTrackerDebug($username) {
$stats = getStats($username);
$query = sprintf("INSERT INTO stats (username, start) VALUES('%s', '%s')",
mysql_real_escape_string(trim($username)),
mysql_real_escape_string($stats));
$result = mysql_query($query);
if(!$result) echo(mysql_error() . "Query = $query");
}
这篇关于通过 PHP 批量插入 MySQL 数据库会跳过名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
