I'm trying to output data from my database but I need it ordering as specified in the database. I have 4 columns that specify what goes where: Row - Which row this belongs to Col - Which column in the above row this belongs to Rows - How many rows we have Columns - How many columns we have As a example, we have in 1 set 7 rows and 7 columns so: Row = 1 Col = 1 Belongs in the top left hand corner followed by: Row = 1 Col = 2 Row = 1 Col = 3 So on so forth, all the way up to row 1 col 7 then it starts again at row 2 col 1. What is the best way for me to do this? As I say, each row in the database specifies where that row belongs this is what's making it difficult for me... Thanks
So on the database you have a separate columns for "Rows" and "Columns". Won't this have to be same on the the database rows? If so, why do you have it repeating on all database rows. Also, will the database have data that is supposed to go to this table?
What I'm building is a planogram which changes from time to time. On one it may be 7*7 on another it may be 6*7 another may be 4*7 so each record does have this built in -- I know, it's what I have to work with though. The planogram will be displayed dynamically so in the first example, it's 7*7 and each record which fits this planogram has it specified in all 49 records that it is 7*7. Each record also has it's own identifier to state where in the 7*7 it belongs: E.g. Row 1 Column 2 in a 7 * 7 planogram
Well, then it would have been better if you used separate db table for planogram with at least 3 columns i.e. ID, rows and column. Then add one single planogram_ID column on the table that defines the position. Anyways, How do you want to display this data? using HTML tables?
Had to Google whiskey tango foxtrot a 'planogram' is... (strikes me as post '90's marketing re-re BS) Is it not static on BOTH axis? (all your examples have the latter axis as 7) IF so, I'd probably have row and column as fields for each item, then use two queries -- one for SELECT * ORDER BY row ASC, column ASC, another for SELECT row, COUNT(column) AS columns. Markup output would be a stone cold **** though... I guess stacked floats good as always.
I'll give you a further rundown of exactly what it is. We have business cards in supermarkets, and our drivers go in with a PDA which has a Windows based program on it. Now for our customers, we're building a simple (thought it would be but turns out it's not as simple as I first thought) web-based interface that pulls the data out and displays it as it would look in the store. Now the actual board that the cards go on are not always 7 * 7, they can change depending on exactly what's on them e.g. a newsletter or flyer instead of a business card which changes the whole dimension of the planogram. I've attached an image of the 7 * 7 version, I don't really have any other data to work with yet.
Anybody? Really struggling with this one. I could select rows/columns from the db and do indidividual queries based on how many rows/columns I need but that just wouldn't be efficient enough. I know that in the above, I have 7 rows and 7 columns, I just need a way to iterate through the results and show col 1-7 on row 1, col 1-7 on row 2, col 1-7 on row 3 until it hits 1-7 on row 7 but have it dynamically select how many rows. Here's some direct data I'm working with:
Scott, I can create a function that will display the data in HTML Table for you. But first, I need to know whats going on with your database table. I see entries '999' on the 'Column' column. And also entry of '0' on 'Row' column. for the grid of 7X7, these number have to be either 0 to 6 OR 1 to 7. Also, You still haven't told us what do you need exactly to show on this grid? The advertiser's name? Amit
Thanks for your reply. I was going to sort the 0 and 999 columns myself but if you could get it all into 1 function that would be super good. 0 is the banner at the top if you notice in the image I provided. 999 are RH_Pockets and LH_Pockets. In the test data above, LH_Pockets is 0 but RH_Pockets is 2 displayed vertical, on top of each other to fit the overall height of the grid. If it helps, I have the working VB code which is useless to me as I'm only a beginner in VB.
Scott, Here is the function you will need. function samyak_draw_table($data_array, $number_of_rows, $number_of_columns) { //Create an empty 2 dimensional array $table_array = array(); for($i = 0; $i<$number_of_rows; $i++) { $table_array[] = array_pad(array(), $number_of_columns, "Locked"); } //Set default values fr the banners $top_banner = "Locked"; $side_banner1 = "Locked"; $side_banner2 = "Locked"; //Calculating how many rows to span to show the banners on the right. $side_banner1_merge = floor($number_of_rows/2); $side_banner2_merge = $number_of_rows -$side_banner1_merge; //Fill out the empty table array with data from the DB, foreach($data_array as $db_row) { if($db_row['Row'] == 0 && $db_row['Column'] ==0) $top_banner = $db_row['advertiser']; elseif($db_row['Row'] == 1 && $db_row['Column'] ==999) $side_banner1 = $db_row['advertiser']; elseif($db_row['Row'] == 2 && $db_row['Column'] ==999) $side_banner2 = $db_row['advertiser']; elseif($db_row['advertiser'] != "NULL") { $current_row = $db_row['Row']-1; $current_column = $db_row['Column']-1; //Add the data only if the given row and columns are within bounds if(isset($table_array [$current_row][$current_column])) $table_array [$current_row][$current_column] = $db_row['advertiser']; } } //Starting to draw the table $str = array(); $str[] = "<table style='border-collapse:collapse; font-family: Arial' border='1'>"; $bgcolor = ($top_banner != "Locked") ? '#cfc' : '#ddd'; $str[] = "<tr><td colspan='$number_of_columns' style=' padding:15px 10px; background-color:$bgcolor; text-align:center'>$top_banner</td></tr>"; for ($i=0; $i<$number_of_rows; $i++) { $rows = $table_array[$i]; $str[] ="<tr>"; foreach($rows as $data) { $bgcolor = "#ddd"; if($data != "Locked") $bgcolor = '#cfc'; $str[] = "<td style='width:80px; padding:40px 10px; background-color:$bgcolor; text-align:center'>$data</td>"; } if($i==0) { $bgcolor = ($side_banner1 != "Locked") ? '#cfc' : '#ddd'; $str [] ="<td rowspan='$side_banner1_merge' style='width:80px; padding:50px 10px; background-color:$bgcolor; text-align:center'>$side_banner1</td>"; } if($i==$side_banner1_merge) { $bgcolor = ($side_banner2 != "Locked") ? '#cfc' : '#ddd'; $str [] ="<td rowspan='$side_banner2_merge' style='width:80px; padding:50px 10px; background-color:$bgcolor; text-align:center'>$side_banner2</td>"; } $str[] ="</tr>"; } $str[] = "</table>"; return ( implode("\n", $str)); } PHP: To use this function First get the data from the db and put it on an array: $data_array. Then pass it like this: echo samyak_draw_table($data_array, 7, 7); PHP: Hope this is helpful. Let me know if you need any help with this. Thanks,
Progress! Not quite there though. I was having trouble with the foreach loop, but I think I resolved it by pulling the data into an array. Problem I have now though is that only the top bar is showing green, everything else is locked... Here's the PHP so far: <?php //connect $db_myHost = ""; $db_myUser= ""; $db_myPassword = ""; $db_myDatabase = ""; mssql_connect($db_myHost, $db_myUser, $db_myPassword) or die(mysql_error()); mssql_select_db("$db_myDatabase") or die(mysql_error()); $dbconn = new PDO('mssql:host='.$db_myHost.';dbname='.$db_myDatabase, $db_myUser, $db_myPassword); try { $dbPDO = new PDO('mssql:host='.$db_myHost.';dbname='.$db_myDatabase, $db_myUser, $db_myPassword); $dbPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { //echo "Error!: " . $e->getMessage() . " die(); } //pull data $sth = $dbconn->prepare(" SELECT id, columns, rows, advertiser, row, col FROM storemedia WHERE (visit_media_id LIKE :visit_media_id) "); //$sth->execute(); $data_array = array($sth->execute(array(':visit_media_id'=>4759))); function samyak_draw_table($data_array, $number_of_rows, $number_of_columns) { //Create an empty 2 dimensional array $table_array = array(); for($i = 0; $i<$number_of_rows; $i++) { $table_array[] = array_pad(array(), $number_of_columns, "Locked"); } //Set default values fr the banners $top_banner = "Locked"; $side_banner1 = "Locked"; $side_banner2 = "Locked"; //Calculating how many rows to span to show the banners on the right. $side_banner1_merge = floor($number_of_rows/2); $side_banner2_merge = $number_of_rows -$side_banner1_merge; //Fill out the empty table array with data from the DB foreach($data_array AS $db_row) { if($db_row['Row'] == 0 && $db_row['Column'] ==0) $top_banner = $db_row['advertiser']; elseif($db_row['Row'] == 1 && $db_row['Column'] ==999) $side_banner1 = $db_row['advertiser']; elseif($db_row['Row'] == 2 && $db_row['Column'] ==999) $side_banner2 = $db_row['advertiser']; elseif($db_row['advertiser'] != "NULL") { $current_row = $db_row['Row']-1; $current_column = $db_row['Column']-1; //Add the data only if the given row and columns are within bounds if(isset($table_array [$current_row][$current_column])) $table_array [$current_row][$current_column] = $db_row['advertiser']; } } //Starting to draw the table $str = array(); $str[] = "<table style='border-collapse:collapse; font-family: Arial' border='1'>"; $bgcolor = ($top_banner != "Locked") ? '#cfc' : '#ddd'; $str[] = "<tr><td colspan='$number_of_columns' style=' padding:15px 10px; background-color:$bgcolor; text-align:center'>$top_banner</td></tr>"; for ($i=0; $i<$number_of_rows; $i++) { $rows = $table_array[$i]; $str[] ="<tr>"; foreach($rows as $data) { $bgcolor = "#ddd"; if($data != "Locked") $bgcolor = '#cfc'; $str[] = "<td style='width:80px; padding:40px 10px; background-color:$bgcolor; text-align:center'>$data</td>"; } if($i==0) { $bgcolor = ($side_banner1 != "Locked") ? '#cfc' : '#ddd'; $str [] ="<td rowspan='$side_banner1_merge' style='width:80px; padding:50px 10px; background-color:$bgcolor; text-align:center'>$side_banner1</td>"; } if($i==$side_banner1_merge) { $bgcolor = ($side_banner2 != "Locked") ? '#cfc' : '#ddd'; $str [] ="<td rowspan='$side_banner2_merge' style='width:80px; padding:50px 10px; background-color:$bgcolor; text-align:center'>$side_banner2</td>"; } $str[] ="</tr>"; } $str[] = "</table>"; return ( implode("\n", $str)); } echo samyak_draw_table($data_array, 7, 7); ?> PHP: Honestly, really appreciate the help! UPDATE Just realized I was using execute instead of FetchAll, new query: //pull data $sth = $dbconn->prepare(" SELECT id, columns, rows, advertiser, row, col FROM storemedia WHERE (visit_media_id LIKE :visit_media_id) "); //$sth->execute(); $sth->execute(array(':visit_media_id'=>4759)); $data_array = $sth->fetchAll(); Code (markup): This leaves every cell locked other than the top banner which looks correct, green and with the title "Grosvenor Grass" Edit 2 I noticed that you were using the wrong columns and were using capitals when they're not in the db. It's row and col that define the row and column. rows and columns define how many rows/columns there are. I changed this, and now have a better visual than before other than locked cells are not locked, they're all green. The correct titles are showing in the correct cells though! Also, although it's not in my database, the top banner is defined by a type. The type is 2. Types: 1 - Active (Green) 2 - Top Banner (Green) 4 - Vialator (Blue) 6 - Locked (Grey)
Scott, 1) I created the function based on the database snippet you sent me. They had Capital heading. 2) The function I created needs you to send the total number of rows and columns as parameter. Its up to you to fetch this info from the db and sent it as the parameter. 3) It is really easy to change the colors based on the type. Just change this part of the code: $bgcolor = "#ddd"; if($data != "Locked") $bgcolor = '#cfc'; PHP: to the one that checks fro the type value.
Thanks. Where are you getting the string locked from though? There's nothing in my db that says locked, I have type (see my last update above yours). Here is a pastebin of a var_dump from the query if it helps: http://pastebin.com/jFjMjcqF
Your https://x.dpstatic.com/d/attachments/93/93760-1954730aac527047d6a79b63edc22c8e.jpg image had cells that said "locked". I set default text for all the empty cells and NULL cells
Having locked by default would be fine, but I really need to get the types working so it shows locked as grey, active as green and vialator as blue. As it stands, all I see is what's in the attachment. I just can't get it to output the correct types and don't understand why the top banner works, and one of the sidebar columns shows green but the other shows grey Here's the code so far: //pull data $sth = $dbconn->prepare(" SELECT id, columns, rows, advertiser, row, col, type FROM storemedia WHERE (visit_media_id LIKE :visit_media_id) "); //$sth->execute(); $sth->execute(array(':visit_media_id'=>4759)); $data_array = $sth->fetchAll(); function samyak_draw_table($data_array, $number_of_rows, $number_of_columns) { //Create an empty 2 dimensional array $table_array = array(); for($i = 0; $i<$number_of_rows; $i++) { $table_array[] = array_pad(array(), $number_of_columns, "Locked"); } //Set default values fr the banners $top_banner = "Locked"; $side_banner1 = "Locked"; $side_banner2 = "Locked"; //Calculating how many rows to span to show the banners on the right. $side_banner1_merge = floor($number_of_rows/2); $side_banner2_merge = $number_of_rows -$side_banner1_merge; //Fill out the empty table array with data from the DB foreach($data_array AS $db_row) { if($db_row['type'] ==2) $top_banner = $db_row['advertiser']; elseif($db_row['row'] == 0 && $db_row['col'] ==999) $side_banner1 = $db_row['advertiser']; elseif($db_row['row'] == 0 && $db_row['col'] ==999) $side_banner2 = $db_row['advertiser']; elseif($db_row['advertiser'] != "NULL") { $current_row = $db_row['row']-1; $current_column = $db_row['col']-1; //Add the data only if the given row and columns are within bounds if(isset($table_array [$current_row][$current_column])) $table_array [$current_row][$current_column] = $db_row['advertiser']; } } //Starting to draw the table $str = array(); $str[] = "<table style='border-collapse:collapse; font-family: Arial' border='1'>"; $bgcolor = ($top_banner != "Locked") ? '#cfc' : '#ddd'; $str[] = "<tr><td colspan='$number_of_columns' style=' padding:15px 10px; background-color:$bgcolor; text-align:center'>$top_banner</td></tr>"; for ($i=0; $i<$number_of_rows; $i++) { $rows = $table_array[$i]; $str[] ="<tr>"; foreach($rows as $data) { $bgcolor = "#ddd"; if($data != "Locked") $bgcolor = '#cfc'; $str[] = "<td style='width:80px; padding:40px 10px; background-color:$bgcolor; text-align:center'>$data</td>"; } if($i==0) { $bgcolor = ($side_banner1 != "Locked") ? '#cfc' : '#ddd'; $str [] ="<td rowspan='$side_banner1_merge' style='width:80px; padding:50px 10px; background-color:$bgcolor; text-align:center'>$side_banner1</td>"; } if($i==$side_banner1_merge) { $bgcolor = ($side_banner2 != "Locked") ? '#cfc' : '#ddd'; $str [] ="<td rowspan='$side_banner2_merge' style='width:80px; padding:50px 10px; background-color:$bgcolor; text-align:center'>$side_banner2</td>"; } $str[] ="</tr>"; } $str[] = "</table>"; return ( implode("\n", $str)); } echo samyak_draw_table($data_array, 7, 7); PHP: Everything is showing as active which defeats the object of what I'm trying to display. The customer needs to see it as it is, either grey for locked, green for active (type 1), blue for vialator (type 4) or grey for locked (type 6) Thanks for your help
Scott, here is the modified function, that colors the grid based on the "type" field. function samyak_draw_table($data_array, $number_of_rows, $number_of_columns) { //Create an empty 2 dimensional array $table_array = array(); for($i = 0; $i<$number_of_rows; $i++) { $table_array[] = array_pad(array(), $number_of_columns, array("Locked", 6)); } //Set default values fr the banners $top_banner = "Locked"; $side_banner1 = array("Locked", 6); $side_banner2 = array("Locked", 6); //Calculating how many rows to span to show the banners on the right. $side_banner1_merge = floor($number_of_rows/2); $side_banner2_merge = $number_of_rows -$side_banner1_merge; //Fill out the empty table array with data from the DB foreach($data_array AS $db_row) { if($db_row['type'] ==2) $top_banner = $db_row['advertiser']; elseif($db_row['row'] == 1 && $db_row['col'] ==999) $side_banner1 = array($db_row['advertiser'], $db_row['type']); elseif($db_row['row'] == 2 && $db_row['col'] ==999) $side_banner2 = array($db_row['advertiser'], $db_row['type']); elseif($db_row['advertiser'] != "NULL") { $current_row = $db_row['row']-1; $current_column = $db_row['col']-1; //Add the data only if the given row and columns are within bounds if(isset($table_array [$current_row][$current_column])) $table_array [$current_row][$current_column] = array($db_row['advertiser'], $db_row['type']); } } //Starting to draw the table $str = array(); $str[] = "<table style='border-collapse:collapse; font-family: Arial' border='1'>"; $bgcolor = '#cfc' ; $str[] = "<tr><td colspan='$number_of_columns' style=' padding:15px 10px; background-color:$bgcolor; text-align:center'>$top_banner</td></tr>"; for ($i=0; $i<$number_of_rows; $i++) { $rows = $table_array[$i]; $str[] ="<tr>"; foreach($rows as $data_array) { $data = $data_array[0]; $type = $data_array[1]; $bgcolor = "#ddd"; if($type ==4) $bgcolor ="#00f"; elseif($type ==1) $bgcolor = '#cfc'; $str[] = "<td style='width:80px; padding:40px 10px; background-color:$bgcolor; text-align:center'>$data</td>"; } if($i==0) { $sidebanner1_text = $side_banner1[0]; dump( $side_banner1); $type = $side_banner1[1]; $bgcolor = "#ddd"; if($type ==4) $bgcolor ="#00f"; elseif($type ==1) $bgcolor = '#cfc'; $str [] ="<td rowspan='$side_banner1_merge' style='width:80px; padding:50px 10px; background-color:$bgcolor; text-align:center'>$sidebanner1_text</td>"; } if($i==$side_banner1_merge) { $sidebanner2_text = $side_banner2[0]; $type = $side_banner2[1]; $bgcolor = "#ddd"; if($type ==4) $bgcolor ="#00f"; elseif($type ==1) $bgcolor = '#cfc'; $str [] ="<td rowspan='$side_banner2_merge' style='width:80px; padding:50px 10px; background-color:$bgcolor; text-align:center'>$sidebanner2_text</td>"; } $str[] ="</tr>"; } $str[] = "</table>"; return ( implode("\n", $str)); } PHP: