如何更新mySQL数据库中的数据？

Question

This is my animals table in the mySQL database:

| id | animal | name | number |
|----|--------|------|--------| 
| 2  |  cat   | john |  6345  |  
| 2  | mouse  | fred |  7463  |
| 3  | monkey | noah |  2342  |

I insert data from a text file, which looks like this (for example):

animal=dog
name=alan
animal=frog
name=sam
animal=bird
name=mike

This is my code:

$id = 1
$sql = "INSERT INTO animals (animal,name,id) values(?,?,?)";
foreach($list as $row) {
$q->execute(array($row['animal'], $row['name'], $id));
}                          

After the insert my animals table looks like this:

| id | animal | name | number |
|----|--------|------|--------|
| 1  |  dog   | alan |        |
| 1  |  frog  | sam  |        |
| 1  |  bird  | mike |        | 
| 2  |  cat   | john |  6345  |  
| 2  | mouse  | fred |  7463  |
| 3  | monkey | noah |  2342  |

Later I have another text file which looks like this:

number=1425
number=9824
number=9932

The numbers are in exactly the same order like the data from the first text file. So what I want to do is just add the numbers, so that my table would look like this:

| id | animal | name | number |
|----|--------|------|--------|
| 1  |  dog   | alan |  1425  |
| 1  |  frog  | sam  |  9824  |
| 1  |  bird  | mike |  9932  | 
| 2  |  cat   | john |  6345  |  
| 2  | mouse  | fred |  7463  |
| 3  | monkey | noah |  2342  |

But I do not know how to do this in one statement. I hope you can help. Thank you very much!

Answer 1

I assume that you are wanting to update the "numbers" in your SQL table?

If so, you will need some sort of identifier to identify the rows which should be affected. So, if you want to set number=235443534 where the animal is equal to "Dog" then you will need to put that identifier in your text file. You may wanted to make the file colon seperated (;)?

Once you have got an identifer, you will need to read the file and loop through it.

<?php
$fileName = "FileNameHere.txt";
$fileToOpen = fopen($fileName,"r") or die("Error opening file :'(");
$readFile = fread($fileToOpen,filesize($fileName));

foreach($readFile as $fileLine){
 $item = split(";",$fileLine); //this will return an array {dog,34423}
 $sql = "UPDATE `animals' SET number='".$item[1]."' WHERE animal='".$item[0]."'";
 //Run your SQL and any cool code here...
}

fclose($fileToOpen);
?>

For more about SQL update and working with files have a look at W3Schools tutorials. They're really good! :D