Possibly simple PHP/MYSQL issue with retrieving and showing data

Question

I have been racking my brains over this for a while now. Here is the data I have in the SQL data base as an example:

ID | TYPE | DATA  
1 | TXT | TEST  
2 | PHP | php  
3 | JS | JAVASCRIPT

That is just an example, there are multiple listing for TXT, PHP and JS throughout the table. What I want to do is retrive all the data and display it all into separate drop down/select boxes. Meaning, select box one would list all data with type TXT, select box two would list all data with type PHP and select box 3 would list all data with type JS. The only way I have came about doing this is doing individual sql queries for each different type. I know there is a way to do it all in 1 query and then display it the way I want to but I just can't seem to figure out how and I know its going to drive me nuts when someone helps and I see just how they did it.

Alex Vidal · Accepted Answer · 2011-01-05 18:56:11Z

1

The only way that I know of to get all of the data in one query is just to do a generic SELECT * FROM tbl, and then you can group them in the code:

$res = mysqli_query('SELECT * FROM tbl');

$data = array();
while($row = mysql_fetch_assoc($res)) {
    $type = $row['type'];
    $data[$type][] = $row;
}

// $data contains all of the record, grouped by the TYPE column

foreach($data as $type => $records) {
    echo "records for $type: <select>";
    foreach($records as $record) {
        echo "<option value='$id'>$id</option>";
    }
    echo "</select>";
}

answered Jan 5, 2011 at 18:56

Alex Vidal

4,10822 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

envoys Over a year ago

I feel like a dummy now haha thats exactly how I want to do it! Thanks!

PeeHaa · Accepted Answer · 2011-01-05 18:55:02Z

0

Just retrieve all records and loop through them using PHP. Use an iterator if the recordset is going to be huge to prevent using too much memory.

$lists = array();

foreach($recordset as $record) {
    $lists[$record['type']][$record['id']] = $record['data'];
}

Know you have an array containing all data.

answered Jan 5, 2011 at 18:55

PeeHaa

73.1k60 gold badges195 silver badges264 bronze badges

Comments

albertopq · Accepted Answer · 2011-01-05 18:57:07Z

0

Just order it by Type and make a loop using "foreach" into the results, changing of select box when the type is different than the preivous.

In this way you only loop once over the array.

answered Jan 5, 2011 at 18:57

albertopq

2012 silver badges5 bronze badges

Comments

Kel · Accepted Answer · 2011-01-05 19:00:13Z

0

You can do kind of grouping with "ORDER BY TYPE":

SELECT id, data
FROM table
ORDER BY type;

Then, in data output loop you can track current type, and build another select box once type changed:

$currentType = "no type";
while($row = mysql_fetch_assoc($res)) {
    if ($currentType != $row['type']) {
        $currentType = $row['type'];
        // start new select box here
    }
    // do some other work here
}

BTW, such approach looks like kind of hack :)

edited Jan 5, 2011 at 19:00

answered Jan 5, 2011 at 18:54

Kel

7,8103 gold badges32 silver badges39 bronze badges

Collectives™ on Stack Overflow

Possibly simple PHP/MYSQL issue with retrieving and showing data

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related