Solved

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

Posted on 2011-03-16
7
346 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

749 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