How to Display Records from a Database in a Browser?_问答_开发者

I'm new to PHP and SQL, and I'm trying to display records from a database in a browser. I'm first trying to test out how to display records when a user selects 'SELECT' from a dropdown menu.

created a function called selectById()

// function selectById ---------------------------------------------------------------------------------------------
        function selectById($pUInput) {
            echo "select by id is being called" . "<br/>";

            $query = mysql_query("SELECT * FROM tblStudents 
                                WHERE id='$pUInput[0]'");
            $result = $db->query($query);

            if (!$row = mysql_fetch_array($sql))
                  {
                  die('Error: ' . mysql_error());
                  } else {
                  display($query, $result);
                  }
                //echo "selected" . "<br/>";
                //echo $pUInput[0];

        }

Inside selectById() -- display() is called. The display function is created to show the records of the database. Right now I'm trying to figure out how to get the two functions working.

// function display -------------------------------------------------------------------------------------------------
function display($pDisplay, $pResult) {
    echo "display is being called";
    $num_results = $results->numRows();

    //display each returned row
    for($i=0; $i<$num_results; $i++) {
        $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
        echo stripslashes($row[id]);
        echo stripslashes($row[first_name]);
        echo stripslashes($row[last_name]);
        echo stripslashes($row[major]);
        echo stripslashes($row[year]);      

    }

    }

PHP Code:

//Call function mainline
mainline();

// Declare the function mainline
function mainline() {

    $uInput = getUserInput();
    echo "Info from mainline...Action:" . " " . $uInput[5] . "<br/>";
    $connectDb = openConnect(); // Open Database Connection
    selectDb($connectDb); // Select Database
    doAction($uInput);
    //display();
    //closeConnect();

}

//Declare function getUserInput ------------------------------------------------------------------------------------
function getUserInput() {

    echo "In the function getUserInput()" . "<br/>";

    // Variables of User Input
    /*$idnum = $_POST["idnum"];              // id (NOTE: auto increments in database)
    $fname = $_POST["fname"];             // first name
    $lname = $_POST["lname"];            // last name
    $major = $_POST["major"];           // major
    $year = $_POST["year"];            // year
    $action = $_POST["action"];       // action (select, insert, update, delete)*/

    $idnum = mysql_real_escape_string($_POST["idnum"]);
    $fname = mysql_real_escape_string($_POST["fname"]);
    $lname = mysql_real_escape_string($_POST["lname"]);
    $major = mysql_real_escape_string($_POST["major"]);
    $year = mysql_real_escape_string($_POST["year"]);
    $action = mysql_real_escape_string($_POST["action"]);


    $userInput = array($idnum, $fname, $lname, $major, $year, $action);

    return $userInput;
}

// function doAction ----------------------------------------------------------------------------------------------
function doAction($pUserInput) {
    echo "In function doAction()" . "<br/>";

    if ($pUserInput[5] == "insert") {       
        //checkStudentFields();
        insert($pUserInput);    
        //echo "I need to insert!";

    } elseif ($pUserInput[5] == "select") {
        selectById($pUserInput);
        echo "I need to select";

    } elseif ($pUserInput[5] == "update") {
        update($pUserInput);
        echo "I need to update";

    } elseif ($pUserInput[5] == "delete") {
        deleteById($pUserInput);
        echo "I need to delete";

    }

}



// Create a database connection ------------------------------------------------------------------------------------
function openConnect() {
    $connection = mysql_connect("localhost", "root_user", "password");
        echo "Opened Connection!" . "<br/>";    
    if(!$connection) {
        die("Database connection failed: " . mysql_error());
    }
    return $connection;
}

// Select a database to ------------------------------------------------------------------------------------------- 
function selectDb($pConnectDb) {
    $dbSelect = mysql_select_db("School", $pConnectDb);
    if(!$dbSelect) {
        die("Database selection failed: " . mysql_error());
    } else {
    echo "You are in the School database! <br/>";   
    }

}

// Close database connection ------------------------------------------------------------------------------------
function closeConnect() {
    mysql_close($connection);
}

// function selectById ---------------------------------------------------------------------------------------------
function selectById($pUInput) {
    echo "select by id is being called" . "<br/>";

    $query = mysql_query("SELECT * FROM tblStudents 
                        WHERE id='$pUInput[0]'");
    $result = $db->query($query);

    if (!$row = mysql_fetch_array($sql))
          {
          die('Error: ' . mysql_error());
          } else {
          display($query, $result);
          }
        //echo "selected" . "<br/>";
        //echo $pUInput[0];

}


// function insert -------------------------------------------------------------------------------------------------
function insert($pUInput) {     
    $sql="INSERT INTO tblStudents (first_name, last_name, major, year)
          VALUES
         ('$pUInput[1]','$pUInput[2]','$pUInput[3]', '$pUInput[4]')";

        if (!mysql_query($sql))
          {
          die('Error: ' . mysql_error());
          }
        echo "1 record added";
}

// function update -------------------------------------------------------------------------------------------------
function update($pUInput) {
    $sql = "UPDATE tblStudents 
        SET first_name = '{$pUInput[1]}',
            last_name = '{$pUInput[2]}', 
            major = '{$pUInput[3]}', 
            year = '{$pUInput[4]}'
        WHERE id = '{$pUInput[0]}'";

    if(!mysql_query($sql))
    {
        die('Error: ' . mysql_error());
    }
    echo "1 record update";

}

// function delete -------------------------------------------------------------------------------------------------
function deleteById($pUInput) {
        // call select();
        $sql="DELETE FROM tblStudents WHERE id='$pUInput[0]'";
        $result=mysql_query($sql);

        if($result){
            echo "Deleted Successfully";
        }else {
            echo "Error";
        }       
}

// function display -------------------------------------------------------------------------------------------------
function display($pDisplay, $pResult) {
    echo "display is being called";
    $num_results = $results->numRows();

    //display each returned row
    for($i=0; $i<$num_results; $i++) {
        $row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
        echo stripslashes(开发者_运维技巧$row[id]);
        echo stripslashes($row[first_name]);
        echo stripslashes($row[last_name]);
        echo stripslashes($row[major]);
        echo stripslashes($row[year]);      

    }

}

SQL Syntax:

CREATE TABLE `tblStudents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `major` varchar(40) NOT NULL,
  `year` date NOT NULL,
  PRIMARY KEY (`id`)
)

I'm not sure if I'm missing something, but looking at the logic for "selectById", I can't quite tell how it is supposed to actually work.

The biggest problem is that I can't tell where you're getting some of your variables from, and it looks like you're using some of them in the wrong way.

Firstly, you call

$query = mysql_query("SELECT * FROM tblStudents WHERE id='$pUInput[0]'");

Which should create a resource in your '$query' variable. This is the plain old, non PDO, way of doing queries. It's important to realize, though, that what's going to be in "$query" is a resource, so if you printed it out, you'd get some identifier like "Resource id #6".

Right - then you seem to do the SAME THING (namely, perform a query) but this time using PDO, and you don't pass your '$db' object a string (which is what the first argument calls for), but rather you pass it a RESOURCE - the string representation of which isn't exactly valid SQL ;)

The other problem is that you haven't actually defined $db object anywhere ... so this is going to be a problem too.

I think that the major problem is that you're using a kind of mash up of PDO and straight forward mysql db access. Maybe take a look a quick squizz at the docs for both to get a clear understanding what the difference between them is. (sorry, I'm a new user, can't post real links)

http://www.php.net/manual/en/book.pdo.php http://www.php.net/manual/en/book.mysql.php

I hope I'm not misunderstanding your problem though. But if I'm reading your code properly I think you're just a little confused as to the use of these two (different) approaches to accessing your DB.