select multiple values from one condition (PHP/MySQL)

I have a table which holds details of exams taken. Each exam entered into this table is entered as a pair of people i.e. two people per exam performed so you have per exam, two people. Each person has their own unique id for each exam taken but each pair has a value called partner_id which is the same for them both.

What I am trying to do is extract the partner_id values from that table in order to then be able to use those values to find the partners of a said person and show/echo onto the page the exam result details of every partner that person has had exam with.

What I have tried so far is:

$partner_ider = mysql_query("SELECT partner_id as value1 from exam WHERE Student_email='eating@gnomes.com'");
$row1 = mysql_fetch_array($partner_ider);

while($row1 = mysql_fetch_array($partner_ider))
{
    echo $row1['value1'];
}

And this:

$result = mysql_query("SELECT * from exam WHERE Student_email='beating@dead.com'");
$row = mysql_fetch_array($result);

while ($row = mysql_fetch_array($result))
{
    echo $row['partner_id'];
}

The result these would give is 2 for that email of which has two entries in the exam table, what I was looking for was 1, 2 which are the values of the two partner_ids for this email's exam records. When I change the email to someone else who only has one, it returns nothing.

What I would be looking to do with the result of 1, 2 is to use those values to select all other people except the original person(eating@gnomes.com) and show their details from out of that table.

What I'm asking for is how would I go about doing the above as I haven't done something like this before?

Answers


In both code snippets, you have at line 2 a mysql_fetch_array() which should not be there. It fetches a row, puts it into $row1 and increments the internal pointer.

When you call mysql_fetch_array() in your while, it fetches the second record then the third etc. until all the rows have been processed.

You should, in both examples, remove the second line and try again.

$partner_ider = mysql_query("SELECT partner_id as value1 from exam WHERE Student_email='eating@gnomes.com'");
//$row1 = mysql_fetch_array($partner_ider);

while($row1 = mysql_fetch_array($partner_ider))
{
    echo $row1['value1'];
}

If your table structure is the following :

  • id
  • student_id
  • student_name
  • student_email
  • student_whatever
  • partner_id

The SQL query would look like

SELECT Student_email FROM exam WHERE partner_id IN (SELECT partner_id FROM exam WHERE student_Email = 'eating@gnomes.com') AND Student_email <> 'eating@gnomes.com';

But you should really split up your table. You have two entities (a student and an exam) and a joining table.

Student

  • id
  • name
  • email

Exam

  • id
  • name
  • (other exam data)

StudentGroup

  • exam_id
  • student_id
  • group

Need Your Help

Function crashes when using _mm_load_dp

c++ sse

I have the following function:

Ember CLI project crashes when I change CSS file. Am I missing something?

ember-cli

I'm pretty new to Ember but have used Ember CLI to create a project and am on my way. Lots to learn but I'm very impressed so far.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.