Solved

Explode field to use within query

Posted on 2008-06-11
10
922 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

839 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