Im going to have to leave it at this point now, and prey someone can help.
The code below takes it further than I have got it today.
The categories all row correctly horizontally across the top, then I have it so that a selection of documents appear but only in relation to the first category and they unfortunately row horizontally and not vertically.
Whats not happening is that its not skipping to the next category and then outputting the documents to the with that next category and so on, again outputting the documents vertically and then moving on again.
I’m messing it up somewhere and I cant see it, or fix it.
<?php
$query4 = "select User from Documents WHERE Corporation = $CORPid";
$result24 = mysql_query($query4);
$rowu4 = mysql_fetch_array($result24, MYSQL_ASSOC);
$corpIDs4 = explode(";",$rowu4['User']);
$acorps4 = implode(',', $corpIDs4);
$query = "select ID, Name, Active from Categories WHERE (Active=1) ORDER BY ID";
$result = mysql_query($query) or die ("Query failed");
//get the number of rows in our result so we can use it in a for loop
$numrows = (mysql_num_rows ($result));
// loop to create rows
if($numrows >0){
echo "<table class='gridtable'>";
// loop to create columns
echo "<tr>";
while ($List = mysql_fetch_assoc($result)){
$for = $List['ID'];
echo " <th><a href='view.php?corp_ID=$CORPid&category=$for' title='$List[Name]'>$List[Name]</a></th> ";
}
echo "</tr> ";
echo "<tr>";
$k=mysql_query("select ID, Title, Description, Active, User, Corporation, Category, Document from Documents WHERE Corporation = '$CORPid' AND User IN ($acorps4) ORDER BY ID");
while($d=mysql_fetch_assoc($k)){
echo " <td>".$d['ID']." - ".$d['Title']."</td> ";
}
echo "</tr> ";
}
echo $end."</table> ";
?>
Sorry for not getting back sooner.
I did this sample the other night based on using that corporation_users table. You might be able to use some of it.
Note: I have not looked at your latest posts.
<?php
include ("db.php");
$Categories = array();
$t=mysql_query("SELECT Name FROM Categories WHERE Active=1 ORDER BY ID ASC");
while($g=mysql_fetch_assoc($t)){
$Categories[] = $g['Name'];
}
//Test result for Categories
//echo "<pre>";
//print_r($Categories);
//echo "</pre>";
// It is unclear how you are setting user ID variable.
// Assumed user ID by session. Change as needed.
$user_id = 1;
// Note many things can be added, for example
// username for each contributor to the document,
// other company info from another table etc
$data = array();
if(isset($user_id)):
$sql ="SELECT
cu.corporation_id,
co.Name AS Corporation,
d.`ID` AS document_id,
d.`Title`,
d.`Category`,
d.`Document`
FROM `corporation_users` AS cu
LEFT JOIN `corporations` AS co
ON co.ID = cu.corporation_id
LEFT JOIN `documents` AS d
ON d.Corporation = cu.corporation_id
WHERE cu.user_id = '$user_id'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$data[$row['document_id']][$row['corporation_id']]['Corporation'] = $row['Corporation'];
$data[$row['document_id']][$row['corporation_id']][$row['Corporation']][$row['Category']]['document_id'] = $row['document_id'];
$data[$row['document_id']][$row['corporation_id']][$row['Corporation']][$row['Category']]['Title'] = $row['Title'];
$data[$row['document_id']][$row['corporation_id']][$row['Corporation']][$row['Category']]['Document'] = $row['Document'];
}
//echo "<pre>";
//print_r($data);
//echo "</pre>";
endif;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>
<table border="1">
<?php
echo '<tr>'."\\r";
//Like a reset button to remove all GETs
echo '<td><a href="?">Show All</a></td>'."\\r";
//echo '<td><a href="?">Corporation</a></td>'."\\r";
$catcnt = count($Categories);
foreach($Categories as $Category):
echo '<td><a href="?Cat=' . $Category . '" title="' . $Category . ' - Click to view all documents">' . $Category . '</a></td>'."\\r";
endforeach;
echo '</tr>'."\\r";
if(!isset($_GET['DocID'])){
foreach($data as $docID => $arry):
foreach($data[$docID] as $CorpID => $arry):
$Corporation = $data[$docID][$CorpID]['Corporation'];
//Using GET we can filter to view only one corporation
if(!isset($_GET['CorpID']) || isset($_GET['CorpID']) && $_GET['CorpID'] == $CorpID):
echo '<tr>'."\\r";
echo '<td><a href="?CorpID=' . $CorpID . '">' . $Corporation . '</a></td>'."\\r";
foreach($Categories as $Category):
//Using GET we can filter to view one category
if(!isset($_GET['Cat']) || isset($_GET['Cat']) && $_GET['Cat'] == $Category){
//General list of documents
if(array_key_exists($CorpID,$data[$docID]) &&
array_key_exists($Corporation,$data[$docID][$CorpID]) &&
array_key_exists($Category,$data[$docID][$CorpID][$Corporation])){
$title = $data[$docID][$CorpID][$Corporation][$Category]['Title'];
$document_id = $data[$docID][$CorpID][$Corporation][$Category]['document_id'];
echo '<td><a href="?CorpID=' . $CorpID . '&Corp=' . $Corporation . '&Cat=' . $Category . '&DocID=' . $document_id . '">' . $title . '</a></td>'."\\r";
}else{
echo '<td> </td>'."\\r";
}
}else{
echo '<td> </td>'."\\r";
}
endforeach;
echo '</tr>'."\\r";
endif;
endforeach;
endforeach;
}else{
$title = $data[$_GET['DocID']][$_GET['CorpID']][$_GET['Corp']][$_GET['Cat']]['Title'];
$document_id = $data[$_GET['DocID']][$_GET['CorpID']][$_GET['Corp']][$_GET['Cat']]['document_id'];
$document = $data[$_GET['DocID']][$_GET['CorpID']][$_GET['Corp']][$_GET['Cat']]['Document'];
$colspan = $catcnt+1;
echo '<tr>
<td colspan="' . $colspan . '">
<strong>' . $title . '</strong><br />
' . $document . '
</td>
</tr>';
}
?>
</table>
</body>
</html>
Hi Drummin,
Thank for coming back, that code above is very different to what I was trying to do, so no wonder I wasnt getting it right.
This is a bit of a step up again, so will give it a shot, but if you had the time to go through where I got to and what I did to get there, you could see better what I am working with.
There was me thinking this was going to be a simple table with dynamic output…
Thanks
Note: I wasn’t sure if user ID’s are related to corporation or document. I also wasn’t sure if each corporation would have one or many documents under each category.
The ID of the user allowed to see these documents is stored in an array in field User with the documents tabel.
So basically the document appeasr under the category if they are associated to it.
This is pretty much the page as it is
<?php
session_start();
require_once('../auth.php');
include "../config.php";
error_reporting(E_ALL);
ini_set('display_errors','Off');
$CORPid = $_GET['corpID'];
$flag=1;
$update="";
$whoIs=$_SESSION['SESS_FIRST_NAME'];
$t=mysql_query("select * from UserAdmin WHERE (Username='".$whoIs."') ORDER BY ID");
while($g=mysql_fetch_assoc($t)){
$cID = mysql_real_escape_string($g['ID']);
$firstName = mysql_real_escape_string($g['First Name']);
$secondName = mysql_real_escape_string($g['Last Name']);
$active = mysql_real_escape_string($g['Active']);
$supera = mysql_real_escape_string($g['Super Admin']);
}
if(isset($_GET['stock_Id']))
{
$stock_Id=$_GET['stock_Id'];
$q=mysql_query("select * from Corporations where ID=$stock_Id") or die (mysql_error());
$rows=mysql_fetch_assoc($q);
$corporationName=$rows["Name"];
$corporationActive=$rows["Active"];
$flag=0;
}
if(isset($_GET['pdf']))
{
$stock_Id=$_GET['pdf'];
$q=mysql_query("select * from Documents WHERE (ID=$stock_Id) AND (User=$cID) AND (Active=1) AND (Corporation=$CORPid)") or die (mysql_error());
$rows=mysql_fetch_assoc($q);
$fid=mysql_real_escape_string($rows["ID"]);
$ftitle=mysql_real_escape_string($rows["Title"]);
$fdescription=mysql_real_escape_string($rows["Description"]);
$flag=0;
$update="1";
}
?>
<!doctype html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>
<div id="header">
<div class="container-outer">
<div class="container-inner">
<div class="table">
<?php
$query4 = "select User from Documents WHERE Corporation = $CORPid";
$result24 = mysql_query($query4);
$rowu4 = mysql_fetch_array($result24, MYSQL_ASSOC);
$corpIDs4 = explode(";",$rowu4['User']);
$acorps4 = implode(',', $corpIDs4);
$query = "select ID, Name, Active from Categories WHERE (Active=1) ORDER BY ID";
$result = mysql_query($query) or die ("Query failed");
//get the number of rows in our result so we can use it in a for loop
$numrows = (mysql_num_rows ($result));
// loop to create rows
if($numrows >0){
echo "<table class='gridtable'>";
// loop to create columns
echo "<tr>";
while ($List = mysql_fetch_assoc($result)){
$for = $List['ID'];
echo " <th><a href='view.php?corp_ID=$CORPid&category=$for' title='$List[Name]'>$List[Name]</a></th> ";
}
echo "</tr> ";
echo "<tr>";
$k=mysql_query("select ID, Title, Description, Active, User, Corporation, Category, Document from Documents WHERE Corporation = '$CORPid' AND User IN ($acorps4) ORDER BY ID");
while($d=mysql_fetch_assoc($k)){
echo " <td>".$d['ID']." - ".$d['Title']."</td> ";
}
echo "</tr> ";
}
echo $end."</table> ";
?>
</div>
</div>
</div>
<!-- End of new bit -->
</div>
I realize you are storing a group of user ID’s in the User column of the documents table. That doesn’t really answer if user ID’s are related to corporation or document. For example
If
document1 is written by user1, user2 and user3
document2 is written by user4, user5 and user6
AND all these users belong to corporation1.
Then if based on corporation, user5 can view document1 because they are in corporation1.
Then if based on document, user5 can view only document2.
This would mean that the corporation_user table would need a document_id column instead of being based on corporation as originally thought.
In testing without the corporation_user table, I didn’t have 100% results using IN ($user_id), in that not all records that have user_id were retrieved, so this version queries all records from documents and sorting after the fact only using records where user ID is in User column of documents table. I also noticed you are using category ID’s and Corporation ID’s instead of names, so I changed the way data array is built and used in the table. Added some basic css to this sample as well.
<?php
session_start();
require_once('../auth.php');
include "../config.php";
//error_reporting(E_ALL);
//ini_set('display_errors','Off');
if(isset($_SESSION['SESS_FIRST_NAME'])){
$whoIs = mysql_real_escape_string($_SESSION['SESS_FIRST_NAME']);
$t = mysql_query("select * from UserAdmin WHERE Username = '".$whoIs."'");
$g = mysql_fetch_assoc($t);
$cID = $g['ID'];
$firstName = $g['First Name'];
$secondName = $g['Last Name'];
$active = $g['Active'];
$supera = $g['Super Admin'];
}
$Categories = array();
$t=mysql_query("SELECT ID,Name FROM Categories WHERE Active=1 ORDER BY ID ASC");
while($g=mysql_fetch_assoc($t)){
$Categories[$g['ID']] = $g['Name'];
}
$user_id = $cID;
$data = array();
if(isset($user_id)):
$sql ="SELECT
d.Corporation AS corporation_id,
d.`ID` AS document_id,
d.`User`,
d.`Title`,
d.`Category`,
d.`Description`,
d.`Document`,
co.Name AS Corporation
FROM `documents` AS d
LEFT JOIN `corporations` AS co
ON co.ID = d.Corporation
WHERE d.`Active` = 1";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$Users = explode(";",$row['User']);
if(in_array($user_id,$Users)){
$data[$row['document_id']][$row['corporation_id']]['Corporation'] = $row['Corporation'];
$data[$row['document_id']][$row['corporation_id']][$row['Category']]['document_id'] = $row['document_id'];
$data[$row['document_id']][$row['corporation_id']][$row['Category']]['Title'] = $row['Title'];
$data[$row['document_id']][$row['corporation_id']][$row['Category']]['Document'] = $row['Document'];
$data[$row['document_id']][$row['corporation_id']][$row['Category']]['Description'] = $row['Description'];
}
}
//echo "<pre>";
//print_r($data);
//echo "</pre>";
endif;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<style type="text/css">
.gridtable {
background-color:#E3E3E3;
white-space:nowrap;
}
.gridtable th{
padding: 1px 3px;
background-color:#5A5F73;
color: #FFFFFF;
white-space:nowrap;
}
.gridtable th a:link{
color: #FFFFFF;
text-decoration: none;
}
.gridtable td{
padding: 1px 3px;
background-color:#FFFFFF;
color: #000000;
white-space:nowrap;
}
.gridtable td a:link{
color: #000000;
text-decoration: none;
}
.gridtable td .doc a:link{
color: #0033FF;
text-decoration: none;
}
</style>
</head>
<body>
<table class='gridtable' cellpadding='0' cellspacing='1'>
<?php
echo '<tr>'."\\r";
//Like a reset button to remove all GETs
echo '<th><a href="?">Show All</a></th>'."\\r";
//echo '<td><a href="?">Corporation</a></td>'."\\r";
$catcnt = count($Categories);
foreach($Categories as $CatID => $Category):
$Category = str_replace("& ","& ",$Category);
echo '<th><a href="?Cat=' . $CatID . '" title="' . $Category . ' - Click to view all documents">' . $Category . '</a></th>'."\\r";
endforeach;
echo '</tr>'."\\r";
if(!isset($_GET['DocID'])){
foreach($data as $docID => $arry):
foreach($data[$docID] as $CorpID => $arry):
$Corporation = $data[$docID][$CorpID]['Corporation'];
//Using GET we can filter to view only one corporation
if(!isset($_GET['CorpID']) || isset($_GET['CorpID']) && $_GET['CorpID'] == $CorpID):
echo '<tr>'."\\r";
echo '<td><a href="?CorpID=' . $CorpID . '">' . $Corporation . '</a></td>'."\\r";
foreach($Categories as $CatID => $Category):
//Using GET we can filter to view one category
if(!isset($_GET['Cat']) || isset($_GET['Cat']) && $_GET['Cat'] == $CatID){
//General list of documents
if(array_key_exists($CorpID,$data[$docID]) &&
array_key_exists($CatID,$data[$docID][$CorpID])){
// Fix ampersand
$Corporation = str_replace("& ","& ",$Corporation);
$title = str_replace("& ","& ",$data[$docID][$CorpID][$CatID]['Title']);
$document_id = $data[$docID][$CorpID][$CatID]['document_id'];
echo '<td><a href="?CorpID=' . $CorpID . '&Cat=' . $CatID . '&DocID=' . $document_id . '">' . $title . '</a></td>'."\\r";
}else{
echo '<td> </td>'."\\r";
}
}else{
echo '<td> </td>'."\\r";
}
endforeach;
echo '</tr>'."\\r";
endif;
endforeach;
endforeach;
}else{
$title = str_replace("& ","& ",$data[$_GET['DocID']][$_GET['CorpID']][$_GET['Cat']]['Title']);
$document_id = $data[$_GET['DocID']][$_GET['CorpID']][$_GET['Cat']]['document_id'];
$document = str_replace("& ","& ",$data[$_GET['DocID']][$_GET['CorpID']][$_GET['Cat']]['Document']);
$Description = str_replace("& ","& ",$data[$_GET['DocID']][$_GET['CorpID']][$_GET['Cat']]['Description']);
$colspan = $catcnt+1;
echo '<tr>
<td colspan="' . $colspan . '">
<strong>' . $title . '</strong><br />
' . $Description . '<br />
<span class="doc"><a href="' . $document . '">' . $title . '</a></span>
</td>
</tr>';
}
?>
</table>
</body>
</html>
Here’s a partial screenshot of how it looks.
Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.
Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.
Hi Drummin,
This awesome thank you very much, works really well, and in honesty there no way I would have got here on my own, so another steep learnng curve, so thanks again.
Can I ask a question about it, the documents outpu according to the person logging in but the page before they select a corporation and that must also influence what documents show.
Its a simple addition, but wondered if you could help me with it.
In my attempt I had this.
from Documents WHERE Corporation = '$CORPid'
Inside
<td><a href="#" title="<?php echo $g['Name']?> - Click to view all documents"><?php echo $g['Name']?></a></td>
<?php } ?>
<?php
$k=mysql_query("select * from Documents WHERE Corporation = '$CORPid' AND User IN ($acorps4) ORDER BY ID");
while($d=mysql_fetch_assoc($k)){ ?>
<tr>
<td><?php echo $d['Title']?></td>
</tr>
<?php } ?>
</tr>
It will make sure that they only see the documents that are related to the company selected.
I may also have to change the order by bit as they now want to be able to manually put a figure in for a document under each category which will start with 0001, so rather than ordering them vertically by the ID it ill be that, but havent built it in yet.
I’m only going to ask also as I know they will ask, but you know when the documents are ordering in the category columns, they dont start at the top if you know what I mean, sometimes they appear further down the column, there no way is there that each new document per category can start at the top and then work they way down in order, thats one I may need ot explain if not.
But like I said thank you, this is precious stuff.
Adding d.Corporation = ‘$CORPid’ to the query should take care of that as long as $CORPid has been defined.
As far as spaces in the columns, this was why I was asking about if corporations would have more than one document per category. Looks like they will have more than one, which is why I built it like I did with corporation names being listed for each document in the far left column. Maybe with some data sorting it could be changed.
Sorry Drummin,
Sorted the AND Corporation = ‘$CORPid’ issue, simple really when you take your time. Shouldn’t be any problem now with the other issue over the inserted figures to change the ordering.
I had to remove the left show all column, as he didnt like it, but he didnt mention about the empty spaces, but im guessing over the weekend he may.
The rest of is class theough Drummin, its absolutely brilliant, thank you and a def learning curve for me.
Here’s a new revision of page. Data is built and displayed differently to eliminate extra rows. That first column is removed, but because of this, there was no way to reset view after picking a category or a document so I added a “Return to list view” link.
<?php
session_start();
require_once('../auth.php');
include "../config.php";
//error_reporting(E_ALL);
//ini_set('display_errors','Off');
// Define $CORPid with GET, POST etc
$CORPid = $_POST['CORPid'];
if(isset($_SESSION['SESS_FIRST_NAME'])){
$whoIs = mysql_real_escape_string($_SESSION['SESS_FIRST_NAME']);
$t = mysql_query("select * from UserAdmin WHERE Username = '".$whoIs."'");
$g = mysql_fetch_assoc($t);
$cID = $g['ID'];
$firstName = $g['First Name'];
$secondName = $g['Last Name'];
$active = $g['Active'];
$supera = $g['Super Admin'];
//echo "<pre>";
//print_r($g);
//echo "</pre>";
}
$Categories = array();
$t=mysql_query("SELECT ID,Name FROM Categories WHERE Active=1 ORDER BY ID ASC");
while($g=mysql_fetch_assoc($t)){
$Categories[$g['ID']] = $g['Name'];
}
$user_id = $cID;
$data = array();
if(isset($user_id) && isset($CORPid) && !empty($CORPid)):
$user_id = mysql_real_escape_string($user_id);
$sql ="SELECT
d.Corporation AS corporation_id,
d.`ID` AS document_id,
d.`User`,
d.`Title`,
d.`Category`,
d.`Description`,
d.`Document`,
co.Name AS Corporation
FROM `documents` AS d
LEFT JOIN `corporations` AS co
ON co.ID = d.Corporation
WHERE d.`Active` = 1 AND d.Corporation = '$CORPid'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$Users = explode(";",$row['User']);
if(in_array($user_id,$Users)){
$data['Documents'][$row['Category']]['document_id'][] = $row['document_id'];
$data['Documents'][$row['Category']]['Title'][] = $row['Title'];
$data['Documents'][$row['Category']]['Document'][] = $row['Document'];
$data['Documents'][$row['Category']]['Description'][] = $row['Description'];
}
}
//get total rows for each category
if(array_key_exists("Documents",$data)):
foreach($data['Documents'] as $Cat => $arry):
$data['counts'][] = count($data['Documents'][$Cat]['document_id']);
endforeach;
//Get max number of rows key
$totalrows = max($data['counts'])-1;
endif;
//echo "<pre>";
//print_r($data);
//echo "</pre>";
endif;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<style type="text/css">
.gridtable {
background-color:#E3E3E3;
white-space:nowrap;
}
.gridtable th{
padding: 1px 3px;
background-color:#5A5F73;
color: #FFFFFF;
white-space:nowrap;
}
.gridtable th a:link{
color: #FFFFFF;
text-decoration: none;
}
.gridtable td{
padding: 1px 3px;
background-color:#FFFFFF;
color: #000000;
white-space:nowrap;
}
.gridtable td a:link{
color: #000000;
text-decoration: none;
}
.gridtable td .doc a:link{
color: #0033FF;
text-decoration: none;
}
</style>
</head>
<body>
<table class='gridtable' cellpadding='0' cellspacing='1'>
<?php
echo '<tr>'."\\r";
$catcnt = count($Categories);
foreach($Categories as $CatID => $Category):
$Category = str_replace("& ","& ",$Category);
if(!isset($_GET['Cat']) || isset($_GET['Cat']) && $_GET['Cat'] == $CatID):
echo '<th><a href="?Cat=' . $CatID . '" title="' . $Category . ' - Click to view all documents">' . $Category . '</a></th>'."\\r";
endif;
endforeach;
echo '</tr>'."\\r";
if(!isset($_GET['DocID'])){
if(array_key_exists("Documents",$data)){
for($i=0;$i<=$totalrows;$i++):
echo '<tr>'."\\r";
foreach($Categories as $CatID => $Category):
//Using GET we can filter to view one category
if(!isset($_GET['Cat']) || isset($_GET['Cat']) && $_GET['Cat'] == $CatID){
if(array_key_exists($CatID,$data['Documents']) &&
array_key_exists($i,$data['Documents'][$CatID]['document_id'])){
// Fix ampersand
$title = str_replace("& ","& ",$data['Documents'][$CatID]['Title'][$i]);
$document_id = $data['Documents'][$CatID]['document_id'][$i];
echo '<td><a href="?Cat=' . $CatID . '&DocKey=' . $i . '&DocID=' . $document_id . '">' . $title . '</a></td>'."\\r";
//Reset to view all categories
if(isset($_GET['Cat']) && $_GET['Cat'] == $CatID && $i == $totalrows):
echo '</tr>
<tr>
<td><span class="doc"><a href="?">Return to list view</a></span></td>'."\\r";
endif;
}else{
echo '<td> </td>'."\\r";
//Reset to view all categories
if(isset($_GET['Cat']) && $_GET['Cat'] == $CatID && $i == $totalrows):
echo '</tr>
<tr>
<td><span class="doc"><a href="?">Return to list view</a></span></td>'."\\r";
endif;
}
}
endforeach;
echo '</tr>'."\\r";
endfor;
}else{
echo '<tr>
<td colspan="' . $catcnt . '">No documents found</td>
</tr>'."\\r";
}
}else{
$title = str_replace("& ","& ",$data['Documents'][$_GET['Cat']]['Title'][$_GET['DocKey']]);
$document_id = $data['Documents'][$_GET['Cat']]['document_id'][$_GET['DocKey']];
$document = str_replace("& ","& ",$data['Documents'][$_GET['Cat']]['Document'][$_GET['DocKey']]);
$Description = str_replace("& ","& ",$data['Documents'][$_GET['Cat']]['Description'][$_GET['DocKey']]);
echo '<tr>
<td>
<strong>' . $title . '</strong><br />
' . $Description . '<br />
<span class="doc"><a href="' . $document . '">' . $title . '</a></span>
</td>
</tr>
<tr>
<td><span class="doc"><a href="?">Return to list view</a></span></td>
</tr>';
}
?>
</table>
</body>
</html>
I just realized $CORPid is only going to work in my example on initial page load. How is $CORPid defined from the “other page” and sent to this page? By GET, by SESSION?
If not by session, then we’ll need to add CORPid as a GET value to all links. Not a big deal. Just need to know how it is sent to page.
Modified for GET CORPid
<?php
session_start();
require_once('../auth.php');
include "../config.php";
//error_reporting(E_ALL);
//ini_set('display_errors','Off');
// Define $CORPid with GET
$CORPid = (isset($_GET['CORPid']) && is_numeric($_GET['CORPid']) ? $_GET['CORPid'] : '');
if(isset($_SESSION['SESS_FIRST_NAME'])){
$whoIs = mysql_real_escape_string($_SESSION['SESS_FIRST_NAME']);
$t = mysql_query("select * from UserAdmin WHERE Username = '".$whoIs."'");
$g = mysql_fetch_assoc($t);
$cID = $g['ID'];
$firstName = $g['First Name'];
$secondName = $g['Last Name'];
$active = $g['Active'];
$supera = $g['Super Admin'];
//echo "<pre>";
//print_r($g);
//echo "</pre>";
}
$Categories = array();
$t=mysql_query("SELECT ID,Name FROM Categories WHERE Active=1 ORDER BY ID ASC");
while($g=mysql_fetch_assoc($t)){
$Categories[$g['ID']] = $g['Name'];
}
$user_id = $cID;
$data = array();
if(isset($user_id) && isset($CORPid) && !empty($CORPid)):
$CORPid = mysql_real_escape_string($CORPid);
$sql ="SELECT
d.Corporation AS corporation_id,
d.`ID` AS document_id,
d.`User`,
d.`Title`,
d.`Category`,
d.`Description`,
d.`Document`,
co.Name AS Corporation
FROM `documents` AS d
LEFT JOIN `corporations` AS co
ON co.ID = d.Corporation
WHERE d.`Active` = 1 AND d.Corporation = '$CORPid'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$Users = explode(";",$row['User']);
if(in_array($user_id,$Users)){
$data['Documents'][$row['Category']]['document_id'][] = $row['document_id'];
$data['Documents'][$row['Category']]['Title'][] = $row['Title'];
$data['Documents'][$row['Category']]['Document'][] = $row['Document'];
$data['Documents'][$row['Category']]['Description'][] = $row['Description'];
}
}
//get total rows for each category
if(array_key_exists("Documents",$data)):
foreach($data['Documents'] as $Cat => $arry):
$data['counts'][] = count($data['Documents'][$Cat]['document_id']);
endforeach;
//Get max number of rows key
$totalrows = max($data['counts'])-1;
endif;
//echo "<pre>";
//print_r($data);
//echo "</pre>";
endif;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<style type="text/css">
.gridtable {
background-color:#E3E3E3;
white-space:nowrap;
}
.gridtable th{
padding: 1px 3px;
background-color:#5A5F73;
color: #FFFFFF;
white-space:nowrap;
}
.gridtable th a:link{
color: #FFFFFF;
text-decoration: none;
}
.gridtable td{
padding: 1px 3px;
background-color:#FFFFFF;
color: #000000;
white-space:nowrap;
}
.gridtable td a:link{
color: #000000;
text-decoration: none;
}
.gridtable td .doc a:link{
color: #0033FF;
text-decoration: none;
}
</style>
</head>
<body>
<table class='gridtable' cellpadding='0' cellspacing='1'>
<?php
echo '<tr>'."\\r";
$catcnt = count($Categories);
foreach($Categories as $CatID => $Category):
$Category = str_replace("& ","& ",$Category);
if(!isset($_GET['Cat']) || isset($_GET['Cat']) && $_GET['Cat'] == $CatID):
echo '<th><a href="?CORPid=' . $CORPid . '&Cat=' . $CatID . '" title="' . $Category . ' - Click to view all documents">' . $Category . '</a></th>'."\\r";
endif;
endforeach;
echo '</tr>'."\\r";
if(!isset($_GET['DocID'])){
if(array_key_exists("Documents",$data)){
for($i=0;$i<=$totalrows;$i++):
echo '<tr>'."\\r";
foreach($Categories as $CatID => $Category):
//Using GET we can filter to view one category
if(!isset($_GET['Cat']) || isset($_GET['Cat']) && $_GET['Cat'] == $CatID){
if(array_key_exists($CatID,$data['Documents']) &&
array_key_exists($i,$data['Documents'][$CatID]['document_id'])){
// Fix ampersand
$title = str_replace("& ","& ",$data['Documents'][$CatID]['Title'][$i]);
$document_id = $data['Documents'][$CatID]['document_id'][$i];
echo '<td><a href="?CORPid=' . $CORPid . '&Cat=' . $CatID . '&DocKey=' . $i . '&DocID=' . $document_id . '">' . $title . '</a></td>'."\\r";
//Reset to view all categories
if(isset($_GET['Cat']) && $_GET['Cat'] == $CatID && $i == $totalrows):
echo '</tr>
<tr>
<td><span class="doc"><a href="?CORPid=' . $CORPid . '">Return to list view</a></span></td>'."\\r";
endif;
}else{
echo '<td> </td>'."\\r";
//Reset to view all categories
if(isset($_GET['Cat']) && $_GET['Cat'] == $CatID && $i == $totalrows):
echo '</tr>
<tr>
<td><span class="doc"><a href="?CORPid=' . $CORPid . '">Return to list view</a></span></td>'."\\r";
endif;
}
}
endforeach;
echo '</tr>'."\\r";
endfor;
}else{
echo '<tr>
<td colspan="' . $catcnt . '">No documents found</td>
</tr>'."\\r";
}
}else{
$title = str_replace("& ","& ",$data['Documents'][$_GET['Cat']]['Title'][$_GET['DocKey']]);
$document_id = $data['Documents'][$_GET['Cat']]['document_id'][$_GET['DocKey']];
$document = str_replace("& ","& ",$data['Documents'][$_GET['Cat']]['Document'][$_GET['DocKey']]);
$Description = str_replace("& ","& ",$data['Documents'][$_GET['Cat']]['Description'][$_GET['DocKey']]);
echo '<tr>
<td>
<strong>' . $title . '</strong><br />
' . $Description . '<br />
<span class="doc"><a href="' . $document . '">' . $title . '</a></span>
</td>
</tr>
<tr>
<td><span class="doc"><a href="?CORPid=' . $CORPid . '">Return to list view</a></span></td>
</tr>';
}
?>
</table>
</body>
</html>
Thank you very much Drummin,
It works so well I cant believe it.
As its a step up for me this, I’m going to create a second project just for myself, to try and apply this myself and learn how to use it for the future.
Just a few things to tie up with it now and then im almost done on it.
Thanks
Hi multichild,
I hope you don’t mind, but I’ve got a couple of suggestions:
As SpacePhoenix pointed out above, the mysql extension is deprecated, and can lead to insecure code. It’s a much better idea to use either mysqli or PDO.
Also, it would be a good idea to separate out your program logic from your HTML. Mixing in DB access, filtering, sorting and other logic into your templates makes your code harder to read and harder to maintain. It’s good to keep your HTML in separate files, away from the bulk of your PHP code. Your markup shouldn’t know anything about where the data comes from, as that’s not its job.
I’d suggest having a read through this article: http://symfony.com/doc/current/book/from_flat_php_to_symfony2.html. It’s not necessary to go all the way to using the Symfony framework, but the first part covers some techniques for cleanly separating your code so it’s more maintainable.
Thank you fretburner, I appreciate the advice.
I’m nearly done with this project now, and will advise the client and go through it all at some point, but might not get the access, but we see.
I’m on a final page where Im trying to utput the documents according to the corporation choose, then category selected and this is where its not working the user id which is stored within an array, and so I have the code below.
var_dump("select * from Documents WHERE Corporation=(".$CORPid.") AND Category=(".$CATid.") AND (Active=1) AND User IN (".$cID.")");
$query =mysql_query("select * from Documents WHERE Corporation=(".$CORPid.") AND Category=(".$CATid.") AND (Active=1) AND User IN (".$cID.")");
while($g=mysql_fetch_assoc($query)){
$ID = mysql_real_escape_string($g['ID']);
$Title = mysql_real_escape_string($g['Title']);
The var_dump is showing
string(94) "select * from Documents WHERE Corporation=(6) AND Category=(54) AND (Active=1) AND User IN (8)"
The array is stored in User and as an example is set out liek this - 1;8;9;10;32;33
So in effect that document with that array in User should show, as it has 8 in it, but its not working.
$cID comes from
$whoIs=$_SESSION['SESS_FIRST_NAME'];
$t=mysql_query("select * from UserAdmin WHERE (Username='".$whoIs."') ORDER BY ID");
while($g=mysql_fetch_assoc($t)){
$cID = mysql_real_escape_string($g['ID']);
If ‘1;8;9;10;32;33’ is an example of the user column in your table, then using User IN (8) isn’t going to work because it’s designed to return columns that match one or more of the values that you supply between the parenthesis.
Lets say your table ‘Users’ looks like this:
id name
-- ----
5 Freddy
6 Brian
7 Roger
8 John
and you run this query:
SELECT * FROM Users WHERE id IN (5, 8);
then you’ll get the rows for Freddy and John returned.
In the case of what you’re trying to do, I suppose you could do something like this:
SELECT * FROM Documents
WHERE Corporation = 6
AND Category = 54
AND Active = 1
AND (User LIKE "8;%" OR User LIKE "%;8;%" OR User LIKE "%;8")
but it’s a bit hacky. If you had a junction table which linked users to documents, it’d be easier (and more efficient) to query for this sort of thing.
AAAAAAAAAAAAAAAAAAAAAAAHHHHHHHHHHHHHHHHHHHHH!
Nightmare sorry fretburner, of course LIKE was the answer.
Cheers