Unable to update

[Hate]

Hello,

 

I've never really used the update command before for mysql and I'm attempting to use it and struggling a little bit. I'm trying to use mysqli prepared statements.. here's the code that I have thus far:

if($query = $database->connection->prepare("UPDATE videos SET comments=?, views=?, uploader=? WHERE title = ?"))
{
    $query->bind_param('iiss', $comments, $views, $uploader, $title);
    $query->execute();
    $result = $query->affected_rows;
    $query->close();
}

 

For some reason I cannot get this working. I have created a modification page for the administrators to be able change any of the values and wanting to update the database to reflect the changes. When using the MySQL UPDATE command do all of the values have to get changed or modified, or am I able to pass back some of the same values? Like with the above code.. if I only wanted to update the views, would I still be able to just pass in the same values for comments and uploader and it would just replace the values?

[MMDE]

I'm not sure what all of your code does, but you should do this to your mysql_query function execution:

mysql_query("UPDATE table SET coltypestring = 'string', coltypenumber = 69 WHERE coltypestring2 = 'string'") or die(mysql_error());

Do you see the two changes? When there's string types, they need ', numbers don't need 'em, and at the end I kill the script if the query returns false (there's an error) and then it prints out the mysql error.

 

You may want to print out the query right before you execute it as well, just to see that it is what you think it is. Maybe show us exactly what it looks like, so we can help you correct it.

[creata.physics]

He's not using procedural style coding so a basic mysql_query may not work without the connections resource linked to the query. He may even be using PDO or the built in mysqli class.

 

@Hate - have you made sure all of your variables have values before trying the update? by echoing or dumping, $comments, $views, $uploader, $title

[Hate]

He's not using procedural style coding so a basic mysql_query may not work without the connections resource linked to the query. He may even be using PDO or the built in mysqli class.

 

@Hate - have you made sure all of your variables have values before trying the update? by echoing or dumping, $comments, $views, $uploader, $title

 

I'm using the built in mysqli class.  Yes, I've tested all of my variables before attempting to do the update. That's why I'm clueless. I know that not all of the values change when doing the update.. so I'm not sure if it's having trouble replacing one value with the same value. For example -- Let's say that the views and uploader change, but the comments doesn't change.. just the same value is passed back in to be updated. Would that be a problem?

[creata.physics]

No it won't be a problem, it'll just consume that much more time updating a field with the same value which isn't a big deal if that code isn't executed on each page.

 

Do you even reach the inside of your if statement?

 

Can you comment out the code you use now and try:

<?php $database->query("update videos set comments= '$comments', views='$views', uploader='$uploader' WHERE title = '$title'"); ?>

 

Honestly I think the issue is with your if statement.

 

You're saying if $query which is not set = true then proceed.

 

If the query that I provided works try this code instead:

<?php
$query = $database->connection->prepare("UPDATE videos SET comments=?, views=?, uploader=? WHERE title = ?");
if( is_object( $query ) )
{
    $query->bind_param('iiss', $comments, $views, $uploader, $title);
    $query->execute();
    $result = $query->affected_rows;
    $query->close();
}

[PFMaBiSmAd]

If you try to update a column to the same value, nothing takes place. An update query actually reads the row to be updated (it must find the row in order to update it, so reading the current values adds only a little overhead.) Only changed values are written to the database.

 

You need to troubleshoot what your code is doing. $query->bind_param() will return a true or false value. You need to test it to see if the bind worked or failed. $query->execute() also returns a true or false value. If either of those statements fail, accessing the $query->error property will tell you why they failed.

 

You actually need to have error checking, error reporting, and error recovery logic in all of your code to test if each step worked or failed. Your error reporting logic would output a user message on a live server - 'Sorry, the requested page cannot be displayed' and it would use something like trigger_error to handle the application level error reporting to log the actual error on a live server and to display it on a development system.

[PFMaBiSmAd]

Further to the above, accessing the ->error property after a ->bind() statement fails won't tell you why the bind failed (at least in php 5.3.8.) You will get a php warning logged/displayed from the bind statement and a false back from the bind statement, but the ->error property is empty. If you ignore the bind error and go on to execute the query, you will get a false back from the ->execute() statement and a vague message in the ->error property stating that - "No data supplied for parameters in prepared statement" (tested both for a wrong number of bind parameters and an invalid field specifier type.)

 

[offtopic rant]

And people wonder why there's an amount of hate directed at php over simple things that should work in an expected and consistent manor.[/rant]