php - 在其他表的帮助下选择 mySQL 数据

Question

我目前正在开发一个留言板，该留言板仅在设置了另一个表中的值时才显示数据。

我的 2 个表如下所示:

tbsubscribers:
-subID (int)
-msg1 (tinyint)
-msg2 (tinyint)
-msg3 (tinyint)
-email (varchar)

tbmessage
-id (int)
-name (varchar)
-mitteilung (varchar)
-sender (varchar)
-datum (datetime)

实际消息位于 tbmessage 表中，要检查用户想要查看哪些消息，请使用 tbsubscribers 表。

现在，我想显示消息。因此，如果用户订阅了 msg1 和 msg2(因此两者的值为 1)，则应选择 tbmessage 中名为 'msg1' 的所有数据 和'msg2'。

我知道如何在 mysql 中使用 select，但不知道如何选择多个表，尤其是在这种情况下，我有点困惑如何执行此操作。

如有任何帮助，我们将不胜感激

Answer 1

一个简单的连接应该可以工作:

SELECT * FROM `tbsubscribers` s
RIGHT JOIN `tbmessage` tb ON (
  CASE WHEN msg1 = 1 THEN name = 'msg1' END OR
  CASE WHEN msg2 = 1 THEN name = 'msg2' END OR
  CASE WHEN msg3 = 1 THEN name = 'msg3' END
)

我创建了一个sqlfiddle here ，它应该展示您正在寻找的功能。

我将进行一些编辑，以阐明您应该如何执行此操作，同时使用上面的查询和另一个查询(如果需要)。

有几种方法可以做到这一点。方式#1:

<?php

/*
 * If you already have username (email) in session,
 * then you can do this:
 */

$query = "SELECT m.* FROM `tbsubscribers` s
RIGHT JOIN `tbmessage` m ON (
  CASE WHEN msg1 = 1 THEN name = 'msg1' END OR
  CASE WHEN msg2 = 1 THEN name = 'msg2' END OR
  CASE WHEN msg3 = 1 THEN name = 'msg3' END
)
WHERE email = " . my_escape_string_function($_SESSION['username']);

/*
 * If tbsubscribers has a '1' in msg1, then all msg1's are grabbed.
 * If tbsubscribers has a '2' in msg2, then all msg2's are grabbed...etc
 */

//Set $rows equal to the result

return $rows;

?>

方法#2:

<?php

/*
 * If you already have username (email) in session,
 * then you can do this:
 */

$query = "SELECT * FROM `tbsubscribers` s
WHERE email = " . my_escape_string_function($_SESSION['username']);

/*
 * Run this query, and fetch the entire userrow, and put into $user
 * An alternative could be to just store the entire user array into
 * $_SESSION, as you wont have to query the database twice.
 */

$tofetch = array();

for ( $i=1; $i<=3; $i++ )
{
    //Let's check msg1, msg2, msg3
    if ( $user['msg' . $i] )
    {
        $tofetch[] = "name = 'msg" . $i . "'";
    }
}

/*
 * If tbsubscribers has a '1' in msg1, and a '1' in msg2 then the array
 * will look like this:
 * array(
 * 0 => "name = 'msg1'"
 * 1 => "name = 'msg2'"
 * )
 */

//Throw an exception if all 'msg1', 'msg2', and 'msg3' are all 0 (Otherwise the query will fail)
if ( empty($tofetch) ) {
    throw new Exception("You're not subscribed to any messages.");
}

/*
 * Now it's a simple query. $tofetch will be imploded, to form a nice
 * where statement. Using the example above, it will look like:
 * name = 'msg1' OR name = 'msg2'
 */
$query = "SELECT *
FROM `tbmessage`
WHERE " . implode(' OR ', $tofetch);

//Set $rows equal to the result

return $rows;