How to search by name to get id?

Hi all. I have 2 tables. Authors and Books (each with their own fields). In the table with the books is the field ID of the author that was displayed. I vushku made it so that instead of the author ID in the table shows the name of the author from the table authors. Did the export table Books. As was supposed it is exported with the other fields, the author name from the other table instead of ID.
Now I do import the table.
And I need to search in database by name and find the ID of the author, substituting it.
Or quote the words of his teacher

author you need to create a new one. it is necessary for his name to search in database and get its ID, then to substitute it into the model you created books

no. nothing should be removed. you load data from a file. instead of the ID of the author you his name. you take that name, make the request to the database (searching by author name), get this author and take from them his ID


Here is the code import. Need to modify the line with $model-> id_avtor1 = $row [4];

public function actionUpload()
{
 $model = new UploadForm();
 if (Yii::$app->request->isPost ) {
 $model->fName = UploadedFile::getInstance($model, 'fName');
 if ($fName =$model->upload()) {
 //file path

 if (($handle = fopen($fName, 'r')) !== false) {


 while (($row = fgetcsv($handle, 1000, ',')) !== false) {
 if ($row[0]=='ID' and $row[0]=='#'){
continue;}
 $model = new Kniga1();
 $model->name = $row [2];
 $model-> id_avtor1 = $row [4];
 $model->creation_date = $row [5];

 if ($model->validate()) {
$model->save();

 } else {
$model->save();
print_r($model->errors);
}
}
fclose($handle);
};
 //... code after the import
}

}
 return $this->render('upload', ['model' => $model]);
 }
March 19th 20 at 08:25
1 answer
March 19th 20 at 08:27
Solution
Good morning.
In this is incorrect.
if ($model->validate()) {
$model->save();
 } else {
$model->save();
print_r($model->errors);
 }

If the attributes are not passed validation, then why try to again to save the model? Nothing will happen. Just print out the error.
Need to modify the line with $model-> id_avtor1 = $row [4];

If $row[4] You have stored the name of the author, make a method that will search in the table `author` of the author by name and return its id.
Something like that, static method in the model Author:
public static function getIdAuthor($name){
 return Author::find()->select('id')->where('name=:name',[':name' => $name])->column();
}

And substitute into the string
$model-> id_avtor1 = Author::idAuthor($row[4]);
Thank you very much.
And what kind of field idAuthor ? I have just such a field is not present.
there are id_avtor, but he is in the books. and the table with the author only have the following fields:
'id' => 'ID',
'name' => 'Name',
'creation_date' => 'Creation Date',
'date_of_change' => 'Date Of Change', - kacey_Shanahan30 commented on March 19th 20 at 08:30
@kacey_Shanahan30, this is a static method.
Look carefully at the answer. - Herminia.Kreig commented on March 19th 20 at 08:33
@Herminia.Kreig, thanks a lot. everything is in order and working.
Can you explain what's going on here? where('name=:name',[':name' => $name])->column();
Don't like stupid to copy code. Want to understand - kacey_Shanahan30 commented on March 19th 20 at 08:36
@kacey_Shanahan30prepared by request, for the sake of security.
See here. - Herminia.Kreig commented on March 19th 20 at 08:39
@Herminia.Kreig, thanks!) - kacey_Shanahan30 commented on March 19th 20 at 08:42
@kacey_Shanahan30, please. - Herminia.Kreig commented on March 19th 20 at 08:45
@Herminia.Kreig, by the Way about code
if ($model->validate()) {
$model->save();
 } else {
$model->save();
print_r($model->errors);
 }


Validation is not working.
removed the $model->save(); after else.
Import the file. choose the file from your computer and press submit. In the end, says 'no File chosen'
What can be the reason ? - kacey_Shanahan30 commented on March 19th 20 at 08:48
@kacey_Shanahan30, I think that the problem with the model loading, rather with validation rules. - Herminia.Kreig commented on March 19th 20 at 08:51
@Herminia.Kreig, I tried to do a save without validation, erasing if and else statements, and writing instead $model->save(false)
All the same - kacey_Shanahan30 commented on March 19th 20 at 08:54
@Herminia.Kreig, the Problem was solved. It seems everything is imported correctly. And error no. But I have created in database a lot of empty rows. endlessly. - kacey_Shanahan30 commented on March 19th 20 at 08:57
@kacey_Shanahan30, I think that the problem is in while() loop and its condition.
Actually it would be better to replace the while() and foreach() - Herminia.Kreig commented on March 19th 20 at 09:00
@Herminia.Kreig, good. then where do I put the code inside the while loop, namely
while (($row = fgetcsv($handle, 1000, ',')) !== false)

I don't think it's in a loop foreache will work correctly.
although I'm probably wrong again due to lack of experience - kacey_Shanahan30 commented on March 19th 20 at 09:03
@Herminia.Kreig, the Problem with the cycle decided. All is working well. In addition to field id_avtor1. Your code is correct, but somewhere in there you need to change something. Because when you download the file it says Array ( [id_avtor1] => Array ( [0] => Id Avtor1 is invalid. ) )
I myself is in search of a problem, but has not yet found its source.
Head until it to do like this option $model = Avtor1::find()->where("name")= $row [4];

Or your adjustment. What do you say ? - kacey_Shanahan30 commented on March 19th 20 at 09:06
@kacey_Shanahan30, something I did not understand what You want to achieve by this?
$model = Avtor1::find()->where("name")= $row [4];
Moreover, the query is written incorrectly, with an error. - Herminia.Kreig commented on March 19th 20 at 09:09
@Herminia.Kreig, nothing. I want to change your code. whatever climbs error Array ( [id_avtor1] => Array ( [0] => Id Avtor1 is invalid. ) )
But so far not very successful - kacey_Shanahan30 commented on March 19th 20 at 09:12
@kacey_Shanahan30where she climbs? First code worked? Now You have to change it and get the error? - Herminia.Kreig commented on March 19th 20 at 09:15
@Herminia.Kreig, no. code I did not change. use your code. just before that I this problem is not faced, as I had problems with the loop. the problem with the cycle I removed. now the final stage. when import the exported table it gives me this error. - kacey_Shanahan30 commented on March 19th 20 at 09:18
@kacey_Shanahan30, all the same did not understand)))
No code here do not understand))) - Herminia.Kreig commented on March 19th 20 at 09:21
@Herminia.Kreig, Look here import code, which also is your code, which solves my above described problem in 1 post. )
public function actionUpload()
{
 $model = new UploadForm();
 if (Yii::$app->request->isPost ) {
 $model->fName = UploadedFile::getInstance($model, 'fName');
 if ($fName =$model->upload()) {
 //file path

 if (($handle = fopen($fName, 'r')) !== false) {


 while (($row = fgetcsv($handle, 1000, ',')) !== false) {

 if ($row[1]==='ID'){
continue;
}
 $modell = new Kniga1();
 $modell->name = $row [2];
 $modell-> id_avtor1 = Avtor1::idAuthor($row[4]);
 $model->creation_date = $row [6];

 if ($model->validate()) {
$modell->save();

 } else {
print_r($model->errors);
}
}
fclose($handle);
 return $this->redirect(['index']);
};
 //... code after the import
}

}
 return $this->render('upload', ['model' => $model]);
 }


I also created the models Avtor1 a static function, as you pointed out above
public static function IdAuthor($name){
 return Avtor1::find()->select('id')->where('name=:name',[':name' => $name])->scalar();
 }

Only I in the end put scalar(), because without it gave some error.

Well, my model download files
class UploadForm extends Model
{
/**
 * @var UploadedFile
*/

 public $fName;

 public function rules()
{
 return [
 [['fName'], 'safe'],
];
}

 public function upload()
{
 if ($this->validate()) {
 $path = \Yii::getAlias('@runtime').'/uploads/' . $this->fName->baseName . '.' . $this->fName->extension;
$this->fName->saveAs($path);

 return $path;
 } else {
 return false;
}
}
}


When you import a file the browser shows the error associated with the line id_avtor1.
5c9b2cf98adb9043243626.png - kacey_Shanahan30 commented on March 19th 20 at 09:24
@kacey_Shanahan30, well, why would You put a scalar? What You column is not pleased? - Herminia.Kreig commented on March 19th 20 at 09:27
@Herminia.Kreig, this )
5c9b2f5eb6acf192672690.png - kacey_Shanahan30 commented on March 19th 20 at 09:30
@kacey_Shanahan30, so column() nothing to do with it. The reason is in the foreign key. Are you trying to update something that can't update because of the integrity of the foreign key. - Herminia.Kreig commented on March 19th 20 at 09:33
@kacey_Shanahan30, you can make a crutch, disabled at the time of import/export inspection of foreign keys, but this, again, is a crutch.
You need to change the logic. - Herminia.Kreig commented on March 19th 20 at 09:36
@Herminia.Kreig, the logic code is of import in the controller ? - kacey_Shanahan30 commented on March 19th 20 at 09:39
@kacey_Shanahan30, well almost.
Again You have a problem with naming variables.
You use $model for different models, for the UploadForm and Kniga1. This makes pwnanza.
Was it not more logical to do so?
$upload = new UploadForm();
$book = new Kniga1();

Now on.
You have configured a foreign key between Kniga1 and Avtor1? That's the problem. - Herminia.Kreig commented on March 19th 20 at 09:42
@kacey_Shanahan30You method returns Avtor1::idAuthor($row[4])? - Herminia.Kreig commented on March 19th 20 at 09:45
@Herminia.Kreig, we are in this method do a search in the database by name, find the ID and substitute it. You have written this method ) - kacey_Shanahan30 commented on March 19th 20 at 09:48
@kacey_Shanahan30, it can nothing to return, for example this poster is not in our database.
The method must return id.
He returns really? - Herminia.Kreig commented on March 19th 20 at 09:51
@Herminia.Kreig, it returns 'nothing'? - kacey_Shanahan30 commented on March 19th 20 at 09:54
@kacey_Shanahan30, well here is the cause of the error.
Why it gets You nothing?
The correct author is in the table? - Herminia.Kreig commented on March 19th 20 at 09:57
@kacey_Shanahan30, You are doing the import into your database.
Therefore, the required of the author may not be in Your table. - Herminia.Kreig commented on March 19th 20 at 10:00
@Herminia.Kreig, All the problem is solved ) there were errors in the controller. thanks again! - kacey_Shanahan30 commented on March 19th 20 at 10:03
@kacey_Shanahan30as decided by that error? - Herminia.Kreig commented on March 19th 20 at 10:06
@Herminia.Kreig, Good afternoon. It was easier than I thought.
In the controller where it was
if ($model->validate()) {
$modell->save();

 } else {
print_r($model->errors);
 }

Removed print_r($model->errors);

Well, in the model of the author changed
public static function IdAuthor($name){
 return Avtor1::find()->select('id')->where('name=:name',[':name' => $name])->scalar();
 }


On
public static function IdAuthor($name){
return
 Avtor1::find()->where(['name' => $name])->scalar();
 }


And Bingo!
Everything works as it should) - kacey_Shanahan30 commented on March 19th 20 at 10:09
@kacey_Shanahan30, scalar, why? - Herminia.Kreig commented on March 19th 20 at 10:12
@Herminia.Kreig, I had to get a scalar value of 1 column 1 line ) - kacey_Shanahan30 commented on March 19th 20 at 10:15
@kacey_Shanahan30, You need to get id))) - Herminia.Kreig commented on March 19th 20 at 10:18
@Herminia.Kreig, I get it)
because the controller in the code imports I ordered
if ($row[1]==='ID'){
continue;
 }


instead
if ($row[0]=='ID' and $row[0]=='#'){
continue;
}


I had last version) - kacey_Shanahan30 commented on March 19th 20 at 10:21

Find more questions by tags Yii