Help with SQL query/PHP loop
Posted: Thu Dec 18, 2008 10:50 am
Hello everyone,
This is an internal program we use to keep track of clients and stuff. We can login, enter some information on a client, and then we can all see it.
I'm trying to add the functionality where you have to "clear" completed tasks. Right now if you log in and update a client, it will only pick one status update per client. So we've all become rather scared of updating stuff, since the previous issue still hasn't been addressed.
What I have is an SQL table for each client. Inside, every status is posted. I've added a "display" field to determined whether it is "cleared" or not. 1 for display. 2 for hide.
But I can't seem to figure out the syntax to make it loop properly. Any help would be appreciated.
Here is the SQL format:
And here is the PHP. Right now, it is still just displaying 1 per client:
I've tried this and similar things, but it just gives me nothing:
Also if anyone has any other advice on how to improve it, I'd love to learn more. I mainly just need to get it to work though, but if I can do anything to speed up the execution time; even better.
This is an internal program we use to keep track of clients and stuff. We can login, enter some information on a client, and then we can all see it.
I'm trying to add the functionality where you have to "clear" completed tasks. Right now if you log in and update a client, it will only pick one status update per client. So we've all become rather scared of updating stuff, since the previous issue still hasn't been addressed.
What I have is an SQL table for each client. Inside, every status is posted. I've added a "display" field to determined whether it is "cleared" or not. 1 for display. 2 for hide.
But I can't seem to figure out the syntax to make it loop properly. Any help would be appreciated.
Here is the SQL format:
Code: Select all
CREATE TABLE `DB_Testing` (
`id` tinyint(255) NOT NULL AUTO_INCREMENT,
`status` mediumtext character SET utf8 collate utf8_unicode_ci NOT NULL,
`priority` tinytext character SET utf8 collate utf8_unicode_ci NOT NULL,
`user` tinytext character SET utf8 collate utf8_unicode_ci NOT NULL,
`date` varchar(255) character SET utf8 collate utf8_unicode_ci NOT NULL,
`assignedto` varchar(255) character SET utf8 collate utf8_unicode_ci NOT NULL,
`display` tinyint(2) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `display` (`display`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
--
-- Table structure for table `clients`
--
CREATE TABLE `clients` (
`id` tinyint(255) NOT NULL AUTO_INCREMENT,
`client` tinytext character SET utf8 collate utf8_unicode_ci NOT NULL,
`priority` tinyint(10) NOT NULL,
`date` tinytext character SET utf8 collate utf8_unicode_ci NOT NULL,
`user` tinytext character SET utf8 collate utf8_unicode_ci NOT NULL,
`assignedto` text character SET utf8 collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=61 ;
Code: Select all
if ($cookie = "true"){
$myusername = $_GET['id'];
$statussort = $_GET['status'];
$prioritysort = $_GET['priority'];
$usersort = $_GET['user'];
$datesort = $_GET['date'];
$clientsort = $_GET['client'];
//output header
echo <<<HEADER
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html>
<head>
</head>
<body>
<div id="leftcontent">
<div class="datatable">
<!--Labels-->
<div class="titlesone"><a href="index-true.php?priority=DESC&id=$myusername">Priority</a></div>
<div class="titlestwo"><a href="index-true.php?client=ASC&id=$myusername">Client</a></div>
<div class="titlesthree">Last Status</div>
<div class="titlesfour"><a href="index-true.php?date=DESC&id=$myusername">Date</a></div>
<div class="titlessix"><a href="index-true.php?user=ASC&id=$myusername">Assigned</a></div>
<div class="clear"></div>
HEADER;
//connect
db_connect();
//query
if (isset($clientsort)){
$sql="SELECT * FROM `clients` ORDER BY `client` ASC";
$result=mysql_query($sql) or die (mysql_error());
}elseif (isset($prioritysort)){
$sql="SELECT * FROM `clients` ORDER BY `priority` DESC";
$result=mysql_query($sql) or die (mysql_error());
}elseif (isset($usersort)){
$sql="SELECT * FROM `clients` ORDER BY `priority`,`user` DESC";
$result=mysql_query($sql) or die (mysql_error());
}elseif (isset($datesort)){
$sql="SELECT * FROM `clients` ORDER BY `priority`,`date` ASC";
$result=mysql_query($sql) or die (mysql_error());
}else{
$sql="SELECT * FROM `clients` ORDER BY `priority`,`date` DESC";
$result=mysql_query($sql) or die (mysql_error());
}
//stick our toe in the water
$totalpull = mysql_num_rows($result) or die (mysql_error());
$i=0;
while($totalpull > $i){
//Loop through client names
$array = mysql_fetch_array($result);
$clientname=($array["client"]);
//select other db
$sqlx = "SELECT * FROM `$clientname` WHERE `display`=1 ORDER BY `id` DESC";
$resultx=mysql_query($sqlx) or die (mysql_error());
//fetch data array
$array = mysql_fetch_array($resultx);
//assign variables
$status=($array["status"]);
$assignedto=($array["assignedto"]);
$assignedto=ucwords($assignedto);
$priority=($array["priority"]);
$priority=strtolower($priority);
$priorityclean=ucwords($priority);
$status = myTruncate2($status, 53);
$date=($array["date"]);
$name=($array["user"]);
$name=ucwords($name);
$i++;
if ($priority==="2"){
echo"<div class=\"prioritynormal\">Medium</div>";
}
if ($priority==="1"){
echo"<div class=\"priorityhigh\">High</div>";
}
if ($priority==="3"){
echo"<div class=\"prioritylow\">Low</div>";
}
if ($priority==="4"){
echo"<div class=\"prioritynone\">Complete</div>";
}
if ($priority==="5"){
echo"<div class=\"prioritysales\">Sales</div>";
}
$kill = array("_");
$clientnameclean = str_replace($kill, " ", $clientname);
echo <<<DATA
<!--Data-->
<div class="client"><a href="view-client.php?client=$clientname&id=$myusername">$clientnameclean</a></div>
<div class="statusexpand"><a href="view.php?client=$clientname&id=$myusername">$status</a></div>
<div class="date">$date</div>
<div class="assigned">$assignedto</div>
<div class="clear"></div>
DATA;
}
echo <<<BOTTOM
</div>
</div>
<div id="rightcontent">
<ul>
<li><a href="index-true.php?id=$myusername">Home</a></li>
<li><a href="add.php?id=$myusername">Add Update</a></li>
<li><a href="add-client.php?id=$myusername">Add Client</a></li>
<li><a href="update.php?id=$myusername">Update Client Contact Info</a></li>
<li><a href="logout.php">Logout</a></li>
</ul>
</div>
</body>
</html>
BOTTOM;
}else{echo "fail";}
?>Code: Select all
if ($cookie = "true"){
$myusername = $_GET['id'];
$statussort = $_GET['status'];
$prioritysort = $_GET['priority'];
$usersort = $_GET['user'];
$datesort = $_GET['date'];
$clientsort = $_GET['client'];
//output header
echo <<<HEADER
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html>
<head>
</head>
<body>
<div id="leftcontent">
<div class="datatable">
<!--Labels-->
<div class="titlesone"><a href="index-true.php?priority=DESC&id=$myusername">Priority</a></div>
<div class="titlestwo"><a href="index-true.php?client=ASC&id=$myusername">Client</a></div>
<div class="titlesthree">Last Status</div>
<div class="titlesfour"><a href="index-true.php?date=DESC&id=$myusername">Date</a></div>
<div class="titlessix"><a href="index-true.php?user=ASC&id=$myusername">Assigned</a></div>
<div class="clear"></div>
HEADER;
//connect
db_connect();
//query
if (isset($clientsort)){
$sql="SELECT * FROM `clients` ORDER BY `client` ASC";
$result=mysql_query($sql) or die (mysql_error());
}elseif (isset($prioritysort)){
$sql="SELECT * FROM `clients` ORDER BY `priority` DESC";
$result=mysql_query($sql) or die (mysql_error());
}elseif (isset($usersort)){
$sql="SELECT * FROM `clients` ORDER BY `priority`,`user` DESC";
$result=mysql_query($sql) or die (mysql_error());
}elseif (isset($datesort)){
$sql="SELECT * FROM `clients` ORDER BY `priority`,`date` ASC";
$result=mysql_query($sql) or die (mysql_error());
}else{
$sql="SELECT * FROM `clients` ORDER BY `priority`,`date` DESC";
$result=mysql_query($sql) or die (mysql_error());
}
//stick our toe in the water
$totalpull = mysql_num_rows($result) or die (mysql_error());
$i=0;
while($totalpull > $i){
//while there are still clients in the "clients" table, execute...
//Loop through client names
$array = mysql_fetch_array($result);
$clientname=($array["client"]);
//get client names
//select individual client table to get the status and display info
$sqlx = "SELECT * FROM `$clientname` WHERE `display`=1 ORDER BY `id` DESC";
$resultx=mysql_query($sqlx) or die (mysql_error());
$displaypull = mysql_num_rows($resultx) or die (mysql_error());
//fetch data array
$array2 = mysql_fetch_array($resultx);
//assign variables
$status=($array2["status"]);
$assignedto=($array2["assignedto"]);
$assignedto=ucwords($assignedto);
$priority=($array2["priority"]);
$priority=strtolower($priority);
$priorityclean=ucwords($priority);
$status = myTruncate2($status, 53);
$date=($array2["date"]);
$name=($array2["user"]);
$name=ucwords($name);
if ($priority==="2"){
echo"<div class=\"prioritynormal\">Medium</div>";
}
if ($priority==="1"){
echo"<div class=\"priorityhigh\">High</div>";
}
if ($priority==="3"){
echo"<div class=\"prioritylow\">Low</div>";
}
if ($priority==="4"){
echo"<div class=\"prioritynone\">Complete</div>";
}
if ($priority==="5"){
echo"<div class=\"prioritysales\">Sales</div>";
}
$kill = array("_");
$clientnameclean = str_replace($kill, " ", $clientname);
echo <<<DATA
<!--Data-->
<div class="client"><a href="view-client.php?client=$clientname&id=$myusername">$clientnameclean</a></div>
<div class="statusexpand"><a href="view.php?client=$clientname&id=$myusername">$status</a></div>
<div class="date">$date</div>
<div class="assigned">$assignedto</div>
<div class="clear"></div>
DATA;
$i++;
}
echo <<<BOTTOM
</div>
</div>
<div id="rightcontent">
<ul>
<li><a href="index-true.php?id=$myusername">Home</a></li>
<li><a href="add.php?id=$myusername">Add Update</a></li>
<li><a href="add-client.php?id=$myusername">Add Client</a></li>
<li><a href="update.php?id=$myusername">Update Client Contact Info</a></li>
<li><a href="logout.php">Logout</a></li>
</ul>
</div>
</body>
</html>
BOTTOM;
}else{echo "fail";}
?>