Solved

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

Posted on 2011-03-16
7
326 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
  • 4
  • 3
7 Comments
 
LVL 108

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 108

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

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 108

Expert Comment

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery including how to code hide show and toggles. 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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now