[SOLVED] Update table with many values at once

[solon]

Hey guys i need to update my table's different rows of the same field, using lets say 10 textfields! And by clicking sumbit to update the same field in the table!

 

I hope it's clear. 

[Prismatic]

UPDATE table SET column = '$var' WHERE column = 'something'

[solon]

Hey Prismatic , thanks for the quick reply but this is not what i m looking for!

Lets say if i have a table with fileds: Name, Surname, Age

 

And i only have data in fields :Name and Surname

 

I have 10 rows of information in the table and i want to update all 10 rows for the field: Age

 

Example:

The data in my table looks like this:

 

Name:

Surname:

Age:

 

name1

surname1

[/td]

 

name2

surname2

 

name3

surname3

.

.

.

.

 

name10

surname10

 

I want to update it to look like this:

 

[td]Name:

Surname:

Age:

 

name1

surname1

21

 

name2

surname2

24

 

name3

surname3

23

.

.

.

.

name10

surname10

20

 

[regdude]

Try this:

UPDATE table name1='Bob', name2='Sarah', name3='John' WHERE id=1

[solon]

Nop that wont work either! I think its a bit moer complicated than this! Thanks anw

[kenrbnsn]

Please show us the code you've been trying. Without seeing what you're doing, we can't help you.

 

Ken

[solon]

Some of it, actually most of it i got form another thread in the forum but unfortunately is a litlle different cause i have to Update a table and not insert data!

This is the code i am using:

<?php
$checkEmpty = 0; //counter to keep track of the amount of empty rows submitted.
$success = array(); //keep records that were successfully inserted into DB for success msg.

$query1 = mysql_query("SELECT * FROM `football` WHERE `date` = '$today' && `time` < '$time' ORDER BY `football`.`time` ASC");
$num_rows = mysql_num_rows($query1);


for ($i = 0; $i < $num_rows; ++$i)
{


		$result = mysql_real_escape_string($_POST['result'][$i]);
		if (empty($result))
		{
			++$checkEmpty; //count each empty row
			continue;
		}
		else
		{
			while($row = mysql_fetch_assoc($query1))
			{
			$game_id = $row[game_id];
			$sql= mysql_query("UPDATE `football` SET `result`='$result' WHERE `date` = '$today' && `time` < '$time' && `game_id`='$game_id'");
			$success[] = array($result);	
			}
		}	
}
//if all 5 rows were empty, echo the error msg. else, print success msg
echo (($checkEmpty == $num_rows) ? ("You can't send a blank form") 
   : ("The following records are added:<br><br>"));

//print information that was added to the DB
$loopCount = count($success);
for ($i = 0; $i < $loopCount; ++$i)
{
  echo "'";

  echo implode("', '", $success[$i]);
  echo "'";
  echo "<br />";
} 


//print information that was added to the DB
echo "<br><br>";
echo "<b>$num_rows</b>";
mysql_close($con)
?>

[kenrbnsn]

We also need to see the form.

 

Ken

[solon]

sorry about that.

Here is the form:

echo "<form name='form1' method='post' action='insert.php'>\n";


echo "<table border=\"0\" width=\"500\" cellpadding=\"3\" cellspacing=\"3\">
    <tr>
        <td align='center'>Event</td>
        <td>Time</td>
        <td>Result</td>
    </tr>";
$query1 = mysql_query("SELECT * FROM `football` WHERE `date` = '$today' && `time` < '$time' ORDER BY `football`.`time` ASC");
$num_rows = mysql_num_rows($query1);
echo $num_rows;
if($query1 >=1)
{
while($row = mysql_fetch_assoc($query1))
{
  echo "<tr>
        <td align='center'>$row[home_team] Vs $row[away_team]</td>
        <td>$row[time]</td>
	<td><input type=\"text\" size=\"10\" name=\"result[]\" /></td>
    </tr>
    
";
}
echo "</table><br>";

echo "<input type='submit' name='submit' value='Submit'>\n";

echo "</form>\n";
}
else echo"error";

[solon]

Ok guys i figured it out !

This is the finished code:

 

The Form:

<?php

echo "<form name='form1' method='post' action='insert.php'>\n";


echo "<table border=\"0\" width=\"500\" cellpadding=\"3\" cellspacing=\"3\">
    <tr>
        <td align='center'>Event</td>
        <td>Time</td>
        <td>Result</td>
    </tr>";
$query1 = mysql_query("SELECT * FROM `football` WHERE `date` = '$today' && `time` < '$time' ORDER BY `football`.`time` ASC");
$num_rows = mysql_num_rows($query1);
echo $num_rows;
if($query1 >=1)
{
while($row = mysql_fetch_assoc($query1))
{
  echo "<tr>
        <td align='center'>$row[home_team] Vs $row[away_team]      $row[game_id]</td>
        <td>$row[time]<input type='hidden' value='$row[game_id]' name=\"game_id[]\" /></td>
	<td><input type=\"text\" size=\"10\" name=\"result[]\" /></td>
    </tr>
    
";
}
echo "</table><br>";

echo "<input type='submit' name='submit' value='Submit'>\n";

echo "</form>\n";
}
else echo"error";
?>

 

And the update php file:

<?php 
$checkEmpty = 0; //counter to keep track of the amount of empty rows submitted.
$success = array(); 
$success2 = array();//keep records that were successfully inserted into DB for success msg.
$query1 = mysql_query("SELECT * FROM `football` WHERE `date` = '$today' && `time` < '$time' ORDER BY `football`.`time` ASC");
$num_rows = mysql_num_rows($query1);


for ($i = 0; $i < $num_rows; ++$i)
{
		$result = mysql_real_escape_string($_POST['result'][$i]);
		$game_id = mysql_real_escape_string($_POST['game_id'][$i]);
		if (empty($result) || empty($game_id))
		{
			++$checkEmpty; //count each empty row
			continue;
			echo "test";
		}
		else
		{

			$sql= mysql_query("UPDATE `football` SET `result`='$result' WHERE `date` = '$today' && `time` < '$time' && `game_id`='$game_id'");
			$success[] = array($result, $game_id);

		}	
}
//if all 5 rows were empty, echo the error msg. else, print success msg
echo (($checkEmpty == $num_rows) ? ("You can't send a blank form") 
   : ("The following records are added:<br><br>"));

//print information that was added to the DB
$loopCount = count($success);
for ($i = 0; $i < $loopCount; ++$i)
{
  echo "'";

  echo implode("', '", $success[$i]);
  echo "'";
  echo "<br />";
} 


//print information that was added to the DB
echo "<br><br>";
echo "<b>$num_rows</b>";
mysql_close($con)
?>

 

Thanks for your time 

[sasa]

1st change form to

<?php
echo "<form name='form1' method='post' action='insert.php'>\n";


echo "<table border=\"0\" width=\"500\" cellpadding=\"3\" cellspacing=\"3\">
    <tr>
        <td align='center'>Event</td>
        <td>Time</td>
        <td>Result</td>
    </tr>";
$query1 = mysql_query("SELECT * FROM `football` WHERE `date` = '$today' && `time` < '$time' ORDER BY `football`.`time` ASC");
$num_rows = mysql_num_rows($query1);
echo $num_rows;
if($query1 >=1)
{
while($row = mysql_fetch_assoc($query1))
{
  echo "<tr>
        <td align='center'>$row[home_team] Vs $row[away_team]</td>
        <td>$row[time]</td>
	<td><input type=\"text\" size=\"10\" name=\"result[".$row['game_id']."]\" /></td>
    </tr>
    
";
}
echo "</table><br>";

echo "<input type='submit' name='submit' value='Submit'>\n";

echo "</form>\n";
}
else echo"error";
?>

and

<?php
$checkEmpty = 0; //counter to keep track of the amount of empty rows submitted.
$success = array(); //keep records that were successfully inserted into DB for success msg.

//$query1 = mysql_query("SELECT * FROM `football` WHERE `date` = '$today' && `time` < '$time' ORDER BY `football`.`time` ASC");
$num_rows = count($_POST['result']);


//for ($i = 0; $i < $num_rows; ++$i)
foreach ($_POST['result'] as $game_id => $result)
{


		$result = mysql_real_escape_string($result);
		if (empty($result))
		{
			++$checkEmpty; //count each empty row
			continue;
		}
		else
		{
			//while($row = mysql_fetch_assoc($query1))
			//{
			//$game_id = $row[game_id];
			$sql= mysql_query("UPDATE `football` SET `result`='$result' WHERE `date` = '$today' && `time` < '$time' && `game_id`='$game_id'");
			$success[] = $result;	
			//}
		}	
}
//if all 5 rows were empty, echo the error msg. else, print success msg
echo ((count($success) == 0) ? ("You can't send a blank form") 
   : ("The following records are added:<br><br>"));
echo "'", implode("', '", $success),"'<br />\n";
//print information that was added to the DB
echo "<br><br>";
echo "<b>$num_rows</b>";
mysql_close($con)
?>