php - Select mySQL data with help of other table

427

I'm currently working on a message board which only displays data if values in another table are set.

My 2 tables look like this:

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

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

The actual messages are in the tbmessage table and to check which messages the user wants to see there is the tbsubscribers table.

Now, I want to display the messages. So if a user subscribed tomsg1 andmsg2 (so the value of both are 1) it should select all the data from tbmessage which has the name'msg1' and'msg2'.

I know how to work with select in mysql but don't know how I could select multiple tables, and especially in this case I'm kind of confused how I would do this.

Any help would be appreciated

333

Answer

Solution:

A simple join should work:

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
)

I created a sqlfiddle here, which should demonstrate the functionality you're looking for.

I'm going to make some edits to clarify how you should do this, using both the query above, and another query if need be.

There are a few ways you can do this. Way #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;

?>

Way #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;

People are also looking for solutions to the problem: php - Incorrect Date/Time Formatting when inserting to mysql database

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question