我可以用 php/mysqli 和 multiple step.
来做到这一点
所以,表只有两列.
身份证、姓名
然后两者都将是唯一的.
我想检查 Name 在数据库中是否可用,如果可用,请获取 ID.
如果不可用,请在数据库上添加 Name 并获取 ID。
我可以用需要多个sql query.
的php/mysql来做到这一点。有没有办法只用一个 mysql 查询来做(检查数据库,如果不存在,请添加它并获取 ID)并获取 ID?
提前感谢!
我的代码(MySQLi Procedural)
function abc($name) {
global $conn;
$checkName = mysqli_query($con,"SELECT * FROM category WHERE name=".mysql_real_escape_string($name));
if (mysqli_num_rows($checkName) > 0) {
$inputName = mysqli_query($con,"INSERT INTO category (name) VALUES ('".mysql_real_escape_string($name)."')");
if (!$inputName) { die(mysqli_error($conn)); }
$checkName2 = mysqli_query($con,"SELECT * FROM category WHERE name=".mysql_real_escape_string($name));
while($blahblah = mysqli_fetch_assoc($checkName2)) {
$returnData[] = $blahblah;
}
} else {
while($blahblah = mysqli_fetch_assoc($checkName)) {
$returnData[] = $blahblah;
}
}
return $blahblah;
}
这只需一行即可完成。使用"插入忽略到...."或"替换为...."。本页参考。
如果你使用面向对象的MySQLi,这就是你的做法:
$mysqli = new mysqli(...);
$name = "Something";
$query = $mysqli->prepare("SELECT id, name FROM table WHERE name=?");
$query->bind_param('s', $something);
$query->execute();
$query->bind_result($id, $name);
$query->store_result();
if($query->num_rows == 1) {
return $id;
} else {
$queryTwo = $this->mysqli->prepare("INSERT INTO table VALUES('', ?);");
$queryTwo->bind_param('s', $name);
$queryTwo->execute();
$queryTwo->close();
$queryThree = $this->mysqli->prepare("SELECT id FROM table WHERE name=?");
$queryThree->bind_param('s', $name);
$queryThree->execute();
$queryThree->bind_result($id);
$queryThree->store_result();
while($queryThree->fetch()) {
return $id;
}
$queryThree->free_result();
$queryThree->close();
}
$query->free_result();
$query->close();