一起运行完全不相关的查询的最佳方法？

I seem to be running into this issue often, usually for checking to see if a $_GET['id'] actually exists in my database before running another query, related or not to the id being received.

我似乎经常遇到这个问题，通常是在运行另一个查询之前检查我的数据库中是否存在$ _GET ['id']，与之相关或不相关。

If a record exists with the same id, then I show the HTML, e.g.:

如果存在具有相同id的记录，那么我将显示HTML，例如：

if ($r) { #query was successful
    $num = mysqli_num_rows($r);
    if ($num == 1) {
        ?>

        <form method="post">
        ...
        <input type="submit" value="Submit" />
        </form>

        <?php
    } else {
        echo 'No records';
    }
} else {
    echo 'Query Failed';
}
mysqli_free_result($r);

The above works, but let's say that I want to perform another unrelated query between my opening/closing form tags; would it be proper to simply do something like this, which I would consider a nested query:

上面的工作，但我要说我想在我的开/关表单标签之间执行另一个不相关的查询;简单地做这样的事情是否恰当，我认为这是一个嵌套查询：

$q = "SELECT id FROM stuff WHERE id = $id";
$r = @mysqli_query($dbc, $q);
if ($r) { #query was successful
    $num = mysqli_num_rows($r);
    if ($num == 1) {
        ?>

        <form method="post">

        <?php
        $q = "SELECT example FROM somewhere";
        $r = @mysqli_query($dbc, $q);
        if ($r) {
            $num = mysqli_num_rows($r);
            if ($num > 0) {
                while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
                    echo $row['example'];
                }
            }
        } else {
            echo 'Query failed';
        }
        ?>

        <input type="submit" value="Submit" />
        </form>

        <?php
    } else {
        echo 'No records';
    }
} else {
    echo 'Query Failed';
}
mysqli_free_result($r);

Or maybe create a boolean, like so:

或者也许创建一个布尔值，如下所示：

$id_status = 0;

$q = "SELECT id FROM stuff WHERE id = $id";
$r = @mysqli_query($dbc, $q);
if ($r) { #query was successful
    $num = mysqli_num_rows($r);
    if ($num == 1) {
        $id_status = 1;
    }
}
mysqli_free_result($r);

if ($id_status) { #Run another query
    ...
}

I currently have a problem when it comes to requiring two separate query results for one task: (1) generating inputs from another table, (2) displaying the current values, (3) checking to see what input that has been generated matches the current value, e.g.:

我当前在一个任务需要两个单独的查询结果时遇到问题：（1）从另一个表生成输入，（2）显示当前值，（3）检查以查看已生成的输入与当前值匹配价值，例如：

$q = "SELECT id, food FROM restaurants";
$q = "SELECT favFood FROM people WHERE id = 1"; #favFood is equal to the id of food
...
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
    ?>
    #generate all food from table, make favFood the current selected input
    <input type="radio" name="yum" value="<?php echo $row['id']; ?>" />
    <?php
}

So as you can see, I am having trouble when it comes to unrelated queries; the last example would probably work with a UNION or something, but i'd have a bunch of repeated rows displaying favFood, so it doesn't feel right, e.g.:

正如您所看到的，我遇到无关查询时遇到麻烦;最后一个例子可能适用于UNION或其他东西，但我有一堆显示favFood的重复行，所以它感觉不对，例如：

favFood | id | food
    1   |  1 | Pizza
    1   |  2 | Burgers
    1   |  3 | Monkey Brains

Before I end this, I just want to say that I have made an attempt to use mysqli_multi_query, but I most likely did something wrong; i'm not even sure if it would work in this case, or what it does exactly.

在我结束之前，我只是想说我尝试使用mysqli_multi_query，但我很可能做错了;我甚至不确定它是否会在这种情况下起作用，或者它究竟是做什么的。

Thanks!

谢谢！

2 个解决方案

#1

If you want to check whether a record exists multiple times in your code, I suggest to create a function which returns a bool.

如果要在代码中多次检查记录是否存在，我建议创建一个返回bool的函数。

function record_exists($dbc, $q){
    $state = false;
    if($r = @mysqli_query($dbc, $q)){
        if(mysqli_num_rows($r) == 1){
            $state = true;
        }
        mysqli_free_result($r);
    }
    return $state;
}

$q = "SELECT id FROM stuff WHERE id = $id";
if(record_exists($dbc, $q)){
    echo 'Yay, it exists';
}

Then if you want to SELECT some fields from a table but the filter depends on a value from another table, you could JOIN two tables in the query like so:

然后，如果要从表中选择某些字段但过滤器依赖于另一个表中的值，则可以在查询中加入两个表，如下所示：

SELECT restaurants.id, restaurants.food
FROM restaurants
JOIN people ON restaurants.id = people.favFood
WHERE people.id = 1

Now you only SELECT the fields you want from table restaurants and filter them on a person id from the table people.

现在，您只需从表餐馆中选择您想要的字段，并在表人员的人员ID上对其进行过滤。

#2

Two reasons why you've got such a problem

你遇到这样一个问题的两个原因

You are doing A LOT of useless stuff.
- no one needs you "query failed" statement.
- 没人需要你“查询失败”的陈述。
- num rows check is also superfluous
- num rows check也是多余的
- most of the code you write is just repetitions
- 你写的大部分代码都只是重复
你正在做很多无用的东西。没人需要你“查询失败”的陈述。 num rows check也是多余的，你编写的大部分代码都只是重复
Like PHP users from the last century you are mixing database calls with HTML output. Now you see that the term "spaghetti code" was coined for a reason.
与上个世纪的PHP用户一样，您将数据库调用与HTML输出混合在一起。现在你看到“意大利面条代码”一词的创造是有原因的。

Your database related code rewritten with simple mysqli wrapper:

你的数据库相关代码用简单的mysqli包装器重写：

$rows = [];
$found = $db->getOne("SELECT id FROM stuff WHERE id = ?i", $id);
if ($found) {
    $rows = $db->getAll("SELECT example FROM somewhere");
}

Now you can proceed to HTML output, either inline or (better) by including a template

现在，您可以通过包含模板来内联或（更好）继续HTML输出

?>
<?php if ($rows): ?>
    <form method="post">
    <?php foreach ($rows as $row): ?>
        <?=$row['example']?>
    <?php endforeach ?>
    <input type="submit" value="Submit" />
    </form>
<?php else: ?>
    No records
<?php endif ?>

#1