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?

I don't think this is a MySQL problem because with my queries, I get all my results fine. It's the format in the PHP that is off and not displaying what I want.

Only returns 3 results. [attachment deleted by admin]

For whatever reason this only returns 16 results, when there should be 96 results. Running without the GROUP BY returns the normal set of results however. [attachment deleted by admin]

The reason the fields are named that, is because I wasn't sure of what the values were in the fields when I created the database. Image is below of phpmyadmin: The table part should only get created everytime there is a new field3 value, which should only be a few times at most. At the moment, however, I'm more worried about getting everything working correctly then going back and fixing any performance issues with regards to html. The way the layout works is that, for each unique field3 value, there should be a green bar. Then below that there should be a darkblue bar where every row has the same field3 as the parent green bar and also has a unique field4 value. After that, there should be the light blue rows when they all have the same field4 that match with the parent darkblue row. I hope that makes it clearer. [attachment deleted by admin]

Hey guys, I am attempting to recreate a script in PHP to display results depending on data from a mysql database, but I cannot get it to display right. In the image above you can see basically what I'm trying to recreate. I put in red what the name of the fields that are corresponding with what get's displayed. In this image you can see what happens with my current code in PHP. The darker blue gets repeated after every line, when it should only repeat when there is a new "field4" value. When the field3 value changes to say "20", the script does not start over and put the light blue bars under the corresponding green bars, as it does on the first image as you can see in this image: The other major problem I have is that, if you look at the first image, is that the number between field 10 and field 9 is basically the two fields divided together, which is fine. However if you notice the darker blue bar, you can see that all the number fields get added together, and then the green bar ontop adds on the darker blue bars together. How would I go about adding dynamic fields to variable that I can then add or divide from? I am completely lost on how to do this part. Here is my code: $searchdatabase2 = "SELECT DISTINCT field3 FROM slssum2 WHERE company = '$companyname' AND month BETWEEN '$startmonth' AND '$endmonth' AND year BETWEEN '$startyear' AND '$endyear' AND field3 != 0 ORDER BY field3 ASC"; $run2 = mysql_query($searchdatabase2) or die (mysql_error()); $searchdatabase = "SELECT * FROM slssum2 WHERE company = '$companyname' AND month BETWEEN '$startmonth' AND '$endmonth' AND year BETWEEN '$startyear' AND '$endyear' AND field4 != 0 ORDER BY field4 ASC"; $run = mysql_query($searchdatabase) or die (mysql_error()); $searchdatabase3 = "SELECT field4 FROM slssum2 WHERE company = '$companyname' AND month BETWEEN '$startmonth' AND '$endmonth' AND year BETWEEN '$startyear' AND '$endyear' AND field4 != 0 ORDER BY field4 ASC"; $run3 = mysql_query($searchdatabase3) or die (mysql_error()); $row3 = mysql_fetch_assoc($run3); while($row2 = mysql_fetch_assoc($run2)) { $field2_3 = $row2['field3']; echo '<table width="915" border="0" cellspacing="0" cellpadding="5" align="center"><tr> <td bgcolor="#ccffcc">'; echo $field2_3; echo '</td> <td bgcolor="#ccffcc" colspan="3">TOTAL</td> <td bgcolor="#ccffcc"> </td> <td bgcolor="#ccffcc"> </td> <td bgcolor="#ccffcc"> </td> </tr>'; while ($row = mysql_fetch_assoc($run)) { $field3 = $row['field3']; $field4 = $row['field4']; $field5 = $row['field5']; $field6 = $row['field6']; $field9 = $row['field9']; $field10 = $row['field10']; if($field3 == $field2_3) { echo '<tr> <td>'; echo $field2_3; echo '</td> <td bgcolor="#ccccff">'; echo $field3; echo '<br />'; echo $field4; echo'</td> <td bgcolor="#ccccff" colspan="2">TOTAL</td> <td bgcolor="#ccccff"> </td> <td bgcolor="#ccccff"> </td> <td bgcolor="#ccccff"> </td> </tr>'; foreach ($row3 as $field4) { echo '<tr> <td> </td> <td> </td> <td bgcolor="#e6e6e6">'; echo $field5; echo '</td> <td bgcolor="#e6e6e6">'; echo $field6; echo '</td> <td bgcolor="#e6e6e6" align="right">'; echo $field10; echo '</td> <td bgcolor="#e6e6e6">'; echo $field9/$field10; echo '</td> <td bgcolor="#e6e6e6" align="right">'; echo $field9; echo '</td> </tr>'; } } } echo '</table><br /><br />'; } ?> [attachment deleted by admin]

Well, it happens if I try to do it say more than once or twice per hour I believe. But the images still load fine via browser, so I have no idea whats happening. I can't imagine Microsoft's server being down that much.

Hey guys, The script I use to generate images, more or less, works almost flawlessly. However, I keep experiencing a problem at random in my script when it comes time to call a JPEG or PNG file from an external server. A lot of the time it will work fine, but many other times it comes back with the error: imagecreatefromjpeg() [function.imagecreatefromjpeg]: Cannot read image data Which causes the script to fail. Right now the images it say it cannot read are these: http://tiles.xbox.com/tiles/UT/EF/1mdsb2JgbA9ECgQLGwMfWSkgL2ljb24vMC84MDAwIAABAAAAAPkqMU4=.jpg http://tiles.xbox.com/tiles/Au/lM/1Wdsb2JgbA9ECgUAGwEfL1hTL2ljb24vMC84MDAwIAABAAAAAPpj6R0=.jpg http://tiles.xbox.com/tiles/6q/kv/1Gdsb2JgbA9ECgUAGwEfL1hSL2ljb24vMC84MDAwIAABAAAAAPsAqfU=.jpg http://tiles.xbox.com/tiles/tQ/UG/1Gdsb2JgbA9ECgUAGwEfV1gmL2ljb24vMC84MDAwIAABAAAAAPspBao=.jpg http://tiles.xbox.com/tiles/qp/Fx/0Wdsb2JgbA9ECgQNGwEfVitXL2ljb24vMC84MDAwIAABAAAAAP5ekbU=.jpg and as you can see, they work fine. So what's going on here? Is Microsoft somehow blocking the attempt? I can view the images fine in the browser, but sometimes it just won't work in the script. But like I said, it's not everytime. The line the errors comes up on are these: $lastxboxgames = imagecreatefromjpeg($lastxboxgames); $lastxboxgames1 = imagecreatefromjpeg($lastxboxgames1); $lastxboxgames2 = imagecreatefromjpeg($lastxboxgames2); $lastxboxgames3 = imagecreatefromjpeg($lastxboxgames3); $lastxboxgames4 = imagecreatefromjpeg($lastxboxgames4); I also have the script to echo the variables back to me when while it's running, so those urls up there came exactly from the script, so it's not that its getting the wrong URL, it just decides that the image isn't good enough. The script, generates my signature image below, and when any of the images that come from the xbox server return an error, they all do, including the avatar image in the top left which is a PNG. Any help is appreciated!

I'm not sure exactly what's doing the coding. Here's the php for the part I'm trying to call on the form page. $select2 = "SELECT lastplayed FROM gxboxlive WHERE gamertag = '$xboxliveid'"; $query2 = mysql_query($select2) or die (mysql_error()); while($list = mysql_fetch_object($query2)){ $lastxboxgames9 = $list->lastplayed; } $num = preg_match_all('!http://[^?#]+\.(?:jpe?g|png|gif)!Ui', $lastxboxgames9, $match, PREG_PATTERN_ORDER); for ($i = 0; $i < count($match); $i++) { $lastxboxgames = $match[$i][0]; $lastxboxgames = urldecode($lastxboxgames); } and here's the php for it on the actual script: $lastgames = urldecode($_GET['lastxboxgames']); I plan on having it show the last 5 games played eventually, but for now I'm just trying to get the most recent.

Hey guys, I have a script that attempts to call the database of my forums to find the last games played on each of their xbox live profiles. However when it attempts to call the image, it encodes the url - so http://tiles.xbox.com/tiles/tT/MX/0mdsb2JgbA9ECgQJGgYfVlpWL2ljb24vMC84MDAwIAABAAAAAP04M6o=.jpg turns into http%3A%2F%2Ftiles.xbox.com%2Ftiles%2FtT%2FMX%2F0mdsb2JgbA9ECgQJGgYfVlpWL2ljb24vMC84MDAwIAABAAAAAP04M6o%3D.jpg which makes the URL not work anymore so the script gives an error. I've tried urldecode() on numerous variables and it won't work. If I call the variable for the image on the page that submits the form it returns a normal address. Anyone have any ideas? Thanks!

Hey guys, I have a script that is used to showcase the newest forum (vbulletin) threads on an external page but I can't figure out a way to add trailing periods to it if the array is over a certain amount of characters. function output_NewestReplies($a = 5,$f = ""){ global $db, $Data; // Define amount to show $Amount = ($a)? intval($a) : 5; // Define Forum(s) To Pull From $Forums = ($f)? $f: ''; $SQL = ''; if($Forums){ $SQL = " where forumid in({$Forums})"; } // Load Template $Template = LoadTemplate("newest_threads.html"); // Collect Data $NewestReplies = $db->query("select * from ".TABLE_PREFIX."thread{$SQL} order by lastpost desc limit 0,$Amount"); while($Thread = $db->fetch_array($NewestReplies)){ $Data .= ParseTemplate($Template, array( 'threadid' => $Thread['threadid'], 'threadname' => $Thread['title'], 'postuserid' => $Thread['postuserid'], 'postusername' => $Thread['postusername'], 'replies' => vb_number_format($Thread['replycount']), 'views' => vb_number_format($Thread['views']), 'lastposter' => $Thread['lastposter'], ) ); } doOutput(); } That's the function to call for new replies, and I'm trying to add trailing periods to " $Thread['title']" if "$Thread['title']" exceeds a certain character limit. Any ideas? Thanks!