Updating multiple columns with button in table

glava · June 13, 2011

Hi freaks, i have "problem" with updating multiple database columns after i SELECT and display DB table content in HTML table.

I have 3 rows in DB for numeric values, and i have displayed them in HTML table cell as textbox default value. Idea is that if that cell in DB is containing some value then is that value shown in textbox, if not then is empty field and i can put some number and update DB with clicking submit button.

HTML table looks like this :

<table width="800" border="1" cellpadding="1" cellspacing="1" align="center">
<tr> 
<td><b>Name: </b></td>
<td><b>Lastname: </b></td>
<td><b>Indeks: </b></td>
<td><b>K1 date: </b></td>
<td><b>K1 grade: </b></td>
<td><b>K2 date: </b></td>
<td><b>K2 grade: </b></td>
<td><b>K3 date: </b></td>
<td><b>K3 grade: </b></td>
</tr>
<?php 
while ($redak=mysql_fetch_array($q))
{ 
?>
<tr>
<td><?php echo $redak["name"]?></td>
<td><?php echo $redak["lastname"]?></td>
<td><?php echo $redak["indeks"]?></td>
<td><?php echo $redak["k1D"]?></td>
<td><input type="text" name="k1O" value="<?php echo $redak["k1O"]?>" size="5"/></td>
<td><?php echo $redak["k2D"]?></td>
<td><input type="text" name="k1O" value="<?php echo $redak["k2O"]?>" size="5"/></td>
<td><?php echo $redak["k3D"]?></td>
<td><input type="text" name="k1O" value="<?php echo $redak["k3O"]?>" size="5"/></td>
<td><input type="submit" value="Submit grade" name="grade"></td> // Button which should submit all values from this row
</tr>
<?php 
}
?>
<td></td>
<td></td>
<td></td>
<td></td>
<td><input type="submit" value="Submit grades" name="grades1"></td>  // Button which should submit/update all values from this column 
<td></td>
<td><input type="submit" value="Submit grades" name="grades2"></td>  // Button which should submit/update all values from this column
<td></td>
<td><input type="submit" value="Submit grades" name="grades3"></td>  // Button which should submit/update all values from this column
</table>
<br><br>

have someone idea how to do it ?

monkeytooth · June 13, 2011

Whats going to be helpful here is a sample of your actual database layout. And the PHP/MySQL code you are quering for updates/inserts/selects. This way we can see what your doing currently then attempt to assess it easier. Also if your page is currently displaying any specific errors from the code, give us that too

glava · June 13, 2011

Whats going to be helpful here is a sample of your actual database layout. And the PHP/MySQL code you are quering for updates/inserts/selects. This way we can see what your doing currently then attempt to assess it easier. Also if your page is currently displaying any specific errors from the code, give us that too

I knew i've forgat something

database :

// first table

CREATE TABLE IF NOT EXISTS `studenti` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `indeks` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `k1D` date NOT NULL,
  `k1O` varchar(2) NOT NULL,
  `k2D` date NOT NULL,
  `k2O` varchar(2) NOT NULL,
  `k3D` date NOT NULL,
  `k3O` varchar(2) NOT NULL,
  `U_ID` int(11) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `indeks` (`indeks`),
  UNIQUE KEY `Email` (`email`),


// second table

CREATE TABLE IF NOT EXISTS `user` (
  `ID` int(4) unsigned NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `level` int(4) default '1',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

and here is quering :

<?php
if(isset($_SESSION['admin'])){
echo"Pozdrav ".$_SESSION['admin']." ! You're loged in as admin.<br><br>"; }
elseif(isset($_SESSION['user'])) { 
    die("You're not authorised for this!") ;
    }
$sql="SELECT * FROM studenti";
if (!$q=mysql_query($sql))
{
echo "Nastala je greska pri izvodenju upita<br />"; 
die();
}
if (mysql_num_rows($q)==0)
{
echo "Nema upisa";
} else {

// here goes HTML table

I have no errors because i did not try anything yet.. don't know syntax for it.

glava · June 14, 2011

could anyone help me with this ?

I need to update some database columns and rows with (submit?) button after i select and "print" database table in HTML table and input values in empty cells in HTML table.. :shrug:

revraz · June 14, 2011

Do you know how to do a MySQL UPDATE statement?

http://dev.mysql.com/doc/refman/5.0/en/update.html

http://www.w3schools.com/php/php_mysql_update.asp

http://www.tizag.com/mysqlTutorial/mysqlupdate.php

glava · June 14, 2011

Yes, that's nice, I read already some tutorials but didn't find nowhere something for my specific situation.. I attach pic of printed table in HTML for better understanding. I print all table content from DB to HTML table, some columns have textboxes in their cells, some textboxes are empty and some not. Is there easyer way then write all of the cells in UPDATE querry ? also i need buttons for updete specific column and buttons for update specific rows..

[attachment deleted by admin]

fugix · June 14, 2011

glava, in order to update your table with new/updated values, you will need to use an UPDATE statement, use the references that another poster provided you,

Do you know how to do a MySQL UPDATE statement?

http://dev.mysql.com/doc/refman/5.0/en/update.html

http://www.tizag.com/mysqlTutorial/mysqlupdate.php

glava · June 14, 2011

I read that references and they are not helpful in my case. I'm trying but don't know how. I think it's a little bit more complicated then it sounds..

i try with :

if (($_POST["ocjenaK1"]) OR ($_POST["ocjenaK2"]) OR ($_POST["ocjenaK3"]))
{
$kol1 = $_POST['k1Otxt'];
$kol2 = $_POST['k2Otxt'];
$kol3 = $_POST['k3Otxt'];
mysql_query("UPDATE studenti SET k1O = '".$kol1."', k2O = '".$kol2."', k3O = '".$kol3."'") or die(mysql_error());
}

but nothing..

i think names of that textboxes aren't correct because i'm geting these textboxes with "do while" loop and don't know how to name all that cells ?

fugix · June 14, 2011

you are using the wrong operator for your if statement, try this

if (!empty($_POST["ocjenaK1"]) || !empty($_POST["ocjenaK2"]) || !empty($_POST["ocjenaK3"]))
{
$kol1 = $_POST['k1Otxt'];
$kol2 = $_POST['k2Otxt'];
$kol3 = $_POST['k3Otxt'];
mysql_query("UPDATE studenti SET k1O = '$kol1' AND k2O = '$kol2' AND k3O = '$kol3'") or die(mysql_error());
}

Also, in the update query that i have edited, you will need to add a where clause in your update statement so the query knows specifically which row to update

glava · June 14, 2011

Tnx for editing my query and i don't know how to use WHERE clause because i wanna to Update all fields that are shown in textboxes. Here is complete code :

// some HTML
<?php
if(isset($_SESSION['admin'])){
echo"Hi ".$_SESSION['admin']." ! you're loged in as admin.<br><br>"; }
elseif(isset($_SESSION['user'])) { 
    die("You're not alowed for this!") ;
    }
$sql="SELECT * FROM studenti";
if (!$q=mysql_query($sql))
{
echo "Mistake on query<br />"; 
die();
}
if (mysql_num_rows($q)==0)
{
echo "No records";
} else {
?>

<table width="800" border="1" cellpadding="1" cellspacing="1" align="center">
<tr> 
<td><b>Ime: </b></td>           // name
<td><b>Prezime: </b></td>    // lastname
<td><b>Indeks: </b></td>      // index
<td><b>K1 datum: </b></td>  // k1 date
<td><b>K1 ocjena: </b></td>  // k1 grade
<td><b>K2 datum: </b></td>
<td><b>K2 ocjena: </b></td>
<td><b>K3 datum: </b></td>
<td><b>K3 ocjena: </b></td>
</tr>
<?php 
while ($redak=mysql_fetch_array($q))
{ 
?>
<tr>
<td><?php echo $redak["ime"]?></td>
<td><?php echo $redak["prezime"]?></td>
<td><?php echo $redak["indeks"]?></td>
<td><?php echo $redak["k1D"]?></td>
<td><input type="text" name="k1Otxt" value="<?php echo $redak["k1O"]?>" size="3" maxlength="3"/></td>
<td><?php echo $redak["k2D"]?></td>
<td><input type="text" name="k2Otxt" value="<?php echo $redak["k2O"]?>" size="3" maxlength="3"/></td>
<td><?php echo $redak["k3D"]?></td>
<td><input type="text" name="k3Otxt" value="<?php echo $redak["k3O"]?>" size="3" maxlength="3"/></td>
</tr>
<?php 
}
?>
<td></td>
<td></td>
<td></td>
<td></td>
<td><input type="submit" value="Spremi" name="ocjenaK1"></td>  // save button
<td></td>
<td><input type="submit" value="Spremi" name="ocjenaK2"></td>
<td></td>
<td><input type="submit" value="Spremi" name="ocjenaK3"></td>
</table>
<br><br>
<?php 
}
if (!empty($_POST["ocjenaK1"]) || !empty($_POST["ocjenaK2"]) || !empty($_POST["ocjenaK3"]))
{
$kol1 = $_POST['k1Otxt'];
$kol2 = $_POST['k2Otxt'];
$kol3 = $_POST['k3Otxt'];
mysql_query("UPDATE studenti SET k1O = '$kol1' AND k2O = '$kol2' AND k3O = '$kol3'") or die(mysql_error());
}
?>
<form name="logout" method="post" action="logout.php">
<input type="submit" name="logout" id="logout" value="Odjavi me!">
</form>
// some HTML

with this code i get the table from attachment. I need to update all fields in textboxes of 1 column when i type some value in it and press button "Spremi" below them. same with other 2 columns.

but with this code all fields (textboxes) in one column have names "k1Otxt" or what ??

[attachment deleted by admin]

fugix · June 14, 2011

the query that you are using now will update all fields with those values yes

glava · June 14, 2011

maybe it should work but it doesn't.. i tryed with

if (isset($_POST["ocjenaK1"]) || ...

instead

if (!empty($_POST["ocjenaK1"]) || ...

but also nothing.. it even doesn't refresh page, maybe is not problem in query itself then in syntax for button click doing it ??

oh i'm frustrated when i know that i'm pain in ass :-\

glava · June 15, 2011

enyone ?

glava · June 15, 2011

I forgot to put form tag for buttons.. now with

<form name="ocjene" method="post" action="">

when i put some values in textfield in column 1 ( k1O ) and click button "Spremi" i got zeros ( 0 ) in every field of that column ??

enyone know why ?

fugix · June 15, 2011

so your update query is being executed, but it updates all fields specified to 0? what are the types of those fields? e.x INT, VARCHAR, etc...

glava · June 15, 2011

yes, now i got zeros ( 0 ) in every field of column if i write numbers in any textbox cell (or in all of them) of that column and press button for update table.

They was VARCHAR, i tryed also with INT . same result..

fugix · June 15, 2011

if the user information are numbers, make the field types INT, if they are words, make them VARCHAR, also, are there any strange characters being inserted? Try to escape your user input

$kol1 = mysql_real_escape_string($_POST['k1Otxt']);
$kol2 = mysql_real_escape_string($_POST['k2Otxt']);
$kol3 = mysql_real_escape_string($_POST['k3Otxt']);
mysql_query("UPDATE studenti SET k1O = '$kol1' AND k2O = '$kol2' AND k3O = '$kol3'") or die(mysql_error());

mikosiko · June 15, 2011

mysql_query("UPDATE studenti SET k1O = '$kol1' AND k2O = '$kol2' AND k3O = '$kol3'") or die(mysql_error());

is not the update problem clear?

glava · June 15, 2011

I change all numeric fields in INT , delete all "strange" characters from table ( in names, lastnames ) .

now cells in columns k1O , k2O and k3O are with default value "0" but i still canot update with my numbers..

glava · June 15, 2011

mysql_query("UPDATE studenti SET k1O = '$kol1' AND k2O = '$kol2' AND k3O = '$kol3'") or die(mysql_error());

is not the update problem clear?

sorry but not realy... AND cannot be in query ?

fugix · June 15, 2011

mysql_query("UPDATE studenti SET k1O = '$kol1', k2O = '$kol2', k3O = '$kol3'") or die(mysql_error());

edit: i accidently changed the commas that you had to AND for some reason, only meant to change your or operator before, the above is what you want

glava · June 15, 2011

mysql_query("UPDATE studenti SET k1O = '$kol1', k2O = '$kol2', k3O = '$kol3'") or die(mysql_error());
edit: i accidently changed the commas that you had to AND for some reason, only meant to change your or operator before, the above is what you want

i changed and still nothing. when i try to echo value taked from textboxes ( echo ".$kol1." ; ) i get empty space so

$kol1 = mysql_real_escape_string($_POST['k1Otxt']);

is not actualy taking values from textboxes ?

dont know why but i'm thinking that textbox names are the problem. Is it possible to 7 cells / textboxes of 1 column have same name ? how then query knows which textbox value goes where in mysql table while updating ?

Is it possible in PHP to define variable "i" which goes +1 ( like i++) in every step of loop which is printing sql rows in HTML table rows then use that variable to generate ID for textbox names like txtbox_1 , txtbox_2 , ... in every row ??

glava · June 16, 2011

Enyone ? help..

Sign In

Updating multiple columns with button in table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information