[SOLVED] multidimensional array and loop logic

[fingirl]

Please help if you can. I’m a bit new to PHP/MySQL and my logic is fuzzy and I can’t solve a problem I have.

 

I have a MySql table that contains information about people and their voiceover acting talents. E.g.

 

ID | firstname | lastname | talentaccents | talentcharacter | talentsoftsell

1  | John        | Smith      | yes              | no                  | yes

2  | Jan          | Doe        | no                | no                | yes

3  | Terry      | Towel      | yes              | yes                | yes

 

There are actually many more columns but this will do to illustrate my problem.

 

For each of the talent columns the contents of the cells can only be either yes or no (as input by administrator).

 

I am trying to set up a search facility for users using a web form with checkboxes so that the names of the people who match all of the talents checked will be displayed.

 

Here’s what the form with the checkboxes looks like.

 

 

<form action="detailedsearch.php" method="post">

 

<p>Accents: <input type = "checkbox" name= "talents[]" value = "talentaccents" /></p>

 

<p>Character: <input type = "checkbox" name= "talents[]" value="talentcharacter" /></p>

 

<p>Soft Sell: <input type = "checkbox" name= "talents[]" value = "talentsoftsell" /></p>

 

<input type="submit" value="Search" />

 

</form>

 

Inside “detailedsearch.php” I have this code:

 

<?php

 

    $talents = $_POST['talents'];

 

    $arraysize = sizeof($talents);

 

    // some code for opening db etc. goes here

 

    for($count=0; $count<$arraysize; $count++)

  {

      $talent=$talents[$count];

 

      $querytalents = "select firstname, lastname from talent where $talent = 'yes'";

      $queryresult = mysql_query($querytalents);

      $numrows = mysql_num_rows($queryresult);

 

      for ($i=0; $i<$numrows; $i++)

      {

          $row = mysql_fetch_array($queryresult);

 

          echo "<p><strong>Name: </strong>".$row[firstname] . " " . $row[lastname]. "</p>";

      }

 

    }

 

 

//more code here to free result etc.

 

Now this works very well but outputs DUPLICATE values because each person may have multiple talents.

 

What I want to do is

1. grab firstname lastname values for any of the matching checkboxes

2. store them in an array

3. compare all the rows

4. echo only one instance of each

 

I added this to the inside for loop:

 

    $talentresults = array(array($row[firstname], $row[lastname]));

 

Now I am stuck. If I print_r the contents of the array I have created naturally it only ever has one member because the member keeps getting overwritten. I figure I need yet another loop but the logic for populating a multidimensional array eludes me. I have read that I need an outer loop and an inner loop to get contents out of a multidimensional array but how to put things in?

 

I am hoping if I can populate the array then use array_unique() function I can echo out my results.

 

Any help will be much appreciated.

[btherl]

Edit: Oh wait, that's not right.. let me think some more

[btherl]

Ok.. solution 1 (in SQL)

 

Construct a query looking like "WHERE $talent = 'yes' OR $talent2 = 'yes' OR $talent3 = 'yes'".  For each talent after the first, you add another "OR".  Then use that query.

 

Solution 2 (in PHP)

 

Use the name as an array key:

 

$talentresults["$firstname-$lastname"] = array($row[firstname], $row[lastname]);

 

That can go in the inner loop, in place of the echoing out.

 

That will remove duplicates naturally, because any identical names will overwrite each other.  Then you can loop through that array and print out each name.

 

 

[benjaminbeazy]

$i = 0;
foreach($talents as $var => $val){

   if($i == 0){
      $where_clause = "`{$var}` = '{$val}'";
   }else{
      $where_clause .= "AND `{$var}` = '{$val}'";
   }
   $i++;
}

$queryresult = mysql_query("select firstname, lastname from talent where ".$where_clause) or die(mysql_error());

 

made some changes

[fingirl]

Ok.. solution 1 (in SQL)

 

Construct a query looking like "WHERE $talent = 'yes' OR $talent2 = 'yes' OR $talent3 = 'yes'".  For each talent after the first, you add another "OR".  Then use that query.

 

 

Thanks, but a couple of questions again I'm afraid.

 

In Solution 1 do you mean

 

$querytalents = "select firstname, lastname from talent where $talents[0] = 'yes' or $talents[1] = 'yes' or $talents[2] = 'yes'";

 

or are you suggesting that I don't need to put my checkbox data into an array?

 

That is should my html form be changed from:

 

<form action="detailedsearch.php" method="post">

 

<p>Accents: <input type = "checkbox" name= "talents[]" value = "talentaccents" /></p>

 

<p>Character: <input type = "checkbox" name= "talents[]" value="talentcharacter" /></p>

 

<p>Soft Sell: <input type = "checkbox" name= "talents[]" value = "talentsoftsell" /></p>

 

<input type="submit" value="Search" />

 

</form>

 

to:

 

<form action="detailedsearch.php" method="post">

 

<p>Accents: <input type = "checkbox" name= "talentaccents" /></p>

 

<p>Character: <input type = "checkbox" name= "talentcharacter" /></p>

 

<p>Soft Sell: <input type = "checkbox" name= "talentsoftsell" /></p>

 

<input type="submit" value="Search" />

 

</form>

 

Sorry - I am confused. Lack of knowledge on my part.

[fingirl]

$i = 0;
foreach($talents as $var => $val){

   if($i == 0){
      $where_clause = "`{$var}` = '{$val}'";
   }else{
      $where_clause .= "AND `{$var}` = '{$val}'";
   }
   $i++;
}

$queryresult = mysql_query("select firstname, lastname from talent where ".$where_clause) or die(mysql_error());

 

 

I think I understand the logic of this and it looks really elegant but when you are setting the $where_clause variable does the $val variable refer to the value set for the checkbox in the html form? Because if so, I will need to change how my table is set up. At the moment I want to search for every time a certain row in the table has the word 'yes' in corresponding columns.

 

I did try your method in my existing code with existing table and get an error message that says: Unknown column '0' in 'where clause'

 

Thanks for the help though - I can keep experimenting along these lines.

[benjaminbeazy]

my example should work if you change the form code to this

 

let me know what you get

 

<form action="detailedsearch.php" method="post">

<p>Accents: <input type = "checkbox" name= "talentaccents" value="yes"/></p>

<p>Character: <input type = "checkbox" name= "talentcharacter" value="yes"/></p>

<p>Soft Sell: <input type = "checkbox" name= "talentsoftsell" value="yes"/></p>

<input type="submit" value="Search" />

</form>

[fingirl]

I can give this a go but if I change the form as you suggest then I will no longer have an array $talents will I? And that was what the foreach loop was applied to in your code snippet. Can I somehow reference the superglobal array in the code snippet you first provided? Apologies if I am being ridiculously dense. 

[benjaminbeazy]

no i am dense, i am extremely tired...

 

 

leave the form code how it was as the talents array, then change the foreach to this..

 

$i = 0;
foreach($talents as $var => $val){

   if($i == 0){
      $where_clause = "`{$val}` = 'yes'";
   }else{
      $where_clause .= "AND `{$val}` = 'yes'";
   }
   $i++;
}

$queryresult = mysql_query("select firstname, lastname from talent where ".$where_clause) or die(mysql_error());

 

does that make sense, only checkboxes that are check should post a variable to the form, so that way we can use the checkbox value as are field name and just put it equal to yes....

[fingirl]

BRILLIANT! And thanks so much. All good now. I did just have to change the AND to an OR because what I wanted  was people with any of the checkboxes checked not all of them.

 

If I also wasn't a tad on the tired side I may not have had to bother you with my second query.

 

All the best.

 

[benjaminbeazy]

I AM THE WINNER!!!!!!!1111111