使用php在mysql db中存储整个json字符串

I am trying to store a string of data in mysql using a text field, but I keep getting an error, even though if i try putting the entire string through phpmyadmin it works fine.

我试图使用文本字段在mysql中存储一串数据,但我不断收到错误,即使我尝试通过phpmyadmin整个字符串,它工作正常。

Error in the consult..You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\"0\":\"kevin9anderson\",\"1\":\"altitudedesign\",\"2\":\"JobSearchLO\",\"3\":\"' at line 1

咨询中的错误。您的SQL语法有错误;查看与MySQL服务器版本对应的手册,以便在'\“0 \”附近使用正确的语法:\“kevin9anderson \”,\“1 \”:\“altitudedesign \”,\“2 \”:\“第1行的JobSearchLO \“,\”3 \“:\”'

  $list = addslashes(json_encode($screen_names));


  $datetime = date('Y-m-d H:i:s');

  $query = "INSERT INTO `db`.`lists` (`id`, `list`, `date`) VALUES (NULL, $list, '2014-12-16 03:29:17')";
  # execute the query.
  $result = $link->query($query) or die("Error in the consult.." . mysqli_error($link));


  // WHAT IS CURRENTLY IN $LIST IS (without slashes):

  /*{"0":"kevin9anderson","1":"altitudedesign","2":"JobSearchLO","3":"xenastar","4":"bukhieade","5":"kundukundu","6":"aqbsoft","7":"blurDesigns","8":"LamidiRazaq","9":"Pixeltw1","10":"topsigsites","11":"akin_wal3","12":"Weisjvo","13":"BSEtech","14":"MikeMalott","15":"TMoellegaard","16":"TheWebBlend","17":"Segko","18":"RevConcept","19":"DesignBumper","20":"TextureDude","21":"temmyify","22":"lyndon_john","23":"KuponoProdVideo","24":"Rachaelparrott","25":"bassey569","26":"acex_hq","27":"CreatorsShop","28":"pybcc","29":"themeyourweb","30":"gpdceo","31":"boyd_mckay","32":"indranilchanda","597":"ajibade_jide","34":"twistedtar","35":"mavtrevor","36":"CheapMattress2","37":"alennwebmaster","38":"Cancun_Paradise","39":"Ambitious1s","40":"edisiblogger","41":"delords","42":"Brugbart","43":"KlassSeo","44":"goofy_is_tubguy","45":"BuyFXsignals","46":"Promo_Assistant","47":"kopphandel_de","48":"Diane_Comeau","49":"techcrates","50":"D2DLondon","51":"Sanjaydhawan512","52":"1bestcellphone","53":"39THSTREETBOYZ","54":"VaptechData","55":"krstenstwrt","56":"BenArthur2","57":"SharpPointBooks","58":"reneeaccounting","59":"jawjuhboy706","559":"wwwfunmoviescom","61":"AUSTINSFINEST11","62":"sitelph","63":"wongcody","64":"replicawatchesu","65":"SashaXarrian","66":"lexxiocom","67":"yayasworld11","68":"JMDD_Web","69":"webgeeksolution","70":"JoshSames","71":"ImajPhotoDesign","72":"clearstart1st","73":"BaileyW2B","74":"KayCockrell6238","75":"ctementor4","76":"samthaboss","77":"NnamdiOkolie","78":"MinimalWP","79":"Itsme_Amos","490":"Iyanuadebo","210":"Kolliga_","82":"sireprince","591":"ayodabs","84":"ViaMylove","85":"MicroWebWorkers","86":"Brodericktgv","87":"Eneidaonc","88":"Kunledway","618":"anjorlah","90":"Godwinask4","91":"Killmylove","92":"lychandom","93":"babatunde2u","94":"AtinaroE","95":"AbudullahiKatu","96":"ty62301","97":"lyft","98":"ThePumaGooner","99":"Femmostical","100":"_aIterations","101":"importunateIy_p","102":"amazed_n1nj4","103":"Hargyberdeyteew","104":"Mauriem88","105":"ronaldadomako","106":"kaffybean","107":"Pretteboi","108":"realemilykopp","109":"slimbabe05","110":"TheArsenal71","111":"femsalinas559","112":"vetabatonline","113":"bizibim","114":"Ms_br33zy","115":"Vickyhans101","116":"m_bash101","117":"Oluwadabbie","118":"SojiDanielx","119":"Aliveinhim19","120":"OLUWAJAMISI","121":"EminowaOluwatoy","122":"oyeleyeolamide","123":"adeogunolawale","124":"Adegoketweets","125":"kejikujjo","126":"mz_ody","127":"JdougieP","128":"Arsha_023","129":"DIEYNA_D","130":"ThePmix","131":"Dyn4casie","132":"jibademike","133":"tomifolawe","134":"fezatudok","135":"WisconsinGunner","136":"ENERGY1069","137":"Mika80m679","138":"damisiabijo","139":"kelvin_tyga","140":"MissssyDee","141":"AfricanDad","142":"FyinInc","143":"Mannyfuntimes","144":"balogunkasim","145":"shestiti","146":"Tunde_rabbi","147":"henry2166","148":"Smokey_Dimples","149":"DaParadise","150":"___andreasG","151":"Hippolite_Jnr","152":"_OLUMO_","153":"rees4life","154":"Adesholac","155":"ajibigad","156":"Bailey_Koren","157":"OmotokeAkanbi","158":"Haydeybahmboh","159":"folorunshosodi1","160":"Maffy_BeatzZ","161":"Iam_bussie","586":"TunjiAdeyeri","163":"labsigirl","164":"khlinton1","165":"topz2k480","166":"firmfaithphc","167":"MULLERFORBES","168":"I_AM_SPLASH","169":"DUUMZY","170":"i_am_Gsax","171":"LeaMarcrum8157","172":"Agags_P","173":"Swift_stunner","174":"Esterzeyl","175":"charmingcharlet","176":"MessinWithJess","177":"arsenalquotes","178":"IrishRecRoom","179":"LadyArseVN","180":"RedWhiteScarf","181":"Football_Speak","182":"Phaetonv2","183":"OPosts","184":"WalexyGooners","185":"luvabric","186":"Ghana_Royalty","187":"AiiShaa_H","188":"Maverickaizer","189":"An4ndPaTiL","190":"JustEmulate","191":"lilnelz","192":"isijay","193":"Gfad_surutu","194":"AFC_Fanbase","195":"Mz_seun","196":"EldridgeMontfor","197":"just_tomi","198":"FaithInWenger","199":"kaliejay981","200":"LaurynBillinger","201":"oniff_tommy","202":"DuntressDaynger","203":"itsJazzyF","204":"koovidcom","205":"4lex_chan","206":"ArsenalHQ","207":"Arsenal_Blogger","208":"RepFlyAsMe","209":"SociallyMilTown","211":"cheespnkprncess","212":"Rabark","213":"pau_LARGE","214":"shreysudan","215":"JamieRorison","216":"Gunnerian","217":"thegooner","218":"Oluwadamilare33","219":"kellyteigan","220":"BillingsWay","221":"Whelts","222":"TJRministries","223":"itsWeze","224":"ARSENALIFE","225":"deshola0532","226":"Tunedey61889241","227":"agate911","228":"nobbleweskey","229":"CoolDewale","230":"Barackobi","231":"Iliyasuzak","232":"b1sh4l","233":"pumafreak100","234":"tomic_L8y","235":"isalako","236":"jackelinebrown","237":"Sherrimdezr","238":"vydami","239":"Arsenal_N5","240":"Lolitamvjti","241":"celebmyswagcom","242":"Denmantrj","243":"aliciaskeeter","244":"ArsenalMOTM","245":"sabrinanusrat94","246":"bala_chn","247":"sneakerwatchcom","248":"gooneramaan","249":"Arsenal_Fanpage","250":"KatiesGooner85","251":"FCInterBlogGFT","252":"YoBenBen","253":"YusufAFC","254":"LargeKatt","255":"SexyFootball_EU","256":"Yockney","257":"OAlmasri","258":"Mysterious_Mee","259":"Timi1776","260":"tha3pLe","261":"Boluwatifesoye","262":"TheGoonerGirls","263":"Nherun","264":"arsenalnewsonly","265":"Mallamofficial","628":"DONJAZZY","267":"__nobility","268":"slimdeeone","269":"nickinala","270":"footballfreebet","271":"amyungace","272":"BishopAy0","273":"GunnersForever_","274":"Antondub","275":"shapezB","276":"01EdCooper","277":"mz_smurfz","278":"LadGooner","279":"thegoal_line","280":"catie_beauty1","281":"_Omoty_","282":"riah_reese","283":"Temmytee92","284":"BaMluvzBR33zY","285":"Retro_Chicago","286":"cityboykidmax","287":"zeegenius","288":"mal_com_XX","289":"ThaAlumni","290":"led110401","291":"FrancieBlackmon","292":"sm000th__gentle","293":"aditoteles","294":"followbackarmy_","295":"iRaptunes","296":"Miss_Timmah","297":"OnlineHYPE","298":"Mz_Stupendous","513":"BossRicky01","300":"Walegzytwet","301":"ark_PR","302":"sexy_veronicaa","303":"Todaysgist","304":"AjeigbeOluwagbe","305":"Iam_Dipor","306":"Mskoyin","307":"Numb_Tweets","308":"Sarphurhat","309":"Futa_weyrey","310":"Amara_USA","311":"yo_itsuti","312":"Prof_guage","313":"Lyricalwizzy","314":"tusheghe","315":"__Faaiza","316":"SeyiPetersBLOG","317":"galacticoHD","318":"Danbros_Media","319":"Car_Ur_Day","320":"Heart4luvEva","321":"kaystrit32","322":"femipeters8","323":"olukayodeoluda1","324":"mesho_bengahzii","325":"I_amAugustus","326":"OscaRankinG","327":"ThatGirl_Palsa","328":"iRock_LV","329":"iam_dannex","330":"bj_abruzzi","331":"Frosh_Pikin","332":"NigeriaParrottv","333":"NaGodwin","334":"Alpha_red1","335":"Bristoltlf","336":"Its_Hoe_K","337":"banjoesan","338":"5ynest_official","339":"all4allNuel","340":"mayream","341":"WETRENDN","342":"FarahNeoteric","343":"jtunga7","344":"rosek1ss","345":"JasminejoyVinni","346":"DilmaMahalia","347":"TRENDS_NG","348":"Bindervrk","349":"doo_esty","350":"adelacuna001","351":"ol_dmm","352":"opelnoni85","353":"JONABOY4","354":"iam_dasaint","355":"Khuunley","356":"_TEENSY_","357":"ShervinSinatra","358":"SinatraMGMT","359":"Qs_imole","360":"fafazi6","361":"YiOliphantino","362":"Fortunesanumi","363":"think_awkward","364":"CalebSumners","365":"coldett","366":"DizzyDortch","367":"THOSEGOODVlBES","368":"DJNARESH","369":"CuteNotesPage","370":"SG_Zu","371":"GODJTrap","372":"BeatingHerUnder","373":"NaDiah_Ash","374":"Lettie_BoyBitch","375":"Thereallaylow","376":"LovableNotebook","377":"officialbskip","378":"Officialsed_","379":"FreakingTrue","380":"EcheMadubuike","381":"TweetLikeGirIs","382":"Welly_Marshall","383":"femaIes","384":"loyal24k","385":"SammyTellem","386":"OfficialSeanB_","387":"_Kyle_Osborne_","388":"NateTheHitmaker","389":"YepillPosts","390":"squiresthetruth","391":"IamAlejandro98","392":"ianthony_H","393":"LZODADON","394":"SaulSorianopxv","395":"Araya_Hope____","396":"MrSmoothNerd","397":"bten_2go","398":"GuinnCarusoxkk","399":"AZEYBOZZ","400":"Mizz_debbie","401":"IsrealKorede","402":"Emmameks","403":"changeam","404":"Tomboy500","405":"Teetwinkles","406":"ILuvSkonk","407":"yemiedabs","408":"BonganiNdlovu_","409":"niyishandle","410":"Sizzune","411":"phemyfreshguy","412":"BabyZee_02","413":"ispeak_sarcasms","414":"ifeaboyeji","415":"JusTheOsh","416":"Ohlunikeh","417":"RyanKnowsGirls","418":"LilSwaggaBoy910","419":"i_am_mykhel","420":"The_Sports_Mind","421":"caduchii","422":"bee_jaybaba234","423":"Cecy_dunsy","424":"OmoMummyGang","425":"Adeeheart","426":"nuteIla","427":"tbhiloveboobs","428":"RealLilGodSODMG","429":"JordanPosts","430":"WorldStarHumour","431":"HotBoyGreedy","432":"obinnacharle","433":"janedanny213","434":"posh_E","435":"philtee1990","436":"IbukunOladeinde","437":"miz_becey","438":"arnoldbaze","439":"abisolajegede","440":"mandy_amazing","441":"_AnjorLaH_","442":"MELDAH_","443":"barrack08","444":"sleekdami","445":"holashegz","446":"iamHaustin","447":"DOLABOMI_","448":"MISTERR_TJ","449":"Buffy_Lautt","450":"topeolaide1","451":"babakemi2010","452":"Amy85Kemi","453":"sahizzle4lyfe","454":"MzStar_Emmy","455":"doyinlicious","456":"iam_freewindz","457":"hinnodz","458":"BolajiHawanat","459":"prizzy_BMR","460":"CeoRnL","461":"Evrybdyluv_Vito","462":"iamKobz","463":"O_oluwadamilola","464":"socialempireent","465":"TheYebowale","466":"MI_Abaga","467":"Iam_noLimits","468":"I_AM_AMAZON","469":"ideevaeva","470":"nwadiutook","471":"steezyTic","472":"Tommy_OC407","473":"Crhedrys","474":"zerah01","475":"Doc_Ade","476":"chronikquotes","477":"pwettiefreda","478":"nifex_magnum","479":"MikeDelevante","480":"ms_bhilz","481":"Shytonsax","482":"yvonne__johnson","483":"T_whizzy101","484":"debonairr","485":"NAMELESS773","486":"reelifeish_210","487":"Ebi_gurl","488":"Guze01","489":"Bluefairy_D","491":"MTN_62130","492":"internal_1","493":"babatundetohib1","494":"thayorbelle","495":"Syndicate61","496":"Tontoblogpost","497":"drboyd03","498":"somadina_arthur","499":"Enginervic","500":"adeboye_adeboye","501":"Cherrylynn991","502":"Orisafunfun","503":"dherine_91276","504":"Mr_Amechi","505":"Makinsworld","506":"Seyi__","507":"FrancescaCiccol","508":"ChandaVarela","509":"DaileEsslin","510":"GenoveraHubch","511":"MarybethKuhnke","512":"omomo14","514":"UzochukwuVera","515":"Plux_Official","516":"Teedeemarley","517":"Shigoopompey","518":"Hofemi","519":"NinjaWolfzHD","520":"martinezwoowwoo","521":"itz_Asod","522":"dixonsamanta","523":"iamdave_8","524":"AyodeleOladunni","525":"babs124qu","526":"Leye4chris","527":"Gem_Olabisi","528":"solabalogun14","529":"Pelzyking","530":"ayobellzz","531":"MrBlackRooney","532":"Eniholha","533":"thenataniel","534":"femi_colin","535":"Hesomatics","536":"TemidayoTemm","537":"DondeonBeke","538":"meedaysweet","539":"Dame_Tania","540":"bukolaokunfolam","541":"tadegboye","542":"Mojipearl","543":"mzz_ozil","544":"RealDarey_Juelz","545":"MrsGiroud","546":"blackyafrika","547":"BiyiKay","548":"leye4real","549":"Yug_isaac","550":"Bhusearyour","551":"HuntellaDotNet","552":"SeriuzBlack","553":"iamdetty","554":"Ay_Adams","555":"_MsJ__","556":"sexychacha_","557":"Ayomidejoe","558":"TrendyShrink","560":"bolagunner","561":"awalitenzube","562":"ogboye_olabisi","563":"jenny_peperempe","564":"MAFGUNTS","565":"MzAyeni","566":"sleekprincess","567":"wazobiagooners","568":"shakarababyy","569":"StudyLuck","570":"miz_khumsy","571":"barack_obash","572":"JimmyTheNerd_","573":"Elle_Toh_Cute","574":"Kassandra_josh","575":"BenOsas007","576":"pweetyboular","577":"isurboi_lance","578":"O_funmibi","579":"pheyimy","580":"ArsenalNL","581":"SoMarkHarmon","582":"Tudamhot","583":"AhmsBaba","584":"Footamb","585":"kylexdavid","587":"lome111","588":"Fapetuemma","589":"Shegsybaba","590":"haybeeone","592":"fhorlarr","593":"biggestdam","594":"tonyblackrooney","595":"pweetymoyo","596":"goonerdaily","598":"adeoyeomotayo","599":"Icelytweets","600":"hoeyn","601":"eroshypnosis","602":"YemojaNews","603":"deedammo","604":"lancelot187","605":"Jay_Trask","606":"celebfrancis","607":"Vospeaks","608":"kunleafolayan","609":"erijeniwt","610":"abssytemi","611":"tolaoguntoyinbo","612":"IllustroP","613":"icyoo7","614":"_attackk","615":"shallomills","616":"Fragiletimbz","617":"OlufemiMakinde","619":"OneBlackBoy","620":"Daetunji","621":"justdaisynow","622":"Aqueouz_B","623":"tobisnoop","624":"Yknight89","625":"ItsOnly_1Dee","626":"noah_riddle","627":"fapetuseun"}
  */

3 个解决方案

#1

Me to OP: Try what FuzzyTree said (NULL,'$list','2014-12-16 03:29:17') quoting $list or '".$list."'

我对OP:尝试使用FuzzyTree所说的(NULL,'$ list','2014-12-16 03:29:17')引用$ list或'“。$ list。”'

OP to me: tried it earlier, it didn't work. but the one you just added did. put it as an answer?

OP给我:早点试过,它没用。但是你刚才添加的那个。把它作为答案?

Encapsulate the $list variable in quoted format, since you're passing JSON string.

以引用格式封装$ list变量,因为您传递的是JSON字符串。

(NULL, '".$list."', '2014-12-16 03:29:17')

#2

You really should consider using PDO which will allow you to use a prepared statement with parameters. You don't need to worry about escaping parameter values:

您真的应该考虑使用PDO,它允许您使用带参数的预准备语句。您无需担心转义参数值:

$pdo = new PDO('mysql:host=localhost;dbname=mydb', $username, $password);
$stmt = $pdo->prepare('INSERT INTO `db`.`lists` (`list`, `date`) VALUES (:list, :date)');
$stmt->bindParam(':list', json_encode($screen_names));
$stmt->bindParam(':date', $date);
$stmt->execute();

As you've tagged your question 'mysql', this will help you determine your connection string: http://php.net/manual/en/ref.pdo-mysql.connection.php

当你标记你的问题'mysql'时,这将帮助你确定你的连接字符串:http://php.net/manual/en/ref.pdo-mysql.connection.php

And, for more on PDO and prepared statements: http://php.net/manual/en/pdo.prepared-statements.php

而且,有关PDO和准备语句的更多信息:http://php.net/manual/en/pdo.prepared-statements.php

#3

$list = addslashes(json_encode($screen_names))

In your $list at beginning and end added slash because of addslashes() used so, it did not made proper string. Changed your insert query to following:

在你的$ list开头和结尾添加了斜杠,因为使用了addslashes(),它没有做出正确的字符串。将您的插入查询更改为以下内容:

$query = "INSERT INTO db.lists (`id`, `list`, `date`) VALUES (NULL, '".$list."', '2014-12-16 03:29:17')";

it stored your json string within quote.

它将你的json字符串存储在quote中。

#1