How to select from table when exists in the table

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 cal开发者_开发问答l 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.

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'];
    } 

---

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.

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.

---

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

---

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):

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

---

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

$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.'") 

---

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).

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.

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.