Tabla de contenido Contenido de esta guía Base de datos Sistemas de administración de bases de datos Escalabilidad Php Tipos de tablas MySQL MyISM Inno!B SQL Query "erramientas php#MySQL Espacios de desarrollo de bases de datos $eb ser%er &MPP' &MPP' componentes principales Instalación de &MPP &MPP' (eri)icación de instalación Comprobación en el mismo site' para actuali*aciones &MPP' &MPP' el Panel de Control Test php Los primeros archi%os php PhpMydmin pps y bases de datos en ndroid Crear base de datos Crear tablas !e)inir propiedades de campos Comprobación de tablas Corrección del contenido de los campos Compro%ación del campo de )echa Consultando una tabla Tablas' copiar' %aciar y suprimir Tablas' importar y e+portar Cone+ión a una base de datos Script de cone+ión Insertar' modi)icar y suprimir con php El comando incluido ,-includes./ en php require_once ai2-gestionedati.php ai2-index.php ai2-accessosessione.php Inter)a* pp ndroid con la base de datos MySQL query-string ai2-interfaccia.php Tareas y reglas de la inter)a* (ariables de la inter)a* Prueba ai2-interfaccia.php localmente 0rabar datos en la tabla con php
Cargar la base de datos en Internet y p1ginas php 2bt3n un dominio con base de datos !escarga tablas desde la base de datos local Carga tablas en la base de datos remota Prepara la p1gina para conectar a la base de datos Subir p1ginas P"P a la 4eb Carpetas disponibles Prueba p1ginas y bases de datos en Internet Prueba acceso a la base de datos y la %ersión php Probando en Internet la inter)a* ai2-interfaccia.php Probando la p1gina ai2-index.php en Internet pp5$eb5P"P5MySQL 2!BC ,2pen !ataBase Connecti%ity/ !atos remotos y administración de di)icultades con pp In%entor pp5$eb5P"P5MySQL' pp5$eb5P"P5MySQL' como se hace !ise6o de la p1gina y componentes Componentes' propiedades y )unción signar comportamientos a componentes Seleccionar y )iltrar registros Importar registros dentro del dispositi%o Compartir el contenido del registro importado
Contenido de esta guía Esta guía e+plica como usar pp In%entor 7 para crear una pp para ndroid 8ue interractue con tablas en una base de datos MySQL9 !esde tu dispositi%o ndroid podr1s: 2rdenar cual8uier campo de la tabla en orden ascendente y descendiente9 ;iltrar los datos de un campo a tu elección' incluso en el mismo orden9 Importar al dispositi%o ndroid los registros de la base de datos9 Compartir ,Sharing/ esos registros' a tra%3s una lista de las pp instaladas en tu dispositi%o' incluyendo a8uellas 8ue pueden mane
9 79 ai2tabea: Tabla de base de datos9 ?9 ai2-connessionedb.php y ai2connessionedb.php: Cone+ión de base de datos9 @9 ai2-index.php y ai2index.php: ccede al la p1gina de administración de la base de datos9 A9 ai2-accessosessione.php y ai2accessosessione.php: (eri)ica el acceso de datos a la base de datos9 9 ai2-gestionedati.php y ai2gestionedati.php: 6ade' edita datos9 9 ai2-interfaccia.php y ai2interfaccia.php: P1gina de inter)a* con la pp ndroid9 Estas p1ginas php han sido escritas en dos %ersiones di)erentes de lengua
Para a8uellos interesados en usar la librería gr1)ica de php ,0! library/' e+pandiendo y enri8ueciendo la inter)a* entre PP ndroid y php # MySQL' en librerias de Internet pueden encontrar ayuda: Gsar 0! library con php: )unciones' )iguras' gra)icos y gradientes9 En Italiano' contiene e
Base de datos En lengua
La tabla llamada !ai2tabea" 8ue contiene las regiones de Italia podria respresentarse de este modo: Id
Kombre
=
Toscana
7
Gmbria
?
La*io
Kotas "abitantes ?7@??' situada en Italia central' con capital ;iren*e9 Negión de Italia central de @ habitantes' en el cora*ón de la peninsula9 "abitantes AA?' con capital Noma9
;echa 7F=F@== =7:7:=A 7F=@F?= =@:=:=F 7F=7F=? 7=:7?:@F
En la tabla superior !ai2tabea"' los elementos de un registro' los campos' est1n identi)icados por un nombre9 dem1s' hay un campo llamado #$d# ' es el campo indice9 un8ue los indices son opcionales' pueden hacer mucho m1s r1pida las operaciones de bus8ueda y orden' especialmente en grandes cantidades de datos9 !entro de la misma base de datos' el nombre de cada tabla debe ser Jnico9 El mismo nombre de campo puede ser usado en di)erentes tablas de la misma base de datos9 Pero mientras 8ue la bus8ueda en la base de datos se reali*an in%ocando nombres de tablas y campos' es me
La cantidad de registros es %irtualmente in)inito' The amount o) records is %irtually in)inite' Siendo su límite la cantidad de espacio del medio 8ue los contiene9 La cantidad de campos y de tipos de datos 8ue cada uno puede contener esta de)inido por 8uien dise6a la base de datos9
El proposito de la base de datos es con%ertir datos en in)ormación9 Si consideramos la in)ormación como conocimiento' los dos t3rminos no son intercambiables' en realidad' hay mucha di)erencia entre ambos9
Los datos es una colección de hechos9
La in)ormación son datos organi*ados o presentados de )orma apropiada para tomar decisiones9
DataBase Managment Systems There are many !BMS ,!ataBase Managment Systems/ 4hich are so)t4are that implements the )unctions to use the database9 These )unctions are: data access' updating' research and selection ,also called )iltering/' the latter in a manner that can be chosen by users' etc9 The best no4n so)t4are o) these !BMSs are: MySQL' 2racle' MS SQL' PosgreSQL' etc9
scalability 2)ten reads Odatabase scalability' 4hich is the ability to maintain the integrity o) the per)ormance o) the database 4ith increasing amount o) data' the 8ueries per)ormed and' e%en more important' as it is possible to e+pand the design o) the database by adding tables' )ields and )unctionality9
php P"P ,recursi%e acronym )or OP"P: "yperte+t Preprocessor "yperte+t Preprocessor' is an interpreted scripting language' originally designed )or programming dynamic 4eb pages9 The php interpreter is a )ree so)t4are distributed under the P"P License9 It supports all !BMS more 4idespread9
MySQL types of tables The tables used by MySQL are %aried' the main ones are
MyISAM Type de)ined by de)ault' it ensures e+cellent per)ormance in speed in data search
InnoDB More comprehensi%e than MyISM' but slo4er because o) the additional )eatures a%ailable to them9 Today ,7F=/ to increase the po4er o) the machine' the per)ormance di))erence bet4een MyISM and Inno!B has become minimal9
This type o) tables are not al4ays a%ailable in the hosting cost9
SQL SQL ,Short Query Language/ is the language used )or operations in !BMS9 !espite being a%ailable in many dialects' these are %ery similar to each other e%en i) implemented on %ery di))erent systems9 To de)ine actions to be per)ormed on the data and databases' SQL uses an almost natural synta+9
The commands are in English and the 4ords # commands identi)y the )unction they per)orm ,select to select' delete to delete' update to upgrade and so on/9 E%erything is in assimilating the 4ay in 4hich the phrase is composed # SQL command and the rest Ocomes )rom itsel)9
Query The user use this term to speci)y the 8uery in a database ,select' insert' delete' update etc9/9
Queries use SQL language ,Structured Query Language/9 The analysis o) the result o) the 8uery is the sub
Queries are commonly used in php pages or similar language and are di%ided into t4o types:
Ne8uest in)ormation' such as data selection 8ueries SELECT: used to select data )rom tables Editing commands' such as deletion' insertion or correction IKSENT: insert data into tables GP!TE: modi)y data in tables !ELETE: delete data )rom tables mong the action 8ueries are also co%ered CNETE: create tables LTEN: modi)y the table structure !N2P: drop the tables
The latter group commands are usually used in the creation o) the database' and much less in normal daily use9
php/MySQL tools Database deelopment spaces There are se%eral so)t4are that autonomously create the de%elopment spaces and allo4 you to start 8uicly 4ith the implementation o) pro
&MPP is multiplat)orm' 4hich means that it 4ors on Linu+' Mac and $indo4s en%ironments9 2n the 4ebsite https:##44 49apache)riends9org#it#inde+9html are a%ailable and do4nloadable pacages )or di))erent 2perating Systems and in %arious languages9 &MPP is a simple and light pache distribution' it maes it e+tremely simple to beginners and de%elopers create 4eb ser%er on your local PC9 The so)t4are is designed )or easy installation and an intuiti%e use9
In &MPP includes a 4eb application ser%er ,pache/' a !BMS ,MySQL/' and a scripting language ,php/9 The supplied so)t4are is )ree and reproduction is )ree9 (ery important is the )act that many o) the Internet ser%er en%ironments using the same components' this maes it simple and intuiti%e to copy php tables and pages )rom a local system to a 4eb ser%er on the Internet9
!eb serer So)t4are' running on a ser%er' that can manage re8uests to trans)er clientRs 4eb pages' typically a 4eb bro4ser9 The set o) the 4hole interconnected 4eb ser%er gi%es li)e to the $orld $ide $eb9
4eb ser%er installed on the local PC simulates the beha%ior that you 4ould ha%e on Intenet' allo4ing more con%enience test' thus )acilitating corrections9
"AM##$ main components pache: most popular 4eb ser%er in the 4orld' processes and responds to re8uests by returning the content to the client computer ,the applicant/ MySQL: !escribed abo%e' 2pen Source and )ree9 It is used' among others' by oomla and $ordPress' allo4s the reali*ation pro)essional applications9 Php: s described abo%e' 4ith it 4oring oomla' !rupal and $ordPress9 Trusted by thousands o) de%elopers' simple to learn' he 4ors per)ectly 4ith MySQL9 Perl is a po4er)ul programming language' )eaturerich' 4ith o%er 7A years o) de%elopment9 PhpMydmin' 4ritten in php' is a )ree so)t4are' distributed under the 0KG license9 It allo4s you to administer the database' create tables' 8ueries' etc9 tested9 The latest %ersion can also be do4nloaded )rom the o))icial 4ebsite http%//!! !&phpMyAdmin&net/
I'S(ALL "AM## !espite the simplicity and the 8uality o) the pacage' someone may )ind some di))iculties9 Some attention can be:
Temporarily !isable the anti%irus be)ore installation9 Install the pacage maing you clic the right button Nun as administrator9 In one screen' usually the second' you can choose 4hich components to install9 ;or simplicity' or not no4ing 4hat e+actly to do' lea%e e%erything as de)ault9 I) necessary' unchec Learn more about BitKami )or &MPPR
The &MPP installation can tae se%eral minutes' the time may also depends on the per)ormance o) the PC in use9 2nce installed 4ill be a%ailable a control panel )rom 4hich you can chec and set all the &MPP components and start or stop the ser%ices9
t the end o) the installation the so)t4are should be located in the )older C: U +ampp U 2b%iously the letter C can be di))erent i) you chose to install on another dis9
"AM##$ installation erification 2nce do4nloaded and installed &MPP must chec its success9
0o to C:U+ampp ,or ade8uate lettre/ and double clic on the )ile +ampp5start9e+e +ampp5start9e+e It should see the &MPP Control Panel
Launch any bro4ser and in the address bar 4rite: http: ## localhost #
Vou should see the main page o) &MPP 4ith the title: &MPP pache W MySQL W P"P W Perl
In the latest %ersions instead o) &MPP pache W MySQL W P"P W Perl might loo: &MPP W pache W P"P W Perl Maria!B
2n the mariadb 4ebsite https:##mariadb9com#b#it#mariadb%sm https:##mariadb9com#b#it #mariadb%smys8lcompatibi ys8lcompatibilita# lita# (he )nglish*language pages are at% https%//mariadb&com/ h ttps%//mariadb&com/ you can read: ;or all practical purposes' Maria!B is a binary b inary replacement )or MySQL' ready to use' 4hich can replace the corresponding %ersion ,eg MySQL A9= A9= Maria!B' MySQL A97 A97 Maria!B' MySQL A9? A9? Maria!B/ 9
"ereRs 4hat it means: The data and the table de)inition )ile ,9)rm/ are binary compatible9 ll client PIs' protocols and structures are identical9 ll )ile names' the tracs' paths' ports' socets' etc should be e8ual9 ll MySQL connectors ,P"P' Perl' Python' a%a' 9KET' My2!BC' Nuby' MySQL C connector etc/ beha%e the same 4ay 4ith Maria!B9
There are some installation problems 4ith phpA o) 4hich is better to be a4are ,it is a bug in the 4ay the old controls the phpA client library compatibility/9 compatibility/9 The mys8lclient pacage also 4ors 4ith Maria!B ser%er9 This means that in most cases simply uninstall MySQL and install Maria!B to use it9 There is no need to con%ert the data )iles i) you are using the same %ersion' such as A9=9
Chec+ at the same site$ for updates& I) &MPP does not start its components' one o) the most )re8uent problems is the use o) port F that &MPP &MPP uses by de)ault 4hile 4hile the same port port is also used by Sype Sype and $indo4s =F9
In the case o) Sype'
In the case o) $indo4s =F' but also )or Sype that relocate it to listen on a di))erent port it may be desirable to change the listening port )or &MPP %arying %arying it by F to another9 To this address http:##pep)r http:## pep)r y9com#tut 9com#tutorials# orials#sol%e sol%eissue issueportFin portFinuseby usebysystem system+ampp +ampp you can read a thorough tutorial9 "o4e%er' this is to change the contents o) httpd9con)' to do it:
0o to the C: U +ampp U apache U con) Mae a copy o) httpd9con) to be ept ready to replace the original in case o) errors9 2pen the httpd9con) )ile 4ith the $indo4s Kotepad Modi)ier %oice Listen F to Listen = ,or other number/ Nestart all Launch a bro4ser and in the address bar' type http: ## localhost: = # and e%erything should be oay9
Solutions )or other incon%enineti indeed %ery rare' can be read at:
http:##44 49corsi
"AM##$ the Control #ane l To start the &MPP control panel' you ha%e to go to C: U +ampp ,or appropriate dri%e letter/' and doubleclic on the )ile +ampp5start9e+e
The &MPP control panel gi%es you complete control o) all the components installed 4ith &MPP9 Vou can use the panel to start # stop se%eral modules' starting the Gni+ shell' open $indo4s E+plorer to %ie4 all running bacground tass9 "ereRs a 8uic o%er%ie4 o) the control panel9
pacheRs ser%ices are running on port = and MySQL
To mae it more in line 4ith the habits o) +ampp5start9e+e icon can be inserted in the $indo4s Start menu in the $indo4s tasbar' ,clic on the icon 4ith the right mouse button and choose item )rom the menu 4hich opens/ To stop e%erything
php test 2nce e%erything loos good the last test is to try php9
!uring the installation o) &MPP it should be created phpin)o9php the )iles 4ithin the )older C: U +ampp U htdocs U dashboard U or C: U +ampp U htdocs U The local ser%er can be con)igured in many 4ays' but it is beyond the scope o) this guide9 Launch a bro4ser and in the address bar' type the address o) phpin)o9php )iles' )or e+ample: http: ##localhost#dashboard#phpin)o9php or http: ## localhost: = # dashboard # phpin)o9php I) the port number has been changed9 The phpin)o9php )iles should go running sho4ing something lie this:
The page is %ery long and sho4s the %ersion php installed 4ith all its attributes9 In particular' through the document 4e are: 5SEN(ENXO!2CGMEKT5N22TY !:#+ampp#htdocs 5SEN(ENXONEQGEST5SC"EMEY http 5SEN(ENXOC2KTE&T5PNE;I&Y no %alue 5SEN(ENXOC2KTE&T5!2CGMEKT5N22TY !:#+ampp#htdocs 5SEN(ENXOSEN(EN5!MIKY postmasterZlocalhost 5SEN(ENXOSCNIPT5;ILEKMEY !:#+ampp#htdocs#dashboard#phpin)o9php and that is 4here they 4ill be inserted )ile9php )ile name9
(he first php files
Start Kotepad' and in a ne4 document type: [. Php echo Ciao mondoR\ . Sa%e the )ile as pro%a9phpR in the )older 4here the )ile phpin)o9php ttention to the )ile su))i+ because Kotepad sa%es 9t+t' i) you ha%e already done to change it in php9 Into your bro4serRs address bar' type the address pre%iously 4ritten replacing phpin)o9php 4ith pro%a9php ,;or e+ample' http: ##localhost#dashboard#pro%a9php/ In the bro4ser you should see the O %iao Mondo message: Ko4 you can use the pache 4eb ser%er locally' create and test php applications using MySQL and more9
phpMyAdmin Composed o) Php scripts' allo4 you to manage the ser%er on 4hich it resides9 To do so you need to install the interpreter o) P"P language and acti%ate an pache $eb ser%er' an operation that has been e+posed in the pre%ious pages9 phpMydmin is present practically on all Internet ser%ers that o))er php # MySQL learn using locally is also %ery use)ul and then use it any4here9 The )igure sho4s the access to phpMydmin page9
It is di%ided into t4o parts one on the le)t and the other on the right:
The le)t' smaller in si*e' is present in all o) phpMydmin pages9 In this area you can read the names o) the MySQL database on the ser%er9 Ke+t to the name o) each database' the number in parentheses indicates the number o) tables contained in it9 Clicing on the name o) the table names are displayed9 The right changes depending on the actions you intend to accomplish9 t the top' a menu o) choices to access phpMydmin ser%ices9 The main choices are: Create a ne4 database ,by speci)ying its name/9 2nce you create a ne4 database entry # menu sho4s OStructure9
Clicing on this page is displayed that describes the table structure 4ith )ields that comprise it and its )eatures9 "ere you can change the table structure9 E+port to e+port tables Import to import tables 2perations to create or rename and copy tables9
Android apps and databases Sometimes' in computer science' you may ha%e the impression starting )rom the end parts )or a pro
Create database 2pen a bro4ser and in the address bar type: http: ## localhost # phpMydmin #
I) the listening port has been changed' the address type 4ould be: http: ## localhost: = # phpMydmin # and any other number o) selected target9
This 4ill open the phpMydmin so)t4are
Clic the !atabase button in the top menu9 It 4ill open the 4indo4 that contains the control 4here to insert the database name9 2n the page that is displayed on the right side o) the monitor to speci)y the name under the label OCreate a ne4 database 4rite Oappin%entor79
The Ocharacter encoding dropdo4n bo+ can be le)t blan' 4ill be the de)ault options set9
Clic OCrea button9
The Oappin%entor7 database 4ill be created and its name added to the database in the le)t column o) phpMydmin9
t this point the created database is empty9 This 4ill open a 4indo4 )or creating a table9
Create tables 2nce the database is created' it is empty' so you must create the tables that compose it9 To create a table' must speci)y the name and number o) )ields that comprise 4ith their characteristics9 To do that you can clic on the button O2ptions in the top menu and the page that opens under the label OCreate tables type the name o) the table and
In some %ersions o) phpMydmin is a lin OKe4 as the )irst item in the list o) database tables9 In the control name' type the name o) the table' ie Oai7tabella9 The number o) proposed )ields should be @ i) not' 4rite @ in the control Onumber o) )ields9
Clic OEsegui button9 The table name and 8uantity o) the )ields that mae up has been de)ined9 ppear a mas )or the placing o) the names o) the )ields and the characteristics o) each o) them
Define the field properties mong the con)igurable )eatures' are important inde+ing a )ield' the uni8ueness o) its %alues9 These )eatures are essential to the )unctioning o) the pp 2S ndroiid that 4ill be inter)aced 4ith this table9 Important' but not essential' it is the inclusion o) descripti%e notes in the camps to )acilitate understanding o) the structure and )uture changes9
This is the de)inition o) the )ields o) the table Oai7tabella:
ppin%entor7: database ai7tabella: database table 4ith )our )ields id: number' primary ey inde+ ,primary ey/9 It allo4s you to uni8uely identi)y each record in the table9 Increased inclusion o) each ne4 record ,GT25IKCNEMEKT/9 In case o) cancellation o) a record the %alue 4ill no longer be used9 By means o) identi)ying uni8uely you can be accessed 8uicly and uni8uely to each record nome: %archar ,7F/' 7Fcharacter string9 (NC"N' %ariablelength string' the real memory occupied in the database is e8ual to the length o) te+t W = note: %archar ,?FF/' the string o) ?FF characters9 data: TIMESTMP' automatically determines the instant at 4hich a gi%en record 4as entered or modi)ied9
0raphically presents a scheme lie the )ollo4ing )igure
;or some operations must point on bo+es 4ith the Tab ey or use the hori*ontal scroll bar at the bottom o) the page that allo4s access to the bo+es that are not displayed9 The actual creation o) a table is made by clicing the Sa%e button9 Immediately a)ter creation' phpMydmin 4ill display a message that your creation and displays the structure9 ;or each table created abo%e' you can ha%e the structure editing page access by maing it acti%e' then clicing the !esign button9 The )ields' including their name' can be changed 4ith the actions o) any buttons Edit in the ction column9
Chec+ tables To see i) the table structure has been de)ined properly' you must insert a )e4 test data9
Clic the Insert button' a 4indo4 4ill open 4ith t4o empty records' pending the entry o) data9 In both records lea%e the id )ield blan' it 4ill increase to entering records9 In the Kame )ield' the (alue column' 4rite: Tuscany and Gmbria in the )irst record in the second In the )ield notes' the (alue column' 4rite: Population ?7@?? in the )irst record and the second record '@ In both records lea%e the date )ield blan' automatically assume the instant 4hen the record 4as entered ,or modi)ied in the )uture/ in the )ormat: year month day hours minutes ] seconds
The actual placing o) data is to clic on the OEsegui button9 Soon a)ter' i) there are no errors' the result is sho4n9
$e can read abo%e:
Sho4 ro4s F= ,7 total' Query too F9FFF@ seconds/ and
4hich is the SQL used to 8uery that sho4ed all records9
s 4e can see:
The id )ield contains the %alues = and 7 respecti%ely )or the )irst and second record that the system added by per)orming the instructions gi%en in the table de)inition9 ;ield data o) both record contains the %alue 7F=F@77 =7:7?:F 4hich means that the records 4ere created in the year 7F=' in the month F@Hpril' the day 77' =7 hours' 7? minutes and F seconds9 These method o) storing the time is %ery con%enient because in the case o) sorting the records on this )ield you do not need any other operation9
Correction of the content of the fields The content o) the record can be changed' it can be done here or later' by clicing the OSQL button' by e+ecuting a 8uery on the acti%e table9 2nce the records are on screen use the action buttons placed ne+t to each record ,edit' copy' delete/9
The same operations can be per)ormed on multiple records by selecting the chec mar in the bottom line9
Chec+ the date field )inal chec can be used to %eri)y that the )ield 4ill be updated as planned' then not only the inclusion o) each ne4 record' but also time the record is modi)ied9 In %ie4 o) all the records as in the pre%ious image' clic Edit the record 4ith id H 7' that is Gmbria9 In the 4indo4 that appears' change the contents o) the memo )ield by 4riting: OPopulation @ in place o) the e+isting te+t' then clic on the ONun button9 !o no4 clic the SQL button in the top bar' all records 4ill be sho4n9
Chec the date o) the record )ield 4ith id H 7' it should contain the date and time 4hen the change 4as made9 2ther4ise' %ie4 the table in !esign mode' and 4rite correctly the data )ield attributes9 In the e+ample belo4 the record 4ith id H 7 has been changed by the system 7F=' F@ month ,pril/' day 77 to =7 hours' 7A minutes and F7 seconds9 ,Ser%er time/
Querying a table Each table can be 8ueried by 4riting a SQL 8uery in the 4indo4 that appears 4hen you clic on OSQL or OSearch in the command bar9
SQL method
By de)ault is sho4ed a selection 8uery that all )ields o) the current table9 Vou must clic on the OEsegui button9
The list o) records generated by the 8uery is displayed in another 4indo4 4ith the command that generated in a message lie: Sho4ing ro4s F =A ,= o) the total' Query too F9FFF@ seconds9/ 2n the page you can edit the 8uery e+ecuted by clicing the Edit button9 "o4e%er' there is no synta+ checing and is there)ore only suitable and e+perienced SQL9 Search method
It is sho4n a page 4ith the list o) )ields as in the ne+t image9
In the 2perator column' you can insert the search terms: MaE etc9 Kote that phpMydmin o))ers only the appro%ed conditions )or each )ield type' i) more than one' clic the dropdo4n menu o) each )ield9
In the (alue column must insert the researched %alue )or each ro49 The controls le)t blan 4ill not be considered9 $rite O(oscana in the )ield name o) control column (alue By clicing the O Esegui button' the 8uery is sent to e+ecution9 $ith the inserted %alues phpMydmin creates' automatically in bacground' the 8uery needed and e+ecutes it9 4indo4 4ill open 4ith a message lie: Sho4 ro4s FF ,= total' Query too F9FFF@ seconds9/ and sho4n in the 8uery in phpMydmin 4rote' )or e+ample: SELECT ^ ;N2M _ai7tabella_ $"ENE_ name_ LI>E ToscanaR 2n the same page clicing a button: OCreate php code generates a code lie: `S8l H OSELECT ^ ;N2M_ ai7tabella_ $"ENE _name_ LI>E TuscanyR \ you can use in your php pages remarable utility to gain )amiliarity 4ith SQL9 Belo4 is a list o) records returned by a 8uery e+ecuted9
(ables$ copy$ empty and delete In phpMydmin' by clicing on the button OTransactions youRll open the page 4here you can:
!uplicate tables' copying them in the same or another database 4ith the data or not9 !elete all table record 4ith the OClear button !elete tables 4ith TNGKCTE command
(ables$ import and e,port To import tables )rom an e+ternal )ile to phpMydmin must clic on the button OImport or OE+port in a top menu9 ;or description see sections:
!o4nload )rom the local database tables Load tables on the remote database
Connection to database To let an application de%eloped in P"P # MySQL could use the data contained in a database that must ha%e access9 It seems a phrase taen )or granted' but it is a tas to be accomplished9 This is possible using a commonly called Opairing process9 To open the connection )rom P"P to MySQL database' it should be using the mys8l5connect ,/ )unction9 I) success)ul' mys8l5connect ,/ returns a lin identi)ier' other4ise it returns ;LSE9 The connection re8uires parameter
hostname: Kame o) the host machine9 It uni8uely identi)ies a location on the $eb9 It can be e+pressed as an IP address or string9 In the case o) local installation hostname it is usually called Olocalhost9 username: Kame o) the user authori*ed to access and data manipulation9 I) the username local installation is generally called Oroot pass4ord: The pass4ord that allo4s access to the database database: The database name 0enerally these parameters then used to mae the connection are inserted into %ariables9 type o) lin could loo lie:
` "ostname H Olocalhost\ ` Gsername H Oroot\ ` Pass4ord H \ ` !atabase H Oappin%entor7\
` Lin H mys8li5connect ,` hostname' ` username' ` pass4ord' ` database/\ i) ,mys8li5connect5errno ,// echo OConnection )ailed9 die ,mys8li5connect5error ,//\ -r in older ersions but still used%
` ! H mys8l5pconnect ,` hostname' ` username' ` pass4ord/ or trigger5error ,mys8l5error ,/' E5GSEN5ENN2N/\
The di))erent type o) connection re8uires to use di))erent types o) 8uery synta+9
Kot to be con)used 4ith SQL synta+ remains the same9
I) it is used as dated code' the one contained in the sample pages do4nloaded it in their name ha%e I7 ,hyphen/' you should ha%e an ans4er lie:
'eprecated( )ysq_pconnect *+( ,he )ysq extension is deprecated and i be re)oed in the future( use )ysqi or P'/ instead in D: \ inetpub \ webs \ servername \ ai2connessionedb.php on ine xy Both ersions can be do!nloaded from the author.s !ebsite&
Connection scripts In My Computer' go to the )older: C:U+amppUhtdocsUdashboard ,the one containing phpin)o9php/ and create a )older named OI79 The OI7 )older 4ill contain all the necessary php pages to pp5$eb5P"P5MySQL operation9 The same order o) the )olders 4ill then be played on ser%ers in the Internet9
2pen Kotepad and 4rite the te+t o) the underlying script bet4een [. Php and. I included9 Sa%e the page in the )older C: U +ampp U htdocs U dashboard U I7 as ai7connessionedb9php The )older may be di))erent )rom the dashboard' it is one 4here the php test 4as appro%ed ,see section php test/9 Pay attention to the )ileRs su))i+ because the $indo4s Kotepad sa%es 9t+t : possibly a)ter sa%ing' change the su))i+ )rom 9t+t to php or the script does not 4or9
This page 4ill 4or 4ith both local and remote databases9 [.php ##echo SEN(EN5KME H 9`5SEN(ENXSEN(EN5KMERY9[br\ i) ,`5SEN(ENXSEN(EN5KMERY HH localhostR/ ##echo They are locally O\ `hostname H Olocalhost\ `username H Oroot\
`pass4ord H \ `database H Oappin%entor7\ `KomeConnessione H mys8l5pconnect,`hostname' `username' `pass4ord/ or trigger5error,mys8l5error,/'E5GSEN5ENN2N/\ else ##echo sono in remoto remotely O\ `hostname H O==97779???9@@@\ `username H OS8l\ `pass4ord H Oabcde)ghi<\ `database H Oappin%entor7\ `KomeConnessione H mys8l5pconnect,`hostname' `username' `pass4ord/ or trigger5error,mys8l5error,/'E5GSEN5ENN2N/\ . [.php ## stringa di connessione mys8l5select5db,`database' `KomeConnessione/\ . [.php ## e+ecuting the 8uery to display the contents o) the table #^ `8uery H OSelect ai7tabella9id' ai7tabella9nome' ai7tabella9note' ai7tabella9data ;rom ai7tabella O\ `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ 4hile ,`ro4 H mys8l5)etch5array,`result// echo O[br9`ro4XidRY' ' `ro4XnomeRY' ' `ro4XnoteRY' ' `ro4XdataRY\ ^# .
2nce complete' open the bro4ser and in the address bar 4rite: http:##localhost:=#dashboard#I7#ai7connessionedb9php $ithout O:= i) you ha%e not changed the port and replacing it i) the port number is di))erent9 Same thing )or the dashboard )older' i) it is a di))erent )older enter the proper name9 Vou should see a page completely 4hite' testing has been success)ul9 I) you 4ant to see 4hat can be read' it is possible to mae a )urther test' the do4nloaded )ile also contains the code: [.php ## esecu*ione della 8uery per %isuali**are il contenuto della tabella ##e+ecuting the 8uery to display the contents o) the table #^ `8uery H OSelect ai7tabella9id' ai7tabella9nome' ai7tabella9note' ai7tabella9data ;rom ai7tabella O\ `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ 4hile ,`ro4 H mys8l5)etch5array,`result// echo O[br9`ro4XidRY' ' `ro4XnomeRY' ' `ro4XnoteRY' ' `ro4XdataRY\ ^# .
Nemo%e # ^ and ^ #' sa%e and re%i%e' should appear on the monitor the contents o) the table Oai7tabella similar to: = Toscana bitanti ?7@?? 7F=F@77 =7:7?:F 7 Gmbria bitanti @ 7F=F@77 =7:7A:F7 99 99 The database name `database can not al4ays be chosen' sometimes is imposed by the Internet pro%ider9 2b%iously the connection data )or the remote database are )ictional
Insert$ modify and delete !ith php The phpMydmin so)t4are is a graphical inter)ace that allo4s you to directly manage databases and tables contained in them9 $ith phpMydmin you can %ie4 the contents o) the database' create' modi)y and delete tables and # or indi%idual records' mae bacups' %ie4 in)ormation about the database components etc9 phpMydmin is a %ery use)ul tool )or anyone' e%en esparto php # MySQL' 4ant to create or speed tests on the database that is implementing9
But once 4ith phpMydmin e%erything is tested and ready )or use' including the connection script' )or database management operations must be something usable also be %ie4ed by nonprogrammers9
script that turns on the Internet and pass4ord authori*ed persons can enter' modi)y' delete records 4ithout no4ing the commands underlying these operations9
;or these operations' the scripts presented in this guide is ai7gestionedati9php
Because this script is intended to run on the Internet' in order to ha%e access to and manage it' it 4ill implement other t4o pages ,do4nloadable )rom the authorRs site/: ai*inde,&php: This page 4ill be access through Gsername and Pass4ord entry 4ith subse8uent con)irmation by clicing on a button9 I) username and pass4ord are correct you 4ill set the ` 5SESSI2K X session %ariable accessRY' and the user 4ill be redirected to ai*gestionedati&php page9 The ` 5SESSI2K X accessoRY session %ariable is able to eep trac o) choices and data transmitted bet4een client and ser%er9 This mechanism eeps trac o) pre%ious selections made by the user ,user name and pass4ord' type it e+actly/9 ai/*accessosessione&php: This page is tested' ,a)ter each operation in ai*gestionedati&php/ the session %ariable ` 5SESSI2K X accessRY I) ` 5SESSI2K X accessRY does not contain the %alues )it the script ai*gestionedati&php cease re)erring to ai7inde+9php page9
n internal mechanism to this page tests i) the script is locally or on the Internet and acts only in the second case9
(he command includes php re0uire1once $ith P"P' lie any other programming language' you may ha%e to run se%eral times the same code that per)orms a particular
;rom ai2-gestionedati.php page: ai/*connessionedb&php' database connection ai/*accessosessione&php' %ariable head ` 5SESSI2K X accessRY ;rom ai*interfaccia&php page: ai*connessionedb&php database connection
ai*gestionedati&php The page code is simpli)ied as much as possible by eliminating 4hat is beyond the mere )unctioning9 The page must be in the OI7 )older and can be edited 4ith $indo4s Kotepad9 This page includes ? )orms that re)er to the same page and 7 )unctions9
)ormKG2(2: Inserting ne4 record9 Communicate the name o) the region to be inserted9 KellRinserimenti' the system adds an id inde+ )or the ne4 record9 It is acti%ated 4hen you clic on button id H Obutton? )ormM2!I;IC: Koti)y id inde+ o) the record to 4hich mae the changes' changes to be made to the )ields9 I) the record 4as
[.php include,ai7accessosessione9phpR/\ . [.php re8uire5once,ai7connessionedb9phpR/\ .
[.php ## Kuo%oNecord Necord ne4 #^ The command: `s8l H OIKSENT IKT2 ai7tabella ,nome/ (LGES ,` nomeKE$R/\ inserts the ne4 record 4ith the %alue o) the %ariable ` nomeKE$ in the Kame )ield9 s established in the table de)inition' the ne4 record is assigned a ne4 id inde+ utomatically' that is uni8ue and progressi%e %alue9 ^# `nomeKE$ H addslashes,isset,`5P2STXnomeKE$RY/ . `5P2STXnomeKE$RY : F/\ i),strlen,`nomeKE$/?/
`8uery H OIKSENT IKT2 ai7tabella ,nome/ (LGES ,`nomeKE$R/\ `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ .
[.php ## modi)ica modi)ication #^ Changing an e+isting record9 This happens 4hen you clic on id H Obutton button )ormM2!I;IC )orm' passing the id inde+ o) records contained in the %ariable ` idmodi)ica9 i) ,`idmodi)ica F/' 4hich controls ` idmodi)ica has a %alue greater than *ero9 I) so in the %ariables `nome and `note %alues are entered )or the 8uey modi)ication o) the record9 The command: `s8l HOGP!TE ai7tabella SET nome H `nomeR' note H `noteR $"ENE id H `idmodi)ica\ inserts the ne4 %alue in the )ields o) ai7tabella table id o) records 4hose %alue is contained in the %ariable `idmodi)ica ^# `idmodi)ica H isset,`5P2STXidmodi)icaRY/ . `5P2STXidmodi)icaRY : F\ i),`idmodi)ica F/ `nome H addslashes,isset,`5P2STXnomeRY/ . `5P2STXnomeRY : Ono nome/\ `note H addslashes,isset,`5P2STXnoteRY/ . `5P2STXnoteRY : Ono note/\ `8uery H OGP!TE ai7tabella SET nome H `nomeR' note H `noteR $"ENE id H `idmodi)ica O\ `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ .
[.php ## cancella delete #^ !elete a record9 This happens 4hen you clic on id H Obutton7 button )ormELIMIK )orm' passing the inde+ id o) the record to be deleted9
i) ,` idcancella F/' chec that the `idcancella has a %alue greater than *ero9 The command: `s8l H !ELETE ;N2M ai7tabella $"ENE id H `idcancella O deletes the record 4hose %alue is contained in the %ariable `idcancella ^# `idcancella H isset,`5P2STXidcancellaRY/ . `5P2STXidcancellaRY : F\ i),`idcancella F/ `8uery H O!ELETE ;N2M ai7tabella $"ENE id H `idcancella O\ `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ .
[.php ## legge tabella read table #^ the 8uery is created to display the record' then sent running9 The result is in the %ariable `result ^# `8uery H OSelect ai7tabella9id' ai7tabella9nome' ai7tabella9note' ai7tabella9data ;rom ai7tabella\ `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ .
[doctype html [html [head [ codi)ica dei caratteri con %ocali accentate ecc9 e titolo pagina page encoding characters [meta charsetHOut) [title0estione dati[#title [ Commands CSS 4ho eat the page and center the te+t
[style typeHOte+t#css body marginle)t: Fp+\margintop: Fp+\marginright: Fp+\marginbottom: Fp+\ te+t align:center\ [#style [#head
[body [ Ini*io tabella che conterrf i dati' essa larga il =FF del suo contenitore ed io bordo di = pi+el9 Start table that contains the data' it is 4ide the =FF o) its container and the edge o) = pi+el [table 4idthHO=FF borderHO=
[ The )irst ro4 o) the tag bet4een [tr table and [# tr identi)iable beginning and end o) a line' the tags bet4een [td and [# td identi)y the beginning and end o) a cell [tr[tdnbsp\[#td[td
[ )orm to the insertion o) a ne4 record' method H Opost is the method o) data transition' action H recalls the same page [)orm nameHO)ormKG2(2 methodHOpost actionH
[ Pulsante per lRin%io dei dati' idHObutton? lRidenti)icati%o' %alueHOKuo%o record il testo che puj essere letto sul pulsante Button to send data' id H Obutton? is the identi)ier' %alue H OKuo%o record is the te+t that can be read on the button [input typeHOsubmit nameHObutton? idHObutton? %alueHOKuo%o record [br
[ Te+t that 4ill be sent to be entered in the Kame )ield o) the ne4 record9 The te+t 4ill be sent content id H OnomeKE$ 4hose %alue 4ill ha%e to be entered by the user in the %alue H [input nameHOnomeKE$ typeHOte+t idHOnomeKE$
[#)orm
[#td[tdnbsp\[#td[tdnbsp\[#td[tdnbsp\[#td[#tr
[ Seconda riga della tabella con intesta*ione delle colonne#campi The second line o) the table 4ith column headers#)ields [tr[tdmodi)ica[#td[tdnome[#td[tdnote[#td[tddata[#td[tdcancella[#td [#tr
[.php #^ Beginning o) the reading o) the record 4ith the 4hile loop9 Each record is read in `ro4 and decomposed in )ields `ro4XidRY `ro4XnomeRY `ro4XnoteRY `ro4XdataRY and then inserted in the corresponding controls9 The number o) ro4s is generated by P"P in the e+act 8uantity to contain all records9 ^# 4hile ,`ro4 H mys8l5)etch5array,`result// ## Neading )ields' not %isible on the page because preceded by ## ##echo `ro4XidRY9 O9`ro4XnomeRY9 O9`ro4XnoteRY9 O9`ro4XdataRY9 O[br\ .
[ Third ro4 start lines )or records beginning )ormM2!I;IC )orm' methodHOpost is the method o) passing data' actionH recalls the same page
[tr[)orm nameHO)ormM2!I;IC methodHOpost actionH [td [ Pulsante per lRin%io dei dati del record da modi)icare [input typeHOsubmit nameHObutton idHObutton %alueHOmodi)ica
[ Identi)ier )or the modi)ication o) the record is the idHOidmodi)ica9 To load the page %alueHOF' in the generation o) the ro4 o) the page %alue assumes the id %alue o) the record represented9 [input nameHOidmodi)ica typeHOte+t idHOidmodi)ica %alueH[.php echo `ro4XidRY . [#td
[ The second cell to the )ield name' %alue assumes the %alue o) a )ield o) the record represented9 [td[input nameHOnome typeHOte+t idHOnome %alueH[.php echo stripslashes,`ro4XnomeRY/ .[#td
[ The third cell )or the Kotes )ield' %alue assumes the %alue o) a )ield o) the record represented9 [td[te+tarea nameHOnote idHOnote[.php echo stripslashes,`ro4XnoteRY/ . [#te+tarea[#td
[ ;ourth cell )or the )ield date ,year' month' day' hours' minutes' seconds/' %alue assumes the %alue o) a )ield o) the record represented9 This %alue is entered as the moment o) creation o) the record and changes at the time o) his e%ery change
[td[.php echo `ro4XdataRY .[#td [#)orm
[ The )i)th cell )or the ;ield !elete methodHOpost is the method o) passing data' actionH recalls the same page [td[)orm nameHO)ormELIMIK methodHOpost actionH
[ Button )or sending data o) the record to delete [input typeHOsubmit nameHObutton7 idHObutton7 %alueHOcancella
[ Identi)ier )or the modi)ication o) the record is idH idcancella O9 To load the page %alueHOF' in the generation o) the ro4 o) the page %alue assumes the %alue o) the id o) the record represented9 [input nameHOidcancella typeHOte+t idHOidcancella %alueH[.php echo `ro4XidRY . [#)orm[#td [#tr [.php #^ The end o) the cycle o) the 4hile loop9 Each loop has created a ne4 line o) the table containing a record ^# . [#table [#body [#html
Lie all the other php pages must be inside the )older I7 2pen a bro4ser and type in the address bar'
http:##localhost: =#dashboard#I7#ai7gestionedati9php
or at least the %alid address )or the ragging ai7gestionedati9php page Nesult o) running code:
ai*inde,&php This page is accessed by entering the Gsername and Pass4ord 4ith subse8uent con)irmation by clicing on a button9 $ith e+act username and Pass4ord get sets the %aiable `5SESSI2K X session %ariable accessRY' and the user 4ill be redirected to ai7gestionedati9php page described abo%e9
$ith the ` 5SESSI2K X accessRY session %ariable is ept trac o) by the user choices made in this case username and pass4ord9 The page contains one )orm to capture usernames and pass4ords' 4hich re)ers to the page itsel)9
Gsername and pass4ord are checed and i) they match those set is launched session5start ,/\ and crated the session %ariable ` 5SESSI2K X accessRY H oR\
Immediately a)ter the user is redirected to the page 4ith ai7gestionedati9php
echo [MET "TTPEQGI( H ONE;NES" C2KTEKT H OF\ GNL H i7 gestionedati9phpR\
Page ai7gestionedati9php described pre%iously9 I) Gser Kame or Pass4ord are not those established' the page is reloaded listening )or a ne4 insertion o) username and pass4ord9 Code commented ai*inde,&php
[.php #^ Nice%e username e Pass4ord9 Katuralmente al primo a%%io le %ariabili sono %uote e 8uindi la pagina %iene caricata completamente e messa in attesa dellRimmissione di Gsername e Pass4ord username and pass4ord9 $hen you )irst start the %ariables are empty and then the page loads completely and put on hold by placing Gsername and Pass4ord ^# `Gsername H isset,`5P2STXGsernameRY/ . `5P2STXGsernameRY : \ `Pass4ord H isset,`5P2STXPass4ordRY/ . `5P2STXPass4ordRY : \ #^
Gsername and Pass4ord head' i) you ha%e to be e+act Gsername H OMiaGsername and Pass4ord H OMyPass4ord Changing OMiaGsername and OMyPass4ord can be customi*ed and replaced 4ith the ones you 4ant9 ^# i) ,`Gsername HH OMiaGsername and `Pass4ord H OMiaPass4ord/ #^ I) the test is success)ul is launched session5start ,/' created the session %ariable ` 5SESSI2K X accessRY H oR\ assigning a %alue to be tested during the boots o) ai7gestionedati9php page the user is redirected to the realai7 gestionedati9php page ^# session5start ,/\ `5SESSI2KXaccessoRY H Oo\ echo [MET "TTPEQGI(HONE;NES" C2KTEKTHOF\ GNLHai7 gestionedati9phpR\ . [doctype html [html [head [meta charsetHOut) [titleI7 e P"P#MySQL[#title [style typeHOte+t#css #^ CSS commands that )ormat the page and te+t^# body marginle)t: Fp+\margintop: Fp+\marginright: Fp+\marginbottom: Fp+\ )ont:rial' "el%etica' sansseri)\ )ontsi*e:=7p+\ table )ontsi*e: F\align:le)t\ %align:top\ [#style [#head [body
[.php #^ Neads the current date on the ser%er' this is not necessary to the operation o) the page' but only demonstration o) data # management *ones 4hich 4ill then be used in the records9 ^# `giorno H date,O</\ `mese H date,On/\ `anno H date,OV/\ `ora H date,O":i/\ `settimana H date,O4/\ `giornosettimana H array ,O!omenica' OLunedi' OMartedi' OMercoledi'O0io%edi' O(enerdi' OSabato/\ `nomemese H array ,= H Ogennaio' O)ebbraio' Omar*o' Oaprile'Omaggio' Ogiugno' Oluglio' Oagosto' Osettembre' Oottobre' Ono%embre' Odicembre/\ echo ,O`giornosettimanaX`settimanaY 9 9 O`giorno 9 9 O`nomemeseX`meseY 9 9 O`anno/9 ore `ora O\ . [brInserire username e pass4ord [ ;orm )or capturing username and pass4ord' method H Opost is the method o) data transition' action H calls the same page9 [)orm nameHO)orm= methodHOpost actionH [ The Gsername te+t that 4ill be sent to be tested 4ill be contained in id H OGsername 4hose %alue 4ill ha%e to be entered by the user in the %alue H [input typeHOpass4ord nameHOGsername idHOGsername[br [ The Pass4ord te+t that 4ill be sent to be tested 4ill be contained in id H OPass4ord the %alue o) 4hich shall be entered by the user in the %alue H [input typeHOpass4ord nameHOPass4ord idHOPass4ord[br [ Button to send data' id H Obutton is the identi)ier' %alue H OLogin is the te+t that can be read on the button [input typeHOsubmit nameHObutton idHObutton %alueHOccedi [#)orm [#body [#html Lie all the other php pages must be inside the )older I7 2pen a bro4ser and type in the address bar'
http%//localhost%23/dashboard/AI/ai*inde,&php
or at least the %alid address 4here ai7inde+9php page Nesult o) running code:
ai*accessosessione&php This page' 4hich must be 4ithin the OI7 )older' chec that the session %ariable ,the one assigned a)ter the Gsername and pass4ord entry has %alid content/9 Code commented ai*access sessione&php
[.php session5start,/\ ## Starts the session #^ Type ser%er' local or internet echo SEN(EN5KME H 9`5SEN(ENXSEN(EN5KMERY9[br\ Tests 4hether locally' i) positi%e result does not head the session %ariable ^# i) ,`5SEN(ENXSEN(EN5KMERY HH localhostR/ else ## other4ise #^ "ead the ` 5SESSI2K X accessRY session %ariable' i) it contains Oo re)er to i7inde+9php page to as insertion o) username and pass4ord9 ^# i),`5SESSI2KXaccessoRY [ Oo/ echo [MET "TTPEQGI(HONE;NES" C2KTEKTHOF\ GNLHai7inde+9phpR\ .
Android App interface !ith MySQL database Ko4 4e ha%e' locally' a 4oring ser%er 4ith MySQL database' php pages to protect' enter' edit and delete records' you must select the page that the table data and at the same time is the inter)ace 4ith the pp5$eb5P"P5MySQL 2perating System ndroid assembled 4ith pp In%entor This inter)ace 4ill be named O ai*interfaccia&phpO9 The data selection so it has already been seen in ai*gestionedati&php )ile 4ith the code: [.php ## 8uery e+ecution to display table `8uery H OSelect ai7tabella9id' ai7tabella9nome' ai7tabella9note' ai7tabella9data ;rom ai7tabella\ `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ echo `ro4XidRY9 O9`ro4XnomeRY9 O9`ro4XnoteRY9 O9`ro4XdataRY9 O[br\ .
They are read and sho4ed all records o) the table and all )ields in each record' 4ithout any ind o) order they content )iltering9 To do it pp5$eb5P"P5MySQL on ndroid de%ice' by imposing on sort )ields and selection' you must choose a method )or passing the commands bet4een pp5$eb5P"P5MySQL and Oai7inter)accia9php9 Pic the one you need to de)ine some rules that operate 4ith the method chosen by ensuring that pp5$eb5P"P5MySQL and Oai7inter)accia9php can communicate 4ith each other9 ;or the passage o) commands )rom the pp to the php page on the Internet ser%er 4as chosen the 8uerystring method9
0uery*string 8uerystring is the part o) a GNL ,Internet address/ containing the data to be passed as input to a program' in this case to a 4eb page in php )ormat9
In the e+ample are used php pages' but it is not the only possible' )or e+ample
By pp' The GNL 8uerystring must contain the complete address )or attaining the 4eb page in Internet9
t the end o) the GNL' the sign o) O. opens the 8uerystring9 The commands passed to the 8uerystring does not include the sign O.' But e%erything that )ollo4s it9
Being implemented in all bro4sers and scripting languages' the synta+ o) the 8uerystring is a de )acto standard' it )ollo4s the )ollo4ing pattern: Question mar . Parameter= H = %alue parameter7 H %alue7 parameter? %alue H ? Each parameter is assigned a %alue by using the OH sign9 The parameters are interspersed 4ith each other by the symbol O The "TTP protocol does not pro%ide the GNL length limits' although some bro4sers restrict this length to about 7FFF characters' ho4e%er' more than enough )or inter)ace purposes9 The number o) parameters is there)ore only limited by the length o) the 8uerystring accepted the bro4ser that 4ill recei%e9 I) the 8uerystring e+ceeds the allo4ed length o) the recei%ing bro4ser' there are no mistaes usually' only that the e+cess %alues are ignored9 Some alphabetic characters ha%e uni8ue meaning 4hen used 4ithin the 8uerystring9 There is no real limitation on their use' but in the planning o) 8uerystring are best a%oided because they create ambiguities and in any case should be coded be)ore they are used9 4esered characters in 0uery*string% Inside GNLs' some characters ha%e speci)ic )unctions and to a%oid con)licts it is best not to use them or' alternati%ely' must be encoded be)ore transmission9 The reser%ed characters are: ` W ' # : \ H . Z ,The hyphen here used as a room di%ider is allo4ed/ 2ther characters may create ambiguity and is not ad%isable to use' they are: ,Space/ O,8uotes/ [ k O' ho4e%er it is better to use them to encode them be)ore transmission9 5rl)ncode )ncoding: In the encoding' each character to be encoded' it is replaced by a triplet composed by O )ollo4ed by t4o characters representing the he+adecimal %alue9 ;or e+ample: 7F represents a space' 7@ represents the ` symbol and so on9 The space can also be represented by the symbol OW' a shorthand notation used to mae the 8uerystring interpreted e%en by those systems that do not support spaces9
To mae the code more understandable in pp5$eb5P"P5MySQL' )or communication bet4een this and Oai7inter)ace has been made sure not to use reser%ed characters9
ai*interfaccia&php The inter)ace code is di%ided into three sections9 Common section%
/ It connects to the database B/ !e)ines the label )or the )ield names and sorting C/ recei%es commands and %alues 4ith the 8uerystring9 The 8uerystring recei%es the command to 4hich o) sections = and 7 to e+ecute9 section 3
Select and )ilter data section
Neturns the contents o) the re8uested records
(as+s and rules interface The table Oai7tabella has @ )ields' the pp can be controlled by:
2rder in ascending or descending order any o) the )ields o) the table9 ;ilter' that is' select' records in the table o) a )ield9 To do this the pp 4ill send the identi)ier o) a )ield and the search string9 The te+t 4ill be matched any4here in the identi)ied )ield9 4eRll sho4 you all the records that ha%e that te+t )ield in the )iltrate9 Neturn to pp5$eb5P"P5MySQL the content o) the re8uested records9
Interface ariables (ariables that 4ill recei%e the %alues as 8uerystring )rom pp5$eb5P"P5MySQL:
`I2: number 4ith %alue = or 7' corresponding to the section to be used9 I) `I2 H =: must be running section =' select and )ilter data I) `I2 H 7: goes running section 7' returns content records re8uested `K: )ield number ,F to ?/ on 4hich to sort the records that 4ill be )iltered `T: sort order a* or *a `C: column # )ield ,F to ?/ on 4hich to apply the )ilter `;: )ilter to apply on the )ield ` C `id: the inde+ 4hose records contained is to be returned to pp5$eb5P"P5MySQL Code commented ai/*interfaccia&php
## collegamento al database ## !atabase Connection [.php re8uire5once,ai7connessionedb9phpR/\ .
[.php ## nomi dei campi e tipo ordinamento ## ;ield names and sort order `Campo H array,Oid'Onome'Onote'Odata/\ `Tipo2rdinamento H array,' O!esc/\ `CampoNicerca H array,Oid'Onome'Onote'Odata/\ .
[.php ## Input 2utput =Hsele*ione#)iltraggio 7Hrestituisce il record id ## Input 2utput = H selection # )iltering 7 H returns the record id `I2 H isset,`50ETXI2RY/ . `50ETXI2RY : F\
## per array `CampoXY : numero del campo sul 8uale ordinare ,da F a ?/ ## rray `CampoXY: number o) the )ield on 4hich to order ,F to ?/ `K H isset,`50ETXKRY/ . `50ETXKRY : =\
## per array `Tipo2rdinamentoXY : tipo ordinamento a* oppure *a ,!esc/ ## rray type ` 2rder XY: Sorting type a* or *a ,!esc/ `T H isset,`50ETXTRY/ . `50ETXTRY : F\
## per array `CampoNicercaXY : colonna#campo sul 8uale applicare il )iltro ,da F a ?/ ## rray ` Campo search XY: column # )ield on 4hich to )ilter ,F to ?/ `C H isset,`50ETXCRY/ . `50ETXCRY : 7\
## )iltro da applicare sul campo `CampoNicercaXY ## ;ilter to apply on the )ield ` ;ield Nesearch XY `; H isset,`50ETX;RY/ . `50ETX;RY : O\
## id del record richiesto ,se `I2 HH 7/ ## Id o) the re8uested record ,i) ` I2 HH 7/ `id H isset,`50ETXidRY/ . `50ETXidRY : F\
#^ Nestituisce una stringa con bacslash prima dei caratteri che compromettono lResecu*ione di una 8uery la )un*ione rende la 8uery utili**abile anche con i caratteri apostro)o , /' doppie %irgolette , /' barra ro%esciata , U / e KGL9 )un*ione stripslashes,/ compie il la%oro in%erso di addslashes,/ %iene usata sui dati letti dalla tabella prima di usarli
Neturns a string 4ith a bacslashes )irst character that a))ect the e+ecution o) a 8uery the )unction maes the 8uery can also be used 4ith the characters apostrophe , /' double 8uote , O/' bacslash ,U/ and KGL9 stripslashes ,/ )unction per)orms the in%erse 4or addslashes ,/ It is used on the data read )rom the table be)ore you use them ^# `; H addslashes,`;/\ .
[.php ## se*ione = ## Section = #^ 2rdina in senso ascendente o discendente uno 8ualsiasi dei campi in della tabella9 ;iltra i record della tabella di un 8ualsiasi campo9 Il testo cercato in un punto 8ualsiasi del campo identi)icato (erranno mostrati tutti i record che nel campo )iltrato hanno 8uel testo9
Sort in ascending or descending order any o) the )ields in the table9 ;ilter records in the table o) a )ield9 The te+t is searched at any point o) the identi)ied )ield $e 4ill sho4 all records that ha%e that te+t )ield in the )iltrate9
^# i),`I2 HH =/ ## esecu*ione della 8uery per %isuali**are tabella ## E+ecute the 8uery to display the table `8uery H OSelect ai7tabella9id' ai7tabella9nome' ai7tabella9note' ai7tabella9data ;rom ai7tabella $here ai7tabella99`CampoNicercaX`CY9 Lie 9`;9R 2rder By ai7tabella99`CampoX`KY9 O9`Tipo2rdinamentoX`TY\
## esecu*ione della 8uery risultato in `result ## E+ecution o) the 8uery result in `result `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ .
[doctype html [html [head [meta charsetHOut) [titleSele*ione dati[#title [style typeHOte+t#css #^ Comandi CSS che )ormattano pagina e testo CSS commands that )ormat the page and te+t ^# body marginle)t: Fp+\margintop: Fp+\marginright: Fp+\marginbottom: Fp+\ )ont:rial' "el%etica' sansseri)\ )ontsi*e:=7p+\ table )ontsi*e: F\align:le)t\ %align:top\ [#style [#head
[body [table 4idthHO=FF borderHO=
[ nomi delle colonne#campi della tabella names o) the columns # )ields o) the table [tr[tdid[#td[tdnome[#td[tdnote[#td[tddata[#td[#tr
[.php ## loop sul risultato della 8uery ## Loop on the result o) the 8uery 4hile ,`ro4 H mys8l5)etch5array,`result// ##echo `ro4XidRY9 O9`ro4XnomeRY9 O9`ro4XnoteRY9 O9`ro4XdataRY9 O[br\ ## mette i record sul display ## Puts the records on the display . [tr [td alignHOle)t %alignHOtop[.php echo `ro4XidRY .[#td [td alignHOle)t %alignHOtop[.php echo stripslashes,`ro4XnomeRY/ .[#td [td alignHOle)t %alignHOtop[.php echo stripslashes,`ro4XnoteRY/ .[#td [td alignHOle)t %alignHOtop[.php echo `ro4XdataRY .[#td [#tr [.php . [#table [#body [#html [.php .
[.php ## se*ione 7 ## Section 7
## Nestituisce il contenututo dei record richiesti ## Neturns the contents o) the re8uested records i),`I2 HH 7/ ## codice della 8uery ## Query code `8uery H OSelect ai7tabella9id' ai7tabella9nome' ai7tabella9note' ai7tabella9data ;rom ai7tabella $here ai7tabella9id H `id O\
## esegue la 8uery ## Nun the 8uery `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ `QuantitaNecord H mys8l5num5ro4s,`result/\ ## 8uantitf record
i),`QuantitaNecord F/ ## se il record cR `Necord H mys8l5)etch5assoc,`result/\ #^ inserisce il contenuto del record nella %ariabile da usare per trasmetterne il contenuto inserts the contents o) the records in the %ariable to use to transmit the content ^# `testo H `NecordXidRY9Un9stripslashes,`NecordXnomeRY/9Un9stripslashes,`NecordXnoteRY/9Un9s else ## other4ise `testo H OIl record `id non esiste\ echo `testo\ ## trasmette alla pp ndroid il contenuto del record ## It transmits to the content o) the record ndroid pp
.
The esecution o) Section 7 deals 4ith the pp and transmit the re8uired records9 The re8uest by the pp Opp5$eb5P"P5MySQL happens in the $eb Component o) pp in%entor that trasmit the id o) the record that 4ants to recei%e9 I) the record does not e+ist' the page Oai7inter)accia9php responds 4ith: OThe record ` id does not e+ist
(est ai*interfaccia&php locally 2pen a bro4ser and type in the address bar'
http%//localhost%23/dashboard/AI/ai*interfaccia&php
or at least the %alid address 4here ai7inter)accia9php page should load a blan page9 This means that the code 4ors because da)ault code: `I2 H isset,`50ETXI2RY/ . `50ETXI2RY : F\ It assumes in the %ariable `I2 to *ero and then do not run any o) the t4o subse8uent sections9 Mae chectest To change `I2 H isset,`50ETXI2RY/ . `50ETXI2RY : F\ 4ith `I2 H isset,`50ETXI2RY/ . `50ETXI2RY : =\ and sa%e the page9 2pen a bro4ser and in the address bar type the address 4here ai7inter)accia9php page The e+ecution o) section = o) the result should loo lie this
To change `I2 H isset,`50ETXI2RY/ . `50ETXI2RY : F\ 4ith
`I2 H isset,`50ETXI2RY/ . `50ETXI2RY : 7\ and sa%e the page9 2pen a bro4ser and in the address bar' type the address )or the ragging ai7inter)accia9php page It should see a result lie this:
It means that e%erything is in place because the line in the page: `id H isset,`50ETXidRY/ . `50ETXidRY : F\ assumes by de)ault the transmission to *ero records pp' then loo )or and not )inding ans4ers: OIl record F non esiste OThe record does not e+ist F Change the code `id H isset,`50ETXidRY/ . `50ETXidRY : F\ 4ith `id H isset,`50ETXidRY/ . `50ETXidRY : &&\
4here && is the id %alue )or an e+isting record' sa%e the page and update9 I) e%erything is in place' it 4ill appear on screen the contents o) that record9
Storing data in the table !ith php Ko4 that the inter)ace bet4een the ndroid app and the php # MySQL database table is ready you must enter the Oreal data in the table and then load e%erything on the Internet9 The best thing is to do it locally so that' in case o) errors' it is easier to spot them and correct them9 Launch ai7 gestionedati9php)ile )or e+ample by typing in the bro4ser bar\ Type http%//localhost%23/dashboard/ai*gestionedati&php $ill be stored names o) some Italian regions and a brie) description o) each o) them9
Contents o) the records in the do4nloadable table9 id
nome
note
data
@
Toscana La Toscana una regione italiana di ?7@?? abitanti' situata nellRItalia centrale' con capoluogo ;iren*e9
7F=A==F =A:7A:?7
7 Gmbria LRGmbria una regione dellRItalia centrale di @ abitanti posta nel cuore della penisola9
7F=@F=F F:?7:F7
Piemonte Il Piemonte una regione dellRItalia nord occidentale di @9?9= abitanti con capoluogo Torino9
7F=FAF F:=F:==
(al dRosta
7F=?=7== =F:F:F
La (alle dRosta ,in )rancese: (all3e dRoste/' con capoluogo osta' ha super)icie di ? 7? m8 e =7A7 abitanti9
Sicilia La Sicilia' u))icialmente Negione Siciliana' una regione italiana autonoma a statuto speciale di @AA@? abitanti' con capoluogo Palermo9
7F=F=@ =@:F=:7?
Sardegna Negione italiana a statuto speciale insieme con 7F=F=F7 le isole e gli arcipelaghi che la circondano estesa 7@=FF m8 ha F?:@F:FA =A@ abitanti distribuiti in ? comuni9 Basilicata La Basilicata o anche comunemente Lucania 7F=@FAF una regione dellRItalia Meridionale di AA 7?F abitanti e ha come F:?:F= capoluogo Poten*a9
La*io Negione italiana a statuto ordinario dellRItalia Centrale di A=A7 abitanti9 Capoluogo e capitale dRItalia Noma9
7F=AF@F =:=A:7A
Load the Internet database and php pages Locally e%erything is 8uite simple and i) you )ollo4ed the directions on the local ser%er and pages should 4or per)ectly9
Some di))iculties may occur' )or sa)ety reasons' 4hen the database is the Internet9
Most pro%iders allo4 access to databases located on their ser%ers only on re8uests )rom IP considered reliable' and one o) the characteristics o) reliability is that the addresses sourced )rom the same pro%ider9
Eg9 i) you 4ant a remote database ser%er on ruba' the inter)ace bet4een the app and the database ser%er must be on ruba and this is true )or nearly e%ery 4ebser%er9
Each 4eb ser%er has its o4n characteristics but they all loo alie' and eg9 ha%e phpMydmin and support php@ # phpA W database' some are )ree9 In )act' there is )ree and you pay in di))erent 4ays such as9 ad%ertising on their pages9
mong these 4e mention three 4here at the moment ,spring 7F=/ in the )irst t4o the cost per)ormance ratio is e+cellent' and the third O)ree9 Kame and domain su))i+ must o) course be chosen to mae a%ailable' the price may %ary 4ith the type o) su))i+ ,it' com' net' etc9/9
ruba ,https:##4449aruba9it#/' based in re**o' is one o) the Italian maret leader )or domains' hosting' cloud etc9 domain type http: ##44 49nomescelto9e+t 4ith unlimited space you can ha%e about ?F euros per year and 4ith another =F ' )i%e databases9 2ccasionally it has %ery competiti%e o))ers 4ith e%en lo4er prices9 Kame o) the database and pass4ord assigned by ruba9 2ne9com ,https:##44 49one9com#it#/ based in !enmar' a domain name lie http: ##4449nomescelto9e+t 4ith =A 0B o) space you can ha%e on less than 7A euro to including database year9 ;rom time to time they are also done %ery competiti%e o))ers )or )ree domains by deli%ering predetermined periods o) time9 !atabase name and pass4ord assigned by one9com lter%ista ,http:##it9alter%ista9o rg#/ houses one o) the largest 4eb community o)
publishers 4ho publish their o4n content on the Internet and itRs )ree9 Vou can decide 4hether to host on their pages one or more ad%ertising campaigns' one supplied by the pro%ider9 The proceeds 4ill be di%ided bet4een the o4ner o) the site and lter(ista9 The insertion o) ad%ertising is not mandatory9
6et a domain !ith database The )ollo4ing e+ample re)ers to the ruba ser%ers' not %ery dissimilar )rom the others9
0o on the 4ebsite ruba https:##44 49aruba9it# page' here you can type a domain name by choosing the su))i+9 2nce cliced enter or the search button' it 4ill last a database and communicated the outcome9 I) the domain name is )ree they 4ill be presented in se8uence a )e4 pages 4here they are o))ered similar names and additional ser%ices that are not able' among them choose MySQL database9 t the end you are sought registration o) their data in order to assign the domain and issue an in%oice9 Payment can be done in %arious 4ays' PayPal' Credit cards' money trans)er etc9 !one the payment by email you are disclosed parameters )or access to the domain %ia )tp ,;ile Trans)er Protocol )ile trans)er protocol/' A o) database names and pass4ords9
Do!nload tables from the local database 2nce you choose your pro%ider and you ha%e dominoes and database' you need to load them on the Internet9 The php pages are already on your PC 4hile the database and the table contained in it is located on the local ser%er9 Since you can not directly trans)er tables )rom the local database to the database )or net4oring' Odo4nload the table )rom the local ser%er on your PC and then trans)er it to the database hosted by the pro%ider9 To do this' access to phpMydmin on the local ser%er by typing: http: ## localhost: =#phpMydmin # e%entually appropriate 4ith the appropriate ser%er port9 In the le)t column clic on Oappin%entor7 database 4hich get opened9 Clic on the table Oai7tabella and then' in the top menu on the OE+port button9 It 4ill be o))ered the SQL )ormat that generally 4ors 4ell9 Narely' )or compatibility problems bet4een di))erent %ersions o) phpMydmin installed on di))erent ser%ers' you must to use one o) the other )ormats9 2nly the user can determine 4hich is the most suitable to the trans)er )ormat9 In any case' normally' has al4ays been )ound that the su))i+ has permission to do so9
2nce you mae the choice o) )ormat' clic on OEsegui9 The table 4ill be Ounloaded on the PC in use in the predetermined position by the bro4ser9
Load tables on the remote database
Load tables on the remote database To load the table Oai7tabella in the database on the Internet need to access phpMydmin on the remote ser%er9
ruba the address )or access is http:##mys8l9aruba9it 4here you are ased to enter username and pass4ord pro%ided by the operator9
;or one9com access is )rom the control panel at https:##login9one9com#cp# 2nce inside' the phpMydmin )unctions the same 4ay9
To the le)t is the list o) databases' one in the case o) one9com and A i) the manager is ruba9
In the case o) ruba must choose one by doubleclicing on it9 The name o) this database is to be included in the Oai7connessionedb9php' 4hich contains the parameters )or the database connection9 ,In `database H Oappin%entor7\ replace appin%entor7 as s)ornito by the pro%ider/9 Then clic on the OImport button' in the middle o) the page 4ill appear 4ith the tools to do so9 Vou must select the import )ormat' necessarily the same as that used in the e+port' then clicing the OChoose ;ile button' you can bro4se on the PC in use by selecting the pre%iously e+ported )ile by then clic on O2pen
$ill begin immediately a)ter the import and at its end' at the top' on the same page' a message appears on the outcome o) import9 I) it is positi%e' the process is terminated\ other4ise changing the type o) )ormat to repeat the e+port )rom the local database' then try to import again on the remote ser%er
#repare the page to connect to the database The page Oai7connessionedb9php contains the parameters )or the database connection9 These are: i) ,` 5SEN(EN XSEN(EN5KMERY HH localhostR/ ` "ostname H Olocalhost\ ` Gsername H Oroot\ ` Pass4ord H \ ` !atabase H Oappin%entor7\ else ` "ostname H O 33777888O\ ` Gsername H O S0l999999O\ ` Pass4ord H O b::rd2222eO\ ` !atabase H O S0l;;;;;;19O\ !ata in bold 4ill be replaced 4ith those pro%ided by ruba or another pro%ider 4ith 4hom you subscribe9 !one this page Oai7connessionedb9php to be sa%ed9 Kote that the database name is OS0l;;;;;;19O' this means that it is the database o) ruba number A' i) it 4ere a real name' others 4ould O S0l;;;;;;13O' OS0l;;;;;;1O' OS0l;;;;;;17O' OS0l;;;;;;18
5pload #<# pages to the !eb Immediately a)ter the purchase' the 4eb space is ready'
The pro%iders also pro%ide an utiliy usually called O;ile Manager )ter entering their credentials on the pro%iderRs 4ebsite' )rom the Control Panel that has been assigned to the domain you log on to the O;ile Manager9 E%en )rom here you can do the normal page load operations' create ne4 )olders' delete )iles and so on9
=olders aailable The php pages may be loaded any4here on the ser%er' pro%ided that all in the same )older' but should change the path on pages pp9
This path 4ill be set in the pp pp5$eb5P"P5MySQL9 The 4ords domain name stands )or the name o) the selected domain9 The )ollo4ing diagram sho4s the layout o) the php pages on any 4eb space9 ll in )older I7 The inde+9php page is placed in the root )older o) the 4eb space' commonly no4n as Oroot ,tree o) )olders/9
So once you had access to the O;ile Manager to create a )older named OI79 To do it' 4rite OI7 in control Create ;older and clic the icon ne+t9 !oubleclic on the name o) the OI7 )older' opening it9 $ith the buttons belo4 to select the your PC )iles: ai7accessosessione9php' ai7connessionedb9php' ai7gestionedati9php' ai7inde+9php' ai7inter)accia9php ai7accessosessione9php' ai7connessionedb9php' ai7gestionedati9php' ai7inde+9php' ai7inter)accia9php
then clic to upload the )iles9
(est pages and database on the Internet 2nce sure that the php pages are all in the same OI7 )older' the GNL to the php pages 4ill be: http: ##nomedominio9e+t#I7#ai7inde+9php' )or access http: ##nomedominio9e+t#I7# ai7inter)accia9php' pp5$eb5P"P5MySQL inter)ace and the latest %ersion o) php http: ##nomedominio9e+t#I7#ai7inde+9php' )or access http: ##nomedominio9e+t#I7# ai7inter)accia9php' pp5$eb5P"P5MySQL inter)ace and in a similar 4ay )or the other php pages loaded on the ser%er9
(est access to the database and php ers ion Into your bro4serRs address bar' type: http: ##4449nomedominio9e+t #I7#ai7connessionedb9php Possible results:
$hite page: the connection has been duly e))ected9 $hite page 4ith the string: 'eprecated( )ysq_pconnect *+( ,he )ysq extension is deprecated and i be re)oed in the future( use )ysqi or P'/ instead in '( 0 inetpub 0 ebs 0 do)ainna)e 0 A$2 0 ai2-connessionedb.php onine xy The connection has been regularly and php %ersion is the latest9 5se the 9 pages that does '-( contain the dash * o) possible error messages
Warning( )ysq_pconnect *+( 1o connection coud be )ade ecause the target )achine actiey refused it. in '( 0 inetpub 0 ebs 0 do)ainna)e 0 A$2 0 ai2-connessionedb.php onine xy
3ata error( 1o connection coud be )ade ecause the target )achine actiey refused it. in '( 0 inetpub 0 ebs 0 do)ainna)e 0 A$2 0 ai2-connessionedb.php onine xy There is probably an error in the data assigned to a %ariable:
`hostname' `username' `pass4ord' `database' controllare che i %alori siano esatti9 To )urther chec remo%e the characters # ^ and ^ # the underlying code that is in the login page and then sa%e it and reload it in Intenet9 [.php #^ `8uery H OSelect ai7tabella9id' ai7tabella9nome' ai7tabella9note' ai7tabella9data ;rom ai7tabella O\ `result H mys8l58uery,`8uery/ or die ,OQuesry )ailed\ 9mys8l5error,//\ 4hile ,`ro4 H mys8l5)etch5array,`result// echo O[br9`ro4XidRY' ' `ro4XnomeRY' ' `ro4XnoteRY' ' `ro4XdataRY\ ^# . By typing the GNL http: ##44 49nomedominio9e+t#I7#ai7db9php access all records 4ill appear9 In the test e+amples o) these pages are used pages containing ,dash/ but i) the php %ersion is more recent use o) pages 4hose name is not ,hyphen/9
(esting on Internet the ai*interfaccia&php interface Into your bro4serRs address bar' type: http: ##4449nomedominio9e+t#I7#ai7inter)accia9php
The monitor should see a blan page9 I) the message: Quesry )ailed\ Table S8l5A9ai7tabellaR does not e+ist' the table 4as not )ound in the database ,S8l5A replaces database name supplied by the pro%ider/9
To do )urther tests to change the de)ault %alue to the %ariables `I2 H isset,`50ETXI2RY/ . `50ETXI2RY : F\ Input 2utput = H selection # )iltering 7 H returns the record id Is `id H isset,`50ETXidRY/ . `50ETXidRY : F\ id o) the re8uested record9 sa%e' reload and launch the page
I) `I2 H = ' all the records 4ill appear i) `I2 H 7 the contents o) the record 4ith id H ` id
I) the code on this page has the problems ai7gestionedati9php page remains acti%e 4ith the danger o) being used by )raudsters
(est the Internet*the ai inde,&php page Into your bro4serRs address bar' type: http: ##4449nomedominio9e+t #I7#ai7inde+9php Page 4ill appear )or a username and pass4ord9 4rong place them and press OLogin' the page 4ill be tested and recharged Oi7inde+9php listening )or username and pass4ord entry9 E+act putting them and then pressing OEnter 4ill load the ai7gestionedati9php page9
Gsername and Pass4ord are read line o) code: ` Gsername HH OMiaGsername and ` pass4ord H OMyPass4ord Neplace MiaGsername and MiaPass4ord 4ith others' sa%e and reload the page on the Internet9
App1>eb1#<#1MySQL "a%ing prepared the part php#MySQL' loaded on Internet ser%ers' tested and ready' the ne+t step is to assemble the app to communicate 4ith table Oai7tabella the remote database9
Opp5$eb5P"P5MySQL is meant to sho4 ho4 to 4or 4ith pp )or ndroid' through a php inter)ace to a MySQL database is not accessible by 2!BC dri%er9 The inter)ace page Oai7inter)accia9php9 ;or comprehensi%e guides on the pp In%entor' also in Italian' see the o))icial site o) the MIT ,Massachusetts Institute o) Technology/ at this address: http:##appin%ento r9mit9edu#e+plore#boos9html
-DBC ?-pen DataBase Connectiity@ ;or large lines' it is a dri%er that uses the standard PIs allo4ing the connection )rom the client to the database9 The PI is independent o) operating systems' programming languages and database systems9 The )irst %ersion' such as !LLs' 4as de%eloped on $indo4s' they )ollo4ed )or GKI&' 2S#7 and Macintosh9
4emote data and management difficulties !ith App Inentor $ith pp In%entor ,March 7F=/ the management o) remote data can be made 4ith %arious components' among them:
Tiny$eb!B: It communicates 4ith a $eb ser%ice to store and retrie%e in)ormation and mae it a%ailable )or other applications9 Sa%ing is necessarily in the )orm o) pairs Tag # (alue9 Tag is the name that identi)ies the data and %alue the true %alue itsel)9 $ith a practical e+ample it is as i) 4e put all data ,contact' sentence etc9/ In a bag ,(alue/ and on each bag label ,Tag/ to locate and use9 There are )ree 4eb space that can be used to test applications 4ith Tiny$eb!B but a)ter testing the application re8uires the creation o) its o4n ser%ice9 ;usiontablesControl: $ith this component you can store and share data in tabular )ormat9 Gsed )or creation' selection' modi)ication and deletion o) data in tabular )ormat' stored on 0oogle ;usion Tables9 https%//deelopers&google&com/fusiontables/docs/3/getting1started The systems using ;usion Tables applications re8uire authentication )or access to 0oogleRs ser%ers and only 4or in this space9 L authentication can be: >ey PI )or de%elopers' allo4ing any manipulation o) tables and data9 ccount Ser%ice Email address' only )or 8uerying data9
lthough e+cellent components in their operation' Tiny$eb!B is impractical to handle large amounts o) data' 4hile ;usiontablesControl )orces and use the 0oogle ser%ers9 T4o other components o) pp In%entor allo4 access to 4eb pages' they are $eb(ie4er and $eb
$eb(ie4er: Component to display $eb pages9 The GNLs o) the pages can be speci)ied in the designer or editor o) Blocs 4ith 0oToGrl method9 In the $eb (ie4er 4indo4' you can sur) the internet on as a normal bro4ser9 The GNL can be inserted 8uerystring to communicate 4ith the code contained in the GNL target page9 $ith this component you can then issue commands and send the te+t to a specially 4ritten 4eb page that can interact 4ith a database9 $eb: llo4s you to manage data through "TTP 0ET or "TTP P2ST9 These methods allo4 you to 8uery the 4eb ser%er issue commands' send te+t and recei%e data bac9 This data can be immediately accessible or processed by their o4n applications' the
popular de%ice pages or share them 4ith the outside 4orld9
2pen pp In%entor at http:##appin%entor9mit9edu#e+plore# and clic on OCreate apps9 Probably it 4ill open the page OMIT pp In%entor Beta 7 ready to assemble the pp' alternati%ely may open a page 4here you are prompted )or logon credentials9
App1>eb1#<#1MySQL$ ho! it !or+s The application interacts' through an inter)ace' 4ith the table Oai7tabella o) a database located on a ser%er Intenet9 The database name can %ary because o)ten the pro%ider establishes their name9 E+amples are on ruba ser%ers but generali*able generali*able to other pro%iders9 ;or pp In%entor app Opp5$eb5P"P5MySQL 7' the main componeti are three9
>ebie!er inter)aces 4ith Oai7inter)accia9php and issues commands to: 2rder any )ield o) the table' in ascending and descending order ;ilter the data o) a )ield o) your choice' e%en the same order9 The returned records 4ill be %isible on the de%ice display9 >eb gi%es commands to Oai7inter)accia9php Oai7inter)accia9php )or: Import records )rom MySQL database to the ndroid de%ice9 The imported records 4ill be %isible on the display and ready )or sharing9 Sharing Component I7 4hich allo4s )ile sharing and # or messages: By starting the component 4ill display a list o) installed applications on the de%ice among those able to manage the in)ormation retrie%ed )rom the $eb component9 By choosing one o) these applications' the sharing can tae place eg9 by electronic mail' social net4or' SMS' and so on9
The opening o) the pp choice )or sharing' it already contains the data )rom the $eb component and then ready to be used9 In pp In%entor there are many methods to )orce the sharing in a certain 4ay only )or sending SMS' only )or email and so on9 Choosing Sharing has pre)erred to choose the user the sharing method you pre)er' 4ithout )orcing it may also produce errors by searching the pp not installed on the de%ice9
Designer page and components
The last column represents the Screen= properties9 ;or the properties o) the other components see the ne+t table9
Components$ properties and functions Components
0roup
Screen=
ppKameHpp5$eb5P"P5MySQL' TitleH I7 e P"P #MySQL
Layout
Kame
Property
T
7 righe' A colonne' lt9 automatic' Larg9 =FF
Table9rr9
Container choice components' inde+ing' )iltering on the )ields' in line 7' label containing choices ListPicer
Gser Int9
lpCampo2rdK
;onSi*eH=F' lt9 e Larg9 automatic
Select the )ield on 4hich 4ill be done sorting r ecords Gser Int9
lp2rdTipoT
;onSi*eH=F' Elements;romStringHaE*'*Ea lt9 e Larg9 automatic
ListPicer
Select the type o) sorting to be done on the chosen )ield 4ith lp2rdTipoT ,a*' *Ea/ Gser Int9
lpCanpo;iltroC
;onSi*eH=F' lt9 e Larg9 automatic
ListPicer
Sele*iona il campo che de%e essere )iltrato Select the )ield that is to be ) iltered Te+tBo+
Gser Int9
tb;ilro;
;onSi*eH=F' "intH)iltro lt9 automatic' Larg9 Fpi+el
Testo#)iltro nella sele*ione dei record in ai7Einter) accia9php Te+t # )ilter in the record selection in ai7Einter)accia9php
Button
Gser Int9
bEsegui
KameHesegui ;onSi*eH=F' lt9 automatic' Larg9 7F
E+ecute button bEsegui sends the inter)ace commands ai7Einter)accia9php Label
Gser Int9
lNCampoK
;onSi*eH=F' lNCampoK'lt9 e Larg9 automatic
$ill contain this choice 4ith ListPicer l pCampo2rdK Label
Gser Int9
lNCampoT
;onSi*eH=F' lNCampoT'lt9 automatic' Larg9 =FF
Type o) order chosen by lp2rdTipoT ,a* or *Ea/ Label
Gser Int9
lNCampoC
;onSi*eH=F' lNCampoC lt9 e Larg9 automatic
$ill contain this choice 4ith ListPicer lpCampo;iltroC Label
Gser Int9
lNCampo;
$ill contain the pre%iously typed te+t in tb;ilro; ,the ) ilter/
;onSi*eH=7' lNCampo;'lt9 e Larg9 automatic
Layout
"
lt9 automatic' ;ill parent
"ori**9rr9
Container reco%ery comments and data sh aring Gser Int9 =F
tbI!record
Kumbers2nlyHsele*ionato';onSi*eH=F' "intHI!' lt9 automatic' Larg9
! o) the record to be r etr ie%ed Gser Int9 b2I! Button il )ile Condi%idi9png da lRimmagine al pulsante
;onSi*eH=F' lt9 automatic' Larg9 7F'
Con)irmation button )or the I! to be r eco%er ed Button
Gser Int9
bCondi%idi
;onSi*eH=F'ImageHCondi%idi9png'lt9 e Larg9automatic
Lancia la condi%isione del record con lRI! recuperatoLaunches o) the record shared 4ith the retrie%ed I! Label
Gser Int9
lNrecord
;onSi*eH=F'BacgroundColorHVello4'lt9automatic' Larg9=FF
$ill contain the contents o) the records retrie%ed and ready )or sh aring Gser Int9
$eb(ie4er=
$eb(ie4ier Prompt)orPermissionHabilitato (isibleHabilitato Social Sharing $eb
Connetti%ity
;ollo4 Lins,Segui collegamenti/Habilitato lt9 automatic' Larg9 automatic
Sharing=
Componente non %isibile
$eb=
Componente non %isibile
Netrie%es data )rom ai7inter)accia9php and places them in lNrecord
Assign behaiors to components lin+%
global %ariable Olin is declared' to 4hich' 4ith a te+t bloc is assigned as the %alue o) the Internet inter)ace address Oai7inter)accia9php I) 4hen starting the pp appears the message: !Warning( )ysq_pconnect *+( 1o connection coud be )ade ecause the target )achine actiey refused it. in '( 0 inetpub 0 ebs 0 do)ainna)e 0 A$2 0 ai2-connessionedb.php onine xy !
the P"P %ersion is outdated' replace the GNL 4ith the printed bloc belo49 and in your application: http: ##44 49(ostro!ominio#I7#ai7inter)accia9php
initialie global ListaCampi to%
0lobal %ariable OListaCampi is declared9 The list 4ill contain the names o) the entries in ListPicer lpCampo2rdK ,Campo2rd9/ lpCanpo;iltroC ,Canpo;iltro/9 The %oices o) ListPicer are the same names o) the camps run by the inter)ace Oi7inter)accia9php9 The )ield names' )or 7 ListiPicer' could also be declared in !esigner picture in their Elements;romString panes9 The choice to do so in the Blocs page is determined by the )act that the content o) the t4o ListiPicer is identical9 This method a%oids repeat 7 times the 4riting on the !esigner tab' It )acilitates a possible modi)ication o) the list' 4hile decreasing the lielihood o) errors9
>hen Screen3&Iniialie :
t the start is initiali*ed Screen= and populated the %ariable OListaCampi 4ith the names o) the camps run by the inter)ace Oai7inter)accia9php )or the table Oai7tabella database9 The names o) the )ields in te+t blocs' are id' none' note' date9 Immediately a)ter the lists are assigned to ListPicer lpCampo2rdK Campo on 4hich to sort lpCampo;iltroC )ield on 4hich to apply the )ilter ;inally the (alori!iStart and KascondeImporta*ioneCondi%isione procedures are in%oed
to aloriDiStart%
Procedure in%oed a)ter the initiali*ation o) Screen=' assigns the label that appears on the display de)aults9 $ith these %alues' in the case o) a clic on the button 4ill per)orm the records 4ill be sorted on the name )ield in the direction o) a*' and sho4ing all records because the inter)ace to the line that recei%es the command `; H isset,`50ETX;RY/ . `50ETX;RY : O\ the character' ac8uired by de)ault 4hich means all records9
to 'ascondeImportaioneCondiisione :
Procedure in%oed at the end o) initiali*ation and Screen= by bEsegui bloc ,to clic on this button/9 They are hidden: lNecord: Label 4hich must contain the records imported )rom the ser%er to the de%ice bCondi%idi: Button )or the share o) imported records )rom the ser%er to the de%ice
it is emptied tbl!record: container o) the record id to import i)Neset' because there are none at startup in the case o) clicing on the button bEsegui because you prepare )or ne4 data9
5se of List#ic+er
Similar to the Button component' at the clic o) ListPicer are displayed to the user a list o) items )rom 4hich to choose 4hile remaining on hold9 $hen the user has chosen' the inter)ace o) ListPicer returns to its pre%ious state9 In the case o) this e%ent pp )terPicing ,a)ter selected/ is considered and taen Selection 4hich corresponds to the ordinal number o) the elements9 The same pro%ision is in the inter)ace in php recei%e the ordinal number o) the )ield on 4hich to operate 4ith the important di))erence that:
ListPice in the origin is = 4hile in the inter)ace )ields is *ero9 Sending data to the inter)ace 4ith the bEsegui button' the %alue = is subtracted )rom the result o) ListPicer >hen lpCampo-rdM After#ic+ing%
ListiPicer that allo4s you to choose the )ield that 4ill run the system9 To clic on it appears the list o) )ields OListaCampi identical to table )ields Oai7tabella database9 The user 4ill ha%e to clic on one o) them and the result 4ill be stored in the label lNCampoK9 It is a numeric %alue correspondent to the order o) entries9 (alue o) the )irst item is e8ual to =
>hen lp-rd(ipo( After#ic+ing%
ListiPicer that lets you choose the sort order bet4een Oa* and O*a9 The content o) this ListPicer is declared in the !esigner page' Elements;romString pane9 Clic on it to appear the rumors Oa* and *a9 The user 4ill ha%e to clic on one o) them and the result 4ill be stored in the label lNCampoT9 It is a numeric %alue correspondent to the order o) entries9 (alue o) the )irst item is e8ual to =
>hen lpCampo=iltroC After#ic+ing%
ListPicer that lets you choose the )ield on 4hich the )ilter 4ill be applied9 To clic on it appears the list o) )ields OListaCampi identical to table )ields Oai7tabella database9 The user 4ill ha%e to clic on one o) them and the result 4ill be stored in the label lNCampoC9 It is a numeric %alue correspondent to the order o) entries9
(alue o) the )irst item is e8ual to =
Select and filter records !hen b)segui clic+% Button that connects the $eb(ie4er component inter)ace
Oi7inter)accia9php passing %alues 4ith the 8uerystring method 9 Call tb;iltro;9"ide>eyboard: "ides the %irtual eyboard99 set lNCampo;9Te+t to tb;iltro9Te+t: It )its into the label lNCampo; typed te+t into the tb;iltro component9 set tb;iltro9Te+t to O: Empty the )ilter component $eb(ie4er=90otoGrl : loads the page to the gi%en GNL )ollo4s the bloc to
n e+ample o) the result o) the e+ecution o) preceding bloc9 "a%ing set:
Sort )ield ,Campo2rd9/ H Kame 2rder Type ,2rdTipo/ H *a ;ilter )ield ,C9;iltro/ H date ;ilter H 7F=A and cliced on the button Nun' )or test records entered in the database' the result is to the le)t9 In )act' the record 4ith id H = and id H ha%e been created or modi)ied in 7F=A as you can read in the date )ield9
Import records into the deice >hen b-+ID&clic+
Clic to import the contents o) the record 4ith the inde+ )ield %alue id Call tbl!record9"ide>eyboard: "ides the %irtual eyboard9 i) not is number. idl!record9Te+t : i) it is not a number then in September Idle record9Te+t to F: F enter into component idl!record9Te+t null occurs 4ithout pressing this button %alues in idl!record9Te+t Set $eb=9GNL to: loads the page to the gi%en GNL It )ollo4s the bloc to
2n the Ointer)accia9php a)ter reading the 8uerystring is launched a 8uery on the id )ield ,4hich has uni8ue content/ o) the table9
I) the 8uery returns the records' the content o) it is copied to the ` %ariable te+t' or in the %ariable ` te+t is inserted: OThe record ` id does not e+ist9 ;inally' 4ith echo ` te+t is sent to the de%ice >hen >eb3&6ot%te,t
turns the contents o) the record )or )or id re8uired9 set lNrecord9te+t to trim trim: remo%e any spaces at the beginning and end o) the string recei%ed get responseContent: the %alue is returned in response Content' then inserted in the Label lNrecord trim remo%e any spaces at the beginning and end o) string set lNrecord9(isible to true: It maes %isible the label that contains the contents o) the returned records ,the one 4ith the yello4 bacground/ set bCondi%idi9(isible to true: it maes %isible the button )or record sharing
s result o) e+ecution o) the preceding bloc9
Inserting in the Te+tBo+ OtbI!record and pressing the Button O2 I! has been re8uested the record 4ith id H )rom the remote database9
The contents o) the record 4ith id H has been imported and placed in the Label OlNrecord ,2n yello4 bacground/9
The button )or records sharing has appeared9
Share the content of the imported records t this point' the records can be shared 4ith the rest o) the 4orld by clicing on the button that has bCondi%idi on 4hether the symbol:
Clicing on the button opens a 4indo4 similar to:
The contents o) the 4indo4 changes depending on the app installed on your de%ice9
Belo4 ho4 you present some o) the possibilities
The o) )icial 4ebsite )or pp In%entor pro )iles ha%e been success)ully tested on ndroid de%ices9 ;or large 8uantity and di%ersity o) ndroid de%ices a%ailable on the maret it has been impossible to test the e+amples on each o) them9 ;or this reason it can not be guaranteed that all the e+amples 4or on e%ery de%ice9 T"E S2;T$NE IS PN2(I!E! OS IS' $IT"2GT $NNKTV 2; KV >IK!' E&PNESS 2N IMPLIE!' IKCLG!IK0' BGT K2T LIMITE! T2' IMPLIE! $NNKTIES 2; MENC"KTBILITV' ;ITKESSR ;2N PNTICGLN PGNP2SE K! K2KIK;NIK0EMEKT9 IK K2 E(EKT S"LL T"E GT"2NS 2N C2PVNI0"T "2L!ENS BE LIBLE ;2N KV CLIM' !M0ES 2N 2T"EN LIBILITV' $"ET"EN IK K CTI2K 2; C2KTNCT' T2NT 2N 2T"EN$ISE' NISIK0 ;N2M' 2N IK C2KKECTI2K $IT" T"E S2;T$NE 2N T"E GSE 2N 2T"EN !ELIK0S $IT" T"E S2;T$NE9 address: http:##4449taccetti9net#pp5 $eb5P"P5MySQL#
They are do4nloadable database table' php pages and pp5$eb5P"P5MySQL here 4ill also be a%ailable )or updating and # or corrections9 ;or communications use the )orm: http:##4449appin%entor9it#Contatti#Contatti9php
