duanmao1975 2014-05-10 11:15
浏览 90
已采纳

PHP MySQL查询数据库和WHERE子句

I am learning PHP and MySQL and am having one or two problems with the build.

I have an HTML form that the user inputs their details and the details of a dog. The script then checks the database for the users name and the dogs name. If both exist within the database then the user_ID on the dog table is changed to change the ownership. If the User does not exist then the users details will be inputted into the database and the ownership changed.

I did have the whole thing working but was not using bindParam for the collection from the form and was advised this would be a much better option. This is where the fun started. I am now able to count the rows on the table using the script below, however, I am not able to use the WHERE clause within the SELECT query. I have tried placing "WHERE name_first = :name_first" but this failed with a "Parameter not defined" error.

I need to be able to user both the first and last name of the user to be able to select that users ID from the database.

I also have an other question in regards to the use of prepared statements. If I use the statement at the top of the script to SELECT from the database and all the forms inputs are bound to $STH, how do I then run a different query, for instance how do I INSERT the user details into the database using the same binds?

Could someone please have a look at the script and tell me where I am going wrong please?

<?php

/***mysql username***/
$user = 'root';

/***mysql password***/
$pass = '';

if ($_SERVER['REQUEST_METHOD'] == "POST") {
try {                   
    $DBH = new PDO('mysql:host=localhost;dbname=kennel_cert;', $user, $pass);
    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    //Queries
    $sql1 = "SELECT user_ID FROM user_details";
    $sql2 = "SELECT dog_ID FROM dog_details";

    $STH = $DBH->prepare("SELECT * FROM user_details");  //Needs a WHERE clause to work
    //var_export($STH);

    //User details form
    $STH->bindParam(':name_first', $_POST['name_first']);
    $STH->bindParam(':name_last', $_POST['name_last']);
    $STH->bindParam(':email', $_POST['email']);
    $STH->bindParam(':telephone', $_POST['telephone']);
    $STH->bindParam(':name_number', $_POST['name_number']);
    $STH->bindParam(':street', $_POST['street']);
    $STH->bindParam(':city', $_POST['city']);
    $STH->bindParam(':county', $_POST['county']);
    $STH->bindParam(':postcode', $_POST['postcode']);

    //Dog details form
    $STH->bindParam(':dog_reg', $_POST['dog_reg']);
    $STH->bindParam(':name', $_POST['name']);
    $STH->bindParam(':microchip', $_POST['microchip']);
    $STH->bindParam(':gender', $_POST['gender']);
    $STH->bindParam(':day', $_POST['day']);
    $STH->bindParam(':month', $_POST['month']);
    $STH->bindParam(':year', $_POST['year']);

    $STH->execute();                                    //Execute the select script

    //Use this to count the users - However without the WHERE it is counting all users not the one submitted into the form
    if($STH->rowCount() > 0) {
        echo "Exists <br>"; }
    else {
        echo "Doesn't exist <br>"; }

    //var_export($userQuery);                           //Displays the contents of the query for testing

    //Find if user exists in database - Again another way of counting the total but not the one inputed into the form
    $userResult = $DBH->query($sql1);

    if ($userResult !== false) {
        $count = $userResult->rowCount();
        echo 'Number of users: '.$count. '<br>';

    foreach($userResult as $row) {
    echo $row['user_ID'].'<br>';
    }
    }

    //Find if dog exists in database - Again another way of counting the total but not the one inputed into the form
    $dogResult = $DBH->query($sql2);

    if ($dogResult !== false) {
        $count = $dogResult->rowCount();
        echo 'Number of dogs: '.$count. '<br>';

    foreach($dogResult as $row) {
    echo $row['dog_ID'].'<br>';
    }
    }



    } catch (PDOException $e) {
        echo $e->getMessage();
        }
        //echo "<p>Data submitted successfully</p>";
}       
//Disconnect from the server
$DBH = null;

?>

OK so I have changed the query to look like this:

$sql = "SELECT user_ID 
        FROM user_details 
        WHERE name_first = :name_first 
        AND name_last = :name_last";

$STH = $DBH->prepare($sql);

When I run this I get this error:

PDOStatement::__set_state(array( 'queryString' => 'SELECT user_ID FROM user_details        WHERE name_first = :name_first AND name_last = :name_last', ))

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

I am completely lost, I am going round in circle and can not find anything that is helping me to solve this.

I did have the script running as I stated using this setup, however, I was told to use the bindParam for the form and this is killing both the script and me.

<?php

/***mysql username***/
$user = 'root';

/***mysql password***/
$pass = '';

if ($_SERVER['REQUEST_METHOD'] == "POST") {
try {                   
    $DBH = new PDO('mysql:host=localhost;dbname=kennel_cert;', $user, $pass);
    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    //Queries
    $userQuery = $DBH->query("SELECT user_ID FROM user_details WHERE name_first = '$first' AND name_last = '$last'"); //Checks if the user exists in the database
    $dogQuery = $DBH->query("SELECT dog_ID FROM dog_details WHERE dog_ID = '$dog_reg' AND name = '$name' AND gender = '$gender'"); 

    //User details form
    $first = $_POST['name_first'];
    $last = $_POST['name_last'];
    $email = $_POST['email'];
    $telephone = $_POST['telephone'];
    $name_number = $_POST['name_number'];
    $street = $_POST['street'];
    $city = $_POST['city'];
    $county = $_POST['county'];
    $postcode = $_POST['postcode'];

    //Dog details form
    $dog_reg = $_POST['dog_reg'];
    $name = $_POST['name'];
    $microchip = $_POST['microchip'];
    $gender = $_POST['gender'];
    $day = $_POST['day'];
    $month = $_POST['month'];
    $year = $_POST['year'];

    $u = "";                                                //Variable for counting users
    $d = "";                                            //Variable for counting dogs



    //var_export($userQuery);                           //Displays the contents of the query for testing

    //Find if user exists in database
    foreach($userQuery as $row1) {                      //Count the number of users in the database
    $u++;
    }

    //Find if dog exists in database
    foreach($dogQuery as $row2) {                       //Count the number of dogs in the database
    $d++;
    }

    //The echos are for testing purposes
    echo "Dog ID is: ".$row2['dog_ID']."<br>";          //Finds the ID of the dog and displays it
    echo "User ID is: ".$row1['user_ID']."<br>";        //Finds the ID of the user and displays it
    $newUserID = $row1['user_ID'];                      //Store the ID for future use
    $newDogID = $row2['dog_ID'];                        //Store the ID for future use

    //Perform if both user and dog exist
    if ($u > 0 && $d > 0) {                             //If both the user and the dog exist in the database change the owner of the dog
        echo "Both Match";                              //Confirm both exist
        $q = $DBH->prepare("UPDATE dog_details SET user_ID = '$newUserID' WHERE dog_ID = '$newDogID'");  //update the table to change ownership
        $q->execute();                                  //Execute the change
        }

    // Perform if only dog exists
    elseif ($u == 0 && $d > 0) {                        //If the user does not exist but the dog does.
        echo "Dog matches but user does not exist";     //Confirm what exists
        //Insert user details into user_details table and set the foreign user_ID key in the dog_details table 
        $q1 = $DBH->prepare("INSERT INTO user_details (name_first,name_last,email,telephone,name_number,street,city,county,postcode) VALUES ('$first','$last','$email','$telephone','$name_number','$street','$city','$county','$postcode')");
        $q1->execute();
        echo "<br>Insert complete<br>";*/


        }
    elseif ($u > 0 && $d == 0) {
        echo "The dog does not exist - this is a problem";  
        //Form needs returning with values and asks user to check details
        }
    elseif ($u == 0 && $d == 0) {
        echo "Both don't match";
        }

    } catch (PDOException $e) {
        echo $e->getMessage();
        }
        //echo "<p>Data submitted successfully</p>";
}       
//Disconnect from the server
$DBH = null;

?>

展开全部

  • 写回答

1条回答 默认 最新

  • dpjtn48868 2014-05-10 11:45
    关注

    Check the manual you need to put the placeholders in the sql before binding parameters:

    $query = "SELECT * FROM user_details 
              WHERE name_first = :name_first 
                AND name_last = :name_last 
                AND email = :email
                etc...";
    
    $STH = $DBH->prepare($query);
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥100 二维码被拦截如何处理
  • ¥15 怎么解决LogIn.vue中多出来的div
  • ¥15 优博讯dt50巴枪怎么提取镜像
  • ¥30 在CodBlock上用c++语言运行
  • ¥15 求C6748 IIC EEPROM程序固化烧写算法
  • ¥50 关于#php#的问题,请各位专家解答!
  • ¥15 python 3.8.0版本,安装官方库ibm_db遇到问题,提示找不到ibm_db模块。如何解决?
  • ¥15 TMUXHS4412如何防止静电,
  • ¥30 Metashape软件中如何将建模后的图像中的植被与庄稼点云删除
  • ¥20 机械振动学课后习题求解答
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部