Solved

Custom MySQL database manipulation table dynamically built with PHP editing tools with JS

Posted on 2011-03-16
7
351 Views
Last Modified: 2012-05-11
I'm trying to build a table that get populated with the results of a query.

Hidden within each row is a form to edit the data.

The problem I'm having is swapping the results in the row with an input fields upon specific row request. My JavaScript targets the first row of the table not the row I'm after. (line 78 -88 )

For convenience I've attached the whole file containing all the pertinent script.

in short I'm trying to build a custom table management table and form. if I'm going about it entirely wrong please let me know.

Thanks In advance for your help. userManagement.php userManagement.php
0
Comment
Question by:Cybervanes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 35158830
I'm not completely sure I understand the question, but I will try to help.  For starters, here is the code in the code snippet.  I believe the two file attachments are the same, right?

Have a look at line 17.  You will never know what went wrong if that query fails.  Please have a look at the next post...
<? require_once('../php/mySQL-init.php'); 
@session_start();
// **** GLOBAL VARS **** //
$userTable = 'user-profiles';
$_SESSION['Name'] = 'Jon Doe';

// **** GET INFORMATIO ABOUT CURRENT USER **** //
$curentUserQ = 'SELECT * FROM `'.$userTable.'` WHERE `Name` = "'.$_SESSION['Name'].'" ';
$curentUser = mysql_fetch_assoc(mysql_query($curentUserQ));

// **** FIND OUT WHO HAS THEM AS THEIR MANAGER **** //
if($curentUser['Privileges'] != "Full"){
	$subordinatesQ = 'SELECT * FROM `'.$userTable.'` WHERE `Manager` = "'.$curentUser['Name'].'" ';
}else{
	$subordinatesQ = 'SELECT * FROM `'.$userTable.'`';	
}
$subordinatesQ = mysql_query($subordinatesQ);

// **** RESULTS TABLE **** //
?>
<div id="dynamicContainer"></div>
<table style='width:100%' border="1">
  <tr align="center" valign="middle">
    <td>Tools</td>
    <td>User Id</td>
    <td>Status</td>
    <td>Full Name</td>
    <td>Email Address</td>
    <td>Manager</td>
    <td>Build Finanical Analysis</td>
    <td>Approve Finanical Analysis</td>
    <td>Edit System Specs</td>
    <td>Change Project Status</td>
    <td>Upload Files</td>
    <td>Download Files</td>
    <td>Delete Files</td>
    <td>Rename Files</td> 
  </tr>
  <? while($subordinates = mysql_fetch_assoc($subordinatesQ)){ ?>
  <tr id="<?=$subordinates['Record-Id']?>" title="<?=$subordinates['Name']?>" align="center" valign="middle">
   <form action="#" name="form-<?=$subordinates['Record-Id']?>" id="form-<?=$subordinates['Record-Id']?>" >
    <td><a id="edit-<?=$subordinates['Record-Id']?>" href="javascript:showForm(<?=$subordinates['Record-Id']?>)">Edit</a><a id="save-<?=$subordinates['Record-Id']?>" href="javascript:saveForm(<?=$subordinates['Record-Id']?>)" style="display:none;">Save</a></td>
  	<? $n='Status'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><select id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"><option value="<?=$subordinates[$n]?>"><?=$subordinates[$n]?></option><option <? if($subordinates[$n]== "")echo'selected="selected"';?> value="Inactive">Inactive</option><option value="Active">Active</option></select></span></td>
	<td><?=$subordinates['Record-Id']?></td>
    <? $n='Name'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><input id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;" /></span></td>
    <? $n='Email'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><input id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"/></span></td>
    <? $n='Manager'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><input id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"/></span></td>
    <? $n='BuildFinanicalAnalysis'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><select id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"><option value="<?=$subordinates[$n]?>"><?=$subordinates[$n]?></option><option <? if($subordinates[$n]== "")echo'selected="selected"';?> value="No">No</option><option value="Yes">Yes</option></select></span></td>
    <? $n='ApproveFinanicalAnalysis'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><select id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"><option value="<?=$subordinates[$n]?>"><?=$subordinates[$n]?></option><option <? if($subordinates[$n]== "")echo'selected="selected"';?> value="No">No</option><option value="Yes">Yes</option></select></span></td>
    <? $n='EditSystemSpecs'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><select id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"><option value="<?=$subordinates[$n]?>"><?=$subordinates[$n]?></option><option <? if($subordinates[$n]== "")echo'selected="selected"';?> value="No">No</option><option value="Yes">Yes</option></select></span></td>
	<? $n='ChangeProjectStatus'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><select id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"><option value="<?=$subordinates[$n]?>"><?=$subordinates[$n]?></option><option <? if($subordinates[$n]== "")echo'selected="selected"';?> value="No">No</option><option value="Yes">Yes</option></select></span></td>
	<? $n='UploadFiles'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><select id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"><option value="<?=$subordinates[$n]?>"><?=$subordinates[$n]?></option><option <? if($subordinates[$n]== "")echo'selected="selected"';?> value="No">No</option><option value="Yes">Yes</option></select></span></td>
	<? $n='DownloadFiles'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><select id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"><option value="<?=$subordinates[$n]?>"><?=$subordinates[$n]?></option><option <? if($subordinates[$n]== "")echo'selected="selected"';?> value="No">No</option><option value="Yes">Yes</option></select></span></td>
	<? $n='DeleteFiles'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><select id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"><option value="<?=$subordinates[$n]?>"><?=$subordinates[$n]?></option><option <? if($subordinates[$n]== "")echo'selected="selected"';?> value="No">No</option><option value="Yes">Yes</option></select></span></td>
	<? $n='RenameFiles'; ?>
    <td><span id="result-<?=$n?>"><?=$subordinates[$n]?></span><span id="input-<?=$n?>"  style="display:none;"><select id="<?=$n?>" name="<?=$n?>" value="<?=$subordinates[$n]?>" style="width:100%; border-style:inset 2px;"><option value="<?=$subordinates[$n]?>"><?=$subordinates[$n]?></option><option <? if($subordinates[$n]== "")echo'selected="selected"';?> value="No">No</option><option value="Yes">Yes</option></select></span></td>
   </form>
  </tr>
  <? } // end while($subordinatesQ = mysql_fetch_assoc ?>
</table>
<script>



function showForm(id){
 // **** show form fields - hide results
 	var toChange = document.getElementById(id).getElementsByTagName('span');
	for(i=0; i < toChange.length; i++){
	var UN =  toChange[i].id.split("-");
	//alert(toChange);
		if(UN[0] == "result"){
			document.getElementById(toChange[i].id).style.display = "none";
		}
		if(UN[0] == "input"){
			document.getElementById(toChange[i].id).style.display = "block";
		}
	}
	
 // **** hide allother edit buttons buttons	
 	var links = document.getElementsByTagName('a');
	for(i=0; i < links.length; i++){
	var UN =  links[i].id.split("-");
		if(UN[0] == "edit"){
			document.getElementById(links[i].id).style.display = "none";
		}
	}
 // **** show save btn
 	document.getElementById('save-'+id).style.display = "block";
}
</script>

<?

?>

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 35158864
The mysql_query() function returns values that must be tested for success or failure.  MySQL is not a black box -- it can and will fail for reasons that are outside of your control.  While you are debugging, this kind of construct is workable (see code snippet).  You also want to remove the @ notation from your code.  That suppresses error messages and at this point in the project you really want all the error messages.  Next, I recommend that you add error_reporting(E_ALL) to your scripts, so you can be sure you are not accidentally relying on an undefined variable.
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
    // MAN PAGE: http://php.net/manual/en/function.var-dump.php
    var_dump($row);
}

Open in new window

0
 
LVL 2

Author Comment

by:Cybervanes
ID: 35158929
Yes both flies are the same... I did not mean to upload the second one.

Lines in question are JavaScript. I really appreciate your input on my PHP MySql methods. Enlighting...

JavaScript lines 78 throug 88

For some reason it's targeting and showing the fields in the 1st row instead for the row that was clicked.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 35158960
Now to the design of the application.  I think you might want to go with one form per row in the HTML table.  You can have as many forms as you want on a page, and each form can uniquely identify the row you are working with, using a hidden (or visible) form input control.  If you want some fancy appear-and-disappear effects, jQuery might be a tool to consider.  You might also want to dispense with the short-open tags like <?=$n?> since these can interfere with XML notation.
0
 
LVL 2

Author Comment

by:Cybervanes
ID: 35166045
Yes there is already 1 form per row... I'm still having a hard time targeting individual rows to change the visibility of the form input fields.

I'll upload an associative array of a sample query this morning. So you can see what I'm talking about.

Please stand by...
0
 
LVL 2

Author Closing Comment

by:Cybervanes
ID: 35211095
Thanks for your help Ray!
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 35212635
Thanks for the points... Good luck with your project! ~Ray
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question