组合三个查询并获得特定的错误警报

Question

我如何简要说明这个查询?我想把它们组合起来但是会得到特定的错误警报和不同的三个变量

<?php
$sql = "SELECT content FROM post where title='tv'";
$sql2 = "SELECT content FROM post where title='radio'";
$sql3 = "SELECT content FROM post where title='net'";
$result = $connection->query($sql);
$result2 = $connection->query($sql2);
$result3 = $connection->query($sql3);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) { 
$tvPrice = $row['content']; 
}
}else{
echo "tv error";
}
if ($result2->num_rows > 0) {
while($row = $result2->fetch_assoc()) { 
$radioPrice = $row['content']; 
}
}else{
echo "radio error";
}
if ($result3->num_rows > 0) {
while($row = $result3->fetch_assoc()) { 
$netPrice = $row['content']; 
}
}else{
echo "net error";
}
?>

Answer 1

使用IN()，您只能返回标题为'tv'， 'radio'和'net'的行。然后将title添加到查询选择中，这样您就知道哪个结果是哪个了。

然后修改代码，将所有结果获取到行数组中，然后检查条目并相应地报告错误

<?php
// make a connection to the database obviously :)
$sql = "SELECT title, content 
FROM post 
WHERE title IN('tv', 'radio', 'net')";
$result = $connection->query($sql);
$rows = $result->fetch_all(MYSQLI_ASSOC);
// better initialise the variables in case you try using them later
$tvPrice = $radioPrice = $netPrice = 0;
foreach ($rows as $row){
if ($row['title'] == 'tv')){
$tvPrice = $row['content']; 
}

if ($row['title'] == 'radio') {
$radioPrice = $row['content']; 
}
if ($row['title'] == 'net') {
$netPrice = $row['content']; 
}
}
if ( $tvPrice == 0 ) echo 'tv error';
if ( $radioPrice == 0 ) echo 'radio error';
if ( $netPrice == 0 ) echo 'net error';
?>

Answer 2

通常你并不需要3个(或更多)变量;使用数组

<?php
$titles = ['tv', 'radio', 'net'];
// Generate the query
$sql = 
"SELECT content, title FROM post WHERE title IN(" 
. implode(", ", array_map(function( $title ) use( $connection ) {
return '"' . mysqli_real_escape_string($connection, $title) . '"';
}, $titles) ) 
. ")";
$result = $connection->query($sql);
$prices = [];
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {

// Check for duplicates
if( !empty( $prices[ $row['title'] . 'Price' ] ) )
echo $row['title'] . " has duplicate error";
else
$prices[ $row['title'] . 'Price' ] = $row['content'];
}
}
// Check if we have all desires rows 
foreach( $titles as $title )
if( empty( $prices[ $title . 'Price' ] ) )
echo "$title missing error";
// If you really need tree variables instead of array:
extract($prices);