inbowns

Thanks to everyone that attempted to help me with this problem. I guess sometimes it's best to grab the bull by the horns and try to wrestle it down yourself. This has been offically solved. By plugging this into my SQL statement it gave me the difference of the employee total and the employee total that actually migrated. I hope this helps someone in the future. (SUM(employees)- SUM(CASE WHEN migrate = 'Yes' THEN 0 ELSE employees END)) AS 'migrate_total' FROM info

This is the SQL statement I'm using today that produced that picture $sql= "SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees) , SUM(IF(migrate = 'Yes', 1, 0)) AS 'migrate_yes' FROM info WHERE effdate = '$date_search_8' GROUP BY carrier WITH ROLLUP LIMIT 0 , 30"; It should look like this carrier Case employees migrated #of migrate employees Aetna 15 6 3 2 HIP 7 71 4 23 Oxford 25 10 1 3 Do you see from the overall total of employees I need to get how many migrated out of the total. I hope this is clear.

In the database there's a migrated column that accepts Yes or No.

Mikoskio in the picture below this is what I currently have do you see where "Employees" are listed well out of those i need to get a total of how many employees of those were yes when selected in the database. I currently have all of the employees from all the carriers regardless if it was yes or no. So what I need is a new column that would show migrated(employees)

I changed it but I'm get 451 from the database and i don't have 451 employees listed in that date range.

Thanks for replying but it doesn't work it gives me a weird number. quote author=Muddy_Funster link=topic=350723.msg1655562#msg1655562 date=1325163381] This should work: SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees) , (( SELECT count(employees) FROM info WHERE migrate = 'yes')) AS 'migrate_yes' FROM info WHERE effdate = '$date_search_8' GROUP BY carrier WITH ROLLUP LIMIT 0 , 30

I'm working on a database what I would like to do is find out of the number of total number of employees that migrated I would only like to find how many migrated. For example there 5 cases there are a 95 total employees out of them 3 cases migrated with a total of 65 migrates. I would like to know how can I get this to work with my existing SQL statement. $sql= "SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees) , SUM(IF(migrate = 'Yes', 1, 0)) AS 'migrate_yes' FROM info WHERE effdate = '$date_search_8' GROUP BY carrier WITH ROLLUP LIMIT 0 , 30"; Any help is greatly appreciated. Thanks in advance.

I guess this will work but i also need it to show if there's for example 3 "no" then the data it should return a zero but currently it doesn't show anything. Essentially i want a count of the "Yes" if there 10 "yes" i want that actual count and if there's a "No" it should give me a zero regardless of how many there are listed. I hope this is clear

Thank you for your response I tried this it didn't work is there something I'm missing something? Do i need JOIN or UNION to make this work?

I was able to get my count to work that I posted in my previous post but I have a problem I would like the post to count everything but if there's a that is Yes in my column but if there all No I would also like it to display 0 in the same output something like this. Type Response = Yes Car 5 Truck 1 Bike 0 Walk 3 Swim 0 Flying 1 So the "0" should show that the database saw "No" and displayed a zero.

Yes I understand because it doesn't run am I using too many AND's

Thank you for your reply I tired that and it works but I have other stuff that goes in the statement.Do you think you can help me clear that up also? $sql ="SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees),COUNT(migrate) FROM info WHERE effdate BETWEEN '$date_search_1' AND '$date_search_1' GROUP BY carrier" AND migrate = 'Yes' ";

I have a database which I was successfully able to pull data from and utilize but now I have a problem. I need to get data from a column that I have Yes or No in and I'm not sure to go about retriving this data. I basically need to count all the data that is listed as Yes. Any help is greatly appreciated.

Sorry but this is not working I'm I missing something?

Something like what you wrote only I need the textboxes to fill in but on on submit when i select something from the select box i would like of the page to refresh and populate the textboxes.

I have a select box and I would like to select data click on it and I guess it should reload the page and populate the text boxes i have below with the data from the database. Below is the code so you can have an idea what i'm trying to do. Is this possible and help would be greatly appreciate. Thanks in advance. <html> <head> <meta name="generator" content="PhpED Version 5.9.5 (Build 5989)"> <title> RSVP Administrator</title> <link rel="shortcut icon" href=""> </head> <body> <fieldset> <legend>Events</legend> <table> <tr> <td> Select an address: </td> <td> <SELECT NAME> <option value=' '> <? echo $option1 ?>;</option> </SELECT> </td> </tr> <tr> <td> Place of Event: </td> <td> <input type="text" name="location" value="<?php if($option1 == " "){ echo " ";} else{ echo $address_1;} ?>"> </td> </tr> <tr> <td> Address 1: </td> <td> <input type="text" name="address_1"> </td> </tr> <tr> <td> Address 2: </td> <td> <input type="text" name="address_2"> </td> </tr> <tr> <td> City: </td> <td> <input type="text" name="city"> </td> </tr> <tr> <td> State: </td> <td> <input type="text" name="state"> </td> </tr> <tr> <td> Zip Code: </td> <td> <input type="text" name="zip_code"> </td> </tr> <tr> <td> Telephone: </td> <td> <input type="text" name="phone"> </td> </tr> </table> </fieldset> </body> </html>

I have the mime setting on please see what i have posted below $headers .= "MIME-Version: 1.0" . "\r\n"; $headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n"; Here's how my script looks that I'm working with. <th>Broker</th><th>Lives</th><th>Quote</th> <?php $connection = mysql_connect("localhost", "root", "") or die (mysql_error()); $db = mysql_select_db ("health",$connection) or die (mysql_error()); $query = "SELECT * FROM data"; $result = mysql_query( $query, $connection) or die(mysql_error()); $num_results = mysql_num_rows($result); while ($row = mysql_fetch_array($result)) { $broker = $row ["broker"]; $client = $row ["client"]; $lives = $row ["lives"]; $email = $row ["email"]; $broker = ucwords(strtolower($broker)); $client = ucwords(strtolower($client)); $broker; // break the string into words. $words = explode(' ',$broker); // reverse the array of words using array reverse $words = array_reverse($words); // implode rebuild the string $broker = implode(' ',$words); $broker = substr($broker, 0, -1); if ($lives == 8 ) { echo " <table> <tr> <td> $broker </td> <td> $lives </td> <td> $quote </td> <td> $email </td> </tr> </table> "; //$email2 = ""; $address = ""; //$address = ""; $subject = "You have a broker that is quoting a group with more than 10 lives"; $body = " <html> <head> <title></title> </head> <body> Upon review of data from $program we found that you a have broker that is quoting groups over 10 lives.<br> It would be best to contact your broker to see if they are in need of your assistance.<br> <br> <br> <table> <tr> <td> Broker's Name: </td> $broker.='<td>'. <b>$broker</b> </td> </tr> <tr> <td> Client: </td> <td> <b>$client</b> </td> </tr> <tr> <td> Lives </td> <td> <b>$lives</b> </td> </tr> <tr> <td> </td> <td> </td> </tr> </table> <br> <br> PLEASE DO NOT RESPOND THIS EMAIL WAS AUTOMATICALLY GENERATED <br> <br> <font size = 1>This transmission is intended only for the use of the individual or entity to which it is addressed. It may contain information that is privileged or confidential, and may be protected by State and/or Federal Regulations. If the Reader of this transmission is not the intended recipient, or the agent responsible for delivering the transmission to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this transmission is strictly prohibited. If you have received this communication in error, please delete it and all of the attachments </font> $broker.='<td>.' </body> </html> "; // Always set content-type when sending HTML email $headers .= "MIME-Version: 1.0" . "\r\n"; $headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n"; $mailheaders = "From: \r\n"; mail($address, $subject, $body, $headers); } } ?>

I tired it but it didn't work. Are saying I should use concatentation?

I really need help with this I have a php script that queries the database and then sends a separate email but I was just recently told they would like each query to to be put in an email so we only send one like the way i made it below (sorry if it's sloppy) if anyone can help I would greatly appreciate it. Thanks in advance ___________________________________ Name | Date | Address | State | _______|_____|______________|______ | John | 3/18 | 215 5th Street | NY | ______ |____ |______________|______ | Bob | 4/15 | 505 North Street | NY | ______ |____ |_____________ |______ | Jane | 5/9 | 6 main Street | MD | ______ |____|_______________ | ______| Ann | 1/12 | 9 West Ave | CA | ______ |____ |_______________|______ |