?
Solved

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

Posted on 2011-03-16
7
Medium Priority
?
359 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 111

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 111

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 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 111

Expert Comment

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

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

777 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