Using variable to generate ID but database won't update

Can someone please take a look at this code to see what is wrong with it? If I type one of the IDs into the $id = “” line then the database updates the record perfectly but it will NOT update it if I use the $id variable. This is the code I have:

$con = mysql_connect("","exampledb","example");
if (!$con)
  die('Could not connect: ' . mysql_error());

mysql_select_db("exampledb", $con);
$product = explode('~',$_POST['cart']);

foreach($product as $val)
  $myproduct = explode(':',$val);
  // scode is now $myproduct[4] and you can do the
  // update stuff with it in the database here

    $id = ($myproduct[4]); // script works ONLY if I type the ID itself (jr016 for example)
	$dedication = substr($myproduct[0],strpos($myproduct[0], ',') + 2);
        UPDATE juniortrophies 
		SET Show_Hide = 'N', dedication = '$dedication'
		WHERE ID='$id'
    if (!mysql_query($sql,$con))
    die('Error: ' . mysql_error());


I’ve tried putting strval in front of ($myproduct[4]); - doesn’t work. I’ve tried rtrim - doesn’t work. I’ve tried strstr - doesn’t work. I’m out of ideas!

I MUST use the myproduct[4] stuff because the values are coming in from a shopping cart. The cart owner (Mal Stewart) Kindly sent me the code which produces the myproduct[4] bit and it DOES produce the correct ID data.

And whats in $is then? What’s in $myproduct anyway? Check with var_dump().

I can see the $id value is wrapped in parenthesis… not sure but maybe removing those will do the trick…

First, please look at dropping the old-style mysql_query and other library calls - the mysql library is no longer part of the language. You should use either mysqli (as you did in the other code you posted) or PDO, and you should use prepared statements instead of just sticking strings into your query, especially as you don’t appear (in the code shown) to do any validation of your user input at all. Also, not a good idea to show username and password on a public site, hopefully those aren’t the actual values.

Instead of adding random function in, use var_dump on the $myproduct array and have a look at what’s coming in from the cart as @chorn said. When you say that “it DOES produce the correct ID data”, is that because you’ve seen it in your code here by displaying it, or because you’ve seen it in the code you were given?

What type of database column is your id field? If it’s an integer, I don’t think you should have quotes around it as that turns it into a string. Again, though, prepared statements would help - the reason I don’t know whether quotes matter is because prepared statements deal with quotes as well (or at least they do in PDO, which I use) - so it’s a long time since I’ve actually put them into a query.

Agreed. I’ve edited the post.

1 Like

Thank you SO much TechnoBear! I DO know that I should not do that but totally forgot to replace those values before posting. I am getting SO frustrated with this!

I’m on the opposite side of the world (Canada) so just got out of bed but wanted to thank you right away. After breakfast finishes cooking I’ll go through the other responses.

1 Like

Quickly tried this but it made no difference. :frowning: Thanks for the thought though.

I did try this. Actually, I tried echo() and print() originally and just now tried var_dump() but none of them work because this script is performed in the background, so there is no page on which it can report errors.

However, 4 or 5 days ago that dawned on me, so I stole a script I use with another table and added it to the bottom of this script. That script sends me an e-mail with whatever information I ask it to. This does two things - confirms that the value of $id and $myproduct are correct but also whether the UPDATE script actually completed.

It currently does complete (it didn’t after a few of my changes) and the e-mail it sends says: scode (ID field) = jr016. Dedication part only:my dedication

This tells me that the ID it is finding IS correct and it is also interpreting $myproduct correctly as well, extrapolating the dedication part out of what was originally “product name: description”. In addition it tells me that there is no leading space in “my dedication”, which is why I only tried rtrim, not ltrim.

Thanks for the question, though chorn - I appreciate you taking the time.

I did try this, actually but since I don’t fully understand the differences I probably did something wrong because when I do use mysqli terms the script doesn’t work!! That’s why I went back to just plain mysql.

The validation of user input is done on the original webpage which sends the order to the secure shopping cart. There is no user input on this page - it is the page which is called by the cart’s Remote Call function and works entirely in the background.

I’m not clear, though, just what you mean by “prepared statements” - could you please elaborate?

As for using var_dump(), I’ve commented on that in my reply to chorn. Thanks for mentioning it though, as it was not a term I’d encountered before - I only knew about echo() and print()

It’s varchar and it also the PRIMARY index, hence it needs to be a string - I assume. The contents are jr001, jr001, etc. I need the “jr” part because there are 9 categories of trophies, so rather than having 9 tables, I want to be able to display the pertinent trophies for each page: Junior Handing (jr), Obedience (ob), Sweepstakes (sw), etc.

At present I’m only dealing with the few junior handling trophies in the file, as my problem isn’t getting the correct trophies to display on the page - it’s getting the sponsored trophy NOT to display.

I did a quick Google search on “prepared statement” and quickly skimmed through the results. I found this:

$stmt = $mysqli->prepare(“SELECT id, label FROM test WHERE id = 1”);

which shows me the format of a prepared statement but has EXACTLY the same problem I’m encountering in my script: I CANNOT hard code a value for ID. It MUST come from a variable extracted from the shopping cart contents and that is exactly the stumbling block I’ve encountered. Unless I’m misunderstanding something in the $stmt above.

I’m pretty sure that my problem is that there is something in the result of $id = $myproduct[4]; which the database doesn’t understand. As far as I can see, the result is correct (jr016) but for some reason the table doesn’t understand it, so won’t update the record.

Hopefully all this has clarified what I’m trying to do, as I really, really need to get this working. With 347 trophies in total, I’ll go crazy trying to manually delete each one as it is paid for via the shopping cart. This is what I had to do for last year’s specialty, because they threw the job at me at the last minute and I didn’t have time to figure out how to have them auto-update. That is what I’m desperately trying to figure out now, before people start donating for next year’s specialty.

Thanks so much for your detailed response, droopsnoot! I have full confidence that if I can get my antique, next-to-no-attention-span brain to get around all this, you folks will help me solve the problem, as you’ve always done in the past. This is THE BEST forum I’ve ever turned to for help, hands down!

OK - I’ve finally found a mysqli tutorial which my aging brain can understand, so have re-done the page but it STILL will NOT update the record!! What on EARTH am I not understanding about this?!

This is the code as it now stands (without the actual username and pw :slight_smile: )

$servername = "localhost";
$username = “…”;
$password = “…”;
$dbname = “…”;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
$product = explode('~',$_POST['cart']);

foreach($product as $val)
  $myproduct = explode(':',$val);
  // scode is now $myproduct[4] and you can do the
  // update stuff with it in the database here

    $id = $myproduct[4];
	$dedication = substr($myproduct[0],strpos($myproduct[0], ',') + 2);
    $sql="UPDATE juniortrophies 
		SET Show_Hide = 'N', dedication = '$dedication'
		WHERE ID = '$id'

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;


I’ve tried using strval() as both $id - strval($myproduct[4]) and WHERE ID = strval($id) - still doesn’t work.

Oh - and I tried using the procedural version of mysqli (from the same source, which is w3schools) but it threw up “unexpected { errors” on 3 lines in Dreamweaver, so I gave up on that and switched to object-oriented, which at least doesn’t have errors.

(a few minutes later - this does NOT work even when I hardcode in the ID value: WHERE ID = ‘jr016’ - this is insane!)

Unfortunately you’ve managed to find a poor example - that’s how you prepare a statement, but in a query that doesn’t actually need it. I use PDO rather than mysqli myself, so it’s a bit different, but the general idea is you’d do something like:

$stmt = $mysqli->prepare("UPDATE juniortrophies set show_hide='N', dedication = ? WHERE id = ?");
$stmt->bind_param("ss", $dedication, $id);  
if ($stmt->affected_rows() > 0 ) { 
  // one or more rows was updated

The two “s” characters show that the two fields are strings, and the variables are provided in the order in which they appear in the query. I think this is valid for mysqli, as I said, I use the other one.

Yes, if the column is varchar then it will need to be a string. I just wondered - normally I have an id field that is entirely unrelated to the data, and is used only to provide a unique id for the row, and in that case it’s an integer and so quotes aren’t necessarily appropriate. In your case, it would be correct.

I haven’t seen the code you added, but what happens if you put something (any character that wouldn’t normally be in there, a hash symbol or an asterisk) around the id field when you echo it into the email? That would show you any additional spaces that might be causing an issue more clearly. At the same time, can you modify that script so that it also provides the number of rows affected by the query?

I thought you meant that you were adding rtrim() to the id field, not the dedication. The id is the only one that matters at this point, as it seems that the problem is in having an id that the database recognises.

There’s no need to have 9 tables - you could just have another column that contains the category code, as long as all the row layouts are the same. In the same way that one of your pages will select rows based on show_hide being set to Y, the earlier page selection could just select them based on show_hide=Y and category=jr. For full “Database Normalisation” your category codes would be defined in another separate table, so that the code that allows you to select a category in the first place is generated from there, not hard-coded. But that’s an entirely separate thing.


What error are you getting from MySQL?

It finally dawned on me that my problem could be due to having the ID field as text, so switched it to INT and voila! I works now! It wasn’t really “voila” as I had some reconfiguring to do but I DID get it to work. The annoying part is that I spent HOURS searching through tutorials and Google, specifically looking to see if the PRIMARY ID field needed to be an INT rather than VARCHAR and found absolutely nothing.

Thanks for the code you included, though droopsnoot - I’ll keep it in case I take that route in the future.

As for your comment about PDO - could you please explain to me what the benefit(s) of PDO would be over mysqli or mysql? I thought PDO was part of mysqli but from what you’ve written here, it’s completely separate?

I’ve actually done something similar. :slight_smile: For the ID code, I’ve use 1101, 1102 … 2201, 2202, etc. up to 9901, 9902… Then on the separate html pages the SELECT is WHERE ID BETWEEN 1101 and 1156. Works like a charm. Having a separate category field didn’t occur to me and if they ever come up with more categories I’ll have to do that but in the meantime, this has worked. :slight_smile:

Thank you so much for your detailed input droopsnoot - it’s greatly appreciated.

That’s been my problem. Because this page operates in the background there IS no final page to which it can post errors. That’s why I set up the e-mail system; so I could see exactly what it thought the field values are. :slight_smile:

Php has a very good error reporting system that always operates in the background.

There are many ways to configure the system including setting your own file name report and can easily be used to check and send your own values to the generated error log.

I’m not really qualified to go into any detail, there are probably loads of sites that will go into more detail. The main thing when I was changing from mysql to something newer was that PDO will link to databases other than MySQL such as MS SQL Server, which I thought might be useful at some point.

As it happens, I also prefer the named parameters that you can use in PDO prepared statements, because I think it makes things look clearer all round. For example:

$sql = "insert into cars (make, model) values (:make, :model)";

and then refer to :make and :model when binding variables to them. In fact the only disadvantage I can find to them is when typing them in on this forum, where it pops up a list of graphics when I hit the colon character.


Yes, that becomes tricky when you can’t see what’s happening. I would suggest you email yourself both the query and the MySQL error as that should tell you all you need to know.

And as John_Betong says, make sure you turn on error reporting in devepment:


Then check the error log.

1 Like

Thanks droopsnoot. I doubt very much that I will EVER try any other kind of database, since I’m still struggling with MYSQL but it’s good to know that about PDO. Thanks also for the info about prepared statements.

Thanks martbean - but I’ve already set up the e-mail method and it worked beautifully. Thanks for the thought though.

Since @droopsnoot was able to provide a small sample of mysqli_*, I’ll explain a few things about mysqli_* compared to PDO. mysqli_* is very different from the old and removed mysql_* functions. Just appending an i after mysql will not always be sufficient enough. You have to understand how mysqli_* works and most importantly, how stmt works since that’s what droopsnoot was guiding you towards.

The only actual difference between a PDO prepared statement and a mysqli_* prepared statement is usually in the query and how the prepared statement is executed. Otherwise, it’s actually pretty easy to toggle between the 2 drivers when learning them.

For mysqli_*, you can only use the ? placeholder in your query. Using the same example from droopsnoot’s sample.

UPDATE juniortrophies set show_hide='N', dedication = ? WHERE id = ?

Notice the ? in the query. You also have to take in effect for the data types you have to also append to the bind_param method. Each data type has its own purpose and using them incorrectly may result in this error Call to a member function bind_param() on boolean. The data types are

s - string
i - integer
d - double
b - blob

I fell into this trap a while back. I wasn’t paying attention to what I was doing and found myself getting the error Call to a member function bind_param() on boolean and I couldn’t for the life of me figure out why it was throwing me that error. Understand, I’ve seen dozens and dozens of errors and I know exactly when they will happen by just looking at the code because I’ve trained my eyes to do so. What I didn’t train myself to do was understand other data types in SQL. So what I essentially was doing was using ENUMs which are kind of like a list of strings in SQL and I was putting integers in there. Then in my bind_param method I kept appending integer data types to those columns/variables. That’s what was throwing me the error Call to a member function bind_param() on boolean. It’s because ENUMs are essentially strings and not integers, but I kept thinking they were real integers because I was putting them in a list of integers.

Moral of the story is, understand which data type you are appending to bind_param before you start executing it. It might not be the correct data type even if you think it is.

In PDO, you have 2 choices. You can either use the ? placeholder or you can use parametrized names. There are also about 3 different ways to bind and execute the prepared statement. All of which pretty much do the same exact thing almost. And that’s to avoid SQL Injections. The only difference is between execution time.

First way

$prepare = $pdo->prepare('UPDATE juniortrophies set show_hide='N', dedication = :dedication WHERE id = :id');
$prepare->bindValue(':dedication', $dedication, PDO::PARAM_STR);
$prepare->bindValue(':id', $id, PDO::PARAM_INT);

Second way

$prepare = $pdo->prepare('UPDATE juniortrophies set show_hide='N', dedication = :dedication WHERE id = :id');
$prepare->bindParam(':dedication', $dedication, PDO::PARAM_STR);
$prepare->bindParam(':id', $id, PDO::PARAM_INT);

Third way

$prepare = $pdo->prepare('UPDATE juniortrophies set show_hide='N', dedication = :dedication WHERE id = :id');

$parameters = [
	':dedication' => $dedication,
	':id' => $id,


Personally, I like the third way more because you don’t have a bunch of bindParam or bindValue lines clogging up your code. You can even cut the spacing and indentation of the array in the third example as well making it use less lines of code. Like so.

$prepare = $pdo->prepare('UPDATE juniortrophies set show_hide='N', dedication = :dedication WHERE id = :id');
$parameters = [':dedication' => $dedication, ':id' => $id];

But there’s your crash course to see how different running either mysqli_* or PDO is like.