Professional Web Applications Themes

Can't figure out how to do this. Help please ? (Newbie) - PHP Development

Hi All, I have a mysql database table that includes 5 fields for skills, ie s1 ... s5. If the person has a skill then the field has a Y if not then it has a N. I've made a search form that has 5 tick boxes and on submit the form is passed to a php script. What I want is, if s1 is selected, everybody that has skill1 is produced. If s1 and s3 are selected everyone that has both these skills is produced etc. So if someone has s1, s2, s3 and s4 and I do a ...

  1. #1

    Default Can't figure out how to do this. Help please ? (Newbie)

    Hi All,

    I have a mysql database table that includes 5 fields for
    skills, ie s1 ... s5. If the person has a skill then the field has a Y
    if not then it has a N. I've made a search form that has 5 tick boxes
    and on submit the form is passed to a php script.
    What I want is, if s1 is selected, everybody that has skill1
    is produced. If s1 and s3 are selected everyone that has both these
    skills is produced etc. So if someone has s1, s2, s3 and s4 and I do a
    search for everyone that has s2 and s3 they should appear but they
    don't. I can see why but can't see how to do it.
    From the form I get 5 variables $s1 ... $s5, so if boxes 1 and
    3 are ticked I get $s1=Y $S2=N $s3=Y $s4=N and $s5=N. I then use a
    select query select * from skills where s1='$s1' and s2='$s2' and
    s3='$s3' and s4='$s4' and s5='$s5' But this will give me ONLY those
    that have s1 and s3 and miss everyone that has those 2 skills and
    others.
    I can get the correct answer by manually running select * from
    skills where s1='Y' and s2='Y' but, short of coding a select
    query for every combination of all 5 skills, I can't see how to get
    the results I want.
    Can anyone give me a hint, please ?

    Thanks,

    John.

    Jon Guest

  2. #2

    Default Re: Can't figure out how to do this. Help please ? (Newbie)

    Hi Jon,

    You may generate a query like this:

    $sql = "select * from skills where";
    $and = '';
    for ($i=1; $i<=5; $i++) {
    if ($_POST["s$i"] == 'Y') {
    $sql .= $and;
    $sql .= " s$i = 'Y'";
    $and = " AND";
    }
    }

    Nice detail: if you put more skills fields in the form and in the
    database, you only have to increase the 5 of the for statement.

    Greetings,

    Henk Verhoeven.

    Jon wrote:
    > Hi All,
    >
    > I have a mysql database table that includes 5 fields for
    > skills, ie s1 ... s5. If the person has a skill then the field has a Y
    > if not then it has a N. I've made a search form that has 5 tick boxes
    > and on submit the form is passed to a php script.
    > What I want is, if s1 is selected, everybody that has skill1
    > is produced. If s1 and s3 are selected everyone that has both these
    > skills is produced etc. So if someone has s1, s2, s3 and s4 and I do a
    > search for everyone that has s2 and s3 they should appear but they
    > don't. I can see why but can't see how to do it.
    > From the form I get 5 variables $s1 ... $s5, so if boxes 1 and
    > 3 are ticked I get $s1=Y $S2=N $s3=Y $s4=N and $s5=N. I then use a
    > select query select * from skills where s1='$s1' and s2='$s2' and
    > s3='$s3' and s4='$s4' and s5='$s5' But this will give me ONLY those
    > that have s1 and s3 and miss everyone that has those 2 skills and
    > others.
    > I can get the correct answer by manually running select * from
    > skills where s1='Y' and s2='Y' but, short of coding a select
    > query for every combination of all 5 skills, I can't see how to get
    > the results I want.
    > Can anyone give me a hint, please ?
    >
    > Thanks,
    >
    > John.
    >
    Henk Verhoeven Guest

  3. #3

    Default Re: Can't figure out how to do this. Help please ? (Newbie)

    Instead of using five fields to keep track of five skills, use a single
    unsigned integer (call it skill), where:

    skill=0: client has no skills
    skill=1: client has just skill #1
    skill=2: client has just skill #2
    skill=3: client has just skill#1 AND skill #2
    skill=4: client has just skill #3

    etc. up to

    skill=31: client has all 5 skills

    Then use the bitwise & operator to test for combinations of skills. For
    example, a client has skill #2 if skill&2==2. He has skill #4 if skill&8==8.
    He has both skill #2 and skill#4 if skill&10==10.

    "Jon" <jonjrussell.plus.com> wrote in message
    news:2qtl7091dif3825l46h316nb03vuvsmlcb4ax.com...
    > Hi All,
    >
    > I have a mysql database table that includes 5 fields for
    > skills, ie s1 ... s5. If the person has a skill then the field has a Y
    > if not then it has a N. I've made a search form that has 5 tick boxes
    > and on submit the form is passed to a php script.
    > What I want is, if s1 is selected, everybody that has skill1
    > is produced. If s1 and s3 are selected everyone that has both these
    > skills is produced etc. So if someone has s1, s2, s3 and s4 and I do a
    > search for everyone that has s2 and s3 they should appear but they
    > don't. I can see why but can't see how to do it.
    > From the form I get 5 variables $s1 ... $s5, so if boxes 1 and
    > 3 are ticked I get $s1=Y $S2=N $s3=Y $s4=N and $s5=N. I then use a
    > select query select * from skills where s1='$s1' and s2='$s2' and
    > s3='$s3' and s4='$s4' and s5='$s5' But this will give me ONLY those
    > that have s1 and s3 and miss everyone that has those 2 skills and
    > others.
    > I can get the correct answer by manually running select * from
    > skills where s1='Y' and s2='Y' but, short of coding a select
    > query for every combination of all 5 skills, I can't see how to get
    > the results I want.
    > Can anyone give me a hint, please ?
    >
    > Thanks,
    >
    > John.
    >

    Allan Abrahamse Guest

  4. #4

    Default Re: Can't figure out how to do this. Help please ? (Newbie)

    Allan Abrahamse <web1652verizon.net> wrote:
    > Instead of using five fields to keep track of five skills, use a single
    > unsigned integer (call it skill), where:
    > skill=0: client has no skills
    > skill=1: client has just skill #1
    > skill=2: client has just skill #2
    > skill=3: client has just skill#1 AND skill #2
    > skill=4: client has just skill #3
    > etc. up to
    > skill=31: client has all 5 skills
    > Then use the bitwise & operator to test for combinations of skills. For
    > example, a client has skill #2 if skill&2==2. He has skill #4 if skill&8==8.
    > He has both skill #2 and skill#4 if skill&10==10.
    yes or you can use 2^ :

    skill_1 = 2;
    skill_2 = 4;
    skill_3 = 8;
    skill_4 = 16;
    skill_5 = 32;
    ....
    skill_10 = 2048
    and so on...

    so :
    if $choice == 12 => skill_2 and skill_3
    if $choice == 64 => skill_6
    if $choice == 208 => skill_4 and skill_6 and skill_7
    if *choice == 1026 => skill_2 and skill_10
    --

    E -00 comme on est very beaux dis !
    ' `) /
    |\_ ==" { denisb laposte ... net }
    denisb Guest

  5. #5

    Default Re: Can't figure out how to do this. Help please ? (Newbie)

    Yes but $choice==12 means he has skill_2 and skill_3 and no other skills. I
    believe the original question was how to detect (say) people with skill_2
    and skill_3 irrespective of any other skills they may have.

    "denisb" <voirma.signature> wrote in message
    news:1gcdt6x.1dud9lbl1ahryN%voirma.signature...
    > Allan Abrahamse <web1652verizon.net> wrote:
    > > Instead of using five fields to keep track of five skills, use a single
    > > unsigned integer (call it skill), where:
    > > skill=0: client has no skills
    > > skill=1: client has just skill #1
    > > skill=2: client has just skill #2
    > > skill=3: client has just skill#1 AND skill #2
    > > skill=4: client has just skill #3
    > > etc. up to
    > > skill=31: client has all 5 skills
    > > Then use the bitwise & operator to test for combinations of skills. For
    > > example, a client has skill #2 if skill&2==2. He has skill #4 if
    skill&8==8.
    > > He has both skill #2 and skill#4 if skill&10==10.
    >
    > yes or you can use 2^ :
    >
    > skill_1 = 2;
    > skill_2 = 4;
    > skill_3 = 8;
    > skill_4 = 16;
    > skill_5 = 32;
    > ...
    > skill_10 = 2048
    > and so on...
    >
    > so :
    > if $choice == 12 => skill_2 and skill_3
    > if $choice == 64 => skill_6
    > if $choice == 208 => skill_4 and skill_6 and skill_7
    > if *choice == 1026 => skill_2 and skill_10
    > --
    >
    > E -00 comme on est very beaux dis !
    > ' `) /
    > |\_ ==" { denisb laposte ... net }

    Allan Abrahamse Guest

  6. #6

    Default Re: Can't figure out how to do this. Help please ? (Newbie)

    Allan Abrahamse wrote:
    > Instead of using five fields to keep track of five skills, use a single
    > unsigned integer (call it skill), where:
    >
    > skill=0: client has no skills
    > skill=1: client has just skill #1
    > skill=2: client has just skill #2
    > skill=3: client has just skill#1 AND skill #2
    > skill=4: client has just skill #3
    >
    > etc. up to
    >
    > skill=31: client has all 5 skills
    >
    > Then use the bitwise & operator to test for combinations of skills. For
    > example, a client has skill #2 if skill&2==2. He has skill #4 if
    > skill&8==8. He has both skill #2 and skill#4 if skill&10==10.
    >
    > "Jon" <jonjrussell.plus.com> wrote in message
    > news:2qtl7091dif3825l46h316nb03vuvsmlcb4ax.com...
    >> Hi All,
    >>
    >> I have a mysql database table that includes 5 fields for
    >> skills, ie s1 ... s5. If the person has a skill then the field has a Y
    >> if not then it has a N. I've made a search form that has 5 tick boxes
    >> and on submit the form is passed to a php script.
    >> What I want is, if s1 is selected, everybody that has skill1
    >> is produced. If s1 and s3 are selected everyone that has both these
    >> skills is produced etc. So if someone has s1, s2, s3 and s4 and I do a
    >> search for everyone that has s2 and s3 they should appear but they
    >> don't. I can see why but can't see how to do it.
    >> From the form I get 5 variables $s1 ... $s5, so if boxes 1 and
    >> 3 are ticked I get $s1=Y $S2=N $s3=Y $s4=N and $s5=N. I then use a
    >> select query select * from skills where s1='$s1' and s2='$s2' and
    >> s3='$s3' and s4='$s4' and s5='$s5' But this will give me ONLY those
    >> that have s1 and s3 and miss everyone that has those 2 skills and
    >> others.
    >> I can get the correct answer by manually running select * from
    >> skills where s1='Y' and s2='Y' but, short of coding a select
    >> query for every combination of all 5 skills, I can't see how to get
    >> the results I want.
    >> Can anyone give me a hint, please ?
    The above solutions might work but they sound overly complicated, or maybe
    it's just my shallow level of understanding :) I think it's easy enough to
    build a string like so:

    <?
    $sql = "select * from skills where ";
    ($s1 == "Y") ? ($sql .= "s1='$s1' and ");
    ($s2 == "Y") ? ($sql .= "s2='$s2' and ");
    ($s3 == "Y") ? ($sql .= "s3='$s3' and ");
    ($s4 == "Y") ? ($sql .= "s4='$s4' and ");
    ($s5 == "Y") ? ($sql .= "s5='$s5' and ");
    # Cut off the last and
    $sql = substr( $sql, 0, ( strlen($sql) - 5 ) );
    # Now we can just execute $sql
    ?>

    Of course, we're assuming that at least one of the variables will be a Y. If
    it's possible that none of them are, then you can build the initial $sql
    variable to not include the "where" part. I'm using part of the ternary
    operator above, simply to avoid a lot of if statements.
    [url]http://www.php.net/manual/en/language.operators.comparison.php[/url]

    ..:Albe

    --
    [url]http://www.ninja.up.ac.za[/url]
    .:Ninja Guest

Similar Threads

  1. can't figure it out; help
    By Henri in forum Macromedia Flash
    Replies: 1
    Last Post: April 15th, 08:00 PM
  2. Trying to figure out CF and IIS
    By Eddie McHam in forum Coldfusion - Getting Started
    Replies: 6
    Last Post: March 29th, 05:03 AM
  3. Trying to figure this out
    By dude9er webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 0
    Last Post: September 12th, 11:58 PM
  4. i can't figure out how to use MD5()
    By lawrence in forum PHP Development
    Replies: 3
    Last Post: August 17th, 09:29 PM
  5. trying to figure out CSS
    By Murray *TMM* in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 17th, 07:59 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139