My ID has no value and cannot be updated by UPDATE

Need help on MySQL UPDATE Query

[scankified]

Hi, for some reason the field I am trying to update cannot be updated because there is no ID being carried through. This is what I have:

"UPDATE books SET readstatus='". $readstatus ."' WHERE book_id=". (int) $book_id;

I put 'echo $sql' to echo show the query, and a value of '0' is being shown at the book ID? I have declared the variableas at the the top like so:

$book_id = $_POST['book_id'];
$readstatus = $_POST['readstatus'];

Before the user clicks 'update book', in the URI the book ID is already present the book being updated? Does anyone have an idea how to correct this? Thanks

[Matthew1980]

hi there scankified,

[EDIT]:

Oop's nearly forgot:-

$book_id = strip_tags(mysql_real_escape_string($_POST['book_id']));
$readstatus = strip_tags(mysql_real_escape_string($_POST['readstatus']));

Now any data coming from a $_POST var is protected as you are using it in a sql query. Dodgy HTML is also taken out ;-p

$sql = "UPDATE `books` SET `readstatus` = '".$readstatus."' WHERE `book_id` = '".(int)$book_id."' LIMIT 1";

try this if you are doing just one record ;-p

[EDIT]
If the value is set in the URL you are setting the value in a link somewhere, which if this is the case, you would need to $_GET as topr8 points out: $_GET from URL's $_POST form's is my general rule of thumb ;-p

Hope this helps you.

Cheers,
MRb

[edited by: Matthew1980 at 4:03 pm (utc) on Mar 17, 2010]

[topr8]

if the bookid is already in the uri, don't you mean

$_GET rather than $_POST

(i may easily have misunderstood what you meant though)

[scankified]

Thanks for that Matthew, but I think I already have this sort of setup at the top of my execution script...

$book_id = $_GET['book_id'];
$readstatus = $_POST['readstatus'];

and then my query... Also torpr8, I am using GET and its still not working :(

[scankified]

Even if I am using GET (which I am) would there be anything else stopping book_id as the php variable, from being populated? If I runt the query assign a straight number instead of the php insertion, it works fine, and updates the record!

[Matthew1980]

Hi there scankified,

Are you checking to see if any $_POST data is being sent to this part of the script, ie, is the form (after submit) being sent with all keys & values set as expected: Use print_r($_POST); to see if this is the case.

Again, use $_GET if you are sending the information to this part of the script via an a href="somepage.php?book_id=10" for example..

I should say really, where are these values being set & sent from first i suppose ;-)

[edit]
It will work properly, that just checks the sql query, not the vars leading upto the sql creation, trace from source where the $_GET/$_POST is set and see how your passing that info to this script ;-p

Just thought, try the sql statement without the (int) ie do this:


$sql = "UPDATE `books` SET `readstatus` = '".$readstatus."' WHERE `book_id` = '".$book_id."' LIMIT 1";

and whether this is $_GET or $_POST try this instead, might not be the cause, but its better to set it here than in the sql statement:

$book_id = (int)$_GET['book_id'];

Cheers,
MRb

[scankified]

basically, I have 10 options in my update form, 10%, 20% 30% etc. as a select option for the readstatus, this is being posted and each option is set like this:

<?php echo '10%'; if (isset($_POST['readstatus'])); ?>
this is the same for every percentage option!

I know this is being passed through the script correctly as I have manually entered a percentage in 1 column and then this is being correctly displayed on my update form as the default one, as I required;

<option value="<?php echo $row['readstatus']?>" SELECTED><?php echo $row['readstatus']?></option>

The method for the form is POST, I tried changing this to GET but no difference... its only the ID that is not being traced once the user clicks Update.

On the page where you can view all information on books, I have this at the top to request the name for the Book from the ID:

$book_id = $_GET['book_id'];
$sql = "SELECT bookname FROM books WHERE book_id=" . $book_id;

This allows me to retrieve the ID, and then like you have specified, my URL is exactly as your have stated:

'userupdatebookprogress.php?book_id=11'

Can you explain this print_r activity, I have never come across this sorry! :D

[Matthew1980]

hi there scankified,

Firstly

I hope as you altered the sql string from what you posted on your last message, as the value from $book_id is not added to the query properly:-

$sql = "UPDATE `books` SET `readstatus` = '".$readstatus."' WHERE `book_id` = '".$book_id."' LIMIT 1";

It needs to be enclosed bewteen the '' single quotes so that when it is passed to the database, the value is then interpreted correctly, the LIMIT 1 clause just says only 1 record to be returned, if that's not how you require it just do this:

$sql = "UPDATE `books` SET `readstatus` = '".$readstatus."' WHERE `book_id` = '".$book_id."' ";

Secondly
print_r();
"Prints human-readable information about a variable" quote from php.net

Use this to view the $_POST array after it's been sent OR when it's set, which is more contextually correct ;-p

It's a great tool to use for seeing what's in an array - good for debugging

$values_in_array = array("one","two","three","four");

print_r($values_in_array);

outputs to screen:

array(
"0" => "one",
"1" => "two",
"2" => "three",
"3" => "four",
)

Thirdly

<option value="<?php echo $row['readstatus'];?>" SELECTED><?php echo $row['readstatus'];?></option>

You missed the ; semi-colon from the echoed var..

Maybe I'm reading this wrong but:-

<?php echo '10%'; if (isset($_POST['readstatus'])); ?>

What's this doing? there is no action to the clause

if(isset($_POST['readstatus']))
{
//do something if this evaluates to true
}else{
//do something if this evaluates to false
}

Depending on how you do this and what you want to happen you don't necessarily need the else{} part

I'm not sure as this is correct syntax though, new to me if it is ;-p

if (isset($_POST['readstatus']));

It's not doing anything?!?

[EDIT]
Something has just occurred to me, could you post the code where the 'book_id' var is actually set in the <a href> link please, see if the link is getting set correctly :)

Hopefully that clears a few issues up for you..

Cheers,
MRb