Travis959

Sorry to bother again, but I've been requested to add more information to this page and I am unsure of how to go about it with the current code. I made a quick image to show what is requested: The sizing of the bottom part is not correct in the image as it should be same width as above tables in the php. But how would I go about adding the "Family Totals" after each family, which just adds all the previous "child_codes" for that family together? And at the end of the report, I need to add up everything together and display in the "SUBTOTALS PRIOR TO ADJUSTMENT". The next part is trickier, as the "CUSTOMER ALW" in the image is just a field that is decided by because it's field3/family is either 90 or 0. This area can have other fields as long as field3/family is 90 or 0. After that, "Adjustment Totals" need to be added which is everything with field4/parent_code = 995. And finally Grand Total is just subtotals minus adjustment totals. I hate asking for so much help, but this new code is definitely above my current abilities. I am going to try to see if I can get this working, but I'm sure someone could figure this out way faster than me.

omg you are awesome, that works perfectly. Thank you so much :D

The values are not summed in the database, these need to be done in the PHP. The other values except the averages(this needs to be done in the php as well), are in the database. There is only one record per month for each "company". And yes there is always a value for a month, it should always have at least a 0. I've included the first 500+rows of the table. I really appreciate all the help. Edit: The file uploader on here says the upload folder is full so I uploaded it here: http://www.gamerhandles.com/slssum2.sql.zip

I agree, and I apologize for the messy names of the fields, but the reason for the names is because the names in the database were named the same as the fields in a file that is generated every night in unix that is imported into said database. Unfortunately that was long before my time, and I doubt they will change the names now. Anyways, here is PFMaBiSmAd's code so far: $query = "SELECT * FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 != 0 AND field3 != '90' ORDER BY field3,field4"; $result = mysql_query($query); $last_heading = null; // remember the last heading (initialize to null) while($row = mysql_fetch_assoc($result)){ $new_heading = $row['field5']; // get the column in the data that represents the heading if($last_heading != $new_heading){ // heading changed or is the first one if($last_heading != null){ // not the first section, close out the previous section here... echo '<tr><td colspan="0">'.$row['field5'].'</td></tr><tr><td valign="middle" align="left" bgcolor="#ccc" width="183px">Units</td>'; } else { // is the first section, output the one-time heading here... echo '<tr><td colspan="0"><hr style="width: 100%; height: 1px; color: #a0a0a0;"></td></tr><tr><td colspan="0" bgcolor="#ccffcc"><strong>FAMILY '.$row['field3'].'</strong><br /></td></tr>'; } $last_heading = $new_heading; // remember the new heading // start a new section, output the heading here... echo '</tr>'; } // output each piece of data under a heading here... echo ''; } if($last_heading != null){ // there was at least one section, close out the last section here... echo "(optionally) do what is needed to close out the last section<br />"; } and the results: My question now is how to create columns next to "Units" and input the data? Also need to create the sales and average rows as well under units, but not sure how to go about it using the code above. Thanks.

Ah sorry about that, I know what each field represents in my head so I didn't even think about it when I posted the help. I labeled what each field is in the image, so that should hopefully help you get a better understating of the placement of the fields.

I would assume UNION would be better, but is there an easier way to do what I want? For instance, can I just do one query to select the whole database, and insert that result into an array and display the results with that? If so, I have no clue how to do it this way.

Hello, I have a script that processes data from a form, and then outputs data from a MySQL table based on which selections are made on the form. What I'm trying to do is create columns showing data for each month depending on which months the user selects, and then a "Total" column at the end of that. The output first determines the "family" the results are in, then the ID the product is for the parent family, and then it outputs the data based on what ID is listed. Unfortunately, I have no idea how to do this without nesting while loops inside other while loops which I know is bad for server performance - and yes this does take a long time to complete. Here is some of my code for this to show what I'm doing wrong: $searchdatabase5 = "SELECT field3 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 != 0 AND field4 != '995' GROUP BY field3"; $run5 = mysql_query($searchdatabase5) or die(mysql_error()); while($row5 = mysql_fetch_assoc($run5)) { $field5_3 = $row5['field3']; echo '<tr><td colspan="0"><hr style="width: 100%; height: 1px; color: #a0a0a0;"></td></tr><tr><td colspan="0" bgcolor="#ccffcc"><strong>FAMILY '. $field5_3 .'</strong><br /></td></tr>'; $searchdatabase12 = "SELECT field4 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 = '$field5_3' AND field4 != 0 GROUP BY field4"; $run12 = mysql_query($searchdatabase12) or die(mysql_error()); while($row12 = mysql_fetch_assoc($run12)) { $field12_4 = $row12['field4']; $searchdatabase6 = "SELECT field3,field4,field5,field6 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field4 = '$field12_4' GROUP BY field3,field4,field5"; $run6 = mysql_query($searchdatabase6) or die(mysql_error()); while($row6 = mysql_fetch_assoc($run6)) { $field6_3 = $row6['field3']; $field6_4 = $row6['field4']; $field6_5 = $row6['field5']; $field6_6 = $row6['field6']; echo '<tr><td colspan="0">'. $field6_5 .' - '. $field6_6 .'</td></tr><tr><td valign="middle" align="left" bgcolor="#ccc" width="183px">Units</td>'; $searchdatabase7 = "SELECT * FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field5 = '$field6_5' ORDER BY year,month ASC"; $run7 = mysql_query($searchdatabase7) or die(mysql_error()); while($row7 = mysql_fetch_assoc($run7)) { $field7_3 = $row7['field3']; $field7_4 = $row7['field4']; $field7_5 = $row7['field5']; $field7_9 = $row7['field9']; $field7_10 = $row7['field10']; $field7_month = $row7['month']; $field7_year = $row7['year']; $startmonth2 = ltrim($startmonth, '0'); echo '<td bgcolor="#e6e6e6" width="95px" nowrap="nowrap" align="right">'; echo $field7_10; echo '</td>'; } Here is the output: I know the code is very bad, but what would be the best way to recreate what I have in the image? Thank you.

This works great. Thanks

Hey all, I am programming a form that lets users check which items they would like to appear on the details screen, and also allow them to input their own data. This form is generated dynamically using fields from a database to create each row. I am hitting a roadblock because of two things. 1) Is there a way for the input fields to not be in the submission url (using $GET), if the checkboxes in their rows are not checked? Right now, all of them get submitted. Here is the url now: creator_custom2.php?product_no[]=9-2-20-198&price_9-2-20-198=9.00&price_9-2-20-204=0.00&price_9-2-20-202=0.00&price_9-2-20-200=0.00&price_9-2-20-206=0.00&price_9-2-20-209=0.00&price_9-2-20-211=0.00&price_9-2-20-195=0.00&price_9-2-20-199=0.00&price_9-2-20-201=0.00&price_9-2-20-205=0.00&price_9-2-20-208=0.00&price_9-2-20-196=0.00&price_9-2-20-210=0.00&price_9-2-20-197=0.00&price_9-2-20-207=0.00&price_9-2-20-203=0.00&price_10-1-20-072=0.00&price_10-1-20-070=0.00&price_10-1-20-071=0.00&price_21404TJXP=0.00&price_21402TJXP=0.00&price_21400TJXP=0.00&price_21396TJXP=0.00&price_21397TJXP=0.00&price_21395TJXP=0.00&price_21398TJXP=0.00&price_21401TJXP=0.00&price_21394TJXP=0.00&price_21399TJXP=0.00&price_21416TJDI=0.00&price_21416TJXP=0.00&price_21419TJDI=0.00&price_21419TJXP=0.00&price_21421TJDI=0.00&price_21421TJXP=0.00&price_21418TJDI=0.00&price_21418TJXP=0.00&price_21415TJDI=0.00&price_21415TJXP=0.00&price_21417TJDI=0.00&price_21417TJXP=0.00&price_21420TJDI=0.00&price_21420TJXP=0.00&price_21398TJDI=0.00&comments=&line=813&discontinued=&seasonal=&newproduct=N&orderby=Description&prices[]=&submit=Create+Design+Sheets Here is what I would like: creator_custom2.php?product_no[]=9-2-20-198&price_9-2-20-198=9.00&submit=Create+Design+Sheets 2) How would I go about linking the checkbox field with the input field? Perhaps this related to #1, but for instance, if I wanted to check the first box, and input $9.00 in it's price field, how would I display this on the next page? I can show which boxes are checked fine, but pulling the price field for each is eluding me. Here is my code for the form fields: $selectimages = "SELECT * FROM product WHERE line = '$line' ORDER BY $orderby+0, $orderby"; $run = mysql_query($selectimages) or die (mysql_error()); while ($row = mysql_fetch_assoc($run)) { $productnumber = $row['Number']; $productdescription = $row['Description']; $price = $row['Price']; $newproduct = $row['NewProduct']; echo '<tr> <td align="left" valign="top" bgcolor="#e6e6e6" width="30"><input type="checkbox" value="'.$productnumber.'" name="product_no[]"></td> <td align="left" valign="top" bgcolor="#e6e6e6" width="150">'.$productnumber.'</td> <td align="left" valign="top" bgcolor="#e6e6e6" width="200">'.$linename.'</td> <td align="left" valign="top" bgcolor="#e6e6e6" width="400">'.$productdescription; if($newproduct == 'Y') { echo ' <font color="#FF0000">(new)</font>'; } echo '</td> <td align="left" valign="top" bgcolor="#e6e6e6" width="120">$<input type="text" value="'.number_format($price, 2, '.', '').'" name="price_'.$productnumber.'" size="10" maxlength="10"></td> </tr>'; } Here's the results page, with just random testing code: $productnumbers = $_GET['product_no']; print_r($productnumbers); $price_list = 'price_'.$productnumbers; echo $_GET['$price_list']; echo $price_list; [attachment deleted by admin]

I have a script to import a daily generated csv file to a database, and it works fine for all other the other files, but on this file I am running into a problem of it reporting back "Column count doesn't match value count at row 1" on some of the rows. There are 20 fields in the database, and some rows have the required 20 fields, but others are missing the very last field, and there is only 19 fields in that row. Is there anything I can do to fix this problem, without adding another field to the file? Basically I just want it to forget the last field in the row, if it's not there in the file, and just go to the next row. Is this possible? Here is my code: $conn = mysql_connect($databasehost, $databaseusername, $databasepassword); if (!$conn) { echo mysql_error(); exit; } if (!mysql_select_db($databasename)) { echo mysql_error(); exit; } else { $sql2 = "DELETE FROM `".$databasetable."`"; mysql_query($sql2); } $fcontents = file($csvfile); for($i=0; $i<sizeof($fcontents); $i++) { $line = trim($fcontents[$i]); $arr = explode("\t", $line); $arr = preg_replace( "`[^a-zA-Z0-9+&@#%=~_|!:/\*,.( ]`", "", $arr); $sql = "INSERT INTO $databasetable VALUES ('". implode("','", $arr) ."')"; mysql_query($sql); echo $sql ."<br>\n"; if(mysql_error()) { echo mysql_error() ."<br>\n"; } } ?>

This is the code I'm using. I'm not sure how the above code is supposed to figure out where there are no rows at. Anyways, it's still not working. Also, my $i7 variable is not getting incremented with "$i7++", it always returns 1 regardless, as I was going to compare the $i7 to the month field in the database. $startmonth2 = ltrim($startmonth, '0'); $i7 = $startmonth2; $searchdatabase8 = "SELECT * FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 = '$field6_3' AND field4 = '$field6_4' AND field5 = '$field6_5' AND month = '$field7_month' AND year = '$field7_year' ORDER BY field3,field4,year,month ASC"; $run8 = mysql_query($searchdatabase8) or die (mysql_error()); while($row8 = mysql_fetch_assoc($run8)) { $field8_10 = $row8['field10']; $field8_month = ltrim($row8['month'], '0'); if(mysql_num_rows($row8) > 0){ echo '<td bgcolor="#ccffcc" width="25px"><strong>'; echo $field8_10; echo '</strong></td>'; } else { echo '<td bgcolor="#ccffcc" width="25px">0</td>'; } $i7++; } }

This is kind of hard to explain, but I will try my best. I have a script that is attempting to display information in a MySQL database by month fields that are chosen by the user. For instance, they can choose for the start date to start in January, and pick the end date which in this example will be April. The script then puts the required field that match the specific dates into a column for each month. For the most part it outputs fine as: Jan: 15, Feb: 14, Mar: 9, Apr: 2. However, sometimes there is nothing in the database for certain months. This causes the script to display wrong so instead I get: Jan 3, Feb: , Mar: , Apr: . In the above example, the Jan: 3 might be wrong, because the field might have been for Mar instead. I need it to display like this when there is no fields in the database: Jan: 0, Feb: 0, Mar: 3, Apr: 0. How would I go about doing this?

In the image above you can see the numerous rows that have "CUSTOMER ALW" in field5. If I wanted to combine all these into one row, and total field9 and field10 separately (like if I used SUM on each field), how would I go about doing that? The reason I want to combine, is because this query is a part of a WHILE loop that has to display whatever fields it queries, and I need it to only show one row, but have all the values totaled together. Hope this explains a little better. [attachment deleted by admin]

That works perfect. Thank you. Another quick question, is there a way to add up all the columns that have the same value in a field? For instance, I have a field with a name in it. There is about 20 rows with the same name in this field, but different values in other fields. I was GROUPing by this name field, but that only gives me the values from the first row. Can I add all the different values up and still GROUP the fields?

I have two columns in my MySQL table that list a month (01,02,03,etc) and a year on each row. The user can choose between what months and what years they would like to pull data from, so I cannot change how the table is laid out. However in my query, I have this: AND month BETWEEN '$startmonth' AND '$endmonth' AND year BETWEEN '$startyear' AND '$endyear'; Which works fine whenever the first month they choose is before the second month they choose. For instance, it works fine if you pick January 2011 as the first option and September 2011 as the second option. However this query will not work if you try to do September 2005 as the first option and January 2011 as the second option. Does anyone know of a way to fix this?