TecTao Posted June 12, 2014 Share Posted June 12, 2014 I am trying to create 3 columns of data from a query. I have used this code before for 2 columns but and now wanting to use for three columns. There are 5 records from a join query. The first two columns display the results, 2 results in the left column and 3 results in the center column. The right column displayw empyt text used in displaying the results, there is no data to populate and it seems to mimic the the number from the center, 3. Here is the URL to see the problem http://specialslocator.com/specials_zip_locator/managersSpecial_display.php?un=830&mS_MGMTcampaignID=1IBu3992p Here is the Code for the query and the 3 column results. <?php $UN_URL = $_GET['un']; $mS_MGMTcampaignID_URL = $_GET['mS_MGMTcampaignID']; ?> <table width="944" border="1" align="center" cellpadding="0" cellspacing="0"> <?php $result = mysql_query( "SELECT * FROM manager_Specials_company_mgmt MSCM LEFT JOIN b2c_coupons B2CC ON (MSCM.mS_MGMT_id = B2CC.m_primeID )WHERE mS_MGMTcampaignID = '$mS_MGMTcampaignID_URL' ORDER BY mS_MGMT_m_company ASC" ); $num_results = mysql_num_rows($result); $numb_tb_rows= ceil($num_results/3) ?> <? if ( $numb_tb_rows > 0 ) { ?> <tr > <td colspan="4" valign="top" class="body_text_10_Center"><strong><?=$mS_campaignName_mS?> is proud to sponsor <?=$num_results?> <? if ( $num_results == 1 ) { ?> company:</strong> <? } elseif ( $num_results > 1 ) { ?> companies:</strong> <? } ?> </td> </tr> <tr class="body_text_10_Center"> <!-- Column 1 --> <td width="320" class="body_text_10_Center" valign="top"> <? // column #1 if($num_results>=1) { for($i=0; $i<$numb_tb_rows; $i++) { $row= mysql_fetch_array($result); ?> <?php if(!empty ($row["m_logo"])) { ?> <div align="center"> <img src="http://marketingteammates.com/_ZABP_merchants/_zcnsLogos/gick.php/<?=stripslashes($row["m_logo"])?>?resize(220)" border="0"></a><br /> </div> <?php } else { } ?> <h6><?=stripslashes($row["mS_MGMT_m_company"])?></h6> <?=stripslashes($row["b2c_m_address1"])?> <?=stripslashes($row["b2c_m_address2"])?><br /> <?=stripslashes($row["b2c_m_city"])?> <?=stripslashes($row["b2c_m_state"])?>, <?=stripslashes($row["b2c_m_zip"])?><br /> Phone: <?=stripslashes($row["b2c_m_phone"])?><br /> Fax: <?=stripslashes($row["b2c_m_fax"])?><br /> <a href="mailto:<?=stripslashes($row["b2c_email"])?>"><?=stripslashes($row["b2c_email"])?></a><br /><br /> Hours of Operation: <?=stripslashes($row["m_coupon_title"])?><br /> <?=stripslashes($row["m_coupon_desc"])?><br /><br /> <div align="center"> <img src="../images/print_image.gif" width="29" height="31" /><br /><br /> <hr width="80%" size="1" /> </div><br /><br /> <? // END column #1 } } ?> </td> <!-- Column 2 --> <td width="320" class="body_text_10_Center" valign="top"> <? // column #2 if($num_results>=2) { for($i=($numb_tb_rows); $i<$num_results; $i++) { $row= mysql_fetch_array($result); ?> <?php if(!empty ($row["m_logo"])) { ?> <div align="center"> <img src="http://marketingteammates.com/_ZABP_merchants/_zcnsLogos/gick.php/<?=stripslashes($row["m_logo"])?>?resize(220)" border="0"></a><br /> </div> <?php } else { } ?> <h6><?=stripslashes($row["mS_MGMT_m_company"])?></h6> <?=stripslashes($row["b2c_m_address1"])?> <?=stripslashes($row["b2c_m_address2"])?><br /> <?=stripslashes($row["b2c_m_city"])?> <?=stripslashes($row["b2c_m_state"])?>, <?=stripslashes($row["b2c_m_zip"])?><br /> Phone: <?=stripslashes($row["b2c_m_phone"])?><br /> Fax: <?=stripslashes($row["b2c_m_fax"])?><br /> <a href="mailto:<?=stripslashes($row["b2c_email"])?>"><?=stripslashes($row["b2c_email"])?></a><br /><br /> Hours of Operation: <?=stripslashes($row["m_coupon_title"])?><br /> <?=stripslashes($row["m_coupon_desc"])?><br /><br /> <div align="center"> <img src="../images/print_image.gif" width="29" height="31" /><br /><br /> <hr width="80%" size="1" /> </div><br /><br /> <? // END column #2 } } ?> </td> <!-- Column 3 --> <td width="320" class="body_text_10_Center" valign="top"> <? // column #3 if($num_results>=3) { for($i=($numb_tb_rows); $i<$num_results; $i++) { $row= mysql_fetch_array($result); ?> <?php if(!empty ($row["m_logo"])) { ?> <div align="center"> <img src="http://marketingteammates.com/_ZABP_merchants/_zcnsLogos/gick.php/<?=stripslashes($row["m_logo"])?>?resize(220)" border="0"></a><br /> </div> <?php } else { } ?> <h6><?=stripslashes($row["mS_MGMT_m_company"])?></h6> <?=stripslashes($row["b2c_m_address1"])?> <?=stripslashes($row["b2c_m_address2"])?><br /> <?=stripslashes($row["b2c_m_city"])?> <?=stripslashes($row["b2c_m_state"])?>, <?=stripslashes($row["b2c_m_zip"])?><br /> Phone: <?=stripslashes($row["b2c_m_phone"])?><br /> Fax: <?=stripslashes($row["b2c_m_fax"])?><br /> <a href="mailto:<?=stripslashes($row["b2c_email"])?>"><?=stripslashes($row["b2c_email"])?></a><br /><br /> Hours of Operation: <?=stripslashes($row["m_coupon_title"])?><br /> <?=stripslashes($row["m_coupon_desc"])?><br /><br /> <div align="center"> <img src="../images/print_image.gif" width="29" height="31" /><br /><br /> <hr width="80%" size="1" /> </div><br /><br /> <? // END column #3 } } ?> </td> </tr> <? } else { echo ""; } ?> </table> Thanks for any help in advance. Mike Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 12, 2014 Share Posted June 12, 2014 (edited) Um, yeah. Take your time and build code that is logically structured things like this are easy. You should be able to have a variable to define the number of columns and be able to change it at will and it just works. Also, don't intermingle PHP logic and the output. It makes it really hard to manage code. I'll post something in a few minutes. Edited June 12, 2014 by Psycho Quote Link to comment Share on other sites More sharing options...
TecTao Posted June 12, 2014 Author Share Posted June 12, 2014 Thanks for the help and direction. I thought the code was pretty logically structured, and you are right about the PHP Logic and output. I'm so use to going in and out of PHP and being a visual person I am geared to view the output as I go along. I'm sure it drives structured code writers nuts. Sorry that it's an interference. Looking forward to see your approach. Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 12, 2014 Share Posted June 12, 2014 (edited) OK, I had some difficulty following some of the logic to determine what the output should be and I'm seeing some problems in the code you have. For example, there is a closing anchor tag: (</a>) after the image, but no opening tag. Anyway, this process allows you to have as many columns as you want by just defining $max_columns at the top of the script. This is only one set of code for outputting records (not multiple for each column). The magic that makes this happen is the modulus operator: '%'. A modulus gives you the remainder of a division. So, if you divide the row count by the max columns (3) you will see a pattern: Row 1 : Modulus of 3: 1 Row 2 : Modulus of 3: 2 Row 3 : Modulus of 3: 0 Row 4 : Modulus of 3: 1 Row 5 : Modulus of 3: 2 Row 6 : Modulus of 3: 0 So, when the modulus is 1 you start a new row. When it is 0 you close the row. I also created a function for creating the output for a single record. This helps to compartmentalize the logic and separate the content from the logic. I don't have your database, so I did not test this. There may be some syntax errors or other minor errors - but the logic should be sound. I don't know why you are using strip_slashes() for the output. You should be using htmlentities() or htmlspecialchars(). I suspect you are storing 'bad' data which is forcing you to remove the slashes. You should look at sanitizing the data before it is stored. Then only use the proper method for escaping based on the output. A value used in a url should use urlencode() and output as 'HTML' should use one of the functions mentioned above. You're also using the deprecated mysql_ functions (which I didn't change). <?php //Define the number of columsn to use $max_columns = 3; //Function to create output for one company function companyOutput($row) { //Create output for the company $company = ''; if(!empty($row["m_logo"])) { $imgURL = stripslashes(urlencode($row["m_logo"])); $company .= "<div align='center'>\n"; $company .= "<img src='http://marketingteammates.com/_ZABP_merchants/_zcnsLogos/gick.php/{$imgURL}?resize(220)' border='0'></a><br />"; $company .= "</div>\n"; } $company .= "<h6>" . stripslashes($row["mS_MGMT_m_company"]) . "</h6>\n"; $company .= stripslashes($row["b2c_m_address1"]) . ' ' . stripslashes($row["b2c_m_address2"]) . "<br />\n"; $company .= stripslashes($row["b2c_m_city"]) . ' ' . stripslashes($row["b2c_m_state"]) . ', ' . stripslashes($row["b2c_m_zip"]) . "<br />\n"; $company .= "Phone: " . stripslashes($row["b2c_m_phone"]) . "<br />\n"; $company .= "Fax: " . stripslashes($row["b2c_m_fax"]) . "<br />\n"; $company .= "<a href='mailto:" . stripslashes($row["b2c_email"]) . "'>" . stripslashes($row["b2c_email"]) . "</a><br /><br />\n"; $company .= "Hours of Operation: " . stripslashes($row["m_coupon_title"]) . "<br />\n"; $company .= stripslashes($row["m_coupon_desc"]) . "<br /><br />\n"; $company .= "<div align='center'>"; $company .= "<img src='../images/print_image.gif' width='29' height='31' /><br /><br />\n"; $company .= "<hr width='80%' size='1' />\n"; $company .= "</div><br /><br />\n"; return $company; } //Get data from the url - and make SQL safe $UN_URL = mysql_real_escape_string($_GET['un']_; $mS_MGMTcampaignID_URL = mysql_real_escape_string($_GET['mS_MGMTcampaignID']); //Create and run query ### DON'T USE * IN THE SELECT - IT IS INEFFICIENT $query = "SELECT mS_MGMT_m_company, m_logo, b2c_m_address1, b2c_m_address2, b2c_m_city, b2c_m_state, b2c_m_zip, b2c_m_phone, b2c_m_fax, b2c_email, m_coupon_title, m_coupon_desc FROM manager_Specials_company_mgmt MSCM LEFT JOIN b2c_coupons B2CC ON (MSCM.mS_MGMT_id = B2CC.m_primeID ) WHERE mS_MGMTcampaignID = '$mS_MGMTcampaignID_URL' ORDER BY mS_MGMT_m_company ASC" $result = mysql_query($query); $result_count = mysql_num_rows($result); //Create the output $output = ''; if(!$result_count) { //There were no results $output .= "<tr><th>There were no results</th></tr>\n"; } else { //Redefine max_columns if total is less than the max //i.e., if there are only 2 records, only use 2 columns $max_columns = min($max_columns, $result_count); //Create header row $companyString = ($result_count==1) ? 'company:' : 'companies:'; $output .= "<tr>\n"; $output .= "<th colspan='{$max_columns}' valign='top' class='body_text_10_Center'>"; $output .= "{$mS_campaignName_mS} is proud to sponsor {$result_count} {$companyString}"; $output .= "</th>\n"; $output .= "</tr>\n"; //Create output for each result record $row_count = 0; while($row = mysql_fetch_assoc($result)) { //Increment the row counter $row_count++; //Open new row if first record in a row if($row_count%$max_columns == 1) { $output .= "<tr>\n"; } //Output the record $output .= "<td>\n" $output .= companyOutput($row); $output .= "</td>\n" //Close row if last record in a row if($row_count%$max_columns == 1) { $output .= "</tr>\n"; } } //If last row was not closed, close it now if($row_count%$max_columns != 1) { $output .= "</tr>\n"; } } ?> <html> <head></head> <body> <table width="944" border="1" align="center" cellpadding="0" cellspacing="0"> <?php echo $output; ?> </table> </body> </html> Edited June 12, 2014 by Psycho Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 12, 2014 Share Posted June 12, 2014 (edited) Hmm, . . .after further consideration I'm not sure I understand your intended output. The code above would put each record in a cell going left to right, top to bottom like this ------------- | 1 | 2 | 3 | ------------- | 4 | 5 | 6 | ------------- | 7 | 8 | 9 | ------------- But, looking at your example page, I think you may just want three cells with the records grouped into the cells like this ------------- | 1 | 4 | 7 | | | | | | 2 | 5 | 8 | | | | | | 3 | 6 | 9 | ------------- Or maybe some other variation of those two. If the format I provided isn't what you want, please give some details. Making that change will be relatively easy with what I already did. Edited June 12, 2014 by Psycho Quote Link to comment Share on other sites More sharing options...
TecTao Posted June 13, 2014 Author Share Posted June 13, 2014 First, thanks so much for your time and for the explaination and the example code. Sorry for the sloppy code, the closed anchor tag was just plain sloppy. I've read up on the modulus operator and I believe I understand. If there are 5 records and 3 columns, is leaves a remainder of .67 which gives a result of 0 and closes the row. In this case closes it on row 2. if it were 15 records, no remainder it would display 5 rows closing, I guess, at the 6th. The function you wrote at the beginning of the script is the output that I had in each of the table columns. I was curious why you wrote the sql select query the way you did, but I read your note and it makes sense, the query doesn't need to work as hard by going through every record. Regarding your question about the order, it can flow you you described in the first illustration -------------| 1 | 2 | 3 |-------------| 4 | 5 | 6 |-------------| 7 | 8 | 9 |------------- It my test query, there are 5 results. Below is the page URL for setting the maximum columns to 3. It is returning 2 columns with 1 result in row 1, 2 results in row 2, and 1 result in rows 3 and 4. http://specialslocator.com/specials_zip_locator/managersSpecial_display_v2.php?un=830&mS_MGMTcampaignID=1IBu3992p If I set the maximum columns to 4, It returns 1 result for row 1, 3 results for row 2 and 1 result for row 3. http://specialslocator.com/specials_zip_locator/managersSpecial_display_v2.php?un=830&mS_MGMTcampaignID=1IBu3992p Here is the uploaded code for 3 rows. <?php include("../_customer_apps/include/session.php"); define( "DIR", "SIMT_town_sponsorship_processing_files/", true ); require( 'zipconfig.php' ); require( DIR . 'lib/db.class.php' ); require( DIR . 'lib/busgroup.class.php' ); //Define the number of columsn to use $max_columns = 3; //Function to create output for one company function companyOutput($row) { //Create output for the company $company = ''; if(!empty($row["m_logo"])) { $imgURL = urlencode($row["m_logo"]); $company .= "<div align='center'>\n"; $company .= "<img src='http://marketingteammates.com/_ZABP_merchants/_zcnsLogos/gick.php/{$imgURL}?resize(220)' border='0'><br />"; $company .= "</div>\n"; } $company .= "<h6>" . ($row["mS_MGMT_m_company"]) . "</h6>\n"; $company .= ($row["b2c_m_address1"]) . ' ' . ($row["b2c_m_address2"]) . "<br />\n"; $company .= ($row["b2c_m_city"]) . ' ' . ($row["b2c_m_state"]) . ', ' . ($row["b2c_m_zip"]) . "<br />\n"; $company .= "Phone: " . ($row["b2c_m_phone"]) . "<br />\n"; $company .= "Fax: " . ($row["b2c_m_fax"]) . "<br />\n"; $company .= "<a href='mailto:" . ($row["b2c_email"]) . "'>" . ($row["b2c_email"]) . "</a><br /><br />\n"; $company .= "Hours of Operation: " . ($row["m_coupon_title"]) . "<br />\n"; $company .= ($row["m_coupon_desc"]) . "<br /><br />\n"; $company .= "<div align='center'>"; $company .= "<img src='../images/print_image.gif' width='29' height='31' /><br /><br />\n"; $company .= "<hr width='80%' size='1' />\n"; $company .= "</div><br /><br />\n"; return $company; } //Get data from the url - and make SQL safe $UN_URL = urlencode($_GET['un']); $mS_MGMTcampaignID_URL = urlencode($_GET['mS_MGMTcampaignID']); //Create and run query ### DON'T USE * IN THE SELECT - IT IS INEFFICIENT $query = "SELECT mS_MGMT_m_company, m_logo, b2c_m_address1, b2c_m_address2, b2c_m_city, b2c_m_state, b2c_m_zip, b2c_m_phone, b2c_m_fax, b2c_email, m_coupon_title, m_coupon_desc FROM manager_Specials_company_mgmt MSCM LEFT JOIN b2c_coupons B2CC ON (MSCM.mS_MGMT_id = B2CC.m_primeID ) WHERE mS_MGMTcampaignID = '$mS_MGMTcampaignID_URL' ORDER BY mS_MGMT_m_company ASC"; $result = mysql_query($query); $result_count = mysql_num_rows($result); //Create the output $output = ''; if(!$result_count) { //There were no results $output .= "<tr><th>There were no results</th></tr>\n"; } else { //Redefine max_columns if total is less than the max //i.e., if there are only 2 records, only use 2 columns $max_columns = min($max_columns, $result_count); //Create header row $companyString = ($result_count==1) ? 'company:' : 'companies:'; $output .= "<tr>\n"; $output .= "<th colspan='{$max_columns}' valign='top' class='body_text_10_Center'>"; $output .= "{$mS_campaignName_mS} is proud to sponsor {$result_count} {$companyString}"; $output .= "</th>\n"; $output .= "</tr>\n"; //Create output for each result record $row_count = 0; while($row = mysql_fetch_assoc($result)) { //Increment the row counter $row_count++; //Open new row if first record in a row if($row_count%$max_columns == 1) { $output .= "<tr>\n"; } //Output the record $output .= "<td>\n"; $output .= companyOutput($row); $output .= "</td>\n"; //Close row if last record in a row if($row_count%$max_columns == 1) { $output .= "</tr>\n"; } } //If last row was not closed, close it now if($row_count%$max_columns != 1) { $output .= "</tr>\n"; } } ?> <html> <head></head> <body> <table width="944" border="1" align="center" cellpadding="0" cellspacing="0"> <?php echo $output; ?> </table> </body> </html> Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 13, 2014 Share Posted June 13, 2014 (edited) Why did you change this to use urlencode() instead of mysql_real_escape_string()? Those values are from a url so, if anything, you would use urldecode(). BUt, even that doesn't protect against SQL injection - which is why I had used mysql_real_escape_string(). I've read up on the modulus operator and I believe I understand. If there are 5 records and 3 columns, is leaves a remainder of .67 which gives a result of 0 and closes the row. In this case closes it on row 2. if it were 15 records, no remainder it would display 5 rows closing, I guess, at the 6th. No. A modulus will not return .67. It will return a whole number. If you use 3 as the number of rows, modulus will return the remainder of record_count divided by three, which will have only three possible values: 0, 1 or 2. As for the problem with the output - I had one copy/paste error on the use of the modulus which cause the problem. The condition to close a row should be this (Note the 1 is now a 0): //Close row if last record in a row if($row_count%$max_columns == 0) { $output .= "</tr>\n"; } I created some mock data to test and all seemed correct. In fact try the script with different values for $max_columns and it should "just work". That's how well written code should work. You should not have to completely rewrite a page because you want to change the output for something trivial as the number of columns. Edited June 13, 2014 by Psycho Quote Link to comment Share on other sites More sharing options...
TecTao Posted June 13, 2014 Author Share Posted June 13, 2014 Thanks again for your time and interest. I misunderstood your comments in the first post regarding replacing strip_slashes() with urldecode(). I am not clear on the differences between htmlentities(), htmlspecialchars(), urldecode() and mysql_real_escape_string() but from your reply I should. Your change to the close row works perfectly, and as I see it I understand the logic. This is much cleaner and provides more control. I am not very strong in object oriented programming but each time I get feedback I see the flow and how it works. Thanks again for all your help and direction. mike Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted June 13, 2014 Share Posted June 13, 2014 (edited) I am not clear on the differences between htmlentities(), htmlspecialchars(), urldecode() and mysql_real_escape_string() but from your reply I should. Yes, many people struggle with those functions. I find this a bit surprising, because for the most part, the name already tells you the purpose. There are several pitfalls, though. You should forget about the following functions: stripslashes() is bullshit and damages your data. Do not use this. In the early days of PHP, there used to be a bug/feature called “Magic Quotes” which automatically applied a kind of primitive SQL-escaping to external input. The idea was to help newbies get their queries right, because many people didn't know anything about escaping and just dropped their URL parameters etc. straight into the query string. Of course this “feature” also meant that you had to remove all the backslash characters whenever you wanted to use the string for something other than a query. This is what stripslashes() does. However, the whole idea of blanket escaping is obviously naïve and technically flawed, so it was frowned upon for many years, then officially deprecated and finally removed. “Magic Quotes” should have become extinct by now. Unfortunately, people still use stripslashes() as a kind of ritual or simply because they've copied and pasted some really, really old code. This is downright harmful, because if there are no backslashes from “Magic Quotes”, then stripslashes() will delete actual backslashes from the string content. htmlentities() is nonsense, do not use it. This function replaces all characters for which there's a named HTML entity. So, for example, the umlaut “ö” becomes “ö”. This may have been useful back in the 90s when Unicode was still new and not well-supported. But in the year 2014, we have “modern” encodings like UTF-8 and no longer need those workarounds. It's a waste of resources. While we're at it: Don't use strip_tags() either. It's an attempt at writing an HTML filter, but it's so primitive that you're likely to mangle the entire input. This function solves your HTML problems in the same way that decapitation cures a headache. There are better alternatives to the following functions: mysql_real_escape_string() is only necessary if you still use the old mysql_* functions. Those are, again, hopelessly outdated and will be removed in one of the next PHP releases. Didn't you see the big red warning signs in the PHP manual? Nowadays, we use modern database interfaces like PDO. Since PDO supports parameterized queries, manual SQL-escaping is mostly a thing of the past. It's also extremely cumbersome and error-prone and should be avoided at all cost. You do need the following functions: htmlspecialchars() escapes characters like “<” and “>” which have a special meaning in HTML. You absolutely must use this function whenever you want to insert a value into your HTML document. Otherwise, your users can inject arbitrary HTML markup into your page, including malicious script elements (see cross-site scripting). Unfortunately, htmlspecialchars() is very difficult to use, and even experienced developers constantly get it wrong. At the very least, you have to define the correct character encoding and set the ENT_QUOTES flag. For example: htmlspecialchars($raw_input, ENT_QUOTES, 'UTF-8'). urlencode() is required whenever you want to insert a value into an URL. Many characters like “/” or “?” have a special meaning within a URL, so if you want to use them as literal characters, you need to encode them. For example, you can't just write "https://yoursite.com/account.php?user=" . $username . You need "https://yoursite.com/account.php?user=" . urlencode($username) . Edited June 13, 2014 by Jacques1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.