current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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='[email protected]'");
$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='[email protected]'");
$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([email protected]) 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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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='[email protected]'");
//$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 = '[email protected]') AND Student_email <> '[email protected]';

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
share|improve this answer
    
Thank you, that worked. How would I go about using those values which for [email protected] would be 12 to pick out of the exam table the exam partners row to the point I could echo them out onto a page? –  madman May 1 '13 at 14:33
    
I'm getting an SQL error of Subquery returns more than 1 row. The table structure is as more or less how you hinted. What I am looking to do is to use 12 to select all fields of the two in this case exam partner's of [email protected]. One person could have many more partners, or just one in exam table. Hope that clears it up a bit more. Thanks for help so far. –  madman May 1 '13 at 14:43
    
Oh. I thought a student only had 1 partner. Then you probably have an exam_id field in your table. Do you want all the exam partners someone has had, or just his partners for a single exam ? –  Nicolas May 1 '13 at 14:57
    
When I've tried using the PHP I'm getting "mysql_query(): Unable to save result set" and "mysql_fetch_assoc() expects parameter 1 to be resource, boolean given". I've also tried the SQL (had to correct the field names for both) for fetching someone's partner and getting a syntax error of "right syntax to use near '== '[email protected]' LIMIT 0, 30' at line 4". Fetching all pairs works as intended. EDIT to respond ot your reply: All partners they have had. Yes, exam_id field is AI so it is different for each person but the partner_id is what is the same for each different partner. –  madman May 1 '13 at 15:08
    
Yes, exam_id field is AI so it is different for each person but the partner_id is what is the same for each different partnership. Hope that makes sense. Do ask if your still not sure. –  madman May 1 '13 at 15:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.