PHP MySQL Database Column Referencing

sorry for bothering you again but I'm really stuck!

How would it be possible to do an if statement referencing wherever or not data was from a certain column or to strip the last number in a column's name and reference it that way?

Basically I have an musicians database, where a person from the PEOPLE table is linked via ID to instruments in the INSTRUMENTS table, I have an e-mail search function that I need to send out the relative data to the relative instruments. 开发者_如何学运维So if someone plays guitar as their 2nd instrument and someone else plays it as their 5th I need the relevant grade, standard and comments to get sent with them in the e-mail function.

I want to get the comments[i] , grade[i], standard[i] of the matching instrument[i]

My tables layout: ( I know it isn';t very efficient and I will look into DB normalization in the future!)

---

TABLE:INSTRUMENTS

COLUMNS:

 id   instrument1   grade2  standard3   comments4   
      instrument2   grade2  standard2   comments2   
      instrument3   grade3  standard3   comments3   
      instrument4   grade4  standard4   comments4   
      instrument5   grade5  standard5   comments5

TABLE: PEOPLE

COLUMNS:

id  first  last  snumber  course  email  graduate  inumber

---

EDIT:

      while($getresults = mysql_fetch_assoc($result)){

      $peoplequery = "SELECT * FROM people WHERE id = {$getresults [id]}";
      $peopleresults = @mysql_query($peoplequery);
      $getpeopleresults = mysql_fetch_assoc($peopleresults);

 //add the details returned by the database to the table:


      $table .= "  
      <td><p>{$getpeopleresults [first]} {$getpeopleresults [last]}</p></td>
      <td><p>{$getpeopleresults [email]}</p></td>
      <td><p>{$getpeopleresults [course]}</p></td>
      <td><p>{$getresults [grade]}</p></td>
      <td><p>{$getresults [standard]}</p></td>
      <td><p>{$getresults [comments]}</p></td></tr>
      <tr><td colspan=8><hr size=1 color=gray></td></tr>
      }

  ";

}

---

You are feeling really stuck because you're trying to squash a square peg in a round hole. This would be really easy if you used a table layout that allowed you to use your DB the way DBs were intended to function.

The only hack that will work for now is to write crazy long SQL statements meticulously trying every possibility or concatenating all the fields and looking for partial matches. Please don't do that. Spend some time to redesign your table layout so you can use joins and query using standard SQL practices.

---

I agree with @Caleb But still you wanna be with this database design, you can do like this, this is most in-efficient way because of your DB structure.

I have not tested this, so there might be some syntax errors.

$selQuery = "select * from instruments LEFT JOIN people ON instruments.id=people.id where instrument1 like 'guitar' OR instrument2 like 'guitar' OR instrument3 like 'guitar' OR instrument4 like 'guitar' OR instrument5 like 'guitar'";

$resQuery = mysql_query($selQuery)

$peoples = array();
while($row = mysql_fetch_assoc($resQuery))
{
   for($i = 1; $i<= 5; $i++ )
   {
       if(strtolower($row['instrument'.$i]) == strtolower('Guitar'))
       {
            $people = array();
            $people['id'] = $row['id'];
            $people['first'] = $row['first'];
            $people['email'] = $row['email'];
            $people['instrument'] = $row['instrument'.$i];
            $people['grade'] = $row['grade'.$i];
            $people['standard'] = $row['standard'.$i];
            $people['comments'] = $row['comments'.$i];
            array_push($peoples , $people);
            break;
       }
   }
}

ok I have reviewed ur code,

make changes as below

//Find instruments searched for:

$query = "SELECT * from instruments where '$search' in (instrument , instrument2, instrument3, instrument4, instrument5)";

$result = @mysql_query($query);

//Start building the table:
    $table = "<th><p><b>You searched for $search</b></p></th>
        <table>
        <th> <p><b>Name </b></p></th>

    <th> <p><b>Email </b></p> </th>

    <th> <p><b>Course </b></p></th>

    <th> <p><b>Grade </b></p></th>

    <th> <p><b>Standard </b></p></th>

    <th> <p><b>Comments</b></p>

</th></tr>";


    $peoples = array();
          while($row = mysql_fetch_assoc($result)){

           if(strtolower($row['instrument']) == strtolower($search))
       {
            $people = array();
            $people['id'] = $row['id'];
            $people['instrument'] = $row['instrument'.$i];
            $people['grade'] = $row['grade'.$i];
            $people['standard'] = $row['standard'.$i];
            $people['comments'] = $row['comments'.$i];
            array_push($peoples , $people);
            continue;
       }

   for($i = 2; $i<= 5; $i++ )
   {
       if(strtolower($row['instrument'.$i]) == strtolower($search))
       {
            $people = array();
                $people['id'] = $row['id'];
                $people['instrument'] = $row['instrument'.$i];
                $people['grade'] = $row['grade'.$i];
                $people['standard'] = $row['standard'.$i];
                $people['comments'] = $row['comments'.$i];
                array_push($peoples , $people);
                break;
           }
       }

 }

       foreach($peoples as $people)
       {
          $peoplequery = "SELECT * FROM people WHERE id = '".$people[id]."'";

          $peopleresults = @mysql_query($peoplequery);

          $getpeopleresults = mysql_fetch_assoc($peopleresults);



  //add the details returned by the database to the table:





          $table .= " 

          <td><p>{$getpeopleresults[first]} {$getpeopleresults[last]}</p></td>

          <td><p>{$getpeopleresults[email]}</p></td>

          <td><p>{$getpeopleresults[course]}</p></td>

                  <td><p>{$people[grade]}</p></td>

          <td><p>{$people[standard]}</p></td>

          <td><p>{$people[comments]}</p></td></tr>

                  <tr><td colspan=8><hr size=1 color=gray></td></tr>

                  }



          ";

}