CODEWITHSUNDEEP
phphtmlmysql
envoys
envoys

Reputation: 3

Possibly simple PHP/MYSQL issue with retrieving and showing data

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.

Upvotes: 0

Views: 120

Answers (4)

Kel
Kel

Reputation: 7780

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 :)

Upvotes: 0

albertopq
albertopq

Reputation: 201

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.

Upvotes: 0

Alex Vidal
Alex Vidal

Reputation: 4108

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>";
}

Upvotes: 1

PeeHaa
PeeHaa

Reputation: 72672

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.

Upvotes: 0

Related Questions