Skip to content
Advertisement

Display data from database using and tags

The main goal I try to get is, the correct display data from the database, let me try to explain with the code.

This is my form code for now:

<form action="" id="omnivaform" onsubmit="nextStep();" method="post">
        <input type="hidden" name="choose_omniva" />
        <div id="OmnivaSelector">
            <?php
            $omniva_variants_query = $db_link->prepare('SELECT COUNT(*) FROM `omniva` ORDER BY `region` ASC;');
            $omniva_variants_query->execute();
            if( $omniva_variants_query->fetchColumn() == 1)
            {
                $omniva_variants_query = $db_link->prepare('SELECT * FROM `omniva`ORDER BY `region` ASC;');
                $omniva_variants_query->execute();
                
                $omniva_variant_record = $omniva_variants_query->fetch();
                ?>
                <select id="omniva_selector" name="omniva_variant" style="display:none">
                    <option value="<?php echo $omniva_variant_record["id"]; ?>"><?php echo $omniva_variant_record["region"]; ?></option>
                </select>
                <?php
            }
            else
            {
                $omniva_variants_query = $db_link->prepare('SELECT * FROM `omniva` ORDER BY `region` ASC;');
                $omniva_variants_query->execute();
                ?>
                <div class="panel panel-primary">
                    <div class="panel-heading">Choose your Omniva</div>
                    <div class="panel-body">
                          <div class="form-group">
                            <select id="omniva_variant_selector" class="form-control" />
                            <?php
                            while( $omniva_variant_record = $omniva_variants_query->fetch() )
                            {
                                ?>
<optgroup label="<?php echo $omniva_variant_record["region"]; ?>">
<option><?php echo $omniva_variant_record["name"]; ?></option>
                                                            </optgroup>
                                <?php
                            }
                            ?>
                            </select>
                          </div>
                    </div>
                </div>
                <?php
            }
            ?>
        </div>
        </script>
    </form>

and the result that I get from this code is a dropdown selection which looks like this

Region 1
  address 1

Region 1
  address 2

Region 2
  address 1

Region 3
  address 1

But I need that this dropdown looks like this:

Region 1
  address 1
  address 2

Region 2
  address 1

Region 3
  address 1
  address 2
  address 3

Region 4
  address 1

If a region has more than one address, then these address show under these regions

Please don’t judge the code, I know this is not perfect, but everything is working, except the dropdown

Here are two images that explain how it’s now and how it should be

First image how it’s for now
Second image, how it’s should be looking

Thank you everyone for helping me!HTML

Advertisement

Answer

 <optgroup label="<?php echo $omniva_variant_record["region"]; ?>">
 <?php while( $omniva_variant_record = $omniva_variants_query->fetchAll() ): ?>     
     <option><?php echo $omniva_variant_record["name"]; ?></option>
 <?php endwhile; ?>
 </optgroup>

You need to use fetchAll() istead of fetch(). fetchAll() returns the values as an associative array.

Note that fetchAll() works only when PDO::ATTR_DEFAULT_FETCH_MODE is set to PDO::FETCH_ASSOC without which you need to supply PDO::FETCH_ASSOC as argument to fetchAll().

Also note that I have used the alternative syntax for the while loop. You are free to use any syntax but this alternate syntax makes the code much easier to read.

Quoting from PHP Manual

PHP offers an alternative syntax for some of its control structures; namely, if, while, for, foreach, and switch. In each case, the basic form of the alternate syntax is to change the opening brace to a colon (:) and the closing brace to endif;, endwhile;, endfor;, endforeach;, or endswitch;, respectively.

For more information visit PHP: Alternative syntax for control structures – Manual

Edit:

You can try this

<?php
    $outgroup = $db_link->prepare("SELECT region FROM `omniva` GROUP BY `region`;");
    $outgroup->execute();
    while ($outgroup_record = $outgroup->fetch()):
?>
        <optgroup label="<?php echo $outgroup_record["region"]; ?>">
            <?php   
            $address_options = $db_link->prepare("SELECT name FROM `omniva` WHERE region=:region GROUP BY name;");
            $address_options->bindParam('region', $outgroup_record['region']);
            $address_options->execute();
            while ($address_options_records = $address_options->fetch()): ?>    
            <option><?php echo $address_options_records["name"]; ?></option>
            <?php endwhile; ?>
        </optgroup>
<?php endwhile; ?>

This will first fetch the unique regions and then for each row returned it will fetch the name whose region matches the value of $outgroup_record['region']. Then proceed to output the result in a <option> tag. Modify the queries as per your need.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement