update multi row with for Schleife

[rafal]

Hello everebody,
i have code to updare many rows at once using "for".
when i click submit button nothing happens, where is the error in my code?

thank you very much for your help

 <?php
$hoster="db.mkq.de";
$username="dbxxxxxx";
$password="xxxxxx";
$db_name="xxxxxx";
mysql_connect("$hoster", "$username", "$password")
or die("cannot connect");
mysql_select_db("$db_name") 
or die("cannot select DB");
$sql="SELECT * FROM gbook";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>www</strong></td>
<td align="center"><strong>email</strong></td>
<td align="center"><strong>pwd</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td align="center">
<input name="id[]" type="text" id="id" value="<? echo $rows['id']; ?>">
</td>
<td align="center">
<input name="www[]" type="text" id="www" value="<? echo $rows['www']; ?>">
</td>
<td align="center">
<input name="email[]" type="text" id="email" value="<? echo $rows['email']; ?>">
</td>
<td align="center">
<input name="pwd[]" type="text" id="pwd" value="<? echo $rows['pwd']; ?>">
</td>
</tr>

<?php
}
?>

<tr>
<td colspan="4" align="center"><input type="submit" name="submit" id="submit" value="update"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>

<?php

if($submit)
{
for($i=0;$i<$count;$i++)
{
$sql1="UPDATE gbook SET www='$www[$i]', email='$email[$i]', pwd='$pwd[$i]' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1)
{
header("location:editmulti3.php");
}
mysql_close();
?> 

[CroNiX]

if($submit)

Where is submit defined?

[rafal]

hello CroNiX,

submit is the submit input to send the form.

[Psycho]

hello CroNiX,

submit is the submit input to send the form.

 

No, it is not. A value is submitted through a POST or GET request through one of the global variables such as $_POST['field_name'] or $_GET['field_name']. $submit is a variable that would need to be defined.

[rafal]

hello Guru,

i have changed this variable as following, but still not working!

<?php
if (isset($_POST['submit']))
{
for($i=0;$i<$count;$i++)
{
$sql1="UPDATE gbook SET www='$www[$i]', email='$email[$i]', pwd='$pwd[$i]' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1)
{
header("location:multiedit3.php");
}
mysql_close();
?>

[CroNiX]

Same thing...where is $www, $email, $pwd coming from in your query? They're not defined! Those also come from $_POST. You are also using the very old and deprecated mysql driver which will be removed from php soon, so you're code will already be obsolete. Use mysqli or PDO.

 

Do yourself a favor and enable error reporting. Check to see if your query actually works, and if not have it display the error so you can tell.  These things should be obvious if you view the errors.

[Psycho]

FYI: Using an input button as a submit button to check if the form was submitted is not the best way. It's possible to submit a form without clicking the button. You should instead check the request method

if($_SERVER['REQUEST_METHOD']=='POST')

And, what are you expecting to "happen". There is nothing in your code that would produce output. Based on the other undefined variables that ch0cu3r pointed out, I would expect you would get new records with empty values.

Edited August 27, 2014 by Psycho

[Psycho]

Some other notes:

 

1. Don't use mysql_ functions. They are deprecated. Use either mysqli_ or PDO. I didn't make any changes though, because I am too lazy at the moment.

 

2. Do not use "SELECT *" and then a row count to determine the number of records. You are making the server select ALL data in the entire table just to get a count. Use the SQL COUNT() function. In fact, never use "*" for your select unless you absolutely need every column.

 

3. Why do you have nested tables?

 

4. Don't use TD tags for the table headers. Use TH tags - that's what they are for and will be bold and centered by default!

 

5. The logic is a mess. You go through all the logic to build the page then, at the end, you have a line that may redirect the user to another page. That should be before any logic to create the page. Otherwise, it is a complete waste of effort if you do redirect.

 

6. Don't run queries in loops. Always try to create ONE query. I didn't modify it here because I think it would confuse you.

 

7. Indent your code so you can "see" the structure

 

8. The ID should not be an input field.

 

 

Here is a complete rewrite. I did this on-the-fly with no testing. So, there may be a few minor errors. But, this is a much better logical workflow than there was previously

<?php
 
//Connect to DB server and select DB
$hoster="db.mkq.de";
$username="dbxxxxxx";
$password="xxxxxx";
$db_name="xxxxxx";
mysql_connect("$hoster", "$username", "$password")
    or die("Unable to connect to DB server: " . mysql_error());
mysql_select_db("$db_name") 
    or die("Cannot select DB: " . mysql_error());
 
//If form was posted, process the data
$updateErrors = array();
$errorMsg = '';
if($_SERVER['REQUEST_METHOD']=='POST')
{
    foreach($_POST['records'] as $id => $record)
    {
        $query = "UPDATE gbook
                  SET www='{$record['www']}', email='{$record['email']}', pwd='{$record['pwd']}'
                  WHERE id='$id'";
        $result = mysql_query($query);
        if(!$result)
        {
            $updateErrors[] = $id;
        }
    }
 
    //If no errors, redirect
    if(!count($updateErrors))
    {
        header("location:editmulti3.php");
        exit();
    }
 
    $errorMsg = "Updates to the following record IDs failed: " . implode(', ', $updateErrors);
}
 
//Create the form
$query = "SELECT id, www, email, pwd FROM gbook";
$result = mysql_query($query)
    or die("Error running query: " . mysql_error());
$formHTML = '';
while($row = mysql_fetch_assoc($result))
{
    $formHTML = "<tr>\n";
    $formHTML = "    <td align='center'>{$row['id']}</td>\n";
    $formHTML = "    <td align='center'>\n";
    $formHTML = "        <input name=\"records[{$row['id']}]['www']\" type='text' id='www' value='{$row['www']}'>\n";
    $formHTML = "    </td>\n";
    $formHTML = "    <td align='center'>\n";
    $formHTML = "        <input name=\"records[{$row['id']}]['email']\" type='text' id='email' value='{$row['email']}'>\n";
    $formHTML = "    </td>\n";
    $formHTML = "    <td align='center'>\n";
    $formHTML = "        <input name=\"records[{$row['id']}]['pwd']\" type='text' id='pwd' value='{$row['pwd']}'>\n";
    $formHTML = "    </td>\n";
    $formHTML = "</tr>\n";
}
 
mysql_close();
 
?>
<html>
<body>
 
<div style="color:#ff0000"><?php echo $errorMsg; ?></div>
 
<form name="form1" method="post" action="">
<table width="500" border="0" cellspacing="1" cellpadding="0">
    <tr>
        <th><strong>Id</th>
        <th><strong>www</th>
        <th><strong>email</th>
        <th><strong>pwd</th>
    </tr>
    <?php echo $formHTML; ?>
    <tr>
        <td colspan="4" align="center"><button type="submit" id="submit">Submit</button></td>
    </tr>
</table>
</form>
 
</body>
</html>

Edited August 27, 2014 by Psycho

[rafal]

Dear Guru,

Thank you very much

you are great man because you have invested alot of time to help me.

thank you very much again

 

when i call the page multiedit3.php i see only the words "id www email pwd" and i see submit button.

i dont see any other inputs, or data from the database.

[rafal]

Dear Guru,

i have changed the code to make it easy but it still not not showing inputs.

this is my last version.

<?php
$hoster="xxx";
$username="xxx";
$password="xxx";
$db_name="xxx";
mysql_connect("$hoster", "$username", "$password")
or die("Unable to connect to DB server: " . mysql_error());
mysql_select_db("$db_name") 
or die("Cannot select DB: " . mysql_error());

//If form was posted, process the data
$updateErrors = array();
$errorMsg = '';
if($_SERVER['REQUEST_METHOD']=='POST')
{
foreach($_POST['records'] as $id => $record)
{
$query = "UPDATE gbook
SET www='{$record['www']}', email='{$record['email']}', pwd='{$record['pwd']}'
WHERE id='$id'";
$result = mysql_query($query);
if(!$result)
{
$updateErrors[] = $id;
}
}
//If no errors, redirect
if(!count($updateErrors))
{
header("location:editmulti4.php");
exit();
}
$errorMsg = "Updates to the following record IDs failed: " . implode(', ', $updateErrors);
}
 
//Create the form
$query = "SELECT id, www, email, pwd FROM gbook";
$result = mysql_query($query)
or die("Error running query: " . mysql_error());
//$formHTML = '';

?>
<html>
<body>
 
<div style="color:#ff0000"><?php echo $errorMsg; ?></div>
 
<form name="form1" method="post" action="">
<table width="500" border="0" cellspacing="1" cellpadding="0">
<tr>
<th><strong>Id</th>
<th><strong>www</th>
<th><strong>email</th>
<th><strong>pwd</th>
</tr>
<?php
while ($row = mysql_fetch_assoc($result))
{
?>
<tr>
<td><?php echo $row[id] ?></td>
<td><input value="<?php echo $row[www] ?>" name="<?php records($row[id])[email] ?>" type="text" id="www"></td>
<td><input value="<?php echo $row[email] ?>" name="<?php records($row[id])[email] ?>" type="text" id="email"></td>
<td><input value="<?php echo $row[pwd] ?>" name="<?php records($row[id])[pwd] ?>" type="text" id="pwd"></td>
</tr>
<?php
}
mysql_close();
?>
<tr><td colspan="4"><button type="submit" id="submit">Submit</button></td></tr>
</table>
</form>
</body>
</html>

Edited August 28, 2014 by rafal

[Psycho]

As I stated, I didn't test that code. I don't have your DB to test it with. But, if you are going to modify code - pay attention to what you are doing. You changed some things that should not. For example you changed the field names using () instead of [], which would prevent them from being correct arrays.

 

One problem with the original code I posted was that the variable $formHTML to hold the form fields was using "=" instead of ".=" to concatenate the data. So, you would only end up with the very last value - a single "<td>".

 

Try this

 

<?php
 
//Connect to DB
$hoster="xxx";
$username="xxx";
$password="xxx";
$db_name="xxx";
mysql_connect("$hoster", "$username", "$password")
    or die("Unable to connect to DB server: " . mysql_error());
mysql_select_db("$db_name") 
    or die("Cannot select DB: " . mysql_error());
 
//If form was posted, process the data
$updateErrors = array();
$errorMsg = ''; //Set default error message
if($_SERVER['REQUEST_METHOD']=='POST')
{
    foreach($_POST['records'] as $id => $record)
    {
        $query = "UPDATE gbook
                  SET www='{$record['www']}', email='{$record['email']}', pwd='{$record['pwd']}'
                  WHERE id='$id'";
        $result = mysql_query($query);
        if(!$result)
        {
            $updateErrors[] = $id;
        }
    }
    //If no errors, redirect
    if(!count($updateErrors))
    {
        header("location:editmulti4.php");
        exit();
    }
    $errorMsg = "Updates to the following record IDs failed: " . implode(', ', $updateErrors);
}
 
//Create the form
$query = "SELECT id, www, email, pwd FROM gbook";
$result = mysql_query($query)
    or die("Error running query: " . mysql_error());
$formHTML = '';
while ($row = mysql_fetch_assoc($result))
{
    $formHTML .= "<tr>\n";
    $formHTML .= "<td>{$row['id']}</td>\n";
    $formHTML .= "    <td><input value='{$row['www']}'   name='records[{$row['id']}]['www']'   type='text' id='www'></td>\n";
    $formHTML .= "    <td><input value='{$row['email']}' name='records[{$row['id']}]['email']' type='text' id='email'></td>\n";
    $formHTML .= "    <td><input value='{$row['pwd']}'   name='records[{$row['id']}]['pwd']'   type='text' id='pwd'></td>\n";
    $formHTML .= "</tr>\n";
}
 
?>
<html>
<body>
 
<div style="color:#ff0000"><?php echo $errorMsg; ?></div>
 
<form name="form1" method="post" action="">
<table width="500" border="0" cellspacing="1" cellpadding="0">
    <tr>
        <th><strong>Id</th>
        <th><strong>www</th>
        <th><strong>email</th>
        <th><strong>pwd</th>
    </tr>
    <?php echo $formHTML; ?>
    <tr><td colspan="4"><button type="submit" id="submit">Submit</button></td></tr>
</table>
</form>
</body>
</html>

[rafal]

Dear Guru,

thank you very much again.

now is see all inputs, but i gived in 1 text input the letter "y".

now i lost all data.

the first input row shows y y y

all next input rows show 6 6 6

[rafal]

whatever i enter in any input, after i click submit, the first row shows y y y

and the next rows show 6 6 6

[rafal]

Hello Guru,

now i restarted the server.

i tell you now what works and what dosnot work.

- i see all inputs.

- i can not update when i enter data in www or email inputs.

- i can update only when i enter data in pwd, but after click submit, the data will be same in all 3 inputs www email pwd.

this is the sitiuation now.

thanks for your help

[jazzman1]

Check the id's value of Psycho's script - WHERE id='$id'

 

BTW: What does schleife mean in English? Is it a German word?

Edited August 28, 2014 by jazzman1

[mikosiko]

Hello Guru,

now i restarted the server.

i tell you now what works and what dosnot work.

- i see all inputs.

- i can not update when i enter data in www or email inputs.

- i can update only when i enter data in pwd, but after click submit, the data will be same in all 3 inputs www email pwd.

this is the sitiuation now.

thanks for your help

 

change this part of the code provided by Psycho:

while ($row = mysql_fetch_assoc($result))
{
    $formHTML .= "<tr>\n";
    $formHTML .= "<td>{$row['id']}</td>\n";
    $formHTML .= "    <td><input value='{$row['www']}'   name='records[{$row['id']}]['www']'   type='text' id='www'></td>\n";
    $formHTML .= "    <td><input value='{$row['email']}' name='records[{$row['id']}]['email']' type='text' id='email'></td>\n";
    $formHTML .= "    <td><input value='{$row['pwd']}'   name='records[{$row['id']}]['pwd']'   type='text' id='pwd'></td>\n";
    $formHTML .= "</tr>\n";
}

with this: (it include just a small change)... and test.... works ok in my side after the change

while ($row = mysql_fetch_assoc($result))
{
    $formHTML .= "<tr>\n";
    $formHTML .= "<td>{$row['id']}</td>\n";
    $formHTML .= "    <td><input value='{$row['www']}'   name='records[{$row['id']}][www]'   type='text' id='www'></td>\n";
    $formHTML .= "    <td><input value='{$row['email']}' name='records[{$row['id']}][email]' type='text' id='email'></td>\n";
    $formHTML .= "    <td><input value='{$row['pwd']}'   name='records[{$row['id']}][pwd]'   type='text' id='pwd'></td>\n";
    $formHTML .= "</tr>\n";
}

[rafal]

thank you very much psycho and mikosiko,

now the code works

here is the working code.

<?php
$hoster="###";
$username="###";
$password="###";
$db_name="###";
mysql_connect("$hoster", "$username", "$password")
    or die("Unable to connect to DB server: " . mysql_error());
mysql_select_db("$db_name") 
    or die("Cannot select DB: " . mysql_error());
 
//If form was posted, process the data
$updateErrors = array();
$errorMsg = ''; //Set default error message
if($_SERVER['REQUEST_METHOD']=='POST')
{
    foreach($_POST['records'] as $id => $record)
    {
        $query = "UPDATE gbook
                  SET www='{$record['www']}', email='{$record['email']}', pwd='{$record['pwd']}'
                  WHERE id='$id'";
        $result = mysql_query($query);
        if(!$result)
        {
            $updateErrors[] = $id;
        }
    }
    //If no errors, redirect
    if(!count($updateErrors))
    {
        header("location:multiedit5.php");
        exit();
    }
    $errorMsg = "Updates to the following record IDs failed: " . implode(', ', $updateErrors);
}
 
//Create the form
$query = "SELECT id, www, email, pwd FROM gbook";
$result = mysql_query($query)
    or die("Error running query: " . mysql_error());
$formHTML = '';
while ($row = mysql_fetch_assoc($result))
{
    $formHTML .= "<tr>\n";
    $formHTML .= "<td>{$row['id']}</td>\n";
    $formHTML .= "    <td><input value='{$row['www']}'   name='records[{$row['id']}][www]'   type='text' id='www'></td>\n";
    $formHTML .= "    <td><input value='{$row['email']}' name='records[{$row['id']}][email]' type='text' id='email'></td>\n";
    $formHTML .= "    <td><input value='{$row['pwd']}'   name='records[{$row['id']}][pwd]'   type='text' id='pwd'></td>\n";
    $formHTML .= "</tr>\n";
}
 
?>
<html>
<body>
 
<div style="color:#ff0000"><?php echo $errorMsg; ?></div>
 
<form name="form1" method="post" action="">
<table width="500" border="0" cellspacing="1" cellpadding="0">
    <tr>
        <th><strong>Id</th>
        <th><strong>www</th>
        <th><strong>email</th>
        <th><strong>pwd</th>
    </tr>
    <?php echo $formHTML; ?>
    <tr><td colspan="4"><button type="submit" id="submit">Submit</button></td></tr>
</table>
</form>
</body>
</html>