updating mysql table in PHP

Question

pantera989 0 Newbie Poster

13 Years Ago

Hi, I need help with PHP/javascript/mysql
I am fairly new to this so excuse me for my bad programming skills. I have PHP page which has to update mysql table and HTML table onclick. It updates HTML table ok but as soon as I refresh page it updates all data in mysql and it should update in by click one by one. Hope you understand what I need. Thx in advance.

<?php

session_start();
require_once("connect.php");
//$query = "SELECT f.flexi, f.status, f.allocation, m.username
//FROM  flexi f, members m";

$query  = "SELECT * FROM flexi";
$result = mysql_query($query);

$query1 = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi1"';
$result1 = mysql_query($query1);
$result1 = $_SESSION['allocate'];

$query2 = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi2"';
$result2 = mysql_query($query2);
$result2 = $_SESSION['alloc'];

echo "<html>

	<body>

	<table border=1 id='mytable'>
	<tr><th>Flexi</th><th>Status</th><th>Allocation</th><th>User</th></tr>";

if (isset($_SESSION['username'])) // he got it.
{
		
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{

echo'
	
	<head>
	<script type="text/javascript">
			
	function change()
	{			
		var first="'.$_SESSION['username'].'";	
		var all="'.$_SESSION['allocate'].'";	
		var x=document.getElementById("mytable").rows
		var y=x[1].cells		
		y[3].innerHTML=first;		
		y[2].innerHTML="allocated";
		exit();
				
	}
	function cchange()
	{	
		var second="'.$_SESSION['username'].'";
		var all="'.$_SESSION['alloc'].'";		
		var x=document.getElementById("mytable").rows
		var y=x[2].cells		
		y[3].innerHTML=second;
		var all2="'.$_SESSION['alloc'].'";	
		y[2].innerHTML="allocated";
		exit();		
	}

	</script>
</head>
';	
	echo "<tr>";
	echo "<td>{$row['flexi']}</td>";
	echo "<td>{$row['status']}</td>";
	echo "<td>{$row['allocation']}</td>";
	echo "<td>{$row['user']}</td>";
	echo "</tr>";		
	
}
}

echo "<td><input type='button' style='float:right' onclick='change(this.form)' value='Allocate'></td>";
echo "<td><input type='button' style='float:right' onclick='cchange(this.form)' value='Allocate'></td>";

?> 

</table>
</body>
</html>

javascript mysql php

Edited 13 Years Ago by pantera989 because: bad coding

3 Contributors
9 Replies
1K Views
9 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by vibhaJ

jrotunda85 9 Junior Poster

13 Years Ago

From what you've posted, the javascript your executing isn't calling the SQL queries to actually perform the update. You need to include something in your javascript to call those queries (or PHP file). It should look something like this:

xmlhttp.open("GET","updatefile.php",true);
xmlhttp.send();

Edited 13 Years Ago by jrotunda85 because: n/a

vibhaJ 126 Master Poster

13 Years Ago

You have placed this code on top.

$query1 = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi1"';
$result1 = mysql_query($query1);
$result1 = $_SESSION['allocate'];

$query2 = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi2"';
$result2 = mysql_query($query2);
$result2 = $_SESSION['alloc'];

That means each time page is refreshed sql query will update record.

You can do it by 2 way.
1) server side
Use submit button.
When user click on submit button page will be posted and top php code will update record. Top php code will be in if condition.
e.g.

if(isset($_POST('submit'))){ //===update query====}

2) Client side
Use Ajax.
Which meanse once user click on button you will send one ajax request to one page which will update data.

vibhaJ 126 Master Poster

13 Years Ago

For case 1 you don't need javascript onclick.

<?
if(isset($_POST['alloc1']))
{	
	$query = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi1"';	
	$result = mysql_query($query);	
	$result = $_SESSION['allocate'];	
	header("location:page.php");// page.php is the page where you want to redirect after success update
	exit;
}
if(isset($_POST['alloc2']))
{	
	$query = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi2"';	
	$result = mysql_query($query);	
	$result = $_SESSION['allocate'];	
	header("location:page.php");// page.php is the page where you want to redirect after success update
	exit;	
}
?> 
// this should be in form
<input type='button' style='float:right' name="alloc1" value='Allocate'>
<input type='button' style='float:right' name="alloc2" value='Allocate'>

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

pantera989 0 Newbie Poster · Answer 1 · 2011-06-29T17:37:03+00:00

From what you've posted, the javascript your executing isn't calling the SQL queries to actually perform the update. You need to include something in your javascript to call those queries (or PHP file). It should look something like this:
xmlhttp.open("GET","updatefile.php",true);
xmlhttp.send();

my DB does update but as soon as I get to page, and it updates all rows not just one row by clicking on it like in HTML.
I have flexi1 and flexi2 and when I click allocate button in HTML 'deallocated' changes to 'allocated' and user to user that is currently logged in, but in database both flexi1 and flexi2 change to 'allocated' and to user that is currently logged in.

pantera989 0 Newbie Poster · Answer 2 · 2011-06-29T18:02:28+00:00

You have placed this code on top.
$query1 = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi1"';
$result1 = mysql_query($query1);
$result1 = $_SESSION['allocate'];

$query2 = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi2"';
$result2 = mysql_query($query2);
$result2 = $_SESSION['alloc'];
That means each time page is refreshed sql query will update record.
You can do it by 2 way.
1) server side
Use submit button.
When user click on submit button page will be posted and top php code will update record. Top php code will be in if condition.
e.g.
if(isset($_POST('submit'))){ //===update query====}
2) Client side
Use Ajax.
Which meanse once user click on button you will send one ajax request to one page which will update data.

Thank you very much for help. Can I use first example just with allocate button I already have?

pantera989 0 Newbie Poster · Answer 3 · 2011-06-29T18:18:03+00:00

For case 1 you don't need javascript onclick.

<?
if(isset($_POST['alloc1']))
{	
	$query = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi1"';	
	$result = mysql_query($query);	
	$result = $_SESSION['allocate'];	
	header("location:page.php");// page.php is the page where you want to redirect after success update
	exit;
}
if(isset($_POST['alloc2']))
{	
	$query = 'UPDATE flexi SET allocation = "allocated", user = "'.$_SESSION["username"].'" WHERE flexi="flexi2"';	
	$result = mysql_query($query);	
	$result = $_SESSION['allocate'];	
	header("location:page.php");// page.php is the page where you want to redirect after success update
	exit;	
}
?> 
// this should be in form
<input type='button' style='float:right' name="alloc1" value='Allocate'>
<input type='button' style='float:right' name="alloc2" value='Allocate'>

I don't want to refresh whole page, just change HTML table cells and mysql table on button click.

vibhaJ 126 Master Poster · Answer 4 · 2011-06-29T19:06:51+00:00

That is called ajax. and case 2 in my above post.
Hope you have some idea what AJAX is.

<script language="javascript" src="http://code.jquery.com/jquery-1.6.1.min.js"></script>
<script language="javascript">
function change()
{
	$.ajax({ // this function will send php request in back so no need to refresh page
	   type: "POST",
	   url: "ajax.php", // in ajax.php check requested flexi variable passed from below line and write update query.
	   data: "flexi=flexi1",
	   success: function(msg){
		// change html portion
	   }
	 });
}
// same for flexi2
</script>

pantera989 0 Newbie Poster · Answer 5 · 2011-06-29T19:10:05+00:00

That is called ajax. and case 2 in my above post.
Hope you have some idea what AJAX is.

<script language="javascript" src="http://code.jquery.com/jquery-1.6.1.min.js"></script>
<script language="javascript">
function change()
{
	$.ajax({ // this function will send php request in back so no need to refresh page
	   type: "POST",
	   url: "ajax.php", // in ajax.php check requested flexi variable passed from below line and write update query.
	   data: "flexi=flexi1",
	   success: function(msg){
		// change html portion
	   }
	 });
}
// same for flexi2
</script>

Never used it before. I guess it's time to learn something new=D Thx for your help.

vibhaJ 126 Master Poster · Answer 6 · 2011-06-29T19:39:26+00:00

Ajax is used for the case:
- Once php page is loaded.
- Then we want any php stuff like database insert,update,delete,select query or any php coding without again refreshing page.
- so here we use ajax, so end user wont realize that some thing happen on server side.
- The code i have given is using jquery. Jquery is JS library, using which we can do all stuff of javascript more easily and min line of coding. Jquery have thousands of function e.h. you have used innerHTML, jquery have http://api.jquery.com/html/.

Hope this helps you.
Mark thread solved.