Jump to content


Photo

Comparing MySQL Database Tables


  • Please log in to reply
4 replies to this topic

#1 stewart715

stewart715
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 19 October 2006 - 07:31 PM

I'm developing a privacy feature based on the following:
The person selects either private mode or public mode..if they select private mode, the value of 1 is inserted into a table.  if they select public mode..the value of 2 is inserted (along with their UID)..that part i already did

this is what im having trouble figuring out doing..

then i want the profile page to select that table based on that persons UID to see whether they chose 1 or 2..if they chose 2..they can show the profile because its public mode but if they chose 1 i want the profile to check a 'buddylist' table to see if the person viewing has their user ID in the column 'buddy' next to the persons profile which their trying to viewings 'UID' (in the same row of course)

Example.. buddy Mike has a UID of 2..buddy Joe has a UID of 4

Mike pics privacy so the table privac mode has

UID  privacyid
2        1

Joe's trying to view mikes profile so since mike picked 1 the page checks buddy list for:

UID  buddy
2        4

if it's not there..then it wont show

how...

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 19 October 2006 - 10:39 PM

This query should do it

<?
$profileid = 2;   // mike 
$userid = 4;  // joe 

$sql = "SELECT p.*
        FROM profile p
        INNER JOIN privac pr ON p.UID = pr.UID
        LEFT JOIN buddylist b ON p.UID = b.UID AND b.buddy = '$userid'
        WHERE p.UID = '$profileid'
            AND ((b.UID IS NOT NULL) OR (pr.privacyid = 2) )";
?>

returns profile data if buddy rec exists or profile is public
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 stewart715

stewart715
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 20 October 2006 - 01:32 AM

Thank you so much!

#4 stewart715

stewart715
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 21 October 2006 - 04:06 AM

I have a php template of the profile, which is where I wanted to place this code... I can't have it pull the profile data from the database..

so if the person is a friend or it is public,it shows the following html..if it is private and theyre not friends..it shows this html instead..how do i do that?

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 21 October 2006 - 08:51 AM

<?php

$res = mysql_query($sql) or die (mysql_error());

if (mysql_num_rows($res) == 0 {

       // cannot view profile
}
else {

      // show profile
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users