?
Solved

Explode field to use within query

Posted on 2008-06-11
10
Medium Priority
?
949 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 2000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 40

Expert Comment

by:Richard Quadling
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
 
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:Richard Quadling
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month16 days, 17 hours left to enroll

864 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