Can some1 help explain whats wrong with these joins or query

[Area_51]

Hello every1, I am abit stuck here, basically I have this query which seems to run fine if I run it in MySQL and displays correctly in browswer:

-----------------------------------------------------------------------

$query = \"SELECT student.studentID, student.forname,

student.surname, Staff_suggestions.suggestionID, Staff_suggestions.staffID, Staff_suggestions.projTitle, Staff_suggestions.projDescription, Preferences.dateDone,

Preferences.timeDone FROM student, Staff_suggestions,

Preferences WHERE Preferences.studentID = student.studentID AND

Preferences.ProjID = Staff_suggestions.suggestionID AND Preferences.approved =\'No\'\";

$result = mysql_query($query) or die (\"Couldn\'t execute query.\");

 

------------------------------------------------------------------------

Basically I am joining these three tables up (Preferences, student and Staff_suggestions), And I am using a couple of columns from each table and they match fine when displayed.

 

A While loop is used here And I am trying to link the \'Radio Buttons\' up so that when a user clicks on a particular record the correct rows are selected from each table. But for some reason this is not the case when i get to the next page i.e. The columns relating to the radio button is selected correctly but then the other columns from the other tables show data which is from completly different rows! What could be the cause here?

 

I mite have a problem with my PHP coding, if that could be the case does anyone have some useful coding which allows me to select the right rows?

 

Thanks

 

J

[pallevillesen]

Your code looks fine, probably a php problem.

 

Post code, I\'ll have a look. Your current posted sql code selects all records - so it\'s probably some problem with your \"subselecting\" code...

 

P.

[Area_51]

I think its something to do with this chunk of coding....

 

-------------------------------------------------------------------------

echo \"<form action=\'allocated_proj.php\' method=\'post\'>n\";

echo \"<table cellpadding=\'5\' border=\'1\' align=center>\";

echo \"<TR><TD><B>User Name</B><TD><B>StudentName</B></TD></TD>

<TD><B>Proposed By</B></TD> <TD><B>Project Title</B></TD>

<TD><B>Date Chose</B></TD><TD><B>Time Chose</B></TD></TR>\";

$counter=1;

while ($row = mysql_fetch_array($result))

{

extract($row);

echo \"<tr><td valign=\'top\'>n\";

echo \"<input type=\'radio\' name=\'studID\'

value=\'$studentID\'n\";

if ( $counter == 1 )

{

echo \"checked\";

}

echo \">

<INPUT TYPE =hidden name=\'projectTitle\' value =\'$projTitle\'>

<INPUT TYPE =hidden name=\'studID\' value =\'$studentID\'>

<INPUT TYPE =hidden name=\'sugID\' value =\'$suggestionID\'>

<INPUT TYPE =hidden name=\'projInfo\' value =\'$projDescription\'>

<font size=\'+1\'><b>$studentID</b></font>

</td><td>$forname $surname</td>

<td>$staffID</td><td>$projTitle</td>

<td>$dateDone</td><td>$timeDone</td>

 

</tr>\";

$counter++;

}

echo \"</table>\";

echo \"<p align=center><input type=\'submit\' value=\'Allocate This To Student\'>

</form>n\";

--------------------------------------------------------------------------------

I am not sure what is going wrong here because when I press submit and it goes to the next page (allocated_proj.php) it will allocate the wrong project to the student.

 

The username next to the radio button works fine but for some reason a different row is selected (from a different row). Shall I post the whole coding?

 

Basically I am using the $_GET method to retrieve the values in variables projectTitle, studID, sugID and projInfo thats why I have put them in textboxes. Good idea or not?

 

Thanks for trying to help me P,

 

J

[pallevillesen]

It look nearly ok to me...

 

You have a hidden field called StudentID along with the radiobuttoned one ? (This will probably submit the LAST rows value of StudentID no matter what you do with the radiobutton - I might be wrong?)

 

And the radiobutton has value=\'$student\'n - why not value=\"$studenID\" ...

 

(For some reason the forum printed {} as (} - but your code was ok).

 

Also - on allocated_proj.php:

 

echo all your variables to screen for debugging.

 

Let me know if anything helped...

 

P.

[Area_51]

hey biopv, thanks for you reply again. I have taken out the code:

 

<INPUT TYPE =hidden name=\'studID\' value =\'$studentID\'>

 

and then echoed the code to the next page i.e. allocated_proj.php and the

value of the $studentID is displayed correctly. However the when the other values are echoed the values till appear wrong i.e. showing the last rows from the other table (Staff_suggestions). I used to retrieve the variables otherwise they dont echo, what could be going wrong here still?

 

$studentID = $_POST[\"studID\"]; //this is correct as radio button

$studTitle = $_POST[\"projectTitle\"]; //shows wrong title

$suggestionID = $_POST[\"sugID\"]; //wrong suggestion id

$projDescription = $_POST[\"projInfo\"]; //wrong description

 

The last three variables above do not match with the table on the previous page i.e. not the correct rows.

 

Thanks

 

J

[pallevillesen]

Aaahhh..... ok....

 

No, off course they don\'t match.... Since you declare them in the last row of the table...

 

You need to do a new sql statement on the new page, using the POSTED studentID...

 

Or change your code so it only POSTS the correct values...

 

if ($counter ==1) {

echo "checked>"; // putting the checked in the right place

//echo ALL THE HIDDEN INPUT FIELDS HERE AS WELL, SO THEY ARE ONLY ECHOED ONCE - WITH THE CORRECT VALUES!!!

echo "<INPUT TYPE =hidden name=\'projectTitle\' value =\'$projTitle\'>

<INPUT TYPE =hidden name=\'studID\' value =\'$studentID\'>

<INPUT TYPE =hidden name=\'sugID\' value =\'$suggestionID\'>

<INPUT TYPE =hidden name=\'projInfo\' value =\'$projDescription\'> 

}

// if not counter ==1 then we just print the usual stuff... BUT WE DO NOT PUT IT IN HIDDEN FIELDS WHICH CONFUSES THE SCRIPT!!

echo "<font size=\'+1\'><b>$studentID</b></font>

</td><td>$forname $surname</td>

<td>$staffID</td><td>$projTitle</td>

<td>$dateDone</td><td>$timeDone</td>

</tr>"; 

$counter++;

[Area_51]

hey biopv, I have tried what you recommended and it managed to make progress. However this time the 1st row always works perfectly (i.e. all rows match). And when I choose the other rows (radio buttons) they seems to match the 1st row in the tables this time, its crazy! heres my changed code:

 

--------------------------------------------------------------------------------

{

 

echo \"<tr><td valign=\'top\'>n\";

echo \"<input type=\'radio\' name=\'studID\' value=\'$studentID\'n\";

if ( $counter == 1 )

{

echo \"checked >\";

echo \"<INPUT TYPE =hidden name=\'projectTitle\' value =\'$projTitle\'>

<INPUT TYPE =hidden name=\'sugID\' value =\'$var4\'>

<INPUT TYPE =hidden name=\'projInfo\' value =\'$projDescription\'>\";

}

echo \"

<font size=\'+1\'><b>$studentID</b></font>

</td><td>$forname $surname</td>

<td>$staffID</td><td>$projTitle</td>

<td>$dateDone</td><td>$timeDone</td>

</tr>\";

$counter++;

}

echo \"</table>\";

=========================================

 

I appreciate you trying to help me biopv, I am really puzzled with this you see. No one else seems to know the solution and I dont know any other way around it. If I knew anyother ways i.e. using radio buttons in loops while linking 3 tables together then im willing to try that,

 

Thanks,

 

J

[pallevillesen]

Hey again,

 

Not it\'s not crazy - it does what it should, since you would have to RELOAD page one in order to

 

1. change studentID

2. move the echo \"selected\" to the correct line (the line with the new studentID).

3. Move the hidden fields to the new row as well...

 

So.. this is the wrong approach... You should just skip all the HIDDEN fields and use studentID...

 

Then on page 2, do the sql query again, select * from students where studentID = \'$studentID\' limit 1 or something like that...

 

That\'s the only way to go...

 

You should clarify something for me - do you want to be able to select multiple students ?

 

(I fear the answer is yes, in which case we have some more work to do...) Anyway.. the correct strategy is to ONLY submit all the studentID\'s to page 2. Then we\'ll do the magic there...

 

P.

[Area_51]

hey, I think I get your idea on using another query on the second page. I dont want multiple students to be chosen at once. The aim really is to transfer all the columns from a selected row to another table (as a new row). Basically combining 3 tables together and grabbing the desired columns from each table to put into one table (if this makes sense that is).

 

I have managed to do a query on the second page where It selects all preferences by a particular student (i.e. where studentID = \'$studentID\'). Some students may have chosen more than once preference. This yields more than one row. I assume I have to do another table with a radio button here too?

 

I know your trying your best to help, thank you very much biopv,

 

Jass

[pallevillesen]

Yes, I think this is the way to go...

 

One possible alternative is to COMBINE all variables into a new variable like

 

$variable = $studentID.\",\".$project.\",\".$sugID;

 

then do then radio button as

 

echo \"<input type=\'radio\' name=\'studID\' value=\'$variable\'n\";

if ( $counter == 1 )

{

echo \"checked >\";

 

When this is submitted you should use

 

$variable = $_POST[\'variable\'];

$variable = explode($variable,\",\");

$studentID = $variable[0];

$project = $variable[1]l; and so on....

 

I\'m not entirely sure how your data looks like... are one of the pages online?

 

P.

[Area_51]

thats seems like a good solution! i didnt know it was possible that way. I will give this idea a try as well. My pages are actually online...

 

* My work is all located on the Universities Server (Apache)

 

* In order to view any of these pages outside the university campus a authentication box appears to have access, which is:

Username - student

Password - clarabel

 

This will then let you view my pages, the one to go through is:

 

http://swift.mis.coventry.ac.uk/~jsv2/Proj...taff_login.html

 

This is basically a simple staff login page I have created...

To log in as a staff (sample data) the details are:

 

Staff Login : ken

password : ken123

--------------------------------------------------------------

A menu should appear showing what this member can access (in staff_menu.php).

 

Within this menu is a link which displays \"View Student Preferences\" - this is the page (studentPreferences.php) where I have the error with the code.

It shows what my table looks like after combining three tables together i.e.

 

* \'User Name\' and \'Student Name\' columns are from the student table.

* \'Proposed By\' and \'Project Title\' columns are from the Staff_suggestions table

* The last two columns are retrieved from the Preferences table.

 

All information shown is correct (match appropriatly) but at the moment with the code I used from you previously only the 1st Row works correctly.

 

However which ever student ID i pick it will work fine, but the other rows are always from the first row in the table.

 

Thanks again biopv..... Has anyone ever done anything similar to what I am trying to do? Or am i just being abit akward?

 

Thanks again,

 

J

[pallevillesen]

Hi, sorry about a late reply... I\'m moving and hence busy... But I\'ll be back monday morning...

 

I can\'t get the login to work, password is apparently wrong, using ken and ken123....

 

I don\'t have time to look at this thouroughly now, but will do tomorrow morning - I\'ll answer monday morning..

 

Have a nice sunday,

 

P.

[Area_51]

hey biopv, not to worry i dont want u going out ur way. i think u must be putting these details on the pop up dialog box..... sorry....

 

the log in details to go past the pop-up dialog box is:

 

username : student

password : clarabel

 

then it shud let u see the web page... this is where u use:

 

username : ken, password ken123 (on the php page).

 

thanks again and happy moving....

 

J

[Area_51]

...oh yeah I did try yr latest suggestion and it seemed to make a better improvement than the rest of the ideas i.e. after doing

 

$variable = $_POST[\'var\']; //var is the name of the radio button

 

I then do a: echo $var;

which displays the results correctly! But when I try to to the explode funtion I can only manage to echo the 1st character of each variable e.g

 

$variable = explode($var,\",\");

$studentID = $var[0];

$projectTitle = $var[1];

$projDescription = $var[2];

 

echo $var;

// displays \'3,monty pythons quest,a snakes and ladders game 2 b dun in vb\'

 

echo $studentID; //displays 3

echo $projectTitle //displays ,

echo $projDescription; //displays m

 

thanks again,

 

J

[pallevillesen]

This one is easy...

 

explode (\",\", $var) instead of explode ($var, \",\");

 

Yes... syntax is sometimes not very logical.... But if everything else works, then you\'re very close now...

[Area_51]

YEEESSSSSS!!!! YIPPEE! IT WORKS! your the man! go on biopv, it works top notch! How can I ever thank yah? you seriously dont understand how much you have helped me here.

 

I will definately acknoledge you (or phpfreaks) on my project.

 

Thank you very much,

 

J

[pallevillesen]

Wonderfull to see happy man...

 

Congrats...

 

I\'m quite happy here as well, finally getting perl to do some heavy analyses, parsing result, putting them into correct DBs etc....

 

Beware of your field delimiter though (using \',\' might be a little risky, or at least check and replace for \',\' before joining the variables... )

 

Have fun,

 

P.