Ridgejp
asked on
Using Modal's in to Retrieve Data from MySql and Populate Forms
I had this question after viewing Use Select Query to Return Results as a Form.
Hi All,
I had the above question answered earlier today and was hoping to also research a similar solution using modals. I have a select query that's returning a data set using mysql and bootstrap. The code looks like this: -
I wanted to click on the 'Edit' button within the table and it open a form with more detailed information from my inventory table using another select query bound to the row it was linked to. The code provided looked like this (which was great): -
How do I use the same principle to trigger the same information but this time into a modal? I'm conformable with getting the modal to load via the button within the table, but how does the button toggle the data request and how does that look from a php perspective?
J
Hi All,
I had the above question answered earlier today and was hoping to also research a similar solution using modals. I have a select query that's returning a data set using mysql and bootstrap. The code looks like this: -
<table id="table" class="table table-bordered table-striped table-hover">
<thead>
<tr>
<th class="success text-center">Part_ID</th>
<th class="success">CAT_ID</th>
<th class="success text-center">Qty</th>
<th class="success text-center">Days.42</th>
<th class="success text-center">Days.14</th>
<th class="success text-center">+/-</th>
<th class="success">Supplier</th>
<th class="success text-center">SFP</th>
<th class="success text-center">Cost</th>
<th class="success text-center">Actions</th>
</tr>
</thead>
<tbody class="searchable">
<?php
$query = "select `partID`, `partDescription`, `qtyInStock`, `sfp`, round((sales42D/42),0) as sales42D, round((sales14D/14),0) as sales14D, round((sales14D/14)-(sales42D/42),0) as salesdiff,`supplierCompany`, `itemCost` FROM inventory";
if (!$result = $mysqli->query($query)) {
$err
= "QUERY FAIL: "
. $query
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
while ($row = $result->fetch_object()) {
$tr = <<<EOD
<tr>
<td class="text-center"> $row->partID </td>
<td> $row->partDescription </td>
<td class="text-center"> $row->qtyInStock </td>
<td class="text-center"> $row->sales42D </td>
<td class="text-center"> $row->sales14D </td>
<td class="text-center"> $row->salesdiff </td>
<td> $row->supplierCompany </td>
<td class="text-center"> $row->sfp</td>
<td class="text-center"> £$row->itemCost </td>
<td class="text-center">
<div class="dropdown">
<button type="button" class="btn btn-default btn-xs dropdown-toggle" data-toggle="dropdown">Options <span class="caret"></span></button>
<ul class="dropdown-menu" role="menu">
<li><a href="#">Edit</a></li>
<li><a href="#">Delete</a></li>
</div>
</td>
</tr>
EOD;
echo $tr;}
?>
</table>
I wanted to click on the 'Edit' button within the table and it open a form with more detailed information from my inventory table using another select query bound to the row it was linked to. The code provided looked like this (which was great): -
<li><a href="editform.php?partid=$row->partID"">Edit</a></li>
<?php
$partID = $_REQUEST[ 'partid' ]; //GET THE VALUE FOR postID from URL
$query = "select `partID`, `partDescription`, `qtyInStock`, `sfp`, round((sales42D/42),0) as sales42D, round((sales14D/14),0) as sales14D, round((sales14D/14)-(sales42D/42),0) as salesdiff,`supplierCompany`, `itemCost` FROM inventory WHERE partID=" . $partID . "";
if ( !$result = $mysqli->query( $query ) ) {
$err
= "QUERY FAIL: "
. $query
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error;
trigger_error( $err, E_USER_ERROR );
}
$row = $result->fetch_object();
?>
<form name="editform" action="post">
<table width="50%" border="0" cellpadding="2" cellspacing="2">
<tbody>
<tr>
<td>partID</td>
<td><input type="text" name="partID" value="<?php echo $row->partID; ?>">
</td>
</tr>
<tr>
<td>partDescription</td>
<td><input type="text" name="partDescription" value="<?php echo $row->partDescription; ?>">
</td>
</tr>
<tr>
<td>qtyInStock</td>
<td><input type="text" name="qtyInStock" value="<?php echo $row->qtyInStock; ?>">
</td>
</tr>
<tr>
<td>sfp</td>
<td><input type="text" name="sfp" value="<?php echo $row->sfp; ?>">
</td>
</tr>
<tr>
<td>sales42D</td>
<td><input type="text" name="sales42D" value="<?php echo $row->sales42D; ?>">
</td>
</tr>
<tr>
<td>supplierCompany</td>
<td><input type="text" name="supplierCompany" value="<?php echo $row->supplierCompany; ?>">
</td>
</tr>
<tr>
<td>itemCost</td>
<td><input type="text" name="itemCost" value="<?php echo $row->itemCost; ?>">
</td>
</tr>
</tbody>
</table>
</form>
How do I use the same principle to trigger the same information but this time into a modal? I'm conformable with getting the modal to load via the button within the table, but how does the button toggle the data request and how does that look from a php perspective?
J
ASKER
Hi Julian,
Thanks for the help and apologies for the delay in replying I've been out all day. Last night I started trying to work on my own fix based on examples I'd found on the internet ... it's only working in part but I may aswell as show you what I did and maybe you can help with where I'm falling over as I'm trying to understand as much as possible.
I found an example that was close to the type of select query I was using and downloaded it into my server and got it up and running based on the example shown ... I then slowly tried to weave it into my existing pages before I saw your example. It doesn't quite work and I wonder if you could point out (gently) the errors involved?
I modified my Edit button to the following: -
I then added the script example I found and just adjusted the sections I needed to open the modal once the button was clicked, which looks as follows: -
My modal code looks like this:-
Whilst the modal opens there's no data inside - the code inside my "testing_select.php" is as follows, as you can see I got a bit confused: -
I was working on the priniciple if I could get it to output the partID again I could bring in the other field later ... I'm sure it's not a million miles away so any input would be great.
J
Thanks for the help and apologies for the delay in replying I've been out all day. Last night I started trying to work on my own fix based on examples I'd found on the internet ... it's only working in part but I may aswell as show you what I did and maybe you can help with where I'm falling over as I'm trying to understand as much as possible.
I found an example that was close to the type of select query I was using and downloaded it into my server and got it up and running based on the example shown ... I then slowly tried to weave it into my existing pages before I saw your example. It doesn't quite work and I wonder if you could point out (gently) the errors involved?
I modified my Edit button to the following: -
<td><input type="button" name="view" value="view" id="<?php echo $row->partID ?>" class="btn btn-info btn-xs view_data" /></td>
I then added the script example I found and just adjusted the sections I needed to open the modal once the button was clicked, which looks as follows: -
<script>
$(document).ready(function(){
$('.view_data').click(function(){
var partID = $(this).attr("id");
$.ajax({
url:"testing_select.php",
method:"post",
data:{partID:partID},
success:function(data){
$('#part_detail').html(data);
$('#dataModal').modal("show");
}
});
});
});
</script>
My modal code looks like this:-
<div id="dataModal" class="modal fade">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title">Part Details</h4>
</div>
<div class="modal-body" id="part_detail">
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
Whilst the modal opens there's no data inside - the code inside my "testing_select.php" is as follows, as you can see I got a bit confused: -
<?php
require_once("rev_config.php");
$uid = access_control();
if(isset($_POST["partID"]))
{
$output = '';
$query = "SELECT `partID`, `partDescription`, `qtyInStock` FROM inventory
WHERE `partID` = '".$_POST["partID"]."'";
if (!$result = $mysqli->query($query)) {
$err
= "QUERY FAIL: "
. $query
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
$output .= '
<div class="table-responsive">
<table class="table table-bordered">';
while ($row = $result->fetch_object()) {
$output .= '
<tr>
<td width="30%"><label>partID</label></td>
<td width="70%">$row->partID</td>
</tr>
';
}
$output .= "</table></div>";
echo $output;
}
?>
I was working on the priniciple if I could get it to output the partID again I could bring in the other field later ... I'm sure it's not a million miles away so any input would be great.
J
Ok lets go through all the problems and suggestions (as I find them)
JavaScript
1. You don't have to do this
2. You are using POST to retrieve data - POST is for updating data - use GET rather. Also not a bug but a convention
3. You are using the success callback - rather use .done() as success has been deprecated. Here is the updated code
The PHP script - this is where the problem is
1. Learn about PHP strings (Single Quoted vs Double Quoted vs HEREDOC) - when to use each and what the differences are.
Single Quoted: Allows un-escaped double quotes but NO variable interpretation. Single quotes are escaped
Double Quoted: Allows un-escaped single quotes, variables are interpreted and double quotes must be escaped
HEREDOC: Allows for un-escaped quotes of any kind AND variable interpretation.
2. Try doing something like this for your incoming variables (assuming you made the change to GET as suggested above)
3. Construct your query in one of these two ways as an alternative to messy string concatenation
Double quoted string with variable embedding - note use of { } - these are optional in this case but good practice to use them in general
4. I have demonstrated more HEREDOC usage in the listing below.
5. In your sample there is no benefit to storing the output in a variable first and then outputting the variable - you might as well just output it directly. I have kept it in a variable to demonstrate the HEREDOC usage
6. This is a bug - you are using single quoted strings inside the while loop which includes this one - line 35 of your PHP listing
7. Not a bug but can mask bugs. The closing ?> in a PHP script is not required if there is no non-PHP code at the end of the page. Adding the ?> can lead to problems where a space after the closing ?> causes header issues.
Here is the updated PHP
You can see a working sample of the above here
JavaScript
1. You don't have to do this
$(document).ready(function(){
You can do this$(function() {
Not a bug but a convention2. You are using POST to retrieve data - POST is for updating data - use GET rather. Also not a bug but a convention
3. You are using the success callback - rather use .done() as success has been deprecated. Here is the updated code
<script>
$(function(){
$('.view_data').click(function(){
var partID = $(this).attr("id");
$.ajax({
url:"t2214.service.php",
method:'GET',
data:{partID:partID}
}).done(function(data){
$('#part_detail').html(data);
$('#dataModal').modal("show");
});
});
});
</script>
4. You are using the id attribute to store your numeric (I assume) id values. I think this might have changed but the spec used to be that id's must be unique and not start with a number or comprise only numbers - this might have changed. However, using custom data attributes might be more appropriate for example<input type="button" name="view" value="view" data-id="<?php echo $row->partID ?>" class="btn btn-info btn-xs view_data" />
...
<script>
...
// YOU RETRIEVE LIKE THIS
var partID = $(this).data('id');
If you are set on using the id attribute then there is no need to wrap (this) in a jQuery object you can simply do this var partID = this.id;
The PHP script - this is where the problem is
1. Learn about PHP strings (Single Quoted vs Double Quoted vs HEREDOC) - when to use each and what the differences are.
Single Quoted: Allows un-escaped double quotes but NO variable interpretation. Single quotes are escaped
Double Quoted: Allows un-escaped single quotes, variables are interpreted and double quotes must be escaped
HEREDOC: Allows for un-escaped quotes of any kind AND variable interpretation.
2. Try doing something like this for your incoming variables (assuming you made the change to GET as suggested above)
$partID = isset($_GET["partID"]) ? $_GET["partID"] : false;
Now you can proceed with a guaranteed value for $partIDif ($partID) {
// good to process
}
else {
// Process bad usage here
}
3. Construct your query in one of these two ways as an alternative to messy string concatenation
Double quoted string with variable embedding - note use of { } - these are optional in this case but good practice to use them in general
$query = "SELECT `partID`, `partDescription`, `qtyInStock` FROM inventory WHERE `partID` = '{$partID}'";
HEREDOC$query = <<< QUERY
SELECT
`partID`, `partDescription`, `qtyInStock`
FROM
inventory
WHERE
`partID` = '{$partID}'
QUERY;
In this case the query is simple so not much to choose between them - however for more complicated queries HEREDOC comes into its own.4. I have demonstrated more HEREDOC usage in the listing below.
5. In your sample there is no benefit to storing the output in a variable first and then outputting the variable - you might as well just output it directly. I have kept it in a variable to demonstrate the HEREDOC usage
6. This is a bug - you are using single quoted strings inside the while loop which includes this one - line 35 of your PHP listing
td width="70%">$row->partID</td>
As described above single quoted strings do not allow for variable interpretation - so $row->partID is interpreted as the string not the value.7. Not a bug but can mask bugs. The closing ?> in a PHP script is not required if there is no non-PHP code at the end of the page. Adding the ?> can lead to problems where a space after the closing ?> causes header issues.
Here is the updated PHP
<?php
require_once("rev_config.php");
$uid = access_control();
$partID = isset($_GET["partID"]) ? $_GET["partID"] : false;
if ($partID) {
$output = '';
$query = "SELECT `partID`, `partDescription`, `qtyInStock` FROM inventory WHERE `partID` = '{$partID}'";
if (!$result = $mysqli->query($query)) {
$err
= "QUERY FAIL: "
. $query
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
$output .= <<< HTML
<div class="table-responsive">
<table class="table table-bordered">
HTML;
while ($row = $result->fetch_object()) {
$output .= <<< ROW
<tr>
<td width="30%"><label>partID</label></td>
<td width="70%">{$row->partID}</td>
</tr>
ROW;
}
$output .= "</table></div>";
echo $output;
}
You can see a working sample of the above here
Here is how quotation marks work in PHP.
https://www.experts-exchange.com/articles/12241/Quotation-Marks-in-PHP.html
https://www.experts-exchange.com/articles/12241/Quotation-Marks-in-PHP.html
ASKER
Hi Julian,
This is where I'm at so far ... based on your support (thanks by the way)...
I've included the updated PHP script so that it looks as follows: -
I've also tweaked the <script> so that it looks also as follows:-
The modal div's remain unaffected but the area where I'm having a problem is with the php for the edit button ... if I use this code (I get an empty modal):-
Whereas, if I tweak it to test that the script and php script is working (by copying the one from your test site - shown below) and manually enter a valid partID into "id" (not "data-id") a code that exists in the database table it works perfectly and populates the modal with the 3 fields I've requested that shows it's working.
So is the problem linked to your earlier comments that the value of "id" in this instance needs to be alpha-numeric and not numeric which all the partID's in my database are? This is not a problem if so I can switch to partDescription which is alpha-numeric and does not begin numerically?
J
This is where I'm at so far ... based on your support (thanks by the way)...
I've included the updated PHP script so that it looks as follows: -
<?php
require_once("rev_config.php");
$uid = access_control();
$partID = isset($_GET["partID"]) ? $_GET["partID"] : false;
if ($partID) {
$output = '';
$query = "SELECT `partID`, `partDescription`, `supplierCompany` FROM inventory WHERE `partID` = '{$partID}'";
if (!$result = $mysqli->query($query)) {
$err
= "QUERY FAIL: "
. $query
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
$output .= <<< HTML
<div class="table-responsive">
<table class="table table-bordered">
HTML;
while ($row = $result->fetch_object()) {
$output .= <<< ROW
<tr>
<td width="30%"><label>Part ID</label></td>
<td width="70%">{$row->partID}</td>
</tr>
<tr>
<td width="30%"><label>Part Description</label></td>
<td width="70%">{$row->partDescription}</td>
</tr>
<tr>
<td width="30%"><label>Supplier Company</label></td>
<td width="70%">{$row->supplierCompany}</td>
</tr>
ROW;
}
$output .= "</table></div>";
echo $output;
}
I've also tweaked the <script> so that it looks also as follows:-
<script>
$(function(){
$('.view_data').click(function(){
var partID = this.id;
$.ajax({
url:"testing_select.php",
method:'GET',
data:{partID:partID}
}).done(function(data){
$('#part_detail').html(data);
$('#dataModal').modal("show");
});
});
});
</script>
The modal div's remain unaffected but the area where I'm having a problem is with the php for the edit button ... if I use this code (I get an empty modal):-
<input type="button" name="view" value="view" data-id="<?php echo $row->partID ?>" class="btn btn-info btn-xs view_data" />
Whereas, if I tweak it to test that the script and php script is working (by copying the one from your test site - shown below) and manually enter a valid partID into "id" (not "data-id") a code that exists in the database table it works perfectly and populates the modal with the 3 fields I've requested that shows it's working.
<td><input type="button" name="view" value="view" id="1007" class="btn btn-info btn-xs view_data" /></td>
So is the problem linked to your earlier comments that the value of "id" in this instance needs to be alpha-numeric and not numeric which all the partID's in my database are? This is not a problem if so I can switch to partDescription which is alpha-numeric and does not begin numerically?
J
ASKER
Hi Julian,
Further to my last comments (see above...)
Just to rule out the issue around 'numeric-only' id's I switched everything across to partDescription so the key area's now look as follows: -
I re-introduced the "var partDescription = $(this).data('id'); " as discussed in your earlier suggestion. The php script looks like this ...
And the edit button was reverted to the following: -
As before I get the same error until I revert back to entering a partDescription value directly into "data-ID" in which case the modal works as expected. So the following entry works without any problem: -
J
Further to my last comments (see above...)
Just to rule out the issue around 'numeric-only' id's I switched everything across to partDescription so the key area's now look as follows: -
<script>
$(function(){
$('.view_data').click(function(){
var partDescription = $(this).data('id');
$.ajax({
url:"testing_select.php",
method:'GET',
data:{partDescription:partDescription}
}).done(function(data){
$('#part_detail').html(data);
$('#dataModal').modal("show");
});
});
});
</script>
I re-introduced the "var partDescription = $(this).data('id'); " as discussed in your earlier suggestion. The php script looks like this ...
<?php
require_once("rev_config.php");
$uid = access_control();
$partDescription = isset($_GET["partDescription"]) ? $_GET["partDescription"] : false;
if ($partDescription) {
$output = '';
$query = "SELECT `partID`, `partDescription`, `supplierCompany` FROM inventory WHERE `partDescription` = '{$partDescription}'";
if (!$result = $mysqli->query($query)) {
$err
= "QUERY FAIL: "
. $query
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
$output .= <<< HTML
<div class="table-responsive">
<table class="table table-bordered">
HTML;
while ($row = $result->fetch_object()) {
$output .= <<< ROW
<tr>
<td width="30%"><label>Part ID</label></td>
<td width="70%">{$row->partID}</td>
</tr>
<tr>
<td width="30%"><label>Part Description</label></td>
<td width="70%">{$row->partDescription}</td>
</tr>
<tr>
<td width="30%"><label>Supplier Company</label></td>
<td width="70%">{$row->supplierCompany}</td>
</tr>
ROW;
}
$output .= "</table></div>";
echo $output;
}
And the edit button was reverted to the following: -
<input type="button" name="view" value="view" data-id="<?php echo $row->partDescription ?>" class="btn btn-info btn-xs view_data" />
As before I get the same error until I revert back to entering a partDescription value directly into "data-ID" in which case the modal works as expected. So the following entry works without any problem: -
<input type="button" name="view" value="view" data-id="AHS_5L" class="btn btn-info btn-xs view_data" />
J
How does this get rendered in the browser - can you do a view source and post here?
<input type="button" name="view" value="view" data-id="<?php echo $row->partDescription ?>" class="btn btn-info btn-xs view_data" />
ASKER
Do you see the problem?
The PHP is not being interpreted as php and is being output as text. Does your script have a PHP extension or is it being loaded by a script with a PHP extension? If not then the PHP is not going to fire.
The PHP is not being interpreted as php and is being output as text. Does your script have a PHP extension or is it being loaded by a script with a PHP extension? If not then the PHP is not going to fire.
ASKER
Sorry - not sure what you mean by ... "Does your script have a PHP extension or is it being loaded by a script with a PHP extension?"
J
J
ASKER
Cracked it! Changed it from: -
Can you see any problems with this?
J
<td><input type="button" name="view" value="view" data-id="<?php echo $row->partDescription?>" class="btn btn-info btn-xs view_data" /></td>
<td><input type="button" name="view" value="view" data-id="$row->partDescription" class="btn btn-info btn-xs view_data" /></td>
Can you see any problems with this?
J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much for all your help! J
Secondly, are you familiar with Bootstap's Modal functionality?
If so then the solution is trivial. You capture the click on the generic button (by class). Use (this) to get the part_id - AJAX that back to your existing PHP script and place the returned HTML into your bootstrap modal template.
To make it a bit easier change your html for your <li> to
Open in new window
I have put together a sample based on your code - I have changed the database code to be static objects and arrays for simplicity but with minor changes it should plug in to what you have.The HTML / PHP in the main page looks like this
Open in new window
The Bootstrap Modal looks like this (source: http://getbootstrap.com/javascript/#static-example)Open in new window
The jQuery looks like this
Open in new window
Working sample here