Solved

Explode field to use within query

Posted on 2008-06-11
10
907 Views
Last Modified: 2013-12-13
Hi,

I have two table, one for users (tbluser) and one for sites (tblgb).

Each admin user is allowed to access certain site data, but not others.  I have a field in tbluser called uaccess which is an imploded field containing ID numbers separated by commas, e.g. 1,2,3.  These ID numbers correspond to the ID number of each site, held in the field, gbID in tblgb.  e.g. uaccess = 1, gbID = 1, uaccess = 2, gbID = 2 etc.

On my admin page, I have a select box to choose which site to modify.  What I want to do is only display the sites in the list that the user is allowed access to.

This is where my problem starts.  I can get it to display all the sites, but no matter what I try, I am unable to get it to display only the sites from uaccess.

This is my code for the select box:

$sql2 = mysql_query("SELECT * FROM tblgbs GROUP BY gbID");
echo '<form action="gbadmin.php" method="post">
Choose Guestbook: <select class="txtselect" name="gbselect">';
while($row2 = mysql_fetch_array($sql2)) {
      $gbID = $row2['gbID'];
      $gbname = $row2['gbname'];
      echo '<option value="'.$gbID.'">'.$gbname.'</option>';
}
echo '</select>
<input class="subbtn" type="submit" name="submit" value="Submit" />
</form>';


I have also written a query to grab the values of the uaccess field:

$sql6 = mysql_query("SELECT uaccess FROM tbluser WHERE uID = '$uactiveID'");
$row6 = mysql_fetch_array($sql6);
$uaccess = $row6['uaccess'];
$access = explode(',', $uaccess);

What I am unable to do is create the relationship between them.  Please can somebody help me with this as I have been trying for a couple of hours without any luck.

Thanks,

John
0
Comment
Question by:john-formby
  • 4
  • 4
  • 2
10 Comments
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21764121
The attached gives an example of how to limit the SELECT statement with a WHERE clause.  The WHERE is created from the uaccess field, so only those IDs (i.e. the ones the user has access to) are pulled back.
$sql6 = mysql_query("SELECT uaccess FROM tbluser WHERE uID = '$uactiveID'");

$row6 = mysql_fetch_array($sql6);

$uaccess = $row6['uaccess'];

$access = explode(',', $uaccess);

foreach ($access as $a) {

	if ($whereclause!="") $whereclause.=" OR ";

	$whereclause="(gbID=\"$a\")";

}

$whereclause="WHERE (".$whereclause.")";
 

$sql2 = mysql_query("SELECT * FROM tblgbs ".$whereclause." GROUP BY gbID");

Open in new window

0
 
LVL 14

Author Comment

by:john-formby
ID: 21764310
Hi purplepomegranite,

I tried adding that code and have:

$sql6 = mysql_query("SELECT uaccess FROM tbluser WHERE uID = '$uactiveID'");
$row6 = mysql_fetch_array($sql6);
$uaccess = $row6['uaccess'];
$access = explode(',', $uaccess);
foreach ($access as $a) {
      if ($whereclause!="") $whereclause.=" OR ";
      $whereclause="(gbID=\"$a\")";
}
$whereclause="WHERE (".$whereclause.")";
 
$sql2 = mysql_query("SELECT * FROM tblgbs ".$whereclause." GROUP BY gbID");
echo '<form action="gbadmin.php" method="post">
Choose Guestbook: <select class="txtselect" name="gbselect">';
while($row2 = mysql_fetch_array($sql2)) {
      $gbID = $row2['gbID'];
      $gbname = $row2['gbname'];
      echo '<option value="'.$gbID.'">'.$gbname.'</option>';
}
echo '</select>
<input class="subbtn" type="submit" name="submit" value="Submit" />
</form>';

The problem is that it does not display anything in the select box.
0
 
LVL 24

Accepted Solution

by:
purplepomegranite earned 500 total points
ID: 21764410
Sorry, I missed a dot out, so the where clause wasn't created properly.

I've also added an error trap to check that the second query executes ok - try it and let me know what you get.
$sql6 = mysql_query("SELECT uaccess FROM tbluser WHERE uID = '$uactiveID'");

$row6 = mysql_fetch_array($sql6);

$uaccess = $row6['uaccess'];

$access = explode(',', $uaccess);

foreach ($access as $a) {

      if ($whereclause!="") $whereclause.=" OR ";

      $whereclause.="(gbID=\"$a\")";

}

$whereclause="WHERE (".$whereclause.")";

 

$sql2 = mysql_query("SELECT * FROM tblgbs ".$whereclause." GROUP BY gbID");

if (!$sql2) {die "Failed to execute query.";}

echo '<form action="gbadmin.php" method="post">

Choose Guestbook: <select class="txtselect" name="gbselect">';

while($row2 = mysql_fetch_array($sql2)) {

      $gbID = $row2['gbID'];

      $gbname = $row2['gbname'];

      echo '<option value="'.$gbID.'">'.$gbname.'</option>';

}

echo '</select>

<input class="subbtn" type="submit" name="submit" value="Submit" />

</form>';

Open in new window

0
 
LVL 40

Expert Comment

by:RQuadling
ID: 21764505
My personal choice would be to create a new table to normalize the lists of IDs. That way you don't have more than 1 piece of information in a cell.

So, create a table called tblAccess which contains three columns -  UniqueID, tblUserID, tblGBID.

You will need to populate this table based upon the uaccess column. Once done I would remove the column.

To get the sites for a user ...

select
   tblgb.columns
from
  tbluser
  inner join
  tblaccess
    on tbluser.uniqueid = tblaccess.tbluserid
  inner join
  tblgb
    on tblaccess.tblgbid = tblgd.uniqueid
order by
  tblgb.columns

sort of thing.

0
 
LVL 14

Author Comment

by:john-formby
ID: 21764524
Hi, I get an error on the if (!$sql2) { die 'Failed to execute query';} line which is syntax error unexpected T_CONSTANT_ESCAPED_STRING
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Author Comment

by:john-formby
ID: 21764532
Ah, it's ok.  I added brackets if (!$sql2) { die('Failed to execute query');}  and it works perfectly now.  Thank you very much for your help.
0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21764549
Oops, it's getting late if I'm missing things like brackets.

Sorry about that, and glad you found the problem.  And moreover, that it now works!
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 21764581
pp (hello again). Would you not consider normalizing the uaccess column a "better" route? A single column will have a physical limit and that could limit the number of entries available. If the ID for the sites gets to be a large number (say in the thousands), then each site id would take a lot more space.

1,2,3
vs
1234,1235,1236

for example.

0
 
LVL 14

Author Closing Comment

by:john-formby
ID: 31466341
Thank you for your very quick and excellent response.  This had been bugging me for hours :-)
0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21764617
Yes, that is a better route undoubtedly.  My code was simply to work with what john-formby currently has.  Though in the long run it may be better to address issues like how the data is stored now rather than having to address it when there are issues such as field length limits.

One of the fundamentals of good database design is that each field should only contain one item of information.  Another is that no data should be repeated in more than one table.  It helps in the long run, both from a coding and a database management point of view.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now