如何在表中存在时从表中进行选择

When a lecture (a row in 'lectures' table) is deleted, it is moved into the 'lectures_deleted' table. This acts as a sort of back-up. In this case, I need to be able to call from both of these tables, so I would like to get the data from the 'lectures' table, though if it does not exist here, I would then like to get it instead from the 'lectures_deleted' table.

当一个讲座(“讲座”表中的一行)被删除时,它将被移动到“lectures_deleted”表中。这可以作为一种备份。在这种情况下,我需要能够从这两个表中调用,所以我想从'讲座'表中获取数据,但如果它在这里不存在,那么我想从而从'lectures_deleted'表。

The code below currently breaks the website. Any help will be much appreciated!

以下代码目前打破了网站。任何帮助都感激不尽!

function getModuleCode($id){
        $result = mysql_query('
            IF EXISTS 
                (SELECT * FROM lectures WHERE lecture_id="'.$id.'") 
            ELSE 
                SELECT * FROM lectures_deleted WHERE lecture_id="'.$id.'";
            ') 
        or die(mysql_error());  
        $row = mysql_fetch_array($result);
        return $row['module_code'];
    }

5 个解决方案

#1

How about:

$result = mysql_query('
            (SELECT * FROM lectures WHERE lecture_id="'.$id.'") 
        UNION 
            (SELECT * FROM lectures_deleted WHERE lecture_id="'.$id.'");
        ')

This way will also select from the lectures_deleted if it is still present in the lectures table, but according to your description i believe this should not happen.

这种方式也会从lectures_deleted中选择,如果它仍然存在于讲座表中,但根据你的描述,我相信这不应该发生。

Alternatively, I would suggest to use a 'deleted' flag (one bit field) in the lectures table, indicating whether or not the lecture has been deleted. This way you do not need to move a deleted entry to another table.

或者,我建议在讲座表中使用“删除”标志(一位字段),指示讲座是否已被删除。这样您就不需要将已删除的条目移动到另一个表。

#2

SELECT 1 AS pref, * FROM lectures WHERE lecture_id="'.$id.'"
UNION
SELECT 2 AS pref, * FROM lectures_deleted WHERE lecture_id="'.$id."'
ORDER BY pref
LIMIT 0,1

#3

Your current query is wrong because you don't return anything if the lecture exists in lectures, so you need to modify it like this:

您当前的查询是错误的,因为如果演讲中存在讲座,您不会返回任何内容,因此您需要像下面这样修改它:

IF EXISTS (SELECT * FROM lectures WHERE lecture_id="'.$id.'")
    SELECT * FROM lectures WHERE lecture_id="'.$id.'"
ELSE 
    SELECT * FROM lectures_deleted WHERE lecture_id="'.$id.'";

However, performance-wise it's better to use the Union-operator in your case since it will only do one select (assuming that all columns in both tables are the same and in the same order):

但是,在性能方面,最好在您的情况下使用Union-operator,因为它只会执行一次select(假设两个表中的所有列都相同且顺序相同):

select * from lectures where lecture_id=$id
union
select * from lectures_deleted where lecture_id=$id

#4

The IF... ELSE decision statement cannot be used in a direct MySQL query. It can only be used in function/stored procedures.
You can try this

IF ... ELSE决策语句不能用于直接MySQL查询。它只能用于函数/存储过程。你可以试试这个

$result = mysql_query(SELECT * FROM lectures WHERE lecture_id="'.$id.'") 
if (mysql_num_rows($result) == 0)
   $result = mysql_query(SELECT * FROM lectures_deleted WHERE lecture_id="'.$id.'")

#5

-1

I know it's not an exact answer to your question, but why not change your data model to have something like a column called is_deleted with a value of 0 or 1 in your lectures table? That way, you don't need to store deleted lectures in a separate table. You just have to include a WHERE clause like WHERE is_deleted = 0 or WHERE is_deleted = 1 to your queries to fetch the deleted or the non-deleted lectures (or omit the WHERE clause to fetch both).

我知道这不是你的问题的确切答案,但为什么不改变你的数据模型,在你的讲座表中有一个名为is_deleted,值为0或1的列?这样,您就不需要将已删除的讲座存储在单独的表中。您只需要在查询中包含WHERE is_deleted = 0或WHERE is_deleted = 1等WHERE子句,以获取已删除或未删除的讲座(或省略WHERE子句以获取两者)。

If you want to solve it in your current data model, I'd do it in 2 queries. I don't think mysql_query will support your current SQL statement. So first query lectures table and if you get zero results, check for it's existence in lectures_deleted.

如果你想在当前的数据模型中解决它,我会在2个查询中完成它。我不认为mysql_query会支持你当前的SQL语句。所以首先查询讲座表,如果得到零结果,请检查它是否存在于lectures_deleted中。

Also, I don't know where your $id variable comes from exactly, but you might want to make sure your query is not vulnerable for SQL injections.

另外,我不知道你的$ id变量究竟来自哪里,但你可能想确保你的查询不容易受到SQL注入攻击。

#1