{"id":1917,"date":"2013-07-11T06:30:29","date_gmt":"2013-07-11T05:30:29","guid":{"rendered":"http:\/\/www.manuelmarangoni.it\/sir-bit\/?p=1917"},"modified":"2013-07-10T14:30:57","modified_gmt":"2013-07-10T13:30:57","slug":"php-mysql-come-connettersi-a-due-database-contemporaneamente","status":"publish","type":"post","link":"https:\/\/www.manuelmarangoni.it\/sir-bit\/1917\/php-mysql-come-connettersi-a-due-database-contemporaneamente\/","title":{"rendered":"PHP, MYSQL \u2013 Come connettersi a due database contemporaneamente"},"content":{"rendered":"<p>Se vi capita di gestire un cliente che dispone di due siti internet, \u00e8 probabile che prima o poi vi ritroverete nella condizione di dover <strong>connettervi contemporaneamente a due database<\/strong>.<br \/>\nIl motivo pu\u00f2 essere vario: promuovere i prodotti di un dominio sull&#8217;altro dominio tramite una procedura automatica; recuperare informazioni o riferimenti dell&#8217;altro dominio; ecc.<\/p>\n<p>La connessione tra due database non \u00e8 sempre immediata.<br \/>\nPrima di tutto dobbiamo distinguere due situazioni:<br \/>\n1) i due database si trovano <strong>sullo stesso server<\/strong>: in questo caso host, user e password di connessione potrebbero coincidere<br \/>\n2) i due database si trovano <strong>su server diversi<\/strong>: \u00e8 necessario eseguire per forza due connessioni diverse<\/p>\n<p>Qua sotto vi mostrer\u00f2 due sistemi per connettervi a database multipli.<br \/>\nIn entrambe le situazioni:<br \/>\n&#8211; i due database avranno il nome (poco fantasioso ma immediato) di <em>primo_database<\/em> e <em>secondo_database<\/em><br \/>\n&#8211; in entrambi i database \u00e8 presente una tabella chiamata <em>prodotti<\/em>, al cui interno ha un campo chiamato <em>id<\/em> (che rappresenta il campo autoincrementale dei record) e un campo chiamato <em>nome<\/em> (varchar di lunghezza a piacere)<br \/>\n&#8211; poniamo per semplicit\u00e0 che in entrambe le tabelle &#8220;prodotti&#8221; ci siano dei record con id=2 . L&#8217;unica differenza sta nel campo <em>nome<\/em>, che avr\u00e0 i seguenti valori:<br \/>\n\t&#8211; tabella di &#8220;primo_database&#8221;: nome=&#8221;libro&#8221;<br \/>\n\t&#8211; tabella di &#8220;secondo_database&#8221;: nome=&#8221;film&#8221;<\/p>\n<p>In questo modo, quando stamperemo a video il campo &#8220;nome&#8221; del record con id=2 avremo la possibilit\u00e0 di capire se <strong>proviene dal primo database o dal secondo<\/strong>.<\/p>\n<h3 style=\"margin-top:50px; margin-bottom:20px; color:#c00;\">Connessione a due database sullo stesso server<\/h3>\n<p>Se i due database si trovano sullo stesso server (probabilmente un server dedicato), in gran parte dei casi la connessione a entrambi avverr\u00e0 con lo stesso host, user e password.<br \/>\nPer distingue quale database coinvolge una delle nostre query, ci basta far <strong>precedere il nome della tabella <em>prodotti<\/em> dal nome del database<\/strong>.<br \/>\nQuindi, se la tabella di ricerca della select sar\u00e0 &#8220;primo_database.prodotti&#8221; significa che coinvolger\u00e0 la tabella &#8220;prodotti&#8221; di &#8220;primo_database&#8221;.<br \/>\nViceversa, se la tabella di ricerca della select sar\u00e0 &#8220;secondo_database.prodotti&#8221; significa che coinvolger\u00e0 la tabella &#8220;prodotti&#8221; di &#8220;secondo_database&#8221;.<!--more--><\/p>\n<div class=\"wp_syntax\">\n<table>\n<tr>\n<td class=\"line_numbers\">\n<pre>1\r\n2\r\n3\r\n4\r\n5\r\n6\r\n7\r\n8\r\n9\r\n10\r\n11\r\n12\r\n13\r\n14\r\n15\r\n16\r\n17\r\n18\r\n19\r\n20\r\n21\r\n22\r\n23\r\n24\r\n25\r\n26\r\n27\r\n28\r\n29\r\n30\r\n31\r\n32\r\n<\/pre>\n<\/td>\n<td class=\"code\">\n<pre class=\"php\" style=\"font-family:monospace;\"><span style=\"color: #009933; font-style: italic;\">\/**\r\n* Connessione a due database sullo stesso server \r\n*\/<\/span>\r\n&nbsp;\r\n<span style=\"color: #000088;\">$db_host<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #0000ff;\">&quot;localhost&quot;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$db_user<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #0000ff;\">&quot;root&quot;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$db_password<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #0000ff;\">&quot;password&quot;<\/span><span style=\"color: #339933;\">;<\/span>\r\n&nbsp;\r\n<span style=\"color: #000088;\">$db_name1<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #0000ff;\">&quot;primo_database&quot;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$db_name2<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #0000ff;\">&quot;secondo_database&quot;<\/span><span style=\"color: #339933;\">;<\/span>\r\n&nbsp;\r\n<span style=\"color: #000088;\">$db1<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_connect<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$db_host<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db_user<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db_password<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n&nbsp;\r\n<span style=\"color: #000088;\">$db2<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_connect<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$db_host<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db_user<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db_password<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n&nbsp;\r\n&nbsp;\r\n<span style=\"color: #990000;\">define<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #0000ff;\">'DB1'<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db1<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #990000;\">define<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #0000ff;\">'DB2'<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db2<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #990000;\">define<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #0000ff;\">'DBNAME1'<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db_name1<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #990000;\">define<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #0000ff;\">'DBNAME2'<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db_name2<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n&nbsp;\r\n&nbsp;\r\n&nbsp;\r\n<span style=\"color: #000088;\">$q<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #0000ff;\">&quot;SELECT * FROM &quot;<\/span><span style=\"color: #339933;\">.<\/span>DBNAME1<span style=\"color: #339933;\">.<\/span><span style=\"color: #0000ff;\">&quot;.prodotti WHERE id=2&quot;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$result<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_query<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$q<\/span><span style=\"color: #339933;\">,<\/span> DB1<span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$row<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_fetch_array<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$result<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #b1b100;\">echo<\/span> <span style=\"color: #000088;\">$row<\/span><span style=\"color: #009900;\">&#91;<\/span><span style=\"color: #0000ff;\">'nome'<\/span><span style=\"color: #009900;\">&#93;<\/span><span style=\"color: #339933;\">.<\/span><span style=\"color: #0000ff;\">&quot;&lt;br \/&gt;&lt;br \/&gt;&quot;<\/span><span style=\"color: #339933;\">;<\/span> <span style=\"color: #666666; font-style: italic;\">\/\/stamper\u00e0 &quot;libro&quot;<\/span>\r\n&nbsp;\r\n<span style=\"color: #000088;\">$q<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #0000ff;\">&quot;SELECT * FROM &quot;<\/span><span style=\"color: #339933;\">.<\/span>DBNAME2<span style=\"color: #339933;\">.<\/span><span style=\"color: #0000ff;\">&quot;.prodotti WHERE id=2&quot;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$result<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_query<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$q<\/span><span style=\"color: #339933;\">,<\/span> DB2<span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$row<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_fetch_array<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$result<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #b1b100;\">echo<\/span> <span style=\"color: #000088;\">$row<\/span><span style=\"color: #009900;\">&#91;<\/span><span style=\"color: #0000ff;\">'nome'<\/span><span style=\"color: #009900;\">&#93;<\/span><span style=\"color: #339933;\">;<\/span> <span style=\"color: #666666; font-style: italic;\">\/\/stamper\u00e0 &quot;film&quot;<\/span><\/pre>\n<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<h3 style=\"margin-top:50px; margin-bottom:20px; color:#c00;\">Connessione a due database (su stesso server o su server diversi)<\/h3>\n<p>Un po&#8217; pi\u00f9 macchinoso \u00e8 il sistema da usare qualora i database risiedano su due server diversi.<br \/>\nIn questo caso host, user e password saranno diversi e <strong>le connessioni saranno completamente distinte<\/strong>.<\/p>\n<p><script type=\"text\/javascript\">\/\/ <![CDATA[\ngoogle_ad_client = \"ca-pub-9984963620559913\"; \/* Sir Bit - post singolo rettangolare *\/ google_ad_slot = \"5334366715\"; google_ad_width = 336; google_ad_height = 280;\n\/\/ ]]><\/script><br \/>\n<script type=\"text\/javascript\" src=\"http:\/\/pagead2.googlesyndication.com\/pagead\/show_ads.js\">\/\/ <![CDATA[\n\/\/ ]]><\/script><\/p>\n<p>Il modo migliore per gestire la situazione \u00e8 di creare una funzione ad hoc, che ho chiamato <em>dbconn<\/em>, in cui come argomento passiamo semplice il valore &#8220;1&#8221; o &#8220;2&#8221; per far capire che vogliamo connetterci rispettivamente a &#8220;primo_database&#8221; e a &#8220;secondo_database&#8221;.<br \/>\nPer connettersi al secondo database, quindi, dovremo semplicemente richiamare la funzione:<\/p>\n<p>dbconn(2);<\/p>\n<p>Una volta effettuata la connessione in questo modo, ci baster\u00e0 eseguire normalmente la query sulla tabella &#8220;prodotti&#8221;.<\/p>\n<p>L&#8217;aspetto macchinoso? Un simile sistema <strong>tiene aperta una sola connessione<\/strong> alla volta, per cui a ogni cambio di database dovremo richiamare la funzione &#8220;dbconn&#8221;.<\/p>\n<div class=\"wp_syntax\">\n<table>\n<tr>\n<td class=\"line_numbers\">\n<pre>1\r\n2\r\n3\r\n4\r\n5\r\n6\r\n7\r\n8\r\n9\r\n10\r\n11\r\n12\r\n13\r\n14\r\n15\r\n16\r\n17\r\n18\r\n19\r\n20\r\n21\r\n22\r\n23\r\n24\r\n25\r\n26\r\n27\r\n28\r\n29\r\n30\r\n31\r\n32\r\n33\r\n34\r\n<\/pre>\n<\/td>\n<td class=\"code\">\n<pre class=\"php\" style=\"font-family:monospace;\"><span style=\"color: #009933; font-style: italic;\">\/**\r\n* Connessione a due database\r\n* utilizzabile sia per database nello stesso server che in server diversi\r\n*\/<\/span>\r\n<span style=\"color: #000000; font-weight: bold;\">function<\/span> dbconn<span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$type<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #009900;\">&#123;<\/span>\r\n&nbsp;\r\n\t<span style=\"color: #b1b100;\">switch<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$type<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #009900;\">&#123;<\/span>\r\n&nbsp;\r\n\t\t<span style=\"color: #b1b100;\">case<\/span> <span style=\"color: #0000ff;\">&quot;1&quot;<\/span><span style=\"color: #339933;\">:<\/span>\r\n\t\t\t<span style=\"color: #000088;\">$db1<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_connect<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #0000ff;\">&quot;host1&quot;<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #0000ff;\">&quot;root&quot;<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #0000ff;\">&quot;password1&quot;<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n\t\t\t<span style=\"color: #990000;\">mysql_select_db<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #0000ff;\">&quot;primo_database&quot;<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db1<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n\t\t\t<span style=\"color: #b1b100;\">break<\/span><span style=\"color: #339933;\">;<\/span>\r\n&nbsp;\r\n\t\t<span style=\"color: #b1b100;\">case<\/span> <span style=\"color: #0000ff;\">&quot;2&quot;<\/span><span style=\"color: #339933;\">:<\/span>\r\n\t\t\t<span style=\"color: #000088;\">$db2<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_connect<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #0000ff;\">&quot;host2&quot;<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #0000ff;\">&quot;root&quot;<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #0000ff;\">&quot;password2&quot;<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n\t\t\t<span style=\"color: #990000;\">mysql_select_db<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #0000ff;\">&quot;second_database&quot;<\/span><span style=\"color: #339933;\">,<\/span> <span style=\"color: #000088;\">$db2<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n\t\t\t<span style=\"color: #b1b100;\">break<\/span><span style=\"color: #339933;\">;<\/span>\r\n&nbsp;\r\n\t<span style=\"color: #009900;\">&#125;<\/span>\r\n&nbsp;\r\n<span style=\"color: #009900;\">&#125;<\/span>\r\n&nbsp;\r\n&nbsp;\r\ndbconn<span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #cc66cc;\">1<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$q<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #0000ff;\">&quot;SELECT * FROM prodotti WHERE id=2&quot;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$result<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_query<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$q<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$row<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_fetch_array<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$result<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #b1b100;\">echo<\/span> <span style=\"color: #000088;\">$row<\/span><span style=\"color: #009900;\">&#91;<\/span><span style=\"color: #0000ff;\">'nome'<\/span><span style=\"color: #009900;\">&#93;<\/span><span style=\"color: #339933;\">.<\/span><span style=\"color: #0000ff;\">&quot;&lt;br \/&gt;&lt;br \/&gt;&quot;<\/span><span style=\"color: #339933;\">;<\/span> <span style=\"color: #666666; font-style: italic;\">\/\/stamper\u00e0 &quot;libro&quot;<\/span>\r\n&nbsp;\r\ndbconn<span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #cc66cc;\">2<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$q<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #0000ff;\">&quot;SELECT * FROM prodotti WHERE id=2&quot;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$result<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_query<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$q<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #000088;\">$row<\/span> <span style=\"color: #339933;\">=<\/span> <span style=\"color: #990000;\">mysql_fetch_array<\/span><span style=\"color: #009900;\">&#40;<\/span><span style=\"color: #000088;\">$result<\/span><span style=\"color: #009900;\">&#41;<\/span><span style=\"color: #339933;\">;<\/span>\r\n<span style=\"color: #b1b100;\">echo<\/span> <span style=\"color: #000088;\">$row<\/span><span style=\"color: #009900;\">&#91;<\/span><span style=\"color: #0000ff;\">'nome'<\/span><span style=\"color: #009900;\">&#93;<\/span><span style=\"color: #339933;\">;<\/span> <span style=\"color: #666666; font-style: italic;\">\/\/stamper\u00e0 &quot;film&quot;<\/span><\/pre>\n<\/td>\n<\/tr>\n<\/table>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Vediamo due modi per connettersi contemporaneamente a due o pi\u00f9 database, per gestire nello stesso sito i dati salvati su due server diversi.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[78],"tags":[32,4],"class_list":["post-1917","post","type-post","status-publish","format-standard","hentry","category-php-mysql","tag-mysql","tag-php","cat-78-id"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/posts\/1917"}],"collection":[{"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/comments?post=1917"}],"version-history":[{"count":6,"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/posts\/1917\/revisions"}],"predecessor-version":[{"id":1922,"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/posts\/1917\/revisions\/1922"}],"wp:attachment":[{"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/media?parent=1917"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/categories?post=1917"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.manuelmarangoni.it\/sir-bit\/wp-json\/wp\/v2\/tags?post=1917"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}