Page 1 of 3

Help with SQL query/PHP loop

Posted: Thu Dec 18, 2008 10:50 am
by TheBrandon
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:

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 ;
 
And here is the PHP. Right now, it is still just displaying 1 per client:

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";}
?>
I've tried this and similar things, but it just gives me nothing:

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";}
?>
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.

Re: Help with SQL query/PHP loop

Posted: Thu Dec 18, 2008 2:38 pm
by syth04
TheBrandon wrote: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.
So you are trying to only view clients that are only cleared ( if $display == 1 ) ?

Re: Help with SQL query/PHP loop

Posted: Thu Dec 18, 2008 2:50 pm
by TheBrandon
syth04 wrote:
TheBrandon wrote: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.
So you are trying to only view clients that are only cleared ( if $display == 1 ) ?
Basically. Just the opposite. (if !$display==1) or (if $display==2)

But I need it to loop and get all of them. So if client A has 2 items set to display, I need those 2 to both show up, then move to the next client which might only have 1.

Re: Help with SQL query/PHP loop

Posted: Thu Dec 18, 2008 2:56 pm
by RobertGonzalez
First recommendation, use 1 for on/true/yes and 0 for off/false/no. PHP will read a 0 (string|integer|float|boolean) as a boolean false when used in comparisons so that makes decision making a lot easier when reading the result set.

Next, if you want all of the rows, select * the rows. If you want all rows for a single user, then add a WHERE userfield = valuetosearchfor. Then while you are looping the result set check the value of the 1/0 field and if it is one, do what you do otherwise do what you do for cases of 0.

Make sense?

Re: Help with SQL query/PHP loop

Posted: Thu Dec 18, 2008 3:03 pm
by TheBrandon
Everah wrote:First recommendation, use 1 for on/true/yes and 0 for off/false/no. PHP will read a 0 (string|integer|float|boolean) as a boolean false when used in comparisons so that makes decision making a lot easier when reading the result set.

Next, if you want all of the rows, select * the rows. If you want all rows for a single user, then add a WHERE userfield = valuetosearchfor. Then while you are looping the result set check the value of the 1/0 field and if it is one, do what you do otherwise do what you do for cases of 0.

Make sense?
Makes sense in theory, yes.

So you're saying do a select * on the client table to get info, then do a select * on the individual client table (DB_test or whatever), dump all of those into arrays, then check if the array result for display is 0/1?

The users really have no use other than archiving/assigning a task. We don't do "Show me all of my tasks" kind of things.

So for the looping, should I do the standard $i=0, while whatever > $i, $i++ kind of thing?

Re: Help with SQL query/PHP loop

Posted: Thu Dec 18, 2008 3:09 pm
by RobertGonzalez
Are you wanting to see all rows or just rows for a user?

Re: Help with SQL query/PHP loop

Posted: Thu Dec 18, 2008 3:16 pm
by TheBrandon
Everah wrote:Are you wanting to see all rows or just rows for a user?
I need to see everything set to displayed. The user is pretty irrelevant.

It could be like this:

Company A: Need to call contact : Posted 12.12.08
Company A: Need contract signed : Posted 12.12.08
Company B: Money received : Posted 12.8.08
Company C: Client has changes : Posted 12.5.08
Company C: Client has questions : Posted 12.5.08
Company C: Client has appt on Monday : Posted 12.5.08

It needs to see these:
- Does the client have any updates? If so, when were they posted, and what priority are they?
- If they have updates, are they set to display? (EVERY client will have SOME KIND of update)
- Display the needed updates in order of priority, then date

The problem is right now, its doing a LIMIT 1 on the status. So if say, Company A needs their contract signed, then they call us, when we enter "Client called" in the database the contract signing will be taken off of the page. We need them all to show until we say "hide this" (basically that its been addressed/completed)

The USER is just something I have on their for security and logging purposes. We all take care of each others tasks, so the user doesn't matter too much. Just that the needed information is displayed.

Re: Help with SQL query/PHP loop

Posted: Thu Dec 18, 2008 3:52 pm
by RobertGonzalez
TheBrandon wrote:
Everah wrote:Are you wanting to see all rows or just rows for a user?
I need to see everything set to displayed. The user is pretty irrelevant.
So all rows where the display flag = 1.
[sql]SELECT * FROM TABLE WHERE display_flag = 1;[/sql]

Now that gives you everything that lives in the table that is to be displayed. Does that need to be filtered from this point?

Re: Help with SQL query/PHP loop

Posted: Thu Dec 18, 2008 3:58 pm
by TheBrandon
Everah wrote:
TheBrandon wrote:
Everah wrote:Are you wanting to see all rows or just rows for a user?
I need to see everything set to displayed. The user is pretty irrelevant.
So all rows where the display flag = 1.
[sql]SELECT * FROM TABLE WHERE display_flag = 1;[/sql]

Now that gives you everything that lives in the table that is to be displayed. Does that need to be filtered from this point?
Yes. They need to be sorted by priority and date, descending.

I know that's just another ORDER BY.

What I'm really having trouble with is the syntax for the loop.

If you look at the PHP I posted, I have it pulling by the display now. But it's only pulling one item per client, not all of them.

Re: Help with SQL query/PHP loop

Posted: Fri Dec 19, 2008 9:21 am
by RobertGonzalez
You need to loop the mysql_fetch_array() function. It only returns the current row of the result then moves the result row pointer to the next row and waits to fetch that for your call.

Now onto another question... do you really have a table for each client? Why are you not keeping the client information in a single table and joining the client information on the clients table instead of table for each client? You could handle everything you need to do in one query that way. Also, we can probably adapt the logic you are using to create your query and order by clauses so that it becomes a little more efficient.

Re: Help with SQL query/PHP loop

Posted: Fri Dec 19, 2008 9:51 am
by TheBrandon
Honestly, it was easier for me to give an ID in a clients table (Client 1 = ID 5) and do the SQL query with that.

You're right, I could have done it with one table, but I thought it would be cleaner and more organized if every client had their own table.

Honestly my knowledge of SQL queries is very limited. I understand most of what I need (select, where, order by, etc.) but when you get into <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> like joins I get lost.

So most of the time I find myself programming the way I know.

However if you can give me an example of the structure you are talking about, I'm always willing to try and learn new things.

I was thinking earlier about this, and let me be sure this is what you mean. (I read your signature, I'm not asking what will happen, just confirming something since I am very limited for time with this project since it's internal. Most of my time has to be spent on clients)

But I need to:
Select everything from the clients table to loop through the client names
Select everything from the individual client tables where they are set to display

Now heres my question. I know I can count how many clients there are, say 7 clients. How do I move my pointer forward, within the structure for the display query?

So I'm curious how to say:

I have 7 clients. Loop through this 7 times.

Get every item set to display WITHIN those 7, without increasing the number 7 until they have all executed.

So:
- Count how many clients
- Assign counter #
- On every execution of the counter #, count how many are set to display and count those, without moving our initial counter # until we are done looping

It seems like it would have to be 2 counters.

Count
while ($whatever > $i){
Pick the client names, for each client we keep going
Assign counter 2
while ($counter2 > $a){
Pull everything set to display
$a++
}
$i++
}

Is that right? Nested loops or whatever?

Thank you very much for your help.

Re: Help with SQL query/PHP loop

Posted: Fri Dec 19, 2008 12:06 pm
by RobertGonzalez
Nested loops will be the way to go. But I would not nest queries. I will explain in my response to your response to this request ( :dubious: uh, huh???). Anyway...

Can you post your client table structure and the structure for just one of the client tables. I don't need to know the client information. I just want to see the structure.

To get the structure you can run the following query, substituting the name of your table for <TABLENAME>:
[sql]SHOW CREATE TABLE <TABLENAME>[/sql]

Re: Help with SQL query/PHP loop

Posted: Fri Dec 19, 2008 1:44 pm
by TheBrandon
Everah wrote:Nested loops will be the way to go. But I would not nest queries. I will explain in my response to your response to this request ( :dubious: uh, huh???). Anyway...

Can you post your client table structure and the structure for just one of the client tables. I don't need to know the client information. I just want to see the structure.

To get the structure you can run the following query, substituting the name of your table for <TABLENAME>:
[sql]SHOW CREATE TABLE <TABLENAME>[/sql]
Sure thing.

This is a client:

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
Explanation:
id - Just an ID
status - The update/assignment. Like call client Monday.
priority - High, Medium, Low, Sales, or Update
user - User that posted the update. This is done automatically, not by the user.
date - Date it was posted. This is done automatically, not by the user.
assignedto - Who needs to work on the assignment. Whoever is listed here, is why the project isn't moving forward. Like "Need email from Bob" and it will be assigned to staff member Bob
display - Whether it will be displayed or not. Basically, whether the task is complete or not.

This is the clients table (the table containing every client):

Code: Select all

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 AUTO_INCREMENT=62 DEFAULT CHARSET=utf8
Same thing as before, but everything is short. The status isn't in there, and there is only entry per client. So when an update is made, it updates this table; it doesn't add a new row.

An example would be:
5, DB_Testing, 1 (High), 12.12.08, Bob, Chris

Then that's the only info entered for that client. When we add a new update, it just updates that row where the client name = client name, and then I use that table to sort everything. That way the dates in this one always show the most updated one, etc.

Thanks again for all of your help. I can't tell you how grateful I am.

Re: Help with SQL query/PHP loop

Posted: Fri Dec 19, 2008 2:48 pm
by RobertGonzalez
It looks like you are maintaining a history log of actions. Is that a somewhat safe assessment?

Re: Help with SQL query/PHP loop

Posted: Fri Dec 19, 2008 3:33 pm
by TheBrandon
Everah wrote:It looks like you are maintaining a history log of actions. Is that a somewhat safe assessment?
Yes. We need to keep track of things since a client only has so many changes to their website per month.

With this, we can go back and make sure they didn't go over the limit.