SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using PHP drop down to filter mysql table data

    Scenario

    I want users to be able to search for their correct printer cartridge/toner based on brand, model or type.

    I have an sql table called "printers" that has 8 columns:
    ID, brand, model, oem, description, type, yield_mono, yield_colour

    I wish to create 3 drop down lists in php for users to search on:

    brand or
    model or
    type

    I have created the code for "brand" but do not know how to add the other two. I have had a good fiddle around trying different options but to no avail.

    I'd really appreciate any help. Here's the code:

    <?php
    /*
    * Testing configuration
    */
    define('ENVIR',0);
    define('ENVIR_DEV',0);
    define('ENVIR_LIVE',1);

    /*
    * Data base credentials (replace this with your db credentials)
    */
    define('DB_USER','xxx');
    define('DB_PWD','xxx');
    define('DB_HOST','localhost');
    define('DB_NAME','xxx');

    /*
    * Connect to the database
    */
    try {
    $db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PWD);
    } catch(PDOExeption $e) {

    if(ENVIR == ENVIR_DEV) {
    echo '<p>',$e->getMessage(),'</p>';
    }

    echo '<p>Unable to connect to database<p>';
    exit;
    }

    /*
    * Template variables
    */
    $tpl = array(
    'filter'=>array(
    '#action' => $_SERVER['SCRIPT_NAME']
    ,'#method' => 'get'
    ,'brand' => array(
    '#values'=>array(
    array('value'=>'','label'=>'All')
    )
    )
    )
    ,'grid'=>array(
    'printers'=>array()
    )
    );

    $tpl = array(
    'filter'=>array(
    '#action' => $_SERVER['SCRIPT_NAME']
    ,'#method' => 'get'
    ,'model' => array(
    '#values'=>array(
    array('value'=>'','label'=>'All')
    )
    )
    )
    ,'grid'=>array(
    'printers'=>array()
    )
    );


    /*
    * Populate form filter brand options
    */
    $stmt = $db->query('SELECT * FROM printers GROUP BY brand ORDER BY brand ASC');

    if($stmt === false) {
    echo '<p>Unable to populate required data to build page.</p>';
    exit;
    }

    while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['filter']['brand']['#values'][] = array(
    'label' => $row['brand']
    ,'value' => $row['brand']
    ,'selected' => isset($_GET['filter'],$_GET['filter']['brand']) && $_GET['filter']['brand'] == $row['brand']
    );
    }




    /*
    * Populate user grid - by brand
    */
    $stmt = $db->prepare(sprintf(
    'SELECT * FROM printers %s'
    , isset($_GET['filter'],$_GET['filter']['brand']) && !empty($_GET['filter']['brand'])?'WHERE brand = :brand':''
    ));

    if($stmt === false) {
    echo '<p>Unable to populate required data to build page.</p>';
    exit;
    }

    $stmt->execute(isset($_GET['filter'],$_GET['filter']['brand']) && !empty($_GET['filter']['brand'])?array(':brand'=>$_GET['filter']['brand']):array());


    while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['grid']['printers'][] = $row;
    }





    /*
    * Start template output
    */
    ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8">
    <title></title>
    </head>
    <body>

    <!-- user filter template -->
    <form action="<?php echo $tpl['filter']['#action']; ?>" method="<?php echo $tpl['filter']['#method']; ?>">
    <fieldset>
    <legend>Find Your Toner</legend>
    <ul>

    <label for="filter-brand">Brand</label>
    <select name="filter[brand]" id="filter-brand">
    <?php
    foreach($tpl['filter']['brand']['#values'] as &$option) {
    printf(
    '<option value="%s"%s>%s</option>'
    ,htmlentities($option['value'])
    ,$option['selected']?' selected':''
    ,htmlentities($option['label'])
    );
    }
    ?>
    </select>

    <label for="filter-model">Model</label>
    <select name="filter[model]" id="filter-model">
    <?php
    foreach($tpl['filter']['model']['#values'] as &$option) {
    printf(
    '<option value="%s"%s>%s</option>'
    ,htmlentities($option['value'])
    ,$option['selected']?' selected':''
    ,htmlentities($option['label'])
    );
    }
    ?>
    </select>

    <label for="filter-type">Type</label>
    <select name="filter[type]" id="filter-type">
    <?php
    foreach($tpl['filter']['type']['#values'] as &$option) {
    printf(
    '<option value="%s"%s>%s</option>'
    ,htmlentities($option['value'])
    ,$option['selected']?' selected':''
    ,htmlentities($option['label'])
    );
    }
    ?>
    </select>


    <input type="submit" name="filter[submit]" value="Find">

    </ul>
    </fieldset>
    </form>

    <!-- data grid template -->
    <table width="100%">
    <caption>Results</caption>
    <thead>
    <tr>
    <th>Brand</th>
    <th>Model</th>
    <th>OEM</th>
    <th>Description</th>
    <th>Type</th>
    <th>Mono Yield</th>
    <th>Colour Yield</th>
    </tr>
    </thead>
    <tbody>
    <?php
    if(!empty($tpl['grid']['printers'])) {
    foreach($tpl['grid']['printers'] as &$printers) {
    printf(
    '<tr>
    <td>%s</td>
    <td>%s</td>
    </tr>'
    ,htmlentities($printers['brand'])
    ,htmlentities($printers['model'])
    ,htmlentities($printers['oem'])
    ,htmlentities($printers['description'])
    ,htmlentities($printers['type'])
    ,htmlentities($printers['mono_yield'])
    ,htmlentities($printers['colour_yield'])
    );
    }
    } else {
    echo '<tr><td colspan="2">No names available</td></tr>';
    }
    ?>
    </tbody>
    </table>

    </body>
    </html>

  2. #2
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    also for some strange reason i can only see results from first two columns

  3. #3
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have fixed it up and now have all the columns results showing, however the results seem to be truncated to only 11 characters including spaces but i cannot see anywhere that this is defined in the query, is there some default I am not aware of?

  4. #4
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by designtheweb1 View Post
    I have fixed it up and now have all the columns results showing, however the results seem to be truncated to only 11 characters including spaces but i cannot see anywhere that this is defined in the query, is there some default I am not aware of?
    Hello designtheweb1,

    I am also having the same problem. Can you please post your whole code..


    Thanks in advance..
    Jaya Gupta

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Can I suggest 2 sanity checks:

    1) look at the source code of your html results, sometimes some css rule you have accidentally applied can hide content.

    2) check your table definitions, if you have varchar(11) set as a definition it will be silently truncating text on save.

    Otherwise, show just the query you are talking about, and a sample output of that query result (leave the html out of the equation for now).

  6. #6
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks cups, can't find any varchar(11) in the code.

    Here's the dropdown box and i've included just one column here. so this: "GX3000, GX3" is truncating automatically at 11 characters, it should be much longer and include several codes


    <label for="filter-type">Type</label>
    <select name="filter[type]" id="filter-type">
    <option value="">All</option><option value="Color Laser">Color Laser</option><option value="Colour Lase">Colour Lase</option><option value="Dot Matrix/">Dot Matrix/</option><option value="Gel Printer" selected>Gel Printer</option><option value="Inkjet">Inkjet</option><option value="Inkjet Fax">Inkjet Fax</option><option value="Inkjet Mult">Inkjet Mult</option><option value="Inkjet Wide">Inkjet Wide</option><option value="Laser Fax">Laser Fax</option><option value="Line/Impact">Line/Impact</option><option value="Mono Laser">Mono Laser</option><option value="Printer">Printer</option><option value="Solid Ink">Solid Ink</option><option value="Solid Ink M">Solid Ink M</option><option value="Thermal Fax">Thermal Fax</option><option value="Thermal Pho">Thermal Pho</option> </select>


    <input type="submit" name="filter[submit]" value="Find">

    </ul>
    </fieldset>
    </form>

    <!-- data grid template -->
    <table width="100%" border="1">
    <caption>Results</caption>
    <thead>
    <tr>
    <th width="100" bgcolor="#99FFFF">Brand</th>
    <th width="200" bgcolor="#99FFFF">Model</th>
    <th width="75" bgcolor="#99FFFF">OEM</th>
    <th width="150" bgcolor="#99FFFF">Description</th>
    <th width="100" bgcolor="#99FFFF">Type</th>
    <th width="75" bgcolor="#99FFFF">Mono Yield</th>
    <th width="75" bgcolor="#99FFFF">Colour Yield</th>
    </tr>
    </thead>
    <tbody>
    <tr>
    <td>Ricoh</td>
    <td>GX3000, GX3</td>
    <td>405660</td>
    <td>Inkk Collec</td>
    <td>Gel Printer</td>
    <td>17,000</td>
    <td>17,000</td>
    </tr><tr>
    </tbody>
    </table>

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    How about you work out next if your mysql results show the truncated text?

    try adding this line of temp debug to your loops.
    PHP Code:
    var_dump($row); 

  8. #8
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks cups, ok did that and yes i see that it is putting a figure on the string i.e. string(11) but I don't know how it is doing that, so is there something i now need to put in the code to rectify that?

    This is the specific result:

    ["model"]=> string(11) "GX3000, GX3"


    This is part of the result:

    array(8) { ["ID"]=> string(4) "2696" ["brand"]=> string(5) "Ricoh" ["model"]=> string(11) "GX3000, GX3" ["oem"]=> string(6) "405660" ["description"]=> string(11) "Inkk Collec" ["type"]=> string(11) "Gel Printer" ["mono_yield"]=> string(6) "17,000" ["colour_yield"]=> string(6) "17,000" }

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    So, you are chasing this 'bug' back towards its source, the row dump suggests strongly that the result is coming directly from SQL.

    If this is the source SQL statement:

    SELECT * FROM printers GROUP BY brand ORDER BY brand ASC

    Then paste that directly into your database management tool (PhpMyAdmin or similar) and take a good look at the results.

    Is `model` truncated at that stage too?

  10. #10
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok i have solved the issue with help. it seems when i created the database it was defaulting to MyISAM. Instead I was advised that it should be MySQL which is also called InnoDB. I tried to change the existing database to no avail so what i did was create a new one, import the csv file then rename it to the original so i did not have to change my sql query. all tested and done now. I do have another issue that I want to address so will set up a new post for that, it is where I have 3 drop downs and I wish to populate the second one according to the first and third one according to the first and second. Thanks cups for your help and if you are feeling strong perhaps you might assist me with this one too. :-)

  11. #11
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    They are sometimes referred to as "chained selects", so you could look that phrase up.

    Essentially your solution will boil down to 1 of 2 options:

    a) Load all the options possible into JS arrays.

    b) Load the next array after a trip back to the server

    b) is frequently achieved using Ajax these days, much will depend on the possible size of the arrays.

  12. #12
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks cups, am now back on this problem to get it sorted out finally, moved house which is always distracting so have to see where i'm at.

  13. #13
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I have the same problem. I need to add one more dropdown 'status' so that when user searches the size and status the perticular information should be displayed. Please tell me how can I add another filter.
    <?php
    /*
    * Testing configuration
    */
    define('ENVIR',0);
    define('ENVIR_DEV',0);
    define('ENVIR_LIVE',1);

    /*
    * Data base credentials (replace this with your db credentials)
    */
    define('DB_USER','root');
    define('DB_PWD','');
    define('DB_HOST','localhost');
    define('DB_NAME','test');

    /*
    * Connect to database
    */

    try {
    $db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PWD);
    } catch(PDOExeption $e) {

    if(ENVIR == ENVIR_DEV) {
    echo '<p>',$e->getMessage(),'</p>';
    }

    echo '<p>Unable to connect to database<p>';
    exit;
    }

    /*
    * Template variables
    */
    $tpl = array(
    'filter'=>array(
    '#action' => $_SERVER['SCRIPT_NAME']
    ,'#method' => 'get'
    ,'size' => array(
    '#values'=>array(
    array('value'=>'','label'=>'All')
    )
    )
    )

    );
    $tpl = array(
    'filter'=>array(
    '#action' => $_SERVER['SCRIPT_NAME']
    ,'#method' => 'get'
    ,'status' => array(
    '#values'=>array(
    array('value'=>'','label'=>'All')
    )
    )
    )
    );
    /*
    * Populate form filter last name options
    */
    $stmt = $db->query('SELECT * FROM shasta GROUP BY size ORDER BY size ASC');

    if($stmt === false) {
    echo '<p>Unable to populate required data to build page.</p>';
    exit;
    }

    while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['filter']['size']['#values'][] = array(
    'label' => $row['size']
    ,'value' => $row['size']
    ,'selected' => isset($_GET['filter'],$_GET['filter']['size']) && $_GET['filter']['size'] == $row['size']
    );
    }

    /*
    * Populate user grid
    */

    // $sizevalue =

    $stmt = $db->prepare(sprintf(
    'SELECT * FROM shasta %s'
    , isset($_GET['filter'],$_GET['filter']['size']) && !empty($_GET['filter']['size'])?'WHERE size = :size':''
    ));



    if($stmt === false) {
    echo '<p>Unable to populate required data to build page.</p>';
    exit;
    }


    $stmt->execute(isset($_GET['filter'],$_GET['filter']['size']) && !empty($_GET['filter']['size'])?array(':size'=>$_GET['filter']['size']):array() );






    while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
    $tpl['grid']['shasta'][] = $row;
    }


    /*+
    * Start template output
    */
    ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8">
    <title>Names</title>
    </head>
    <body>

    <!-- user filter template -->
    <form action="<?php echo $tpl['filter']['#action']; ?>" method="<?php echo $tpl['filter']['#method']; ?>">

    <ul>
    <li>
    <label for="filter-last-name">Size</label>

    <select name="filter[size]" id="filter-last-name">
    <?php
    foreach($tpl['filter']['size']['#values'] as &$option) {
    printf(
    '<option value="%s"%s>%s</option>'
    ,htmlentities($option['value'])
    ,$option['selected']?' selected':''
    ,htmlentities($option['label'])
    );
    }
    ?>
    </select>


    <select name="filter[status]" id="filter-last">
    <?php
    foreach($tpl['filter']['status']['#values'] as &$option) {
    printf(
    '<option value="%s"%s>%s</option>'
    ,htmlentities($option['value'])
    ,$option['selected']?' selected':''
    ,htmlentities($option['label'])
    );
    }
    ?>
    </select>
    <input type="submit" name="filter[submit]" value="Show">
    <FORM>
    <INPUT TYPE="button" value="Insert" onClick="parent.location='sample1.php'">
    <INPUT TYPE="button" value="Update" onClick="parent.location='sample7.php'">

    </FORM>

    </li>



    </ul>

    </form>
    <table width="400" border="0" cellspacing="1" cellpadding="0">
    <tr>
    <td>
    <table width="400" border="1" cellspacing="0" cellpadding="3">
    <tr>
    <td colspan="4"><strong>List data from mysql </strong> </td>
    </tr>

    <tr>
    <td align="center"><strong>Sno</strong></td>
    <td align="center"><strong>Area</strong></td>
    <td align="center"><strong>Size</strong></td>
    <td align="center"><strong>Status</strong></td>

    </tr>
    <tbody>
    <?php
    if(!empty($tpl['grid']['shasta'])) {
    foreach($tpl['grid']['shasta'] as &$name) {
    printf(
    '<tr>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>

    </tr>'
    ,htmlentities($name['sno'])
    ,htmlentities($name['area'])
    ,htmlentities($name['size'])
    ,htmlentities($name['status'])
    );
    }
    } else {
    echo '<tr><td colspan="4">No names available</td></tr>';
    }

    ?>
    </tbody>


    </table>
    </td>
    </tr>
    </table>
    <!-- data grid template -->


    </body>
    </html>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •