Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Jan 2012
    Posts
    49
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Query to pick up INT in database

    Guy's

    Please can someone help....

    I've a DB with some INT fields. They basically have a 1 or 0 in them indicating that they are a type of service.

    Code:
    mysql> SELECT * FROM products;
    +-----------+-----------+
    | service_a  | service_b   |
    +-----------+-----------+
    |(INT)         |(INT)      |
    |1              |0            |
    +-----------+-----------+
    My form is a select on another page -

    Code:
    <label for="services">Support Service:
            <select name="services" id="services" class="fs">
                <option value="All">All</option>
                <option value="service_a">Service A</option>
                <option value="service_b">Service B</option>
    </select></label>
    For the life of me I cant get a query to fetch the data, is there anyone out there that can help me on this?

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    That database data structure and the HTML data structure don't match.

    Is only one value allowed to be selected as the HTML indicates - if so then you should have one field in the database and give it a different value for each of the options.

    If more than one value is allowed to be set then checkboxes would be a better way of handling it in the HTML. Alternatively you'd need a "multiple" attribute on the select to indicate that more than one option can be selected at the same time.

    Even if multiples are allowed your database structure is still wrong. You should not have multiple fields in the one table containing services like that as adding an extra service would mean changing the table and also accessing the information for the services is far more difficult than it would be if you restructured it with a second table where each record contained the key from the first table and a field containing the name of a selected service. Then you'd simply retrieve all of the records with the main key in order to get a list of all the seoected services.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    stephen_ (06-20-2012)

  • #3
    New Coder
    Join Date
    Jan 2012
    Posts
    49
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Hi Stephen,

    I cant change the database as its set up so that validated information can be uploaded to it from a word form.

    My thinking is that if I use something like
    Code:
    if ($service_a == 1)
      {
      echo 'Service_A</br>'; 
      }
    which I've used to display the data, surely I can use something similar for a query no?

  • #4
    New Coder
    Join Date
    Jan 2012
    Posts
    49
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by felgall View Post
    That database data structure and the HTML data structure don't match.

    Is only one value allowed to be selected as the HTML indicates - if so then you should have one field in the database and give it a different value for each of the options.

    If more than one value is allowed to be set then checkboxes would be a better way of handling it in the HTML. Alternatively you'd need a "multiple" attribute on the select to indicate that more than one option can be selected at the same time.

    Even if multiples are allowed your database structure is still wrong. You should not have multiple fields in the one table containing services like that as adding an extra service would mean changing the table and also accessing the information for the services is far more difficult than it would be if you restructured it with a second table where each record contained the key from the first table and a field containing the name of a selected service. Then you'd simply retrieve all of the records with the main key in order to get a list of all the seoected services.
    Is there any way with the way the database structured as it is that will allow a query to pick up which services have 1 if wildcard searched for;
    e.g Someone types service_a, all records with 1 in service_a will output?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Please remember that I don't use PHP, so I might goof the PHP code, but the concepts here should be right:
    Code:
    <?php
        $sql = "SELECT service_a, service_b FROM products WHERE ...some condition...";
        $result = mysql_query( $sql ) or die( mysql_error() );
        $row = mysql_fetch_assoc($result);
        $selectA = ""; ( 
        $selectB = "";
        $selectAll = "";
        if ( $row["service_a"] == 1 && $row["service_b"] == 1 ) 
        {
            $selectAll = "selected";
        } else {
            if ( $row["service_a"] == 1 ) $selectA = "selected";
            if ( $row["service_b"] == 1 ) $selectB = "selected";
        }
    ?>
    <label for="services">Support Service:
            <select name="services" id="services" class="fs">
                <option value="All" <?php echo $selectAll; ?> >All</option>
                <option value="service_a" <?php echo $selectA; ?> >Service A</option>
                <option value="service_b" <?php echo $selectB; ?> >Service B</option>
    </select></label>
    But I certainly agree with Felgall that a pair of checkboxes would make much more sense here than that <select> would.

    That would be easy:
    Code:
    <?php
        $sql = "SELECT service_a, service_b FROM products WHERE ...some condition...";
        $result = mysql_query( $sql ) or die( mysql_error() );
        $row = mysql_fetch_assoc($result);
        $checkA = ( $row["service_a"] == 1 ) ? "checked" : "";
        $checkB = ( $row["service_b"] == 1 ) ? "checked" : "";
    ?>
    <label>
        <input type="checkbox" name="service_a" value="1" <?php echo $checkA; ?> />
        Service A
    </label>
    <label>
        <input type="checkbox" name="service_b" value="1" <?php echo $checkB; ?> />
        Service B
    </label>
    Would then need some minor mods to the code that processes the submit of this <form>, but those would be trivial.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    stephen_ (06-27-2012)

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Is there any way with the way the database structured as it is that will allow a query to pick up which services have 1 if wildcard searched for;
    e.g Someone types service_a, all records with 1 in service_a will output?
    Trivial.

    Code:
    $service = $_POST["service"];
    $sql = "SELECT * FROM products where $service = 1;"
    ...
    Though in this case I would expect to use a set of radio buttons or a <select> for the "service" in the <form>.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Jan 2012
    Posts
    49
    Thanks
    13
    Thanked 0 Times in 0 Posts
    @Old Pedant thanks for this i'll try in the morning, i've also tried another route that someone kindly made me look at using foreach - http://pastie.org/private/xcgurjmysgqyhik46xvyka

    Would there be any way to implement this with your idea?

    The services are listed (line 107)....

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    That code, including the <select>, is so different that what you presented in this forum that I'm not even sure there is any relelvancy to what I gave.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    Jan 2012
    Posts
    49
    Thanks
    13
    Thanked 0 Times in 0 Posts
    @Old Pedant Totally appriciate the help you've given - my reason for looking at another approach is to see if it could be an easier way to implement for what I'm trying to do.

  • #10
    New Coder
    Join Date
    Jan 2012
    Posts
    49
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thanks for helping ----

    Each service is an INT field in the DB so yes 1 field.

    Area is all in one varchar.

    Sorry about the design of this!

    Multiples would be cool - would really like the keyword (wildcard) text input to find any org_name and any services that are entered.

    e.g if user types in 'childminder' all records that have 1 in their childminder INT field to show up as well.
    Last edited by stephen_; 06-27-2012 at 12:34 PM.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    I got all your messages. I think I'll reply privately for now.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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