UPDATE SET doesn't work

Problems with the Windows version of XAMPP, questions, comments, and anything related.

UPDATE SET doesn't work

Postby OrcaSoul » 24. June 2014 23:18

I need to update a database (scw_db) with a table (tblsplintersbowlinventory) from a list that contains id's, when I read an ID I want to set the txtVenue field of the matching record to "Shoreline 2014".

The code:
Code: Select all
<?php
   $con = mysql_connect("localhost","xxxxx","xxxxx");
   if (!$con)
   {
      die('Could not connect: ' . mysql_error());
   }
   mysql_select_db("scw-db", $con);
   $filename = "InventoryList.txt";
   $arr = file($filename);
   foreach($arr as $r_bowlcode){
       print $r_bowlcode . "<br />";
      $query="UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='$r_bowlcode'";
      mysql_query($query);
      echo "Record Updated";
   }
   mysql_close();
?>


This runs through the list, prints that it updated the record - but when I check the record in myPHPadmin it has not changed.

And I really need to get this working by tomorrow - Wed, 6/25 - so a simple and complete answer that just works would really be appreciated.
Last edited by OrcaSoul on 24. June 2014 23:40, edited 2 times in total.
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: UPDATE SET doesn't work

Postby JJ_Tagy » 24. June 2014 23:31

Remember to troubleshoot your code and echo mysql errors.
JJ_Tagy
 
Posts: 678
Joined: 30. January 2012 13:44
Operating System: Windows 10 Pro x64

Re: UPDATE SET doesn't work

Postby OrcaSoul » 24. June 2014 23:52

JJ_Tagy wrote:Remember to troubleshoot your code and echo mysql errors.


I added the following right after the <?php line:
Code: Select all
   error_reporting(E_ALL);
   ini_set('display_errors', TRUE);
   ini_set('display_startup_errors', TRUE);   


And then added error checking to the query line:
Code: Select all
$query="UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='$r_bowlcode'" or die('Error: '.odbc_errormsg());;


And all I get is:
Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in C:\xampp\htdocs\SCW_Files\SCW_addVenue.php on line 5

And the rest prints out:
Ash08-005
Record UpdatedAsh13-002
Record UpdatedAsh14-001
Record UpdatedBlacklimba14-001
Record UpdatedBlacklimba14-002
Record UpdatedKhaya14-001


Which is the list of ID's...
How do I get the error (if any) the is causing it not to set the field?

If it were actually failing on the set, wouldn't it "die"?
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: UPDATE SET doesn't work

Postby OrcaSoul » 25. June 2014 00:46

OK, I see that part of the problem was from using mysql_ extents, should have used mysqli_ instead.
I revised the code:
Code: Select all
<?php
   error_reporting(E_ALL);
   ini_set('display_errors', TRUE);
   ini_set('display_startup_errors', TRUE);   
   $con = mysqli_connect("localhost","xxxx","xxxxxxxxx");
   if (!$con)
   {
      die('Could not connect: ' . mysql_error());
   }
   mysqli_select_db($con, "scw-db");
   $filename = "InventoryList.txt";
   $arr = file($filename);
   foreach($arr as $r_bowlcode){
       print $r_bowlcode . "<br />";
      $query="UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='$r_bowlcode'" or die('Error: '.$php_errormsg());;
      mysqli_query($con, $query);
      echo "Record Updated<br />";
   }
   mysqli_close($con);
?>

And it runs with no errors - but still does not make the update!

Edit: Changed odbc_errormsg to $php_errormsg - still does not update the record!
Last edited by OrcaSoul on 25. June 2014 01:20, edited 1 time in total.
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: UPDATE SET doesn't work

Postby JJ_Tagy » 25. June 2014 00:50

You put the error output after the mysql query execution, not in the actual query statement.
JJ_Tagy
 
Posts: 678
Joined: 30. January 2012 13:44
Operating System: Windows 10 Pro x64

Re: UPDATE SET doesn't work

Postby OrcaSoul » 25. June 2014 02:03

JJ_Tagy wrote:You put the error output after the mysql query execution, not in the actual query statement.


Not sure what you mean - did you read the edited code, with the
Code: Select all
$query="UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='$r_bowlcode'" or die('Error: '.$php_errormsg());

statement?
Not sure how you can get it closer to the specific statement.
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: UPDATE SET doesn't work

Postby JJ_Tagy » 25. June 2014 03:47

Think about it logically. You are saying that the variable equals a string (which will of course return true unless it is unable to set the variable) or die (which it will ignore because it already is true). You should be running the mysqli_query or die. Or at least add the error message after the query is executed.
JJ_Tagy
 
Posts: 678
Joined: 30. January 2012 13:44
Operating System: Windows 10 Pro x64

Re: UPDATE SET doesn't work

Postby OrcaSoul » 25. June 2014 05:31

JJ_Tagy wrote:Think about it logically. You are saying that the variable equals a string (which will of course return true unless it is unable to set the variable) or die (which it will ignore because it already is true). You should be running the mysqli_query or die. Or at least add the error message after the query is executed.


OK, I combined the code:
Code: Select all
mysqli_query($con,"UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='$r_bowlcode'" or die('Error: '.$php_errormsg()));

It runs - but still does not update the record.

And this is modeled from the code at http://www.w3schools.com/php/php_mysql_update.asp...

What am I missing?
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: UPDATE SET doesn't work

Postby Altrea » 25. June 2014 05:34

construct the SQL statement before executing it and echo it out as plain text.
Doing so you will be able to copy the code to past it in phpmyadmin SQL Tab
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8298
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: UPDATE SET doesn't work

Postby OrcaSoul » 25. June 2014 07:13

Altrea wrote:construct the SQL statement before executing it and echo it out as plain text.
Doing so you will be able to copy the code to past it in phpmyadmin SQL Tab


OK, I tried this:
Code: Select all
      $query="UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='$r_bowlcode'" or die('Error: '.$php_errormsg());
      echo &query;
      mysqli_query($con, $query);

I got the following:
Ash08-005
UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='Ash08-005 'Record Updated
Ash13-002
UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='Ash13-002 'Record Updated
Ash14-001
UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='Ash14-001 'Record Updated
Blacklimba14-001
UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='Blacklimba14-001 'Record Updated
Blacklimba14-002
UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='Blacklimba14-002 'Record Updated

So...I have it set to give me an error, if there is one. It does not give me an error - and yet the update does not happen.

Look, I am using code from a site on the web, that is supposed to work. Does anyone understand why it doesn't?
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: UPDATE SET doesn't work

Postby Altrea » 25. June 2014 09:57

[quote="OrcaSoul"]
Code: Select all
      $query="UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='$r_bowlcode'" or die('Error: '.$php_errormsg());

That is sooo damn wrong...

Use the correct error function (mysqli_error)
Use it at the correct position (see the examples on the function description)
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8298
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: UPDATE SET doesn't work

Postby OrcaSoul » 25. June 2014 18:12

Altrea wrote:
OrcaSoul wrote:
Code: Select all
      $query="UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='$r_bowlcode'" or die('Error: '.$php_errormsg());

That is sooo damn wrong...

Use the correct error function (mysqli_error)
Use it at the correct position (see the examples on the function description)


Finally - a straight clear concise suggestion!
Thank you.
OK, I read the links and made some changes that should fit:
Code: Select all
<?php
   $con = mysqli_connect("localhost","xxxx","xxxxxxxx");
   if (!$con)
   {
      die('Could not connect: ' . mysqli_error($con));
   }
   mysqli_select_db($con, "scw-db");
   $filename = "InventoryList.txt";
   $arr = file($filename);
   foreach($arr as $r_bowlcode){
      print $r_bowlcode . "<br />";
      if (!mysqli_query($con, "UPDATE tblsplintersbowlinventory SET txtVenue='Shoreline 2014' WHERE txtProductBowlCode='$r_bowlcode'")) {
          printf("Errormessage: %s\n", mysqli_error($con));
          echo "Record NOT Updated<br />";
      } ELSE {   
         echo "Record Updated<br />";
      }
   }
   mysqli_close($con);
?>

And this runs with no errors reported.
But still no actual update.
I am wondering about the syntax of the
Code: Select all
WHERE txtProductBowlCode='$r_bowlcode'
part - $r_bowlcode is a variable, is that the right way to write it?
In some examples I see it written:
Code: Select all
WHERE txtProductBowlCode='".$r_bowlcode."'
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: UPDATE SET doesn't work

Postby Altrea » 25. June 2014 18:31

Does the value inside that variable contain a whitespace at the end so that the where clause will never match?
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8298
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: UPDATE SET doesn't work

Postby OrcaSoul » 25. June 2014 19:13

Altrea wrote:Does the value inside that variable contain a whitespace at the end so that the where clause will never match?

Not that I can see...I changed the print line:
Code: Select all
print ":" . $r_bowlcode . ":<br />";

to show the entire value, I get:
:Ash08-005 :
Record Updated
:Ash13-002 :
Record Updated
:Ash14-001 :
Record Updated
:Blacklimba14-001 :
Record Updated

I also checked the entry in the table, there are no spaces or white-space visible.

I added some code to see if the $r_bowlcode really exists,
Code: Select all
      $sql="SELECT txtProductBowlCode,intProductID FROM tblsplintersbowlinventory WHERE txtProductBowlCode='$r_bowlcode'";
      
      if ($result=mysqli_query($con,$sql))
      {
         while ($row=mysqli_fetch_row($result))
         {
            printf ("%s (%s)\n",$row[0],$row[1]);
         }
         mysqli_free_result($result);
      }   

This gives me the same - with no printout of the record data...however, if I remove the
Code: Select all
WHERE txtProductBowlCode='$r_bowlcode'
I get:
:Ash08-005 :
Ash08-005 :
:Ash07-001: (246) :AshWenge07-001: (260) :AshWenge07-002: (261) :Ash07-004: (247) :Ash07-005: (248) :AshSc07-006: (250) :AshSc07-007: (251) :AshSc07-008: (252) :AshSc07-009: (253) ...

And yes, the "Ash08-005 (503) " does show up...and there is no white-space...

So...it appears the problem is the way the variable is referred to in the WHERE clause of the statement.

I've tried to find an example of WHERE used with a variable, and I must not be asking Google the correct question. What IS the variable syntax?
==================================================
Edit to add: IF I change the WHERE phrase to ask for the first bowl specifically:
Code: Select all
WHERE txtProductBowlCode='Ash08-005'

I get:
:Ash08-005 :
:Ash08-005: (503) Record Updated
:Ash13-002 :
:Ash08-005: (503) Record Updated

So it is DEFINITELY the syntax on the variable...
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: UPDATE SET doesn't work

Postby Altrea » 25. June 2014 19:28

OrcaSoul wrote:So...it appears the problem is the way the variable is referred to in the WHERE clause of the statement.

Nooo, that is wrong.

OrcaSoul wrote:
Altrea wrote:Does the value inside that variable contain a whitespace at the end so that the where clause will never match?

Not that I can see...I changed the print line:
Code: Select all
print ":" . $r_bowlcode . ":<br />";

to show the entire value, I get:
:Ash08-005 :
Record Updated
:Ash13-002 :
Record Updated
:Ash14-001 :
Record Updated
:Blacklimba14-001 :
Record Updated

Change it to this:
Code: Select all
var_dump($r_bowlcode);


OrcaSoul wrote:I added some code to see if the $r_bowlcode really exists,
Code: Select all
      $sql="SELECT txtProductBowlCode,intProductID FROM tblsplintersbowlinventory WHERE txtProductBowlCode='$r_bowlcode'";
      
      if ($result=mysqli_query($con,$sql))
      {
         while ($row=mysqli_fetch_row($result))
         {
            printf ("%s (%s)\n",$row[0],$row[1]);
         }
         mysqli_free_result($result);
      }   

This gives me the same - with no printout of the record data...

Which is the biggest hint that your WHERE doesn't match.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8298
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 25 guests