Help with SQL query/PHP loop
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Help with SQL query/PHP loop
So perhaps what you need is a requests table with a fulfillment flag and another table to track updates to that request. So say a customer says they want a document added as a downloadable file to a page. You can enter a row into the requests table then when something is done to that request you can track it in the requests_updates table. When the request is marked fulfilled you can add that row to the requests_updates table and mark that request as fulfilled in the requests table.
A request can then be keyed on a client as a relationship to the clients table. Users should be handled the same way, as should priorities. So in effect your schema could look like this:
[sql]CREATE TABLE `clients_requests` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The client request identifier', `clientid` INT(11) NOT NULL COMMENT 'This is the id of the client making the request, should relate to the clients table', `request` text NOT NULL COMMENT 'The actual request made by the customer', `status` varchar(255) NOT NULL COMMENT 'Simple string of text to describe the status of the request', `priority` int(11) NOT NULL DEFAULT 0 COMMENT 'Relationship to requests_status that will pull the status type', `user` int(11) NOT NULL DEFAULT 0 'The user id of the user that entered this request', `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The date and time this request was entered', `assignedto` int(11) NOT NULL DEFAULT 0 COMMENT 'The staff member ID to assign this to', `fulfilled` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Simple boolean flag that tells us if this request is fulfilled or not', PRIMARY KEY (`id`), KEY `fulfillment` (`fulfilled`) KEY `assignedto` (`assignedto`) KEY `priority` (`priority`) KEY `user` (`user`)) ENGINE=MyISAM DEFAULT CHARSET=utf8[/sql]
[sql]CREATE TABLE `clients_requests_logs` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The log entry ID', `requestid` INT(11) NOT NULL COMMENT 'The ID of the request', `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The date this log entry was made', `staffid` int(11) NOT NULL DEFAULT 0 COMMENT 'The id of the staff member that made the log entry', PRIMARY KEY (`id`), KEY `requestid` (`requestid`) KEY `staffid` (`staffid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8[/sql]
After this you would need to make a clients table, a staff member table and a priorities table that would hold the information that will be related to from the two I posted here.
What this does is allow you to show a listing of all your clients, then, if you want to you can drill down into a client and see all of their requests, then, if you want to you can drill down the request into the details and status of the request. In any event, at most you would ever execute two queries on a page. But that would only be for showing clients and requests together at the same time or showing requests and details together at the same time.
Make sense?
A request can then be keyed on a client as a relationship to the clients table. Users should be handled the same way, as should priorities. So in effect your schema could look like this:
[sql]CREATE TABLE `clients_requests` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The client request identifier', `clientid` INT(11) NOT NULL COMMENT 'This is the id of the client making the request, should relate to the clients table', `request` text NOT NULL COMMENT 'The actual request made by the customer', `status` varchar(255) NOT NULL COMMENT 'Simple string of text to describe the status of the request', `priority` int(11) NOT NULL DEFAULT 0 COMMENT 'Relationship to requests_status that will pull the status type', `user` int(11) NOT NULL DEFAULT 0 'The user id of the user that entered this request', `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The date and time this request was entered', `assignedto` int(11) NOT NULL DEFAULT 0 COMMENT 'The staff member ID to assign this to', `fulfilled` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Simple boolean flag that tells us if this request is fulfilled or not', PRIMARY KEY (`id`), KEY `fulfillment` (`fulfilled`) KEY `assignedto` (`assignedto`) KEY `priority` (`priority`) KEY `user` (`user`)) ENGINE=MyISAM DEFAULT CHARSET=utf8[/sql]
[sql]CREATE TABLE `clients_requests_logs` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The log entry ID', `requestid` INT(11) NOT NULL COMMENT 'The ID of the request', `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The date this log entry was made', `staffid` int(11) NOT NULL DEFAULT 0 COMMENT 'The id of the staff member that made the log entry', PRIMARY KEY (`id`), KEY `requestid` (`requestid`) KEY `staffid` (`staffid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8[/sql]
After this you would need to make a clients table, a staff member table and a priorities table that would hold the information that will be related to from the two I posted here.
What this does is allow you to show a listing of all your clients, then, if you want to you can drill down into a client and see all of their requests, then, if you want to you can drill down the request into the details and status of the request. In any event, at most you would ever execute two queries on a page. But that would only be for showing clients and requests together at the same time or showing requests and details together at the same time.
Make sense?
-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Re: Help with SQL query/PHP loop
Yeah. But we want all of the status updates set to display on one page. Not so you have to drill down into another PHP page; I have that now, and everyone wants it the other way. Because then everyone has to click every client to see if something needs to be done, go back, and do it all over.
Can you show me an example query/loop for the schema you posted, that would pull all of the updates marked to display, order them by priority, and then move on to the next client?
Would you just Select * from clients_requests and then do a "WHERE fulfilled = 1" or something?
Like SELECT * FROM clients_requests WHERE fulfilled=1 ORDER BY priority DESC?
I see what you're building though. Then for the "individual" client pages, it would be basically the same thing, but doing "WHERE clientid = XX"
To pull and display the info, you mentioned an array earlier. Would you recommend an array like $priority[$result][$i] or whatever, with the $i++ moving us forward?
Am I getting this right?
Thank you for all of your help. I'm learning a lot from this thread.
Can you show me an example query/loop for the schema you posted, that would pull all of the updates marked to display, order them by priority, and then move on to the next client?
Would you just Select * from clients_requests and then do a "WHERE fulfilled = 1" or something?
Like SELECT * FROM clients_requests WHERE fulfilled=1 ORDER BY priority DESC?
I see what you're building though. Then for the "individual" client pages, it would be basically the same thing, but doing "WHERE clientid = XX"
To pull and display the info, you mentioned an array earlier. Would you recommend an array like $priority[$result][$i] or whatever, with the $i++ moving us forward?
Am I getting this right?
Thank you for all of your help. I'm learning a lot from this thread.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Help with SQL query/PHP loop
None of the queries would require drill downs. It just makes the queries lighter if you drill down. Anyway, if you want all of it on one page, then select your clients into one result and select all the requests into another result. Loop the clients result then while you are inside that loop, loop the requests result and match each request to the client id as you are looping.
So basically you would have:
This is untested and the field names in this example do not map to the field names in the table structure I presented earlier (I have to let you do something in this don't I
). But the concept is simply... get all clients, get all requests, loop all clients then loop all requests and show only those requests that match the given client (based on loop position) AND that have a flag that tells the request to be displayed.
So basically you would have:
Code: Select all
<?php
$sql = 'SELECT * FROM `myclients`';
if (! $result = mysql_query($sql)) [
die('Could not query the clients: ' .mysql_error()); // No, you would not do this for production
}
// Initialize the clients array
$clients = array();
// Loop the clients result set and read it into the clients array
while ($row = mysql_fetch_array($result)) {
$clients[] = $row;
}
// Get the count of clients in the result - You'll need this for looping
$clientscount = count($clients);
// Lather, rinse ... you get the idea
$sql = 'SELECT * FROM `clientrequests`';
if (! $result = mysql_query($sql)) [
die('Could not query the client requests: ' .mysql_error()); // Again, not something to be done in production
}
// Good coding practice - initialize your variables
$requests = array();
// Now loop the requests result set
while ($row = mysql_fetch_array($result)) {
$requests[] = $row;
}
// And again get the counts for looping later
$requestscount = count($requests);
// Now handle the looping starting with the clients
for ($i = 0; $i < $clientscount; $i++) {
echo '<h2>' . $clients[$i]['client_name'] . '</h2>'; // client_name in this line refers to the table column name
// Now loop their requests
for ($j = 0; $j < $requestscount; $j++) {
// Match the request to the client AND check if it is set to show
if ($requests[$j]['client_id'] == $clients[$i]['client_id'] && $requests[$j]['display_flag']) {
echo $requests[$j]['request_title']; // This could be any collection of data you want to see from the request
}
}
}-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Re: Help with SQL query/PHP loop
Thanks. I'll give that a shot hopefully today if I have time.
And yeah, I'm glad to change the field names. It's the least I can do.
Thank you for helping. I'll let you know how it works.
And yeah, I'm glad to change the field names. It's the least I can do.
Thank you for helping. I'll let you know how it works.
-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Re: Help with SQL query/PHP loop
Ok, I have it connecting and pulling the client names in the H2's.
I had a question here though:
Isn't that saying if my clientid in the requests table matches the clientid in the clients table AND the fulfilled number in the requests table?
If we're using a boolean for the requests fulfilled part, it would never match would it?
Or am I misunderstanding that? It looks like "if 9 = 9 & 1" or something to me. Or did I misinterpret what fields it should be pulling?
I had a question here though:
Code: Select all
FOR ($j = 0; $j < $requestscount; $j++) {
// Match the request TO the client AND CHECK IF it IS SET TO SHOW
IF ($requests[$j]['clientid'] == $clients[$i]['clientid'] && $requests[$j]['fulfilled']) {
echo $requests[$j]['request']; // This could be any collection of DATA you want TO see FROM the request
}
}If we're using a boolean for the requests fulfilled part, it would never match would it?
Or am I misunderstanding that? It looks like "if 9 = 9 & 1" or something to me. Or did I misinterpret what fields it should be pulling?
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Help with SQL query/PHP loop
What it is saying is if the client id from the clients list matches the client id in the requests list (TRUE) AND the flag that tells you that it should be displayed is something that does not evaluate to boolean false (1, for example) THEN show the row.
You evaluations are going to be one of:
You evaluations are going to be one of:
- TRUE and TRUE
- TRUE and FALSE
- FALSE (since the second would never evaluate)
-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Re: Help with SQL query/PHP loop
Woohoo! I got it pulling data!
Now to begin the long process of mixing them together.
Thank you so much for your help. I learned a lot in this process.
Now to begin the long process of mixing them together.
Thank you so much for your help. I learned a lot in this process.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Help with SQL query/PHP loop
Glad I could help.
-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Re: Help with SQL query/PHP loop
Hey I've got it working.
I was just wondering the best way to test if there are any "fulfilled" results and if not, drop them to the bottom of the list? (Maybe set their priority to a certain number?)
Here's what I've got on the index:
It works great. A lot faster than the old build.
Just wondering if there was a quick way to add that. Cause right now, it will say multiple client names together. I know normally I could do an "if fulfilled=0" thing, but I'm not sure where that would need to be in the looping process or if there is a more efficient way to test for that.
It works though. So if it will be a lot of trouble to add that, no biggie.
Thanks again for your help. I've already started using the "&&" in my other coding.
I was just wondering the best way to test if there are any "fulfilled" results and if not, drop them to the bottom of the list? (Maybe set their priority to a certain number?)
Here's what I've got on the index:
Code: Select all
<?php
db_connect();
$sql = 'SELECT * FROM `clients` ORDER BY `priority`,`date` DESC';
if (! $result = mysql_query($sql)) {
die('Could not query the clients: ' .mysql_error()); // No, you would not do this for production
}
// Initialize the clients array
$clients = array();
// Loop the clients result set and read it into the clients array
while ($row = mysql_fetch_array($result)) {
$clients[] = $row;
}
// Get the count of clients in the result - You'll need this for looping
$clientscount = count($clients);
// Lather, rinse ... you get the idea
$sql = 'SELECT * FROM `clients_requests`';
if (! $result = mysql_query($sql)) {
die('Could not query the clients requests: ' .mysql_error()); // Again, not something to be done in production
}
// Good coding practice - initialize your variables
$requests = array();
// Now loop the requests result set
while ($row = mysql_fetch_array($result)) {
$requests[] = $row;
}
// And again get the counts for looping later
$requestscount = count($requests);
//output header
echo <<<HEADER
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html>
<head>
<style type="text/css">
@import "reset.css"; /* just some basic formatting, no layout stuff */
@import "style.css"; /* formatting */
</style>
</head>
<body>
HEADER;
include "inc/menu.php";
echo <<<HEADER
<div id="leftcontent">
<div class="datatable">
HEADER;
// Now handle the looping starting with the clients
for ($i = 0; $i < $clientscount; $i++) {
echo "<div class=\"clientname\">";
echo '<h2><a href="view-all.php?clientid=' . $clients[$i]['id'] . '">' . $clients[$i]['client'] . '</a></h2>'; // client_name in this line refers to the table column name
echo "</div><div class=\"clear\"></div>";
// Now loop their requests
for ($j = 0; $j < $requestscount; $j++) {
// Match the request to the client AND check if it is set to show
//if clientid (clients_requests) = clientid (clients)
if ($requests[$j]['clientid'] == $clients[$i]['id'] && $requests[$j]['fulfilled']) {
$status=$requests[$j]['request'];
$status = myTruncate2($status, 100);
echo "<div class=\"status\">";
if($requests[$j]['assignedto']=="1"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="2"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="3"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="4"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="5"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="6"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="7"){echo "<span class=\"user\">Bob:</span>";}
if($requests[$j]['priority']=="1"){echo "<div class=\"priorityhigh\">High</div>";}
if($requests[$j]['priority']=="2"){echo "<div class=\"prioritynormal\">Normal</div>";}
if($requests[$j]['priority']=="3"){echo "<div class=\"prioritylow\">Low</div>";}
if($requests[$j]['priority']=="4"){echo "<div class=\"prioritynone\">None</div>";}
if($requests[$j]['priority']=="5"){echo "<div class=\"prioritysales\">Sales</div>";}
echo "<p>";
echo $status; // This could be any collection of data you want to see from the request
echo "</p>";
echo "</div><div class=\"clear\"></div>";
}
}
}
echo <<<BOTTOM
</body>
</html>
BOTTOM;
Just wondering if there was a quick way to add that. Cause right now, it will say multiple client names together. I know normally I could do an "if fulfilled=0" thing, but I'm not sure where that would need to be in the looping process or if there is a more efficient way to test for that.
It works though. So if it will be a lot of trouble to add that, no biggie.
Thanks again for your help. I've already started using the "&&" in my other coding.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Help with SQL query/PHP loop
So you want to see all of the requests, with the ones that are fulfilled at the top of the list and the ones that are not fulfilled at the bottom?
-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Re: Help with SQL query/PHP loop
Opposite, but yes.Everah wrote:So you want to see all of the requests, with the ones that are fulfilled at the top of the list and the ones that are not fulfilled at the bottom?
fulfilled=1 at the top, fulfilled=0 at the bottom.
Or the other way. I can likely edit something like that to work.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Help with SQL query/PHP loop
Add an order by clause on the fulfilled column. Do it DESC to get 1's first, then 0's. ASC to get 0's first, then 1's.
-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Re: Help with SQL query/PHP loop
Hmm, I tried that before posting but it's not doing anything. I know it works though cause if I plug it into MySQL it works just fine. Must be something I've done on the PHP end.Everah wrote:Add an order by clause on the fulfilled column. Do it DESC to get 1's first, then 0's. ASC to get 0's first, then 1's.
I'll try and figure it out.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Help with SQL query/PHP loop
Take the second comparison out of the for loop. With that in there only those fulfilled = 1 rows will show. Perhaps you can use that as a means to display that the row is fulfilled:
This is hecka pseudo code, but you get the idea...
Code: Select all
<?php
for (...) {
if ($requests[$i]['fulfilled']) echo 'I am fulfilled';
}-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Re: Help with SQL query/PHP loop
Hmm, I tried this, or did I misunderstand your instruction?
Code: Select all
for ($i = 0; $i < $clientscount; $i++) {
echo "<div class=\"clientname\">";
echo '<h2><a href="view-all.php?clientid=' . $clients[$i]['id'] . '&userid=' . $useridcookie . '">' . $clients[$i]['client'] . '</a></h2>'; // client_name in this line refers to the table column name
echo "</div><div class=\"clear\"></div>";
// Now loop their requests
for ($j = 0; $j < $requestscount; $j++) {
// Match the request to the client AND check if it is set to show
//if clientid (clients_requests) = clientid (clients)
if ($requests[$j]['clientid'] == $clients[$i]['id']) {
if ($requests[$j]['fulfilled'] == "1"){
$status=$requests[$j]['request'];
$status = myTruncate2($status, 100);
echo "<div class=\"status\">";
if($requests[$j]['assignedto']=="1"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="2"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="3"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="4"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="5"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="6"){echo "<span class=\"user\">Bob:</span>";}elseif
($requests[$j]['assignedto']=="7"){echo "<span class=\"user\">Bob:</span>";}
if($requests[$j]['priority']=="1"){echo "<div class=\"priorityhigh\">High</div>";}
if($requests[$j]['priority']=="2"){echo "<div class=\"prioritynormal\">Normal</div>";}
if($requests[$j]['priority']=="3"){echo "<div class=\"prioritylow\">Low</div>";}
if($requests[$j]['priority']=="4"){echo "<div class=\"prioritynone\">None</div>";}
if($requests[$j]['priority']=="5"){echo "<div class=\"prioritysales\">Sales</div>";}
echo "<p>";
echo $status; // This could be any collection of data you want to see from the request
echo "</p>";
echo "</div><div class=\"clear\"></div>";
}
}
}
}