blocks|key|2803082|text|这就是你的问题|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2803083|foreach($json['items']+as+$row){
++++...
++++$videoId+=+$row['id']['videoId'];
++++$videoId1+=+$row['id']['videoId'];
++++$videoId2+=+$row['id']['videoId'];
+++++$sql+=+"INSERT+INTO+users(channelid,+videoId,+videoId1,+videoId3)+VALUES('$channelid',+'$videoId',+'$videoId1',+'$videoId2')++++
++++++++ON+DUPLICATE+KEY+UPDATE+channelid='$channelid',+videoId='$videoId',+videoId1='$videoId1',+videoId2='$videoId2';
++++++++";
++++...
}|code-block|syntax|javascript|2803084|基本上发生的是获取您的第一个JSON数据(我将只关注值部分)：|2803085|+//iteration+1
+VALUES('UCiP20o92_7jUYwj-ulVLifw',+'kHPqpxDE50c',+'kHPqpxDE50c',+'kHPqpxDE50c')
//iteration+2
+VALUES('UCiP20o92_7jUYwj-ulVLifw',+'DbAlCl3kk-M',+'DbAlCl3kk-M',+'DbAlCl3kk-M')
//iteration+3
+VALUES('UCiP20o92_7jUYwj-ulVLifw',+'7EDPROQa4i0',+'7EDPROQa4i0',+'7EDPROQa4i0')|2803086|现在想想会发生什么。由于您的表的设置方式(我可以补充说是不正确的)，我敢打赌chanelid是唯一的。所以每次循环都会覆盖上一次循环中的id。|offset|length|style|CODE|2803087|2803088|它是3个相同的videoId插入7EDPROQa4i0|blockquote|2803089|2803090|2803091|顺便说一句，这就是你所看到的。|2803092|要修复它，您必须在JSON循环之后插入/更新。通过构建我们可以在一个查询中插入的视频in数组。您不需要为JSON数据的每个循环(其设置方式)执行一次查询。|2803093|如下所示：|2803094|$loop+=+mysqli_query($conn,+"SELECT+channelid,+videoId+FROM+users+ORDER+BY+id")
or+die+(mysqli_error($conn));

while+($row+=+mysqli_fetch_array($loop))
{
++++$channelid+=+$row['channelid'];
++++$url+=+'https://www.googleapis.com/youtube/v3/search?order=date&part=snippet&channelId='.$channelid+.'&maxResults=3&key=KEY';
++++$content+=+file_get_contents($url);+
++++$json+=+json_decode($content,+true);++++

++++if(!isset($json['items']))+continue;+//skip+if+no+items

++++$videos+=+['videoId'=>'','videoId1'=>'','videoId2'=>''];
++++$i+=+0;
++++foreach($json['items']+as+$items)
++++{
++++++++$key++=+0==$i+?+'videoId'+:+'videoId'.$i;
++++++++$videos[$key]+=+$items['id']['videoId'];+
++++++++%2B%2B$i;
++++}

++++$sql+=+"INSERT+INTO+users(channelid,+videoId,+videoId1,+videoId3)+VALUES('$channelid',+'{$videos['videoId']}',+'{$videos['videoId1']}',+'{$videos['videoId2']}')++++
++++++++ON+DUPLICATE+KEY+UPDATE+channelid=VALUES(channelid),+videoId=VALUES(videoId),+videoId1=VALUES(videoId1),+videoId2=VALUES(videoId2);
++++++++";
++++mysqli_query($conn,+"SET+NAMES+utf8");
++++if(!mysqli_query($conn,$sql))
++++{
++++++++die('Error+:+'+.+mysqli_error($conn));
++++}
}|2803095|这就解决了当前的问题。然而，这里有一个更大的问题，那就是表是如何设置的，以及如何使用它。在你拥有它的方式，你被限制在3个视频，即使这是你需要的全部，在某些时候你可能想要更多。每个人都想要更多，这是既定的。要将其更新为具有4个、5个或6个列，需要在中添加额外的列，这是不好的。|2803096|更深层的问题|BOLD|2803097|更好的方法是对每个视频使用1行。那么我们如何做到这一点呢？|2803098|首先要做的是导出这个表(这样您就有了一个备份)|2803099|然后将channelid的索引更改为复合(复合)“唯一”索引，该索引包含两个字段，分别是channelid和videoId。这两个参数必须相同，才能将该行视为重复行。|2803100|接下来，在channelId上添加一个简单的索引(非惟一的)。您希望它有自己的索引，因为为了让复合索引正常工作，您通常不需要同时提供两个字段(按照索引的顺序)。这就是说，我确实忘记了(在DB和Mongo等其他东西之间)，所以如果它是第一个，你也许可以使用它。你可以随时解释并确保它使用它。不管怎样，你想要那个索引，因为我们要用它来做查找，很好地索引它是很好的。|2803101|接下来，您必须完全摆脱videoId1和videoId2。如果您不想丢失其中的数据，那么可以创建一个迁移脚本，将这些值从这两个字段中提取出来，并为它们创建一个新行。|2803102|然后转到代码上。|2803103|$loop+=+mysqli_query($conn,+"SELECT+DISTINCT+channelid+FROM+users+ORDER+BY+id")
or+die+(mysqli_error($conn));

while+($row+=+mysqli_fetch_array($loop))
{
++++$channelid+=+$row['channelid'];
++++$url+=+'https://www.googleapis.com/youtube/v3/search?order=date&part=snippet&channelId='.$channelid+.'&maxResults=3&key=KEY';
++++$content+=+file_get_contents($url);
++++$json+=+json_decode($content,+true);

++++if(!isset($json['items']))+continue;+//skip+if+no+items

++++foreach($json['items']+as+$items)
++++{
++++++++$videoId+=+$items['id']['videoId'];
++++++++$sql+=+"INSERT+INTO+users(channelid,+videoId)+VALUES('$channelid',+'$videoId')++++
++++++++ON+DUPLICATE+KEY+UPDATE+channelid=VALUES(channelid),+videoId=VALUES(videoId);
++++++++";
++++++++mysqli_query($conn,+"SET+NAMES+utf8");
++++++++if(!mysqli_query($conn,$sql))
++++++++{
++++++++++++die('Error+:+'+.+mysqli_error($conn));
++++++++}
++++}
}|2803104|这更接近于原始查询，但我们在第一个查询和DISTINCT中只使用了一个字段。其余部分应该不言而喻。|2803105|当你提取数据时，你只需要循环结果，因为每一行都是一个视频。从长远来看，这会让事情变得更简单，我保证。|2803106|Other+things|2803107|您还会遇到SQL注入问题，因为这可能不太容易破解，但为什么要冒这个风险呢？任何时候，只要将数据连接到查询中，就会打开SQLInjection的大门。即使数据是100%25安全的，您也不会因为总是准备查询而损失任何东西。有很多关于如何做到这一点的教程。|2803108|您可以在复制部分中使用VALUES(column)，我就是这么做的。这使我们不必复制变量(它不应该出现在这里)，但即使当您准备它时，它也会使它更容易。|2803109|命名约定，例如channelid、videoId和channelId。这看起来似乎没什么大不了的，但事实确实如此。虽然每个人都有自己的方式，但最重要的部分是“保持一致”。我更喜欢没有大写的，空格的下划线，复数表名。所以对我来说，这些应该是channel_id，video_id。通过了解这一点并保持一致性，我不必再回顾一年前创建的表的模式，并怀疑它是channelid、ChannelId、channelID、channelId还是channel_id。正如你所看到的，大小写的版本比小写的版本(4%2B和2)有更多的变化。列名中的空格在MySQL中是不允许的，因为您必须用回格对它们进行转义，所以这永远不是问题。|2803110|您覆盖了内部循环中的$row变量，即用于JSON的变量，因此我将其改为$items。您也不需要来自JSON的channelid，因为您已经在查询(和url)中找到了它。|2803111|享受吧！|2803112|entityMap^0|0|0|0|0|12|8|0|0|0|0|0|0|0|0|0|0|0|6|0|0|0|3|9|18|9|1I|7|0|5|9|0|B|8|K|8|0|0|0|K|8|0|0|0|C|0|0|B|E|0|7|9|H|7|P|9|3B|A|3M|8|4V|9|55|9|5F|9|5P|9|60|A|0|A|4|Z|6|1I|9|0|0^^$0|@$1|2|3|4|5|6|7|25|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|26|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|27|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|28|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|29|8|@$M|2A|N|2B|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|2C|8|@]|9|@]|A|$]]|$1|R|3|S|5|T|7|2D|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|2E|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|2F|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|2G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|2H|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|2I|8|@]|9|@]|A|$]]|$1|12|3|13|5|D|7|2J|8|@]|9|@]|A|$E|F]]|$1|14|3|15|5|6|7|2K|8|@]|9|@]|A|$]]|$1|16|3|17|5|6|7|2L|8|@$M|2M|N|2N|O|18]]|9|@]|A|$]]|$1|19|3|1A|5|6|7|2O|8|@]|9|@]|A|$]]|$1|1B|3|1C|5|6|7|2P|8|@]|9|@]|A|$]]|$1|1D|3|1E|5|6|7|2Q|8|@$M|2R|N|2S|O|P]|$M|2T|N|2U|O|P]|$M|2V|N|2W|O|P]]|9|@]|A|$]]|$1|1F|3|1G|5|6|7|2X|8|@$M|2Y|N|2Z|O|P]]|9|@]|A|$]]|$1|1H|3|1I|5|6|7|30|8|@$M|31|N|32|O|P]|$M|33|N|34|O|P]]|9|@]|A|$]]|$1|1J|3|1K|5|6|7|35|8|@]|9|@]|A|$]]|$1|1L|3|1M|5|D|7|36|8|@]|9|@]|A|$E|F]]|$1|1N|3|1O|5|6|7|37|8|@$M|38|N|39|O|P]]|9|@]|A|$]]|$1|1P|3|1Q|5|6|7|3A|8|@]|9|@]|A|$]]|$1|1R|3|1S|5|6|7|3B|8|@$M|3C|N|3D|O|18]]|9|@]|A|$]]|$1|1T|3|1U|5|6|7|3E|8|@]|9|@]|A|$]]|$1|1V|3|1W|5|6|7|3F|8|@$M|3G|N|3H|O|P]]|9|@]|A|$]]|$1|1X|3|1Y|5|6|7|3I|8|@$M|3J|N|3K|O|P]|$M|3L|N|3M|O|P]|$M|3N|N|3O|O|P]|$M|3P|N|3Q|O|P]|$M|3R|N|3S|O|P]|$M|3T|N|3U|O|P]|$M|3V|N|3W|O|P]|$M|3X|N|3Y|O|P]|$M|3Z|N|40|O|P]|$M|41|N|42|O|P]]|9|@]|A|$]]|$1|1Z|3|20|5|6|7|43|8|@$M|44|N|45|O|P]|$M|46|N|47|O|P]|$M|48|N|49|O|P]]|9|@]|A|$]]|$1|21|3|22|5|6|7|4A|8|@]|9|@]|A|$]]|$1|23|3|-4|5|6|7|4B|8|@]|9|@]|A|$]]]|24|$]]

Here is your problem

<pre><code>foreach($json['items'] as $row){
 ...
 $videoId = $row['id']['videoId'];
 $videoId1 = $row['id']['videoId'];
 $videoId2 = $row['id']['videoId'];
 $sql = "INSERT INTO users(channelid, videoId, videoId1, videoId3) VALUES('$channelid', '$videoId', '$videoId1', '$videoId2') 
 ON DUPLICATE KEY UPDATE channelid='$channelid', videoId='$videoId', videoId1='$videoId1', videoId2='$videoId2';
 ";
 ...
}
</code></pre>

Basically what happens is take your first JSON data (I'll focus just on the Values part):

<pre><code> //iteration 1
 VALUES('UCiP20o92_7jUYwj-ulVLifw', 'kHPqpxDE50c', 'kHPqpxDE50c', 'kHPqpxDE50c')
//iteration 2
 VALUES('UCiP20o92_7jUYwj-ulVLifw', 'DbAlCl3kk-M', 'DbAlCl3kk-M', 'DbAlCl3kk-M')
//iteration 3
 VALUES('UCiP20o92_7jUYwj-ulVLifw', '7EDPROQa4i0', '7EDPROQa4i0', '7EDPROQa4i0')
</code></pre>

Now think what happens. Because of how you table is setup(improperly I may add), I'd be willing to bet that <code>chanelid</code> is unique. So each loop you overwrite the id's from the previous loop.

<blockquote>
 it's 3 same videoId's inserting 7EDPROQa4i0
</blockquote>

Which by the way is "Exactly" what you see happen.

To fix it you have to insert/update after the JSON loop. By building an array of video Ids that we can insert in one query. You don't need 1 query for each loop of the JSON data (the way it's setup).

Like this:

<pre><code>$loop = mysqli_query($conn, "SELECT channelid, videoId FROM users ORDER BY id")
or die (mysqli_error($conn));

while ($row = mysqli_fetch_array($loop))
{
 $channelid = $row['channelid'];
 $url = 'https://www.googleapis.com/youtube/v3/search?order=date&amp;part=snippet&amp;channelId='.$channelid .'&amp;maxResults=3&amp;key=KEY';
 $content = file_get_contents($url); 
 $json = json_decode($content, true); 

 if(!isset($json['items'])) continue; //skip if no items

 $videos = ['videoId'=&gt;'','videoId1'=&gt;'','videoId2'=&gt;''];
 $i = 0;
 foreach($json['items'] as $items)
 {
 $key = 0==$i ? 'videoId' : 'videoId'.$i;
 $videos[$key] = $items['id']['videoId']; 
 ++$i;
 }

 $sql = "INSERT INTO users(channelid, videoId, videoId1, videoId3) VALUES('$channelid', '{$videos['videoId']}', '{$videos['videoId1']}', '{$videos['videoId2']}') 
 ON DUPLICATE KEY UPDATE channelid=VALUES(channelid), videoId=VALUES(videoId), videoId1=VALUES(videoId1), videoId2=VALUES(videoId2);
 ";
 mysqli_query($conn, "SET NAMES utf8");
 if(!mysqli_query($conn,$sql))
 {
 die('Error : ' . mysqli_error($conn));
 }
}
</code></pre>

That takes care of the immediate issue. However there is a much bigger issue here, which is how your table is setup and how you are using it. The way you have it you are limited to only 3 video and even if that is all you need, at some point you might want more. Everyone always wants more, it's a given. To update it to have 4 or 5 or 6 requires you add additional columns in, which is bad.

Deeper Issues

A better way is to use 1 Row per video. So how do we do that? 

The first thing to do is export this table (so you have a backup)

Then change the index for <code>channelid</code> to a compound (composite) "Unique" index, that has 2 fields both the <code>channelid</code> and <code>videoId</code> as the fields. Both of these have to be the same for the row to be considered a duplicate.

Next add a simple index (non-unique) back on <code>channelId</code>. You want it to have it's own index because for the compound one to work generally you'll wan't to supply both fields (in the order of the index). That said I do forget (between the DB and some other things like Mongo), so you may be able to use one if it's the first one. You can always do explain and make sure it uses it. Anyway, you want that index because we are going to be using that to do lookups and well indexing it is good. 

Next you will have to get rid of <code>videoId1</code> and <code>videoId2</code> completely. If you have data in there you don't want to lose you can make a migration script that will pull the values out of those 2 fields and create a new row for them.

Then onto the code.

<pre><code>$loop = mysqli_query($conn, "SELECT DISTINCT channelid FROM users ORDER BY id")
or die (mysqli_error($conn));

while ($row = mysqli_fetch_array($loop))
{
 $channelid = $row['channelid'];
 $url = 'https://www.googleapis.com/youtube/v3/search?order=date&amp;part=snippet&amp;channelId='.$channelid .'&amp;maxResults=3&amp;key=KEY';
 $content = file_get_contents($url);
 $json = json_decode($content, true);

 if(!isset($json['items'])) continue; //skip if no items

 foreach($json['items'] as $items)
 {
 $videoId = $items['id']['videoId'];
 $sql = "INSERT INTO users(channelid, videoId) VALUES('$channelid', '$videoId') 
 ON DUPLICATE KEY UPDATE channelid=VALUES(channelid), videoId=VALUES(videoId);
 ";
 mysqli_query($conn, "SET NAMES utf8");
 if(!mysqli_query($conn,$sql))
 {
 die('Error : ' . mysqli_error($conn));
 }
 }
}
</code></pre>

This is much closer to your original, but we use only one field in the first query and <code>DISTINCT</code>. The rest should be self explanatory. 

When you pull the data out, you'll just have to loop though the results as each row will be 1 video. It will make things simpler in the long run, promise.

Other things

You also have SQL Injection issues, which is so so as this is probably less hackable, but why risk it. Anytime you concatenate data into a query you open the door of SQLInjection. Even if the data is 100% safe, you lose nothing by always preparing your queries. There is plenty of tutorials on how to do this.

You can use <code>VALUES(column)</code> in the on duplicate part, which I did. This saves us from having to duplicate the variable (which shouldn't be here) but even when you prepare it, it will make it easier.

Naming conventions, such as <code>channelid</code> vs <code>videoId</code> vs <code>channelId</code>. It may seem like not a big deal, but it really is. While everyone has their own way of doing it the most important part is "be consistent". I prefer no-caps, underlines for spaces, plural table names. So for me these would be <code>channel_id</code>, <code>video_id</code>. By knowing this and being consistant I don't have to look back at the schema of a table I made a year ago and wonder if it's <code>channelid</code>, <code>ChannelId</code>, <code>channelID</code>, <code>channelId</code> or <code>channel_id</code>. AS you can see just looking there is more variation in the cased versions then the lowercase version (4+ vs 2). Spaces in column name are a non-starter in MySQL as you have to escape them with backtics so that's never a question.

You were overwriting the <code>$row</code> variable in the inner loop, the one for JSON, so I changed that to <code>$items</code> instead. You also don't need the <code>channelid</code> from the JSON because you already have it in the query (and the url).

Enjoy!

I have a problem - I need to insert data from json to mysql but it's inserting last videoId and it's 3 same videoId's inserted 7EDPROQa4i0. I need to insert from top to bottom from json to database. I cannot understand what's wrong

JSON DATABASE

<pre><code>{
 "resultsPerPage": 3
 },
 "items": [
 {
 "id": {
 "videoId": "kHPqpxDE50c"
 },
 "snippet": {
 "channelId": "UCiP20o92_7jUYwj-ulVLifw",
 },
 }
 },
 {
 "id": {
 "videoId": "DbAlCl3kk-M"
 },
 "snippet": {
 "channelId": "UCiP20o92_7jUYwj-ulVLifw",
 },
 }
 },
 {
 "id": {
 "kind": "youtube#video",
 "videoId": "7EDPROQa4i0"
 },
 "snippet": {
 "channelId": "UCiP20o92_7jUYwj-ulVLifw",
 },
 }
 }
 ]
}
</code></pre>

PHP CODE:
 

<pre><code> $loop = mysqli_query($conn, "SELECT channelid, videoId FROM users ORDER BY id")
 or die (mysqli_error($conn));

 while ($row = mysqli_fetch_array($loop))
 {
 $url = 'https://www.googleapis.com/youtube/v3/search?order=date&amp;part=snippet&amp;channelId='.$row['channelid'].'&amp;maxResults=3&amp;key=KEY';
 $content = file_get_contents($url);
 $json = json_decode($content, true);

 foreach($json['items'] as $row)
 {
 $channelid = $row['snippet']['channelId'];
 $videoId = $row['id']['videoId'];
 $videoId1 = $row['id']['videoId'];
 $videoId2 = $row['id']['videoId'];
 $sql = "INSERT INTO users(channelid, videoId, videoId1, videoId3) VALUES('$channelid', '$videoId', '$videoId1', '$videoId2') 
 ON DUPLICATE KEY UPDATE channelid='$channelid', videoId='$videoId', videoId1='$videoId1', videoId2='$videoId2';
 ";
 mysqli_query($conn, "SET NAMES utf8");
 if(!mysqli_query($conn,$sql))
 {
 die('Error : ' . mysqli_error($conn));
 }
 }
 }
$conn-&gt;close();
?&gt;
</code></pre>

Php insert from json data to mysql from top to bottom in while

数据库

MySQL

JSON

我有一个问题-我需要插入数据从json到mysql，但它是插入最后一个videoId，它是3个相同的videoId插入7EDPROQa4i0。我需要从上到下从json到数据库的插入。我不明白出了什么问题JSON数据库{  "resultsPerPage": 3 }, "items": [  {   "id": {   ...

问Php将json数据从上到下插入到mysql中
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Php将json数据从上到下插入到mysql中EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Php将json数据从上到下插入到mysql中
EN