neocamel Posted September 2, 2009 Share Posted September 2, 2009 Hey everyone, I have a database with a single table that I am trying to display the data on a webpage using php. All I have left to do is format the display of the table data. What I need to be able to do is have certain rows displayed in different sections of the page. For example... (I know this isn't real code but you get the idea...) <html> <h2>SEO Tasks</h2> display data fields that have the type = "seo" <h2>wordpress tasks</h2> display data fields that have the type = "wordpress" </html> To do this, I added a column in the "jobs" table called "type" which lets me differentiate between the different types of tasks. The loop that currently fetches and displays ALL jobs, regardless of 'type' is... $html .= '<tr><td><input type="checkbox" name="job['.$this->jobs[$i]['id'].']" value="1"'.$checked.'/></td> <td>'.$this->jobs[$i]['name'].'</td></tr> <div style="clear:both;"></div>'."\n"; basically, Is there a way to have 5 different loops, one for each type of task, that displays only the rows where type = a specified type? So I can display portions of the table in different sections of the page? I'll be happy to provide more information about the project if necessary. Thanks so much for your help! Quote Link to comment https://forums.phpfreaks.com/topic/172869-organizing-mysql-data-with-php/ Share on other sites More sharing options...
mikesta707 Posted September 2, 2009 Share Posted September 2, 2009 There is probably an easier way to do this, but the way I would do it if I was confronted with this problem would be too loop through the results, and assign keep pushing the data from each individual row into an array for the full column. so for example $SQL = "SELECT col1, col2, col3, col4 FROM table WHERE Criteria"; $execute = mysql_query($SQL); $col1 = array(); $col2 = array(); //etc/ while($row = mysql_fetch_assoc($execute)){ $col1[] = $row['col1']; $col2{} = $row['col2']; //etc. } //now I iterate through each individual array echo "<h2>COL 1 Jobs</h2>"; foreach($col1 as $jobs){ echo $jobs."<br />"; } //etc. Quote Link to comment https://forums.phpfreaks.com/topic/172869-organizing-mysql-data-with-php/#findComment-911112 Share on other sites More sharing options...
neocamel Posted September 2, 2009 Author Share Posted September 2, 2009 I had someone else help me write the main code for this project. I think one of the reasons the code is written the way it is is because the data from the table is displayed in the form of a checklist. The user can see which items are checked, check or uncheck items, and click update and it updates the database. Will your fix still possibly for in this situation? Quote Link to comment https://forums.phpfreaks.com/topic/172869-organizing-mysql-data-with-php/#findComment-911114 Share on other sites More sharing options...
ignace Posted September 2, 2009 Share Posted September 2, 2009 You don't need 5 different loops just make sure the type is included into the query then you can add this little trick: $type = null;//start empty while ($row = mysql_fetch_assoc($result)) { if ($type !== $row['type']) {//here we compare the type of the current row with the one currently stored echo '<h2>', $row['type'], '</h2>'; $type = $row['type']; } echo '..'; } In order for this to work you need to sort on type something like: ORDER BY type This will make sure all type's of the same are grouped together so instead of: seo wordpress seo wordpress you'll get: seo seo wordpress wordpress Everytime the type changes thus from null to 'seo' and from 'seo' to 'wordpress' it'll output a h2 header once giving you: <h2>seo</h2> ..seo type rows.. <h2>wordpress</h2> ..wordpress type rows.. Quote Link to comment https://forums.phpfreaks.com/topic/172869-organizing-mysql-data-with-php/#findComment-911116 Share on other sites More sharing options...
mikesta707 Posted September 2, 2009 Share Posted September 2, 2009 yeah do what ignace said. much better solution. Quote Link to comment https://forums.phpfreaks.com/topic/172869-organizing-mysql-data-with-php/#findComment-911117 Share on other sites More sharing options...
neocamel Posted September 2, 2009 Author Share Posted September 2, 2009 Ok, I think I'm understanding what you suggest, but I'm having some trouble figuring out how to implement your code. I thought I'd just post the code that is currently outputting my table data, and perhaps you can help me figure out what changes to make. <?php class Job { const TABLE = 'jobs'; private $database; // Stores a database object private $site; private $jobs = array(); // Stores an associative array of all jobs public function __construct($site) { global $database; $this->database = $database; $this->site = $site; $this->fetch_jobs(); } // Fetches all the jobs in the current site and puts them into an array private function fetch_jobs() { $site = $this->database->real_escape_string($this->site); $query = "SELECT * FROM ".self::TABLE." WHERE site ='$site' ORDER BY type"; $result = $this->database->query($query); $rows = $result->num_rows; for($i=0;$i<$rows;$i++) { $this->jobs[$i] = $result->fetch_assoc(); } } // Generate the form for updating completed jobs public function jobs_form() { $rows = count($this->jobs); if($rows == 0) $html = '<div>There are currently no jobs in this site.</div>'; else { $html = '<form method="post" action="'.$_SERVER['PHP_SELF'].'?site='.$this->site.'"> <table> <div style="clear:both;"></div>'."\n"; for($i=0;$i<$rows;$i++) { $checked = ($this->jobs[$i]['complete'] == 1) ? ' checked="checked"' : ''; $html .= '<tr><td><input type="checkbox" name="job['.$this->jobs[$i]['id'].']" value="1"'.$checked.'/></td> <td>'.$this->jobs[$i]['name'].'</td></tr> <div style="clear:both;"></div>'."\n"; } $html .= '<input type="submit" name="submit" value="Update"/> </form>'."\n"; } return $html; } // Reads in the jobs submitted, compares with the fetched jobs and updates the database accordingly public function multi_update($array) { // If everything has been unchecked, use an empty array $ids = (!empty($array['job'])) ? array_keys($array['job']) : array(); $rows = count($this->jobs); for($i=0;$i<$rows;$i++) { $complete = (in_array($this->jobs[$i]['id'],$ids)) ? '1' : '0'; $query = "UPDATE ".self::TABLE." SET complete='$complete' WHERE id='{$this->jobs[$i]['id']}'"; $this->database->query($query); // Once the jobs have been updated, we need to refetch them $this->fetch_jobs(); } return true; } // Recursively prints out the contents of an array. (For debugging) public function recursive_print_array($array) { echo '<pre>'; foreach($array as $key=>$value) { if(!is_array($array[$key])) echo $key.'=>'.$value."\n"; else $this->recursive_print_array($array[$key]); } echo '</pre>'; } } ?> Quote Link to comment https://forums.phpfreaks.com/topic/172869-organizing-mysql-data-with-php/#findComment-911118 Share on other sites More sharing options...
neocamel Posted September 2, 2009 Author Share Posted September 2, 2009 let me also say in advance, thanks for the help! Quote Link to comment https://forums.phpfreaks.com/topic/172869-organizing-mysql-data-with-php/#findComment-911202 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.