I'm trying to get information from a list(listDIV) to show up in my form (formDIV), I'm getting the information from a database using JSON. I'm only getting the error "Database erro, please select something else." in my textarea field, which leads me to believe there is something wrong with my PHP.
HTML & JS:
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.0/jquery.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function() {
$("#productList").change(function() {
var action = $("#productForm").attr('action');
var form_data = {
id: $('#productList').val(),
name: $("#name").val(),
price: $("#price").val(),
img: $("#img").val(),
description: $("#description").val()
};
var id="something";
$.getJSON("getProduct.php",form_data,function(data){
switch(data.retval){
case 0: $("#name").value(data.data.prodName);
break;
default: $("#description").html("Database error, please select something else.");
break;
}
});
});
});
</script>
</head><body>
<div id="listDIV">
<select id="productList" name="productList" size="8">
<option value="123" class="prodID">Terminator Series</option>
<option value="124" class="prodID">2001 A Space Odyssey</option>
<option value="125" class="prodID">Serenity</option>
<option value="126" class="prodID">Alien Quadrilogy</option>
<option value="127" class="prodID">12 Monkeys</option>
<option value="128" class="prodID">Final Fantasy</option>
</select>
</div><div id="formDIV">
<form id="productForm" action="getProduct.php" method="post">
<input type="text" id="name" placeholder="Name" /><br />
<input type="text" id="price" placeholder="Price" /><br />
<input type="text" id="img" placeholder="Image" /><br />
<textarea id="description" placeholder="Description"></textarea><br />
<input type="submit" id="save" value="Save" />
</form>
</div>
</body></html>
PHP (getProduct.php):
<?php
ob_start();
session_start();
mysql_connect('localhost', 'root', 'root') or
die('Could not connect: ' . mysql_error());
mysql_select_db('productDB') or
die ('Can\'t use database: ' . mysql_error());
// retval: 0 - login ok, 1 - login failed, 2 - internal error
$json = array("retval" => 2, "data" => NULL, "debug" => "");
$id=json_decode($_REQUEST['id']);
$prodID=$id->id;
$sql="SELECT * FROM productTB WHERE prodID=" . $prodID;
$json['debug'] .= "SQL query was: ".$sql."
";
$result=mysql_query($sql);
if (!$result) {
$json['debug'] .= "SQL query failed
";
$json['debug'] .= "Other output: ". ob_get_contents();
ob_end_clean();
die(json_encode($json));
}
$count=mysql_num_rows($result);
if($count==1){
$json['retval'] = 0;
$json['data'] = mysql_fetch_assoc($result);
} else {
$json['retval'] = 1;
}
$json['debug'] .= "Other output: ". ob_get_contents();
ob_end_clean();
echo json_encode($json);
Table Structure for my DB:
--
-- Table structure for table `productTB`
--
CREATE TABLE `productTB` (
`prodID` int(11) NOT NULL,
`prodName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`prodPrice` decimal(10,2) NOT NULL,
`prodDesc` longtext COLLATE utf8_unicode_ci NOT NULL,
`prodImg` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`prodID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
You need to pass in the variable.. not the string
$sql="SELECT * FROM productTB WHERE prodID=" . $prodID;
Though I would recommend using preparedstatements
EDIT:
You are not passing ID to your php page.. Here's what you can access - These are what you are passing to your PHP -
Change
var form_data = {
name: $("#name").val(),
price: $("#price").val(),
img: $("#img").val(),
description: $("#description").val()
};
to
var form_data = {
id: $('#productList').val(), // <-- pass the id
name: $("#name").val(),
price: $("#price").val(),
img: $("#img").val(),
description: $("#description").val()
};
You either need to Pass ID to the page or query to get the ID first from the DB. Then you can use the ID in your query to get the results