Array problem

[soundser]

I have an array defined like so:

$combined = Array
(
    [28-04-05] => 9873-9874
    [23-01-04] => 3054887
    [11-02-04] => 3076388
    [20-05-04] => 3078718
    [24-05-04] => 3133455
    [29-04-04] => 3122869
    [09-04-04] => OC04103
    [07-09-04] => 3134649
    [20-09-04] => 3301564
    [08-07-04] => OC0441
    [03-02-05] => 3503233
    [05-11-04] => 3374477
    [21-09-04] => 3345523
    [22-09-04] => 3314203
    [28-09-04] => LK0486
    [21-10-04] => 3345879
    [03-12-04] => 3431131
    [04-01-05] => 3459872
    [13-04-05] => 3596663
    [03-03-05] => LK04136
    [21-04-05] => 3610857
    [07-04-05] => 3566293
    [01-11-02] => HOVCHILD
    [11-11-04] => LK04106
    [14-02-05] => OC05006
    [15-12-04] => OC04102
    [12-02-04] => LK0425
    [21-07-04] => OC0442
    [27-08-04] => oa41
    [19-01-05] => OC05004
    [20-08-03] => oa4
    [??] => oa8
    [08-09-04] => oa14
    [0719/04] => oa16
    [17-08-04] => oa18
    [25-10-04] => oa38
    [14-07-04] => oa5
    [13-03-02] => oa46
    [15-07-02] => oa6
    [30-07-02] => oa7
    [16-07-03] => oa9
    [15-07-04] => OC0419
) 

I am trying to update a mysql table called Ocean that has fields 'RCVD' and 'REF_NO' among others with the following SQL:

foreach($combined as $RCVD => $REF_NO)
{
 $sql = "UPDATE `Ocean` SET `RCVD` = '" .$RCVD ."' WHERE `REF_NO` =" .$REF_NO ." LIMIT 1 ;";
 $result = mysql_query($sql);
  if (mysql_query($sql)) 
  {
    echo("<p class=\"content\">Information updated.</p>");
  } 
  else 
  {
    echo("<p class=\"content\">Error updating information: ");
// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
  
   $message  = 'Invalid query: ' . mysql_error() . "\n";
   $message .= 'Whole query: ' . $sql . "</p>";
   die($message);
  }
} 

This returns the following to the browser window:

Information updated.

Information updated.

Information updated.

Information updated.

Information updated.

Information updated.

Information updated.

Error updating information: Invalid query: Unknown column 'OC04103' in 'where clause' Whole query: UPDATE `Ocean` SET `RCVD` = '09-04-04' WHERE `REF_NO` =OC04103 LIMIT 1 ;

It seems that all goes well until the loop reaches an array value begining with a letter rather than a digit, e.g.

[09-04-04] => OC04103

That is a letter O not a zero at the begining of the value. How can I fix this? I've tried to enclose the value with backticks like this:

[09-04-04] => `OC04103`

but this didn't help.

Heeeeelp someone!
Thanks.

[jason]

Try placing singl quotes around $REF_NO in your SQL statement. All of the mumbers before that entry are numeric (with the first one being treated as a subtraction statement), so MySQL is treating them as numeric types. This entry introduces letters, so MySQL sees it as a string and strings need to be quoted. I assume you want to treat all of those entries as strings, so adding teh quotes should slove your problem.

--Jason

[soundser]

Try placing singl quotes around $REF_NO in your SQL statement. All of the mumbers before that entry are numeric (with the first one being treated as a subtraction statement), so MySQL is treating them as numeric types. This entry introduces letters, so MySQL sees it as a string and strings need to be quoted. I assume you want to treat all of those entries as strings, so adding teh quotes should slove your problem.

--Jason

Thanks Jason. That helped! The loop ran without error and replaced some of the values but left some unchanged. I cut out part of my array for brevity but here's the whole thing:

Array
(
    [28-04-05] => 9873-9874
    [04-28-05] => 9548
    [20-04-05] => 9831
    [18-11-03] => 178132
    [03-11-04] => 3388148
    [11-07-00] => 1492447
    [12-03-02] => 2154949
    [11-06-02] => 2258912
    [24-05-02] => 2264564
    [17-05-02] => 2297239
    [18-12-02] => 2300872
    [08-12-04] => 3414704
    [22-11-02] => 2457021
    [17-01-03] => 2494839
    [22-10-02] => 2500729
    [17-12-02] => 2557774
    [10-12-02] => 2558986
    [30-07-04] => 3251144
    [30-01-03] => 2622986
    [01-05-03] => 2666457
    [20-03-03] => 2681876
    [23-04-03] => 2724146
    [23-05-03] => 2724146
    [27-05-03] => 2724146
    [07-06-04] => 3127720
    [15-07-03] => 2782502
    [11-05-04] => 2796623
    [08-08-03] => 2812937
    [22-07-03] => 2832643
    [23-07-03] => 2834900
    [31-07-03] => 2844749
    [01-08-03] => 2846184
    [09-10-03] => 2882337
    [25-09-03] => 2904230
    [11-11-03] => 2924448
    [17-11-03] => 2934721
    [04-12-03] => 2952541
    [30-08-04] => 3198139
    [21-11-03] => 2949676
    [11-03-04] => 2953304
    [05-12-03] => 2972160
    [08-12-03] => 2966701
    [12-12-03] => 2972305
    [16-12-03] => 3008549
    [06-01-04] => 3028952
    [23-01-04] => 3054887
    [11-02-04] => 3076388
    [20-05-04] => 3078718
    [24-05-04] => 3133455
    [29-04-04] => 3122869
    [09-04-04] => OC04103
    [07-09-04] => 3134649
    [20-09-04] => 3301564
    [07-10-04] => 3350647
    [12-10-04] => 3175836
    [18-05-04] => 3186983
    [22-06-04] => 3197737
    [04-08-04] => 3259437
    [08-07-04] => OC0441
    [03-02-05] => 3503233
    [05-11-04] => 3374477
    [21-09-04] => 3345523
    [22-09-04] => 3314203
    [28-09-04] => LK0486
    [21-10-04] => 3345879
    [16-12-04] => 3350687
    [07-12-04] => 3410453
    [20-12-04] => 3392874
    [11-10-04] => 3361506
    [24-11-04] => 3422977
    [29-11-04] => 3385057
    [01-12-04] => 3388682
    [17-11-04] => 3391557
    [09-12-04] => 3422033
    [12-11-04] => 3414323
    [14-12-04] => 3420326
    [17-01-05] => 3479945
    [03-12-04] => 3431131
    [04-01-05] => 3459872
    [13-04-05] => 3596663
    [03-03-05] => LK04136
    [21-04-05] => 3610857
    [07-04-05] => 3566293
    [01-11-02] => HOVCHILD
    [11-11-04] => LK04106
    [14-02-05] => OC05006
    [15-12-04] => OC04102
    [12-02-04] => LK0425
    [21-07-04] => OC0442
    [27-08-04] => oa41
    [19-01-05] => OC05004
    [20-08-03] => oa4
    [??] => oa8
    [08-09-04] => oa14
    [0719/04] => oa16
    [17-08-04] => oa18
    [25-10-04] => oa38
    [14-07-04] => oa5
    [13-03-02] => oa46
    [15-07-02] => oa6
    [30-07-02] => oa7
    [16-07-03] => oa9
    [15-07-04] => OC0419
) 

There are 319 records to be updated but only 103 were updated! I'm really not sure why but maybe looking at the array above could give a clue. Do you see anything troublesome?

[jason]

Just looking at the array I have no idea. Are you getting any kinds of errors as it runs? Do you know which values are being missed? Sometimes outputting the SQL query string as a debugging line helps you locate problems. That's usually one of the first tings that I try. You can also try calling mysql_error() after running the query to see if any error messages are being silently returned.

Good luck.

--Jason

[soundser]

Just looking at the array I have no idea. Are you getting any kinds of errors as it runs? Do you know which values are being missed? Sometimes outputting the SQL query string as a debugging line helps you locate problems. That's usually one of the first tings that I try. You can also try calling mysql_error() after running the query to see if any error messages are being silently returned.

Good luck.

--Jason

Thanks for having a look. No errors are reported and I don't know which records are being missed. I'll investigate though and post back any progress.

Thanks!

--Tom

[soundser]

I found that I could use a program to convert Microsoft Excel spreadsheets into MySQL databases which eliminated the need to run the script above. The program worked well for me if anyone has a similar need.

Thanks much.

--Tom

[jason]

Glad to hear you solved the problem. I'll have to check out that converter. I looks like it could come in handy someday.

--Jason

[gerilya]

I found that I could use a program to convert Microsoft Excel spreadsheets into MySQL databases which eliminated the need to run the script above. The program worked well for me if anyone has a similar need.

Imagine that phpMyAdmin can do just that