0

My problem seems like a rather small one to me, yet, I cannot figure out a proper solution. The Setup: I have a table 'city_locations' with the columns 'country', 'city', 'longitude', 'latitude'. The countrys are given by 2-Letter ISO codes. I want them to be full names.

For this, I have imported the table 'countrycodes', containing only the columns 'name' and 'code'.

$namechange = $con->prepare("UPDATE city_locations SET country=? WHERE country=?");

$list = $con->prepare("SELECT name, code FROM countrycodes");
$list->execute();
$list->bind_result($name, $code);

$namechange->bind_param('ss', $name, $code);

while ($list->fetch()){
    while ($namechange->execute()) {}
    echo "$code is now $name <br>";
}

I succesfully retrieve all pairs in the (outer) while loop.

$namechange->execute(); however doesn't do anything - I tried it with and without the while loop, tried using LIMIT 0, 10000 in the query (though I'm not entirely sure I understand LIMIT right). With and without the while loop, the statement doesn't do anything. With LIMIT 0, 10000 the statement cannot be preapred properly (gives an error).

I also tried to bind the params new in every step of the while loop - didn't seem to do anything either.

When running the same command from my web interface, it works fine. However, in that case, I have to type all 200+ codes manually. Seems like a bit much work.

Thanks a lot for your help, Kjeld.

EDIT: $con is of type mysqli.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
Kjeld Schmidt
  • 771
  • 8
  • 19
  • It's mysqli. So, would it give me the first 10.000 of the table or the first 10.000 fitting my query? – Kjeld Schmidt Feb 05 '14 at 10:36
  • it will return from index 0 -> 10.000 – Pogrindis Feb 05 '14 at 10:38
  • @JeroenBollen mysqli OO style – Pogrindis Feb 05 '14 at 10:39
  • Tangential question: Would changing to PDO be smarter or is it more a matter of taste? I'm still quite early in my project (under 1000 lines of code) and definitely willing to change. – Kjeld Schmidt Feb 05 '14 at 10:41
  • Shouldn't the bind params be inside the first while loop? – Anthony Feb 05 '14 at 10:41
  • I've always preferred PDO but that's just opinion. – Jeroen Feb 05 '14 at 10:42
  • i thought you bound param before executing ? – Pogrindis Feb 05 '14 at 10:42
  • You can perform this update within 1 MySQL query that doesn't even require parameters. `UPDATE city_locations c SET c.country = (SELECT name FROM countrycodes where code = c.country)`. As for actual problem you have, you bound parameters before you even have their values. – N.B. Feb 05 '14 at 10:44
  • It seems you want to issue new queries while retrieving data from a previous one. Make sure that mysqli is using [buffered mode](http://www.php.net/manual/en/mysqlinfo.concepts.buffering.php) (the default). Additionally, don't forget to do error checking. – Álvaro González Feb 05 '14 at 10:44
  • You should bind the param **$namechange->bind_param('ss', $name, $code);** inside the while loop. – har2vey Feb 05 '14 at 10:44
  • @Anthony: I tried that, didn't seem to do much. I assumed it wouldn't be necessary, as I have other code where I change a variable after binding it. – Kjeld Schmidt Feb 05 '14 at 10:45
  • @N.B. Seems like a cool solution! Unfortunately, for most of the rows it just emptied the "country"-column, but some have been replaced properly. I'm just going to import the original again and for future, similiar problems try binding inside the loop again. Thank all of you for your help! – Kjeld Schmidt Feb 05 '14 at 10:51
  • @KjeldSchmidt - Yeah, I was just reading the docs on both and it looks like the basic idea is solid, since the values are applied with the `fetch()`. However, there may be an underlying timing issue that keeps this clever approach from working : "A column can be bound or rebound at any time, even after a result set has been partially retrieved. The new binding takes effect the next time mysqli_stmt_fetch() is called." This suggests to me that the binding is not quite in time with the param binding. Thinking on it more... – Anthony Feb 05 '14 at 10:52
  • What happens if you remove the second while loop? So go from : `while ($namechange->execute()) {}` to `$namechange->execute();` ? Seems like you only have the one statement per outer loop, so the inner loop may not being getting run at all. – Anthony Feb 05 '14 at 10:54
  • Nothing, no errror, but looking trough my table, it also didn't seem to do anything at all. Of course, since it's a bit over 500.000 rows, I didn't look at them all - I assumed that it would change at least one row per code/name-combination, which it didn't do. – Kjeld Schmidt Feb 05 '14 at 10:56
  • @N.B. Repeated tries leave me with a weird result: If I use your query, all 'country'-fields that used to be ZW become Zimbabwe, the last entry of 'countrycodes' - however, all other fields just get straight deleted, even though matchups do exist. I'm guessing on this, but I would guess it iterates trough countrycodes, replacing the codes by names, but in the next iteration, replacing proper names with null - I looked deeper into countrycodes - there are no 'empty' rows or anything. Any ideas? Thanks a lot. – Kjeld Schmidt Feb 05 '14 at 11:51
  • Since I can't see your dataset, it's hard to do anything but guess around. What comes to mind is that you don't have records for certain country codes, therefore you update everything with empty string. You can expand my example and use INNER JOIN while updating, so you force the lookup on entries that exist in countrycodes. I've no time to actually write the answer/query for you, but it should be pretty straight forward. [Take this as example](http://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables) – N.B. Feb 05 '14 at 11:56

1 Answers1

0
$namechange = $con->prepare("UPDATE city_locations SET country=? WHERE country=?");
$list = $con->prepare("SELECT name, code FROM countrycodes");
$list->execute();
$list->bind_result($name, $code);



while ($list->fetch()){
    $namechange->bind_param('ss', $name, $code);
    $namechange->execute();

    echo "$code is now $name <br>";
}

it will probably solve your problem.

Halil Bilgin
  • 513
  • 4
  • 14
  • So we've ended up with a question that doesn't even attempt to provide [error messages](http://php.net/manual/en/mysqli.error-list.php), an uncommented code dump as answer and an accepted answer flag with a "does not work" comment. This is going to be extremely helpful to whoever browses here in the future. – Álvaro González Feb 06 '14 at 11:46
  • There were no error messages, even with error_reporting(E_ALL); (besides the failed prepared statement, which is because of an improper query). Since nearly everyone commented that this would be the proper solution, I assumed this to be the proper way, which would usually work for others (As stated, I had already decided that I didn't even want to solve my problem anymore). If you think it would be more fitting to the general guidelines of SO to remove the correct answer, I will do so. – Kjeld Schmidt Feb 06 '14 at 12:55
  • Update: While working on a related problem, I realised all 'code' entries retained a linebreak when I imported the .csv. Quickly importing the tables again, I tried this solution just out of curiosity and it worked. – Kjeld Schmidt Feb 06 '14 at 14:05
  • @KjeldSchmidt The mysqli extension has (IMHO, an absurd amount of) functions to obtain the error messages and they are not going to call themselves—the blue words in my comment were a link to one of them. If you finally figured out what the problem was and how this code will fix it, the most helpful course of action would be to edit the answer and add a little explanation. If you don't have enough rep for that, a comment would also be great. – Álvaro González Feb 06 '14 at 15:39