使用mySQL在我的数据库中输入数组

时间:2021-07-25 07:24:07

I know I am doing this completely wrong, but current I have:

我知道我这样做完全错了,但我现在有:

$genre0 = addslashes(strip_tags($movieInfo[genres][0][name]));
$genreid0 = addslashes(strip_tags($movieInfo[genres][0][id]));
...
$genre4 = addslashes(strip_tags($movieInfo[genres][4][name]));
$genreid4 = addslashes(strip_tags($movieInfo[genres][4][id]));

Then

mysql_query($query = "INSERT INTO tblMovies (movie_id, genre_id, genre_name) VALUES ($movie_id , $genreid0, $genre0");
...
mysql_query($query = "INSERT INTO tblMovies (movie_id, genre_id, genre_name) VALUES ($movie_id , $genreid4, $genre4");

I know this is extremely repetitive, but it is the only way I can figure out that works. I have tried a ton of for each statements to no avail. I have tried following this one insert multiple rows via a php array into mysql and a few others, but cannot seem to figure it out.

我知道这是非常重复的,但这是我能弄明白的有效方法。我已经尝试了大量的每个声明无济于事。我试过跟随这一个通过php数组插入多行到mysql和其他几个,但似乎无法搞清楚。

This is embedded in a SELECT/WHILE statement that runs through a list of movies...

它嵌入在SELECT / WHILE语句中,该语句贯穿电影列表......

Any tips or suggestions would be greatly appreciated!

任何提示或建议将不胜感激!

Thanks!

3 个解决方案

#1


2  

You can use prepared statements for handy execution of your query:

您可以使用预准备语句轻松执行查询:

      $stmt = $dbh->prepare("INSERT INTO tblMovies (movie_id, genre_id, genre_name)
                             VALUES (:movie_id , :genreid, :genre")");

      $stmt->bindParam(':movie_id ', $movie_id);
      $stmt->bindParam(':genreid', $genreid);
      $stmt->bindParam(':genre', $genre);

      for($i=0; $i < count($movieInfo['genres']); $i++){
         $movie_id = i;
         $genreid = addslashes(strip_tags($movieInfo['genres'][i]['id']));
         $genre = addslashes(strip_tags($movieInfo['genres'][i]['name']));
         $stmt->execute();
      }

#2


0  

If you insist on using mysql_XXX:

如果你坚持使用mysql_XXX:

foreach ($movieInfo['genres'] as $genre) {
    $genre = mysql_real_escape_string(strip_tags($genre['name']));
    $genreid = mysql_real_escape_string(strip_tags($genre['id']));
    mysql_query("INSERT INTO tblMovies (movie_id, genre_id, genre_name) VALUES ($movie_id , '$genreid', '$genre'");
}

#3


0  

I would insist that insert multiple records in database by firing single query instead of firing seperate query for each record.

我坚持通过触发单个查询而不是为每个记录触发单独的查询来在数据库中插入多个记录。

E.g.:

INSERT INTO tblMovies
  (genre_id, genre_name)
VALUES
  ('Name 1', 'Value 1'),
  ('Name 2', 'Value 2'),
  ('Name 3', 'Value 3'),
  ('Name 4', 'Value 4');


//Prepare the entire query using foreach and then fire single query.
foreach ($movieInfo['genres'] as $genre) {
    $genre = mysql_real_escape_string(strip_tags($genre['name']));
    $genreid = mysql_real_escape_string(strip_tags($genre['id']));
    $parts[] = "('.$genreid.', '.$genre.')";
}

$str = implode("," $parts);
$query = "INSERT INTO tblMovies (genre_id, genre_name) VALUES ".$str.";";
mysql_query($query) or die(mysql_error());

Note: I have used mysql as you have mentioned in question but it is recommended to switch to mysqli or PDO. Also, from the question you have mentioned, I assume that $movie_id is auto-increment.

注意:我已经使用了你提到的mysql,但是建议切换到mysqli或PDO。另外,从你提到的问题,我假设$ movie_id是自动增量。

#1


2  

You can use prepared statements for handy execution of your query:

您可以使用预准备语句轻松执行查询:

      $stmt = $dbh->prepare("INSERT INTO tblMovies (movie_id, genre_id, genre_name)
                             VALUES (:movie_id , :genreid, :genre")");

      $stmt->bindParam(':movie_id ', $movie_id);
      $stmt->bindParam(':genreid', $genreid);
      $stmt->bindParam(':genre', $genre);

      for($i=0; $i < count($movieInfo['genres']); $i++){
         $movie_id = i;
         $genreid = addslashes(strip_tags($movieInfo['genres'][i]['id']));
         $genre = addslashes(strip_tags($movieInfo['genres'][i]['name']));
         $stmt->execute();
      }

#2


0  

If you insist on using mysql_XXX:

如果你坚持使用mysql_XXX:

foreach ($movieInfo['genres'] as $genre) {
    $genre = mysql_real_escape_string(strip_tags($genre['name']));
    $genreid = mysql_real_escape_string(strip_tags($genre['id']));
    mysql_query("INSERT INTO tblMovies (movie_id, genre_id, genre_name) VALUES ($movie_id , '$genreid', '$genre'");
}

#3


0  

I would insist that insert multiple records in database by firing single query instead of firing seperate query for each record.

我坚持通过触发单个查询而不是为每个记录触发单独的查询来在数据库中插入多个记录。

E.g.:

INSERT INTO tblMovies
  (genre_id, genre_name)
VALUES
  ('Name 1', 'Value 1'),
  ('Name 2', 'Value 2'),
  ('Name 3', 'Value 3'),
  ('Name 4', 'Value 4');


//Prepare the entire query using foreach and then fire single query.
foreach ($movieInfo['genres'] as $genre) {
    $genre = mysql_real_escape_string(strip_tags($genre['name']));
    $genreid = mysql_real_escape_string(strip_tags($genre['id']));
    $parts[] = "('.$genreid.', '.$genre.')";
}

$str = implode("," $parts);
$query = "INSERT INTO tblMovies (genre_id, genre_name) VALUES ".$str.";";
mysql_query($query) or die(mysql_error());

Note: I have used mysql as you have mentioned in question but it is recommended to switch to mysqli or PDO. Also, from the question you have mentioned, I assume that $movie_id is auto-increment.

注意:我已经使用了你提到的mysql,但是建议切换到mysqli或PDO。另外,从你提到的问题,我假设$ movie_id是自动增量。