Page 1 of 1

PHP MySQL - Searching for values in an array

Posted: Tue Dec 10, 2013 1:17 am
by unwiredphpnovice
I am trying to read values from a user input text form and compare them with the values in an array generated from a table (student) in a MySQL database (college). It is reading the values from the array but it is not executing any of the code after the point I indicated. It appears to be a problem with the form element. Any assistance is appreciated. Thanks

Here is the code

Code: Select all

require 'connectionparameters.php';
$link2 = mysql_connect (HOST, USER, PASS) or die(mysql_error());
  mysql_select_db ('college', $link2);
  $result2 = mysql_query("SELECT * FROM student");
    while($row2 = mysql_fetch_array($result2)){
      print_r($row2);

// This is where the code fails.

    if (isset($_POST['enrollButton'])){
      // DEBUG CODE echo "button clicked";
      if(rtrim($_POST['stu_name']) == "" or rtrim($_POST['student_id']) == ""){
        echo "<div style='color:red;font-size:large'>Please enter your name and student ID.</div>";
      }elseif ($row2[0] == rtrim($_POST['student_id']) && $row[1] == rtrim($_POST['stu_name'])){
          //set the studentFoundFlag
          $studentFound = 1;
          // DEBUG CODE echo "Student found";
        }else {
          // DEBUG CODE echo "Student ont found";
        }
    }// Ends while loop.
    }// Ends "when eroll button was clicked

Re: PHP MySQL - Searching for values in an array

Posted: Tue Dec 10, 2013 6:26 am
by Celauran
How does it fail? What errors are you seeing?

Re: PHP MySQL - Searching for values in an array

Posted: Wed Dec 11, 2013 3:05 am
by CoursesWeb
Hi
Check with var_export($_POST); to see you there are all the necesary post data received.

Re: PHP MySQL - Searching for values in an array

Posted: Wed Dec 11, 2013 6:38 am
by unwiredphpnovice
I reworked the code.

I am still getting errors though:

stu_name and student_id are columns in the student table and $_POST['stu_name'] and $_POST['student_id'] are input fields in the form element.

Code: Select all


if (isset($_POST['enrollButton'])){
      if($_POST['stu_name'] == "" || $_POST['student_id'] == ""){
        echo "<div style='color:red;font-size:large'>Please enter your name and student ID.</div>";
      }elseif (isset($_POST['stu_name']) && isset($_POST['student_id'])) {
        $link2 = mysql_connect (HOST, USER, PASS) or die(mysql_error());
        mysql_select_db ('college', $link2);

        // Parse error: syntax error, unexpected 'stu_name' (T_STRING) in C:\xampp\htdocs\thisfile.php on line 30

        $result2 =  mysql_query('SELECT * FROM student WHERE stu_name = "$_POST['stu_name']" AND student_id = "$_POST['student_id']"', $link2);
        $stu_found = mysql_num_rows($result2);
        if ($stu_found>=1){
          // Code to execute if student found 
        }
        }else {
          echo "<div style='color:red;font-size:large'>One or more of the fields you entered is invalid.</div>";
          echo "<div style='color:red;font-size:large'>Please enter your correct name and student ID.</div><br>";
        }
    }// Ends "when eroll button was clicked


Re: PHP MySQL - Searching for values in an array

Posted: Wed Dec 11, 2013 7:15 am
by Celauran

Code: Select all

        $result2 =  mysql_query('SELECT * FROM student WHERE stu_name = "$_POST['stu_name']" AND student_id = "$_POST['student_id']"', $link2);
There's the problem. Actually, a bunch of problems.

To address the parse error, you're enclosing the query in single quotes, which you're breaking with the single quotes around your array key. The query can be rewritten thus:

Code: Select all

$query = "SELECT foo, bar FROM student WHERE stu_name = '{$_POST['stu_name']}' AND student_id = {$_POST['student_id']}";
Of course, there remains the problem of passing unescaped user data into your query. You really ought to be using prepared statements. mysql_ functions are deprecated and do not support prepared statements. Take the time to familiarize yourself with PDO.

Re: PHP MySQL - Searching for values in an array

Posted: Thu Dec 12, 2013 12:45 am
by unwiredphpnovice
I have seen PDO before, just not familiar at all with it. I understand it is related to object oriented programming?

I reworked the code; it appears to indicate that a database connection has been made. Here is what I have so far:

Code: Select all


if (isset($_POST['enrollButton'])){
    // Checks to see if the text fields have been filled out.
    if($_POST['stu_name'] == "" || $_POST['student_id'] == ""){
        // If they have not been filled out then a request for user input is echoed.
        echo "<div style='color:red;font-size:large'>Please enter your name and student ID.</div>";
    }
    // Assigns the user input for the Name form field the name $stu_name.
    $stu_name = $_POST['stu_name'];
    // Assigns the user input for the Student Id form field the name $student_id.
    $student_id = $_POST['student_id'];
    // Assigns the newly opened link a handle
    $link2 = mysql_connect (HOST, USER, PASS) or die(mysql_error());
    // Selects the 'college' database for query.
    mysql_select_db ('college', $link2);
    // Makes a selection query for the column names 'student_id' and 'stu_name'.
    $result2 =  mysql_query("SELECT student_id,stu_name FROM student WHERE student_id = mysqli_real_escape_string($student_id) AND stu_name = mysqli_real_escape_string($stu_name)", $link2);

/*DEBUG CODE*/
    /*
    if ($link2 != NULL){
      echo "MySQL connected";
    }
    */   
    
    // This is where I am getting an error message *******
    
    // Loops through each item in the array and assigns the contents to the variable name $row2.
    while ($row2 = mysql_fetch_array($result2)){
        // Assigns the number or rows returned to the variable name '$stu_found'
        $stu_found = mysql_num_rows($result2) or die(mysql_error());
        // Checks to determine if user input filled.
        if (isset($_POST['stu_name']) && isset($_POST['student_id'])) {
            print_r($row2);
            if ($stu_found=1){
                
                // Student found code goes here
            }
            else {
                echo "<div style='color:red;font-size:large'>One or more of the fields you entered are invalid.</div>";
                echo "<div style='color:red;font-size:large'>Please enter your correct name and student ID.</div><br>";
            }// Ends if/else construct - ($stu_found>=1).
        }// Ends if (isset($_POST['stu_name']).
    }// Ends if (isset($_POST['enrollButton'])).
}// Ends while loop.

The error I am getting is:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\Assign2\assign2_courses.php on line 41

Re: PHP MySQL - Searching for values in an array

Posted: Thu Dec 12, 2013 1:09 am
by CoursesWeb
String values must be added between quotes.
Try this sql query:

Code: Select all

$query2 = "SELECT student_id, stu_name FROM student WHERE student_id = {$student_id} AND stu_name = '{$stu_name}'";
 

Re: PHP MySQL - Searching for values in an array

Posted: Thu Dec 12, 2013 2:11 am
by unwiredphpnovice
I am getting the same error message at the same line. Here is what the changes look like that you suggested:

Code: Select all

// Code
$query2 =  "SELECT student_id,stu_name FROM student WHERE student_id = mysqli_real_escape_string({$student_id}) AND stu_name = mysqli_real_escape_string('{$stu_name}')";
$result2 = mysql_query($query2, $link2);
// Code

Re: PHP MySQL - Searching for values in an array

Posted: Thu Dec 12, 2013 3:26 am
by CoursesWeb
Uncomment

Code: Select all

$query2 =  "SELECT student_id,stu_name FROM student WHERE student_id = ". mysqli_real_escape_string($student_id) ." AND stu_name = '".mysqli_real_escape_string($stu_name) ."'"; 

Re: PHP MySQL - Searching for values in an array

Posted: Thu Dec 12, 2013 6:22 am
by Celauran
You can't just embed a function call in the query string like that. You're also mixing mysqli_ and mysql_ functions. That's not going to work.

Re: PHP MySQL - Searching for values in an array

Posted: Thu Dec 12, 2013 7:28 pm
by unwiredphpnovice
I have made the corrections and the part that I was having trouble with before seems to be working however when I search for a student who' s name and student id are in not in the database it does nothing. I would like it to default to the else provision at the end of the code. Also what I am eventually trying to do is to search the course table and the student table. The student table for the name and id of the student entered in the form field and the course that was selected in the course selection menu.

Here's the code in it's entirety:

Code: Select all


// connection parameters file
require 'connect.php';
// Establishes a connection to MySQL.
$link1 = mysql_connect (HOST, USER, PASS) or die(mysql_error());
// Selects the colleg database
mysql_select_db ('college', $link1);
// Assigns the mysql_query to $result
$result1 = mysql_query("SELECT * FROM courses", $link1);
echo "Select a course name<br><br>";
echo "<select name='course' id='course'>";
// Assigns the $result1 to $row1(array) and loops through the array.
while($row1 = mysql_fetch_array($result1)){
    // Applies the second value of the $row array to the drop down list and assigns the first value of the array as its value.
    echo "<option value='$row1[code]'>$row1[course_name]</option>";
}// Ends first while loop.
mysql_close($link1);
// Finish off the select control.
echo "</select><br><br>";
// Checks to see if the enroll student button has been clicked.
if (isset($_POST['enrollButton'])){
    // Checks to see if the text fields have been filled out.
    if($_POST['stu_name'] === "" || $_POST['student_id'] === ""){
        // If they have not been filled out then a request for user input is echoed.
        echo "<div style='color:red;font-size:large'>Please enter your name and student ID.</div>";
    }
    // Assigns the newly opened link a handle
    $link2 = mysql_connect (HOST, USER, PASS) or die(mysql_error());
    // Selects the 'college database for query.
    mysql_select_db ('college', $link2);
    // Assigns the user input for the Name form field the name $stu_name.
    $stu_name = mysql_real_escape_string($_POST['stu_name']);
    // Assigns the user input for the Student Id form field the name $student_id.
    $student_id = mysql_real_escape_string($_POST['student_id']);
    // Makes a selection query for the column names 'student_id' and 'stu_name'.
    $result2 =  mysql_query("SELECT student_id,stu_name FROM student WHERE student_id = '{$student_id}' AND stu_name = '{$stu_name}'", $link2);
    // Tests to see if the result fot the query was sucessful. 
    if($result2 === FALSE){
      die(mysql_error());
      }
      //DEBUG CODE else{echo "Query successful";}
      
    // Loops through each item in the array and assigns the contents to the variable name $row2.
    while ($row2 = mysql_fetch_array($result2)){
        // Assigns the number or rows returned to the variable name '$stu_found'
        $stu_found = mysql_num_rows($result2) or die(mysql_error());
        // Checks to determine if user input filled.
        if (isset($stu_name) && isset($student_id) && isset($_POST['course'])) {
          // Tests to determine if any rows were returned matching the Name and Student ID.
            if ($stu_found==1){
              // DEBUG CODE print_r($row2);
              $course = mysql_real_escape_string($_POST['course']);
              $result3 = mysql_query("SELECT * FROM courses WHERE course_name = '{$course}'");
              $row3 = mysql_fetch_array($result3);
              print_r($row3);
              // Student found code goes here
            }else{
              echo "<div style='color:red;font-size:large'>One or more of the fields you entered are invalid.</div>";
              echo "<div style='color:red;font-size:large'>Please enter your correct name and student ID.</div><br>";
            }
        }// Ends if (isset($_POST['stu_name']).
    }// Ends while loop.
  }// Ends if (isset($_POST['enrollButton'])).


Re: PHP MySQL - Searching for values in an array

Posted: Fri Dec 13, 2013 6:46 am
by Celauran
mysql_query which, again, you shouldn't be using, returns false on error, not on an empty results set. Try counting the number of rows returned instead.

Re: PHP MySQL - Searching for values in an array

Posted: Sat Dec 14, 2013 6:30 pm
by unwiredphpnovice
Thanks. I change mysql_functions() to mysqli_functions() in all of the code and made all the adjustments. The code is still not able to print out the $row3 array. I think it may have something to do with the way I am trying to access selection list or perhaps even a problem with the selection list itself. The $result3 query was successful and should of returned the record in the courses table that included the course selected.

Here is the code for the selection list within the form:

Code: Select all

// prior php code 

echo "Select a course name<br><br>";
echo "<select name='course' id='course'>";
// Assigns the $result1 to $row1(array) and loops through the array.
while($row1 = mysqli_fetch_array($result1)){
    // Applies the second value of the $row array to the drop down list and assigns the first value of the array as its value.
    echo "<option value='$row1[code]'>$row1[course_name]</option>";
}// Ends while loop.

// php code continued

This is how I have included this in the html form element.

Code: Select all


<form action="index.php" method="post">
<?php
include "php_file.php";
?>
Please enter your name:<input type= 'Text' value=''  name='stu_name' /><br>
Please enter your student ID<input type= 'Text' value=''  name='student_id' /><br><br>
<input type='submit' name='enrollButton'  value='Enroll Student' />
</form>

Also I am still not understanding why the script is not defaulting to the else provision when the $result2 query did not return any rows. I tried it using an elseif construct ( elseif($stu_found==0){ code to execute } ) instead of just else and it still does not work.

Re: PHP MySQL - Searching for values in an array

Posted: Sun Dec 15, 2013 12:23 am
by CoursesWeb
The array string keys must be added between quotes:

Code: Select all

echo '<option value="'. $row1['code'] .'">'. $row1['course_name'] .'</option>';
 
- For $stu_found, try to see what values it has, with:

Code: Select all

var_dump($stu_found);
 

Re: PHP MySQL - Searching for values in an array

Posted: Sun Dec 15, 2013 3:22 am
by unwiredphpnovice
Thanks. I added the quotes and the concatenation to the array string keys.

Code: Select all

 

var_dump($stu_found); // prints: int(1)