Jump to content

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


tycoonbob

Recommended Posts

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 by tycoonbob
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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);
?>
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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"?

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

@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>';
      }
Link to comment
Share on other sites

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 by tycoonbob
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.