PHP form pull MySQL data for field when another field is selected

tycoonbob · January 2, 2014

Hi everyone.

So I have a HTML form using PHP to pull a column from a MySQL table. However, I have a second field in my form that I want to pull a second row of data from that same table, when the first row is selected.

Example DB Table:

id=1 <---This is Primary Key

name=ItemA <---This is the data that shows in the drop down list

sku=1234 <---This is the data I want to throw into $_POST['sku'] when something is selected in name

Here is the code I currently have for my form:

<form method="POST" action="submitadd.php" />
<table id="add">


<tr>
<td class="headings"><b>Species:</b></td>
<td><select name=species:>
      <option value="select">Choose a Species</option>
      <?php
      $prodquery="SELECT name FROM products ORDER BY name ASC";
      $result=mysqli_query($con,$prodquery) or die(mysqli_error($con));
      while ($row = mysqli_fetch_array($result)) {
        echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
      }
      ?>
    </select>
</td>
</tr>


<tr>
<td class="headings"><b>SKU:</b></td>
<td><input type="text" name="sku" value="<?php echo $row['sku']; ?>" readonly="readonly" size="35" /></td>
</tr>

Currently, the SKU field is a readonly field but I want it to pull data from the database when someone makes a select on the dropdown above. I assume this will require javascript? I have no experience with javascript, and was hoping someone could help me out or at least point me in the right direction. I don't have a clue how to search for this on Google.

Thanks.

Edited January 2, 2014 by tycoonbob

JIXO · January 2, 2014

Hi, you will need AJAX to accomplish this, here is an exact example.

Barand · January 2, 2014

Why not select sku and name from the table then, in the dropdown, set the value to $row['sku'] instead of name. $_POST['species'] will then contain the required sku

tycoonbob · January 2, 2014

Hi, you will need AJAX to accomplish this, here is an exact example.

Thanks for the link. I have never touched AJAX before so I'm sure I will have questions, but I see at least what is going on with this. Hopefully I can make this work.

Why not select sku and name from the table then, in the dropdown, set the value to $row['sku'] instead of name. $_POST['species'] will then contain the required sku

If I change my query to "SELECT name, sku FROM products ORDER BY name ASC" my query breaks and no results are returned. I know the query is valid though, since I can run it against the database directly via phpMyAdmin. Can you provide a little more detail on what you are suggesting here?

Barand · January 2, 2014

When you say "it breaks" are you getting an error message? If so, what?

tycoonbob · January 3, 2014

When you say "it breaks" are you getting an error message? If so, what?

No error message. The drop down shows values, but the values all just say "name". Like 40 of them.

Barand · January 3, 2014

post the code that's producing that

tycoonbob · January 3, 2014

add.php:

<html>
 <head>
  <link rel="stylesheet" type="text/css" href="css/styles.css" media="screen" />
  <title>Add Inventory</title>
  <script>
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getsku.php?q="+str,true);
xmlhttp.send();
}
</script>
 </head>
 <body>
<?php
session_start();


require_once('includes/config.inc.php');
require_once('includes/functions.inc.php');




$thisPage='add';
include('includes/navbar.inc.php');
?>


<h1>Add New Inventory Record</h1>
<form method="POST" action="submitadd.php" />


<table id="add">


<tr>
<td class="headings"><b>Species:</b></td>
<td><select name="species:" onchange="showUser(this.value)">
      <option value="select">Choose a Species</option>
      <?php
      $prodquery="SELECT name FROM products ORDER BY name ASC";
      $result=mysqli_query($con,$prodquery) or die(mysqli_error($con));
      while ($row = mysqli_fetch_array($result)) {
        echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
      }
      ?>
    </select>
</td>
</tr>


<tr>
<td class="headings"><b>SKU:</b></td>
<td><input type="text" name="sku" value="<div id="txtHint" />" readonly="readonly" size="35" /></td>
</tr>
</table>
<br />
<input type="submit" name="submit" value="submit" class="button1" />
</form>


</body>
</html>

getsku.php:

<?php
$q = intval($_GET['q']);


require_once('includes/config.inc.php');


$sql="SELECT sku FROM products WHERE name = '".$q."'";


$result = mysqli_query($con,$sql);


echo "<table border='1'>
<tr>
<th>SKU</th>
</tr>";


while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['sku'] . "</td>";
  echo "</tr>";
  }
echo "</table>";


mysqli_close($con);
?>

tycoonbob · January 3, 2014

Using Firebug with Firefox, I can see that the AJAX script is working, but it's calling the "name" incorrectly.

Example, the Species name is (Aulonocara rubescens "Ruby Red") but the $_GET that is being called is:

http://site.com/getsku.php?q=Aulonocara%20rubescens%20%22Ruby%20Red%22

With getsku.php getting that variable for q= by using $_GET, I am assuming it's getting:

Aulonocara%20rubescens%20%22Ruby%20Red%22

Instead of:

Aulonocara rubescens "Ruby Red"

So everything is working, but because the incorrect name, the SQL query is just returning no results.

Does this seem like my problem, and how can I fix it?

tycoonbob · January 3, 2014

Wait, I'm wrong. While the URL is wrong, Firebug is showing me this:

Params:

q Aulonocara rubescens "Ruby Red"

So it's seeing the variable correctly. So something must be wrong with getsku.php?

JIXO · January 3, 2014

In getsku.php try this :

$q = intval(html_entity_decode($_GET['q']));

tycoonbob · January 3, 2014

In getsku.php try this :
$q = intval(html_entity_decode($_GET['q']));

With this, I'm experiencing the same behavior, but still not working as expected.

Does this look right:

echo "<table border='1'>
<tr>
<th>SKU</th>
</tr>";


while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['sku'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

I just feel that the problem is somewhere with this. I can see that the AJAX function is working as it's pulling the name out correctly, but I can't confirm that the query in getsku.php is correct, or that it's returning the results correctly.

JIXO · January 3, 2014

Hi, you have added a div as the value of sku input element, try this for add.php :

<html>
 <head>
  <link rel="stylesheet" type="text/css" href="css/styles.css" media="screen" />
  <title>Add Inventory</title>
  <script>
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getsku.php?q="+str,true);
xmlhttp.send();
}
</script>
 </head>
 <body>
<?php
session_start();


require_once('includes/config.inc.php');
require_once('includes/functions.inc.php');




$thisPage='add';
include('includes/navbar.inc.php');
?>


<h1>Add New Inventory Record</h1>
<form method="POST" action="submitadd.php" />


<table id="add">


<tr>
<td class="headings"><b>Species:</b></td>
<td><select name="species:" onchange="showUser(this.value)">
      <option value="select">Choose a Species</option>
      <?php
      $prodquery="SELECT name FROM products ORDER BY name ASC";
      $result=mysqli_query($con,$prodquery) or die(mysqli_error($con));
      while ($row = mysqli_fetch_array($result)) {
        echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
      }
      ?>
    </select>
</td>
</tr>
</table>
<div id="txtHint"><b>SKU:</b></div>
<input type="submit" name="submit" value="submit" class="button1" />
</form>


</body>
</html>

getsku.php

<?php
$q = html_entity_decode($_GET['q']);


require_once('includes/config.inc.php');


$sql="SELECT sku FROM products WHERE name = '".$q."'";


$result = mysqli_query($con,$sql);


echo "<table border='1'>
<tr>
<th>SKU</th>
</tr>";


while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><input type='text' name='sku' value='" . $row['sku'] . "' readonly='readonly' size='35' /></td>";
  echo "</tr>";
  }
echo "</table>";


mysqli_close($con);
?>

And also you do not need to use intval(), hope this solves the problem.

tycoonbob · January 3, 2014

Hi, you have added a div as the value of sku input element, try this for add.php :

<html>
 <head>
  <link rel="stylesheet" type="text/css" href="css/styles.css" media="screen" />
  <title>Add Inventory</title>
  <script>
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getsku.php?q="+str,true);
xmlhttp.send();
}
</script>
 </head>
 <body>
<?php
session_start();


require_once('includes/config.inc.php');
require_once('includes/functions.inc.php');




$thisPage='add';
include('includes/navbar.inc.php');
?>


<h1>Add New Inventory Record</h1>
<form method="POST" action="submitadd.php" />


<table id="add">


<tr>
<td class="headings"><b>Species:</b></td>
<td><select name="species:" onchange="showUser(this.value)">
      <option value="select">Choose a Species</option>
      <?php
      $prodquery="SELECT name FROM products ORDER BY name ASC";
      $result=mysqli_query($con,$prodquery) or die(mysqli_error($con));
      while ($row = mysqli_fetch_array($result)) {
        echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
      }
      ?>
    </select>
</td>
</tr>
</table>
<div id="txtHint"><b>SKU:</b></div>
<input type="submit" name="submit" value="submit" class="button1" />
</form>


</body>
</html>

getsku.php

<?php
$q = html_entity_decode($_GET['q']);


require_once('includes/config.inc.php');


$sql="SELECT sku FROM products WHERE name = '".$q."'";


$result = mysqli_query($con,$sql);


echo "<table border='1'>
<tr>
<th>SKU</th>
</tr>";


while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td><input type='text' name='sku' value='" . $row['sku'] . "' readonly='readonly' size='35' /></td>";
  echo "</tr>";
  }
echo "</table>";


mysqli_close($con);
?>

And also you do not need to use intval(), hope this solves the problem.

Those changes to getuser.php did the trick. I've set the input type='hidden' also, since that is a field I don't care if it's seen. Working like a treat!

Thanks!

tycoonbob · January 3, 2014

Follow up question to this.

It seems to be working, for the most part. Some of the species names has characters in them which are not being escaped, such as " ' ", and " # ". This is not allowing the name to be pulled properly, thus creating an incomplete record in the database. A specific example is:

Cynotilapia afra "Lion's Cove"

Which is exactly how it appears in my database. When getsku.php runs, it is setting q= to:

Cynotilapia afra "Lion

stopping at the " ' ", which is why I assume it's not escaping properly. How can I make sure it escapes the characters like this?

The same thing is happening with:

Haplochromis sp. "#44 Thickskin"

Which is setting q= to:

Haplochromis sp. "

Other than this issue, things are working great.

mac_gyver · January 3, 2014

in general, any text content that you output on a html page, that could contain any html special characters in the content, should be passed through htmlspecialchars() when it is output, with the second parameter set to ENT_QUOTES, so that any html special characters in it won't break the html markup on the page.

tycoonbob · January 3, 2014

in general, any text content that you output on a html page, that could contain any html special characters in the content, should be passed through htmlspecialchars() when it is output, with the second parameter set to ENT_QUOTES, so that any html special characters in it won't break the html markup on the page.

Thanks. So I see a common thing out there is creating an html sanitizing function such as:

function htmlsan($htmlsanitize){
return $htmlsanitize = htmlspecialchars($htmlsanitize, ENT_QUOTES, 'UTF-8');
}

Is this the path I should be heading toward? Add this to my functions include, but then how/where would I use this in my script? Would this be used in getsku.php or would it be used in the main php in question (and yet, would it be in the AJAX script or in the div "txtHint"?

JIXO · January 3, 2014

Oh, sorry about that, I totally forgot the escaping part for SQL.

Instead of using htmlenitities(), I think you should use mysqli_real_escape_string() :

$sql="SELECT sku FROM products WHERE name = '".mysqli_real_escape_string($con, $q)."'";

Sorry for the late reply.

mac_gyver · January 3, 2014

@JIXO, while it's true that the external data being put into the query needs to be escaped, the current problem is because names containing html special characters are being echoed out in the html and the special characters are breaking the html. only a portion of the value is being submitted because of this.

another point, the value =' ... ' attribute should be the row id that corresponds to the name. this will result in the least amount of data being submitted and will result in the fastest sql query.

JIXO · January 4, 2014

@JIXO, while it's true that the external data being put into the query needs to be escaped, the current problem is because names containing html special characters are being echoed out in the html and the special characters are breaking the html. only a portion of the value is being submitted because of this.

It's strage to me that # character can break HTML page :

The same thing is happening with:

Haplochromis sp. "#44 Thickskin"

In either ways, this might help you tycoonbob if this is the case

      while ($row = mysqli_fetch_array($result)) {
        echo '<option value="' . $row['name'] . '">' . $row['name'] . '</option>';
      }

tycoonbob · January 4, 2014

Thanks for the continued help. I apologize for not updating with this info already, but I have made a few changes to reflect how I want to use this function. Here is my current getsku.php:

<?php
$q = html_entity_decode($_GET['q']);

require_once('includes/config.inc.php');

$sql="SELECT sku FROM products WHERE name = '".$q."'";

$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result))
  {
  echo "<td><input type='hidden' name='sku' value='" . $row['sku'] . "' readonly='readonly' size='35' /></td>";
  }

mysqli_close($con);
?>

Here is my current add.php (my main php page):

<html>
 <head>
  <link rel="stylesheet" type="text/css" href="css/styles.css" media="screen" />
  <title>Add Inventory</title>
  <script>
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getsku.php?q="+str,true);
xmlhttp.send();
}
</script>
 </head>
 <body>
<?php
session_start();

require_once('includes/config.inc.php');
require_once('includes/functions.inc.php');

// Check login status -- if not logged in, redirect to login screen
if (check_login_status() == false) {
  redirect('login.php');
}
$thisPage='add';
include('includes/navbar.inc.php');
?>

<h1>Add New Inventory Record</h1>
<form method="POST" action="submitadd.php" />

<table id="add">

<tr>
<td class="headings"><b>Species:</b></td>
<td><select name="species:" onchange="showUser(this.value)">
      <option value="select">Choose a Species</option>
      <?php
      $prodquery="SELECT name FROM products ORDER BY name ASC";
      $result=mysqli_query($con,$prodquery) or die(mysqli_error($con));
      while ($row = mysqli_fetch_array($result)) {
        echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
      }
      ?>
    </select>
</td>
</tr>

<div id='txtHint' />

<tr>
<td class="headings"><b>Fry Count:</b></td>
<td><input type="text" name="frycount" value="<?php echo $row['quantityfry']; ?>" size="35" maxlength="4" /></td>
</tr>

<tr>
<td class="headings"><b>Juvie Count:</b></td>
<td><input type="text" name="juviecount" value="<?php echo $row['quantityjuv']; ?>" size="35" maxlength="4" /></td>
</tr>

<tr>
<td class="headings"><b>Adult Count:</b></td>
<td><input type="text" name="adultcount" value="<?php echo $row['quantityadult']; ?>" size="35" maxlength="4" /></td>
</tr>

<tr>
<td class="headings"><b>Notes:</b></td>
<td><input type="text" name="notes" value="<?php echo $row['notes']; ?>" size="35" maxlength="255" /></td>
</tr>

<tr>
<td class="headings"><b>Location:</b></td>
<td><select name="location">
  <?php
  $options = set_and_enum_values($con, 'inventory', 'location');
  foreach($options as $option):
?>
    <option><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>
</tr>

<tr>
<td class="headings"><b>Owner:</b></td>
<td><select name="owner">
<?php
  $options = set_and_enum_values($con, 'inventory', 'owner');
  foreach($options as $option):
?>
    <option><?php echo $option ?></option>
<?php endforeach; ?>
</select></td>
</tr>

</table>
<br />
<input type="submit" name="submit" value="submit" class="button1" />
</form>

</body>
</html>

Now I've tried using:

$q = mysql_real_escape_string(html_entity_decode($_GET['q']))'

And things still worked, but it didn't fix my problem. I feel like it's breaking around the $_GET though, since When I select:
Cynotilapia afra "Lion's Cove"

My GET URL is:

GET site/app/getsku.php?q=Cynotilapia%20afra%20%22Lion

Which is incomplete, and the correct value is never set to sent to the MySQL query. The only thing I can figure is something is going wrong with the AJAX script around:

xmlhttp.open("GET","getsku.php?q="+str,true);

But I know absolutely nothing about AJAX, which is why I'm begging for help.

Edited January 4, 2014 by tycoonbob

JIXO · January 4, 2014

Hi tycoon, give this a try :

$q = html_entity_decode(urldecode($_GET['q']));

tycoonbob · January 4, 2014

Hi tycoon, give this a try :
$q = html_entity_decode(urldecode($_GET['q']));

With the above, I am still experiencing the same behavior, but the code works.

Do you not think the issue is related to the AJAX script? It just seems to me that since the GET address is incorrect, that the problem can't be with the getsku.php page, and the problem lies in the add.php page.

mac_gyver · January 4, 2014

only part of your data is being submitted because of the reason given in post #16 and #19.

if you look at the 'view source' of your form page, you will see that all the value is present, but only the part up to the ' is being submitted.

Sign In

PHP form pull MySQL data for field when another field is selected

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information