GET issue

[JamesBowtell]

Hey all i'm having an issue with my GET method

i have one web form with a dropdown box with a list of Degree Schemes that are added to the drop down box using data from a MySQL database, this works fine.

When a user selects a Degree Scheme from the listbox and clicks the button the user will be directed to the studentDegree.PHP webpage where the result will display, but for some reason the GET method is not getting the information and not passing it in to the WHERE clause can anyone see my problem.

There is no error being reported, but it displays 'There are 0 student(s) studying this Degree'. I know each degree in the drop down box should have atleast 1 student associated with them!

Main

<?
// script to display all the degrees

// connection information
$hostName = "localhost";
$userName = "root";
$password = "12345";
$dbName = "SOC_DBtest";

// make connection to database
mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");

mysql_select_db($dbName) or die("Unable to select database $dbName"); 

// Select all the fields in all the records of the Employees table
$query = "SELECT *
          FROM tbl_degree";

$result = mysql_query($query);

// Determine the number of employees
$number = mysql_num_rows($result);

// Create drop-down menu of employee names 

print "View students enroled on a specified degree scheme:<p>
    <form action=\"StudentDegree.php\" method=\"get\">
    <select name=\"DegreeID\">
    <option value=\"\">Select a Degree</option>";

for ($i=0; $i<$number; $i++) {
     $DegreeId  = mysql_result($result,$i,"DegreeId");
     $Degree_Name = mysql_result($result,$i,"Degree_Name");
     print "<option value=\"$DegreeId\">$Degree_Name</option>";
}

print "</select><input type=\"submit\" value=\"submit\"
    name=\"submit\"></form>";

// Close the database connection
mysql_close();
?>

StudentDegree

<?
// script to display who has which computers

// connection information
$hostName = "localhost";
$userName = "root";
$password = "12345";
$dbName = "SOC_DBtest";


// make connection to database
mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");

mysql_select_db($dbName) or die( "Unable to select database $dbName".mysql_error()); 


// Select the fields from the appropriate tables
$query =


"SELECT tbl_student.Student_Id, tbl_student.Student_FName, tbl_student.Student_SName, tbl_degree.Degree_Name 
FROM tbl_degree INNER JOIN tbl_student ON tbl_degree.Degree_Id = tbl_student.Degree_Id
WHERE ((tbl_student.Degree_Id = {get'DegreeID'}))"; 


$result = mysql_query($query);

// Determine the number of records returned
$number = mysql_num_rows($result);


// Print the relevant information

print "There are $number student(s) studying this Degree:<p>";

for($i=0; $i<$number; $i++) {
     $studentId = mysql_result($result, $i, "student_Id");
     $stud_FName = mysql_result($result, $i, "Student_FName");
     $stud_SName = mysql_result($result,$i,"Student_SName");
     $stud_Degree = mysql_result($result,$i,"Degree_Name");
     print "$studentId - $stud_FName $stud_SName - $stud_Degree<br>";
}

// Close the database connection
mysql_close();
?>

thanks

[penagate]

Your issue is one of syntatic confusion

Request variables are stored in several arrays, POST in $_POST, GET in $_GET, and all in $_REQUEST.

The query should therefore be thus:

$query = 'SELECT `tbl_student.Student_Id`, `tbl_student.Student_FName`, `tbl_student.Student_SName`, `tbl_degree.Degree_Name` FROM `tbl_degree` INNER JOIN `tbl_student` ON `tbl_degree`.`Degree_Id` = `tbl_student`.`Degree_Id` WHERE `tbl_student`.`Degree_Id` = '.(int)$_GET['DegreeID'];

I am assuming, of course, that your ID is in fact an integer. If you are using a textual ID then you can use something like this:

[...] WHERE `tbl_student`.`Degree_Id` = \''.$_GET['DegreeID'].'\'';

[JamesBowtell]

cheers for the help but still doesn't work

[penagate]

OK, couple more things that should do it:

1. Each <option> tag should have a "name" attribute. That is the bit that gets passed in the request variable, not the text itself. For example, in a GET method form:
   

   <select name="var">
     <option name="something">Something</option>
   </select>

   will produce a query string of var=something.
   
2. It is more efficient to get an associative array from your query result, than to use individual mysql_result() calls to fetch each field. For example, you can replace your for() loop with this:
   

   while ($row = mysql_fetch_assoc($result)) {
     $studentId = $row['student_Id'];
     // etc...
   } 

[visualAd]

What a mess!!!! I suggest before continuing, you rewirte your code. Put the queries, etc. at the top of your script and load the data into an array. Then put the HTML at bottom of your script display the data.

Also, PHP is an HTML embedded scripting language. Not the other way around; print and echo shouldn't be used to display large chunks of HTML.

[JamesBowtell]

but doesn't my code do what you have done in the 1st option.....i'm kinda new at PHP and it took me forever to make this code...lol

[JamesBowtell]

getting really confused now...lol

[penagate]

I'm talking rubbish. value is correct. Not name.

Have you checked to see if the IDs are all correct?

[JamesBowtell]

yes they all correct

this is really bugging me now!

[JamesBowtell]

when i use GET method it, i can see it's working as it's putting the degree id in the address bar of internet explorer

if i select the 5 option in the dropdown box it's Degree_ID will appear in the address bar

http://localhost/assignment2/StudentDegree.php?DropDownBox=5&submit=submit

But why do i still get 'There are 0 student(s) studying this Degree:'

[visualAd]

but doesn't my code do what you have done in the 1st option.....i'm kinda new at PHP and it took me forever to make this code...lol

This is what I mean. I don't often do this, but I have rewritten the code to show you what I mean. No neeed to escape quotes now, no need to use the mysql_result function.

<?php
/* queries go at the top of the script */

// script to display all the degrees

// connection information
$hostName = "localhost";
$userName = "root";
$password = "12345";
$dbName = "SOC_DBtest";

// make connection to database
mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");

mysql_select_db($dbName) or die("Unable to select database $dbName"); 

// Select all the fields in all the records of the Employees table
$query = "SELECT *
          FROM tbl_degree";

$result = mysql_query($query);

$data = array();

/* loadthe data into an array read to display */
while(($row = mysql_fetch_assoc($result))) {
    $data[] = $row;
}
 
// print_r($data); // if you want to seethe structure of the array uncomment this line, it is useful for debugging

/* now the output at the bottom - nice and clean */
?>
<html>
  <body>
    <form action="StudentDegree.php" method="get">
      <select name="DegreeID">
        <?php foreach($data as $row): ?>
          <option value="<?php echo($row['DegreeId']) ?>"><?php echo($row['Desgree_Name']) ?></option>
        <?php endforeach; ?>
      </select>
      <input name="submit" value="submit" />
    </form>
  </body>
</html>

<?php
$degreeId = (int) $_GET['DegreeID']; // notice how this corresponds to the name of the select tag above

// script to display who has which computers


// connection information - ideally connection info should be in a differnt file
$hostName = "localhost";
$userName = "root";
$password = "12345";
$dbName = "SOC_DBtest";


// make connection to database
mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");

mysql_select_db($dbName) or die( "Unable to select database $dbName".mysql_error()); 


/* putting the query in a separate variable is good practice, don't get out of this habbit
   in databases that support it, it is also a good idea to use whitespace in th query to make
   it more readable - I also gave your column names aliaes, makng them easier to access in PHP */
$query =
"SELECT 
    tbl_student.Student_Id AS Student_Id, 
    tbl_student.Student_FName AS Student_FName,
    tbl_student.Student_SName AS Stdent_SName,
    tbl_degree.Degree_Name AS Degree_Name
 FROM 
    tbl_degree INNER JOIN tbl_student ON tbl_degree.Degree_Id = tbl_student.Degree_Id
 WHERE 
    tbl_student.Degree_Id = {$degreeId}"; 


$result = mysql_query($query);

/* Again the query rusults have been loaded into an array */
$data = array();

while (($row = mysql_fetch_assoc($result))) {
    $data[] = $row;
}

// Determine the number of records returned
$number = count($data);

/* again, the output is separate and clean */
// Print the relevant information
?>
<html>
  <body>
    <h1>Results</h1>
    <p>There are <?php echo($number) ?> student(s) studying this Degree:</p>";
    <ul>
      <?php foreach($data as $row): // notice how we have used the column alias names here?>
         <li><?php echo("{$row['Student_Id'} - {$row['Student_Fname']} 
                             {$row['Student_SName']} - {$row['Degree_Name']}") ?></li>
      <?php endforeach; ?>
    </ul>
  </body>
</html>

[JamesBowtell]

Same again keeps sayin 'There are 0 student(s) studying this Degree'

could it be something to do with my testing server...i'm using XAMPP

[visualAd]

What happens if you execute the query from the console? Use, echo($query) to getthe query text and paste it into the console. Does it look correct?

[JamesBowtell]

um beginner her...lol

[penagate]

Go into phpMyAdmin, pick aDB, and it will show you a box where you can run any query.

just go to localhost if you're using xampp and phpMyAdmin will be in the list on the left.

[visualAd]

You could also check the query is executing in the PHP by using:

if (! ($result = mysql_query($query))) {
    echo(mysql_error());
    die;
} 

If there are any errors, you'll now see them. Sorry, I should have mentioned that earlier.

[JamesBowtell]

done that if i do query and manually enter a where clause it works fine!

[JamesBowtell]

can i post my work for you to take a look at with my database?

[visualAd]

Well, if you don't get an error using the code from post 16, it means that the query has returned an empty result set.

[JamesBowtell]

Cheers guys for all your help finally got it working! u will be mentioned in my course work for helping!

[penagate]

If you don't mind me asking, what was the problem?

[JamesBowtell]

i used my original code i posted first

i just added

$dropdownbox = (int) $_GET['dropdownbox'];

to the result page

and added the variable to the where clause

cheers for all your help