根据使用php的下拉列表选择，从数据库中获取数据 [英] Want to fetch data from database based on dropdown list selection using php
问题描述
这是我的php文件;
<？php
$ servername = xxxxxxx ;
$ username = xxxxxx;
$ password = xxxxxx;
try {
$ conn = new PDO（mysql：host = $ servername; dbname = vnet，$ username， $密码）;
//将PDO错误模式设置为异常
$ conn-> setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
echoConnected successfully;
}
catch（PDOException $ e）
{
echo连接失败：$ e-> getMessage（）;
}
$ sql =SELECT id，planname，price FROM plan;
/ *您可以向sql语句添加order by子句，如果名称将按字母顺序显示* /
echo< select name = planname value =''>计划名称< / option>; //列表框选择命令
foreach（$ conn-> query（$ sql）as $ row）{//存储在$ row
echo中的数组或记录< option value = $ row [id] > $ row [planname]< / option>;
/ *选项值通过循环添加数组* /
}
echo< ; / select>; //关闭列表框
if（isset（$ _ REQUEST ['planname']））{
//连接应该在这个页面上
$ sql = mysql_query（plan plan from plan where planname =。$ _ REQUEST ['planname']）;
$ res = mysql_fetch_assoc（$ sql）;
echo $ res ['price']; die;
}
echo'< input type =text3name =price []id =pricevalue ='，$ row ['price']，' disabled =disabled/>';
？>
我在下拉列表中列出了该列表，但无法根据planname动态获取价格。任何人都可以帮助我出来这个？
$ sql = mysql_query（ = $ _ REQUEST [ 'planname']）。
您正在列计划名称中进行搜索，但通过定义< option> ; 作为
echo< option value = $ row [id]> $行[planname] LT; /选项>中;
您正在发送ID作为值。
所以你的查询应该是：
$ sql = mysql_query（select price from plan where id =$ _ REQUEST [ 'planname']）;
//更好：pdos准备语句
$ stmt = $ conn-> prepare（select sub_id from sub where sub_id =？）;
$ stmt-> execute（array（$ _ GET ['planname']））;
另请阅读其他评论。你正在混合mysql_ * api和PDO，你应该只使用PDO。 为什么我不应该在PHP中使用mysql_ *函数？ 看到这一点：我如何阻止SQL注入PHP？
您的代码的结构将使维护真的很麻烦，您应该首先做所有的逻辑工作，收集所有的数据和然后在下一步显示您的html和数据。
如何实施您的计划
您需要/可能想使用两个不同的脚本，以获得您的动态ui。 （你可以使用相同的文件，但事情会变得凌乱，最好分割任务）
1。前端：
如前所述，您应该以有意义的顺序结构化代码。您可以看到我首先设置数据库连接，然后进行查询并且已经获取结果。这样我就已经拥有了所有的数据，我开始输出其他的东西（如果发生错误，因为我注意到有一些无效的数据/任何我仍然可以重定向到另一个页面，因为没有一个头发送） 。
要启动输出，我在脚本中添加了一些基本的HTML结构，不知道你是否已经拥有，至少不在你的代码段中。
所以我添加了标题和正文，在标题中是JavaScript代码，它将执行对后端的请求并接收响应以作相应的操作。
注意：
我不太熟悉vanilla javascript，所以我刚刚遵循
教程 http://www.w3schools.com/ajax/ajax_php。 asp
我想你应该检查一下jQuery，如果还没有，这样做真的很容易。
除此之外，我减少了一些噪音，并使用其他代码格式化，基本上我不喜欢使用echo输出我的HTML，因为一些IDE不能做
我还添加了一个< p>< / p>
<？php
$ servername ='xxxxxxx';
$ username ='xxxxxx';
$ password ='xxxxxx';
try {
$ conn = new PDO（mysql：host = $ servername; dbname = vnet，$ username，$ password）;
$ conn-> setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
} catch（PDOException $ e）{
trigger_error（连接失败：$ e-> getMessage（））;
}
$ selectPlans =SELECT id，planname，price FROM plan;
$ rows = $ conn-> query（$ selectPlans） - > fetchAll（PDO :: FETCH_ASSOC）;
？>
<！DOCTYPE html>
< html>
< head>
< script type =text / javascript>
函数getPrice（id）{
var xmlhttp = new XMLHttpRequest（）;
xmlhttp.onreadystatechange = function（）{
if（xmlhttp.readyState === 4&& xmlhttp.status === 200）{
var jsonObj = JSON.parse（xmlhttp .responseText）;
if（jsonObj.success === true）{
document.getElementById（price）。value = jsonObj.price;
} else {
document.getElementById（price）。innerHTML = jsonObj.message;
}
}
};
xmlhttp.open（GET，ajax.php？id =+ id，true）;
xmlhttp.send（）;
}
< / script>
< / head>
< body>
< select name =plannameid =plannameSelectonchange =getPrice（this.value）>
<？php foreach（$ rows as $ row）：？>
< option value =<？= $ row ['id']？>><？= $ row ['planname']？>< / option>
<？php endforeach; ？>
< / select>
< input type =textname =price []value =id =pricedisabled =disabled>
< p id =error>< / p>
< / body>
2。后端:(在这种情况下叫做ajax.php）
一个简单的代码，没有什么特别之处。
第一步：验证输入。在这种情况下，我只需检查 $ _ GET -Array中是否存在id。我在我告诉前端的数组中使用了 json_encode（），无论操作是否成功。第一种失败的情况是如果没有id。
然后连接到数据库，询问错误，如果是这样，立即返回给用户（通过使用 echo ），再次通过 json_encoded 数组。
准备用于选择ID的价格的语句（我在这里跳过了错误，可能需要添加）。然后执行它。
检查它是否成功 - >返回json_encoded数组成功和价格，或再次设置成功false并返回数组与错误消息。
<？php
$ servername ='xxxxxxx';
$ username ='xxxxxx';
$ password ='xxxxxx';
if（！isset（$ _ GET ['id']））{
echo json_encode（array（'success'=> false，'price'=>'' message'=>'no id given'））;
退出;
}
try {
$ conn = new PDO（mysql：host = $ servername; dbname = vnet，$ username，$ password）;
$ conn-> setAttribute（PDO :: ATTR_ERRMODE，PDO :: ERRMODE_EXCEPTION）;
} catch（PDOException $ e）{
trigger_error（连接失败：$ e-> getMessage（））;
echo json_encode（array（'success'=> false，'price'=>''，'message'=>'shit happen'$ e-> getMessage（）））;
退出;
}
$ stmt = $ conn-> prepare（SELECT price FROM plan WHERE id =？）;
$ stmt-> execute（array（$ _ GET ['id']））;
$ result = $ stmt-> fetch（PDO :: FETCH_ASSOC）;
if（$ result === false）{
trigger_error（'Query failed：'。$ conn-> errorInfo（））;
echo json_encode（array（'success'=> false，'price'=>''，'message'=>'shit happen'））;
退出;
} else {
echo json_encode（array（'success'=> true，'price'=> $ result ['price']，'message'=>''
退出;
}
I have a php file and mysql database with fields named planname and price,and i want a dropdown list of all the planname from database and according to the planname the price of particular planname should be shown in text box below. Here is my php file;
<?php
$servername = xxxxxxx;
$username = xxxxxx;
$password = xxxxxx";
try {
$conn = new PDO("mysql:host=$servername;dbname=vnet", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
$sql="SELECT id,planname,price FROM plan";
/* You can add order by clause to the sql statement if the names are to be displayed in alphabetical order */
echo "<select name=planname value=''>Plan Name</option>"; // list box select command
foreach ($conn->query($sql) as $row){//Array or records stored in $row
echo "<option value=$row[id]>$row[planname]</option>";
/* Option values are added by looping through the array */
}
echo "</select>";// Closing of list box
if(isset($_REQUEST['planname'])){
// connection should be on this page
$sql = mysql_query("select price from plan where planname =".$_REQUEST['planname']);
$res = mysql_fetch_assoc($sql);
echo $res['price'];die;
}
echo '<input type="text3" name="price[]" id="price" value="', $row['price'], '" disabled="disabled" />';
?>
I got the list in dropdown but not able to get price according to planname dynamically.can anyone help me out of this?
$sql = mysql_query("select price from plan where planname =".$_REQUEST['planname']);
You are searching in the column planname, but by defining the <option>'s as
echo "<option value=$row[id]>$row[planname]</option>";
You are sending the id as value.
So your query should be:
$sql = mysql_query("select price from plan where id =".$_REQUEST['planname']);
// better: pdos prepared statements
$stmt = $conn->prepare("select sub_id from sub where sub_id = ?");
$stmt->execute(array($_GET['planname']));
Also read the other comments. You are mixing the mysql_* api and PDO, you should only use PDO. Why shouldn't I use mysql_* functions in PHP? And see this when you are at it: How can I prevent SQL injection in PHP?
The structure of your code will make maintainance really troublesome, you should first do all the logical work, gather all the data and then display your html and the data in the next step.
How to do implement your plan
You need / might want to use two different scripts, to get your dynamic ui. (You could use the same file but things could get messy and it is better to split tasks)
1. The frontend:
As previously said, you should structure code in a meaningful order. You can see I am first setting up the database connection, then doing the querying and already fetching of the result. This way I already have all the data needed before I start to output other stuff (if something goes wrong as in I notice there is something invalid with the data/whatever I could still redirect to another page as there has not been a header sent).
To start the output, I added some basic HTML structure to your script, don't know if you already had it, at least it is not in your snippet.
So I added header and body, in the header is the javascript code which will execute the request to the backend and receive the response to act accordingly.
Note:
I am not really familiar with vanilla javascript, so I just followed a tutorial http://www.w3schools.com/ajax/ajax_php.asp
I think you should check out jQuery if you haven't yet, it makes things really really easy.
Other than that I reduced some noise and used other code formatting than you, basically I don't like to use echo to output my HTML as some IDEs are not able to do syntax highlighting when done so.
I also added a <p></p> in which the error message can be displayed to the user, if something in the backend goes wrong.
<?php
$servername = 'xxxxxxx';
$username = 'xxxxxx';
$password = 'xxxxxx';
try {
$conn = new PDO("mysql:host=$servername;dbname=vnet", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
trigger_error("Connection failed: " . $e->getMessage());
}
$selectPlans = "SELECT id, planname, price FROM plan";
$rows = $conn->query($selectPlans)->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
function getPrice(id){
var xmlhttp = new XMLHttpRequest();
xmlhttp.onreadystatechange = function() {
if (xmlhttp.readyState === 4 && xmlhttp.status === 200) {
var jsonObj = JSON.parse(xmlhttp.responseText);
if(jsonObj.success === true){
document.getElementById("price").value = jsonObj.price;
}else{
document.getElementById("price").innerHTML = jsonObj.message;
}
}
};
xmlhttp.open("GET", "ajax.php?id=" + id, true);
xmlhttp.send();
}
</script>
</head>
<body>
<select name="planname" id="plannameSelect" onchange="getPrice(this.value)">
<?php foreach ($rows as $row): ?>
<option value="<?= $row['id'] ?>"><?= $row['planname'] ?></option>
<?php endforeach; ?>
</select>
<input type="text" name="price[]" value="" id="price" disabled="disabled">
<p id="error"></p>
</body>
2. The backend: (in this case called ajax.php)
A simple piece of code, nothing special to do.
First step: validating the input. In this case, I simply check if there is an id in the $_GET-Array. I used json_encode() on an array in which I tell the frontend whether the operation was successfull or not. The first case of failure would be if there was no id.
Then connect to the database, ask for errors and if so return them immediately to the user (by using echo), again via the json_encoded array.
Prepare the statement for selecting the price of the id (I skipped the error check here, you might want to add it). Then execute it.
Check if it was successfull -> return the json_encoded array as success and with the price, or set success false again and return the array with an error message.
<?php
$servername = 'xxxxxxx';
$username = 'xxxxxx';
$password = 'xxxxxx';
if(!isset($_GET['id'])){
echo json_encode(array('success' => false, 'price' => '', 'message' => 'no id given'));
exit;
}
try {
$conn = new PDO("mysql:host=$servername;dbname=vnet", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
trigger_error("Connection failed: " . $e->getMessage());
echo json_encode(array('success' => false, 'price' => '', 'message' => 'shit happened' . $e->getMessage()));
exit;
}
$stmt = $conn->prepare("SELECT price FROM plan WHERE id = ?");
$stmt->execute(array($_GET['id']));
$result = $stmt->fetch(PDO::FETCH_ASSOC);
if($result === false){
trigger_error('Query failed: ' . $conn->errorInfo());
echo json_encode(array('success' => false, 'price' => '', 'message' => 'shit happened'));
exit;
} else {
echo json_encode(array('success' => true, 'price' => $result['price'], 'message' => ''));
exit;
}
这篇关于根据使用php的下拉列表选择，从数据库中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
