Organizing mySQL data with php

neocamel · 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!

mikesta707 · 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.

neocamel · 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?

ignace · 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..

mikesta707 · September 2, 2009

yeah do what ignace said. much better solution.

neocamel · 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>';
    }
}
?>

neocamel · September 2, 2009

let me also say in advance, thanks for the help!

Sign In

Organizing mySQL data with php

Recommended Posts

neocamel

Link to comment

Share on other sites

mikesta707

Link to comment

Share on other sites

neocamel

Link to comment

Share on other sites

ignace

Link to comment

Share on other sites

mikesta707

Link to comment

Share on other sites

neocamel

Link to comment

Share on other sites

neocamel

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information