...

View Full Version : Trying to update multiple rows mysqli



surreal5335
07-26-2011, 10:01 AM
I need to update all the rows in a table.

Currently I am using a foreach to loop through the various input values and update the database in the appropriate places

Here is the code I am using so far:


function update_admin()
{
// create new record
if(isset($_POST['submit']))
{

$Database = new mysqli('localhost', '#####', '#######', '######');

mysqli_report(MYSQLI_REPORT_ERROR);


$ids_array = get_user_ids();
$num_row = get_user_rows();
$x = 1;
while($x < $num_row)
foreach($ids_array as $id)
{
$user_name = htmlentities($_POST['user_'.$id.'_name'], ENT_QUOTES);
$nick_name = htmlentities($_POST['nick_'.$id.'_name'], ENT_QUOTES);
$update_super = $_POST['update_'.$id.'_super'];
$set_super = 0;
if(isset($update_super))
{
$set_super = 1;
}

if ($stmt = $Database->query("UPDATE views SET view_name='".$user_name."', view_pass='".$nick_name."', view_priv='".$update_super."' WHERE view_id='".$id."'" ))
{

$stmt->execute();

}
else
{
echo 'Error: could not prepare SQL statement.<br />';
}
}
$stmt->close();
header("Location: home.php");


}

}// end submit_form()


Right now I am not getting any results from this, site just takes a long time to reload the page.

Side note: $ids_array = get_user_ids(); does work properly, so no worries there.

I appreciate the help

Fou-Lu
07-26-2011, 03:19 PM
This isn't a statement:


if ($stmt = $Database->query("UPDATE views SET view_name='".$user_name."', view_pass='".$nick_name."', view_priv='".$update_super."' WHERE view_id='".$id."'" ))
{

$stmt->execute();

}

That's a direct query. The execute will fail on that (fatally). $stmt is a resultset, not a prepared statement, choose one or the other that you'd like to use. If you choose the continue querying, you must escape that input data provided by the post.
If you choose a prepared statement, you prepare the query BEFORE the foreach call, and you bind your parameters and execute from the loop.

surreal5335
07-27-2011, 08:37 AM
Thanks for the reply, I got the query going using the query not the statement.

Although I am now having an issue with my checkbox 'view_priv' getting populated from the database correctly. I had it working before fine and have not changed the code that populates it on the page. My issue must be in the update_user() function with the redirect at the end.

Here is what I have:


function update_admin()
{
// create new record
if(isset($_POST['submit']))
{

$Database = new mysqli('localhost', '######', '#########', '########');

mysqli_report(MYSQLI_REPORT_ERROR);


$ids_array = get_user_ids();
$num_row = get_user_rows();
$x = 1;




foreach($ids_array as $id)
{
$user_name = htmlentities($_POST['user_'.$id.'_name'], ENT_QUOTES);
$nick_name = htmlentities($_POST['user_'.$id.'_pass'], ENT_QUOTES);
if($user_name == '' || $nick_name == '')
{
echo 'Please fill in all the user names and passwords.';
sleep(4);
header("Location: home.php");
}

$update_super = $_POST['update_'.$id.'_super'];
$set_super = 0;
if(isset($update_super))
{
$set_super = 1;
}

$user_name = $Database->real_escape_string($user_name);
$nick_name = $Database->real_escape_string($nick_name);



if ($Database->query("UPDATE views SET view_name='".$user_name."', view_pass='".$nick_name."', view_priv='".$update_super."' WHERE view_id='".$id."'" ))

{


}
else
{
echo 'Error: could not prepare SQL statement.<br />';
}
} // end foreach


header("Location: home.php");
$Database->close();
} // end if(isset($_POST['submit']))

}// end submit_form()



Any ideas?

Thanks for the help



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum