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

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
LVL 2
CybervanesAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
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
 
Ray PaseurCommented:
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
 
Ray PaseurCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CybervanesAuthor Commented:
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
 
CybervanesAuthor Commented:
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
 
CybervanesAuthor Commented:
Thanks for your help Ray!
0
 
Ray PaseurCommented:
Thanks for the points... Good luck with your project! ~Ray
0
All Courses

From novice to tech pro — start learning today.