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: -

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

Open in new window


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>

Open in new window


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

Open in new window


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
RidgejpManaging DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
First up your PHP does not need to change.

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
<li><a href="editform.php"data-partid="<?php echo $row->partID;?>">Edit</a></li>

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
<ul class="part-list">
<?php 
  // ARRAY USED FOR SIMPLICITY
  // REPLACE WITH YOUR RECORD LOOP
  $rowset = array(1,2,3,4,5,6);
  foreach($rowset as $row) {
    echo <<< LI
    <li><a href="editform.php" data-partid="{$row}">Edit partid {$row}</a></li>
LI;
  }
?>
</ul>

Open in new window

The Bootstrap Modal looks like this (source: http://getbootstrap.com/javascript/#static-example)
<div class="modal fade" tabindex="-1" role="dialog" id="modalContent">
  <div class="modal-dialog" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
        <h4 class="modal-title">Modal title</h4>
      </div>
      <div class="modal-body">
        <p>One fine body&hellip;</p>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
        <button type="button" class="btn btn-primary">Save changes</button>
      </div>
    </div><!-- /.modal-content -->
  </div><!-- /.modal-dialog -->
</div><!-- /.modal --><!-- END -->

Open in new window


The jQuery looks like this
<script>
$('.part-list a').click(function(e) {
  e.preventDefault();
  var partid = $(this).data('partid');
  $.ajax({
    url: 't2210.editform.php',
    data: {partid: partid},
    type: 'GET',
    dataType: 'HTML'
  }).done(function(resp) {
    $('#modalContent .modal-body').html(resp)
    $('#modalContent').modal('show');
  });
});
</script>

Open in new window


Working sample here
0
RidgejpManaging DirectorAuthor Commented:
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: -

<td><input type="button" name="view" value="view" id="<?php echo $row->partID ?>" class="btn btn-info btn-xs view_data" /></td>

Open in new window


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>

Open in new window


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">&times;</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>  

Open in new window


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;  
 }
?>

Open in new window


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
0
Julian HansenCommented:
Ok lets go through all the problems and suggestions (as I find them)
JavaScript
1. You don't have to do this  
$(document).ready(function(){ 

Open in new window

You can do this
$(function() {

Open in new window

Not a bug but a convention

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

Open in new window

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');

Open in new window

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;

Open in new window


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;

Open in new window

Now you can proceed with a guaranteed value for $partID
if ($partID) {
   // good to process
}
else {
   // Process bad usage here
}

Open in new window


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}'";

Open in new window

HEREDOC
$query = <<< QUERY
SELECT 
  `partID`, `partDescription`, `qtyInStock` 
FROM 
  inventory 
WHERE 
  `partID` = '{$partID}'
QUERY;

Open in new window

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>

Open in new window

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;  
 }

Open in new window


You can see a working sample of the above here
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Ray PaseurCommented:
0
RidgejpManaging DirectorAuthor Commented:
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: -

<?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;  
 }

Open in new window


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>

Open in new window


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

Open in new window


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>

Open in new window


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
0
RidgejpManaging DirectorAuthor Commented:
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: -

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

Open in new window


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;  
 }

Open in new window


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

Open in new window


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

Open in new window


J
0
Julian HansenCommented:
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" />

Open in new window

0
RidgejpManaging DirectorAuthor Commented:
Good Morning Julian,

Results attached and it appears to be rendering itself correctly - you can see the partDescription is present in the table twice the first row is the partID but the second row is the partDescription - what does this mean?

J

View Source
0
Julian HansenCommented:
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.
0
RidgejpManaging DirectorAuthor Commented:
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
0
RidgejpManaging DirectorAuthor Commented:
Cracked it! Changed it from: -

<td><input type="button" name="view" value="view" data-id="<?php echo $row->partDescription?>" class="btn btn-info btn-xs view_data" /></td>

Open in new window


<td><input type="button" name="view" value="view" data-id="$row->partDescription" class="btn btn-info btn-xs view_data" /></td>

Open in new window


Can you see any problems with this?

J
0
Julian HansenCommented:
That is the only way to do it. I suspect that you placed the <?php echo ?> in your output based on my earlier post which made the wrong assumption about how you were outputting your HTML. I assumed you were in HTML mode hence the <?php echo ?>.

When in script mode (between <?php ?> tags) variables can be directly included in the string.
When in HTML mode you need to output the string using an echo inside <?php ?> tags.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RidgejpManaging DirectorAuthor Commented:
Thanks very much for all your help! J
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.