CODEWITHSUNDEEP
phpmysql
CCates
CCates

Reputation: 1255

mySQL Visibility Issue

So I want to fine tune searching. I have one table which contains information, requests, with a respective network. I have another table, which logs visibility and it's respective network, and, who can view it, based on either it being "public" or "private".

The end result I want is that, those who are on the table can view private networks, while, anyone can view a public network.

    if ($whichnetwork == "Global"){
    $query = mysql_query("SELECT * FROM requests ORDER BY dateposted DESC");
} else {
    $query = mysql_query("SELECT * FROM requests WHERE network = '$whichnetwork' ORDER BY dateposted DESC");
}   

while ($row = mysql_fetch_assoc($query)){

            echo "<div id='".$row["id"]."' class='item'>";
            echo '<div style="width: 75%;">';
            echo "<h4 style='display: inline;'>".$row["user"]." requests:</h4><br/>";
            echo "<h2 style='display: inline;'>".$row["title"]."  </h2>";
            echo '</div>';

            echo '<h3 id="button" style="border-radius: 10px; padding: 4px; color: white; float: right; position: relative; top: -50px; border: 2px solid grey; background-color: #C0504D;">Apply</h2>';
            echo "</div>";
}

In my code, I only have a reference to the requests table, however, there is another table which has, Network, Username and Visibility.

How would I make this work??? I've failed many times at implementing it, thanks, Chris.

Schema:

network Table:

network text    utf8_general_ci     No                                   
username    text    utf8_general_ci     No                                   
visible text    utf8_general_ci     No

Relevant Requests table:

network text    utf8_general_ci     No  
user    text    utf8_general_ci     No

Upvotes: 0

Views: 137

Answers (1)

Naveen Kumar
Naveen Kumar

Reputation: 4601

You should add index fields into your table to start off, that would help in joining your tables and querying.

Alter the network table as follows

user_id         int auto_increment primary key,
username        nvarchar(30) ,
network         text,
visibility_id   int

alter your request tables

user_id         int ,
network         text

add another table visibility

visibility_id  int // values stored and meaning(0-Public, 1-Private)
visibility     nvarachar(10) (public, private etc)

Query to check if current user has access to the searched network and network is public or private

select user_id, visibility from network where network='UserProvided';

Then if visibility_id is 1 it is private check if userId got for network is same as the current user then display only that network

else

if visibility_id is 0 then it is public you can display that from request.

Upvotes: 1

Related Questions