Use data (2 int, 1 bool, 1 string) from an online Database, prob with int and bool

Hi,

I need to connect to a database and retrieve information and use them.

Project:
I need to do two Unity application for a client:

- Touchscreen viewer of an hotel:

- View the hotel in 3d, rotate around etc
- A menu to see per floor with clickable rooms (All in GUIs). There are 2 types of floors, the third one (45 rooms), and 4th to 9th(33rooms / floors). Every information is generated dynamicaly (room number, name of the type of room, size, and page of the second menu).
- A page per room(All in GUIs).
which display few information about the room and a cad.



- Just a Simple interface in Unity
this app is for my client to send information of the room online.
There are tree text fields and a tickbox. For: number of the room(int), name of the buyer(string), sold or not (tickbox)(bool).
I know I could do this with a mysql program or something else, but I'm really late on this project and I don't have the time to learn that now (it's for monday).


Problem:

In my first menu, when you click on a room I need to display if it is available and the name of the buyer. (same for menu 2)
To do that, I've got a bool sold or not and a switch between two textures (sold in red - available in green).

Everything else in the project is done...


So i've got a script send that I use 4 months ago to learn the interaction with de database. I had to stop learning beacuse of the amount of work and now I need to get back to it.

Can someone guide me to do this ? I'm already connected to my data base.

Here is the logic I had in mind:

Do a table with all the ap number from 301 to 345, 401-433, 501-533, ...
and then when you open the menu it loads all the info and store them into variables.
The available/sold will be displayed, the buyer's name will be displayed in a text field.
OR
Each time you click on an apartment to see his info, it load the specific row in the array. So imported gradualy

How can I do to read an Int ? to know which room is it.
How can I do to read a bool and export a bool? (itm.SoldOrNot = rdr["SoldOrNot"].ToString(); with SoldOrNot a bool.

How can I do to write in a specific line (if in the Application 2 the client enter the room 407 informations, it needs to update this line and not another).
How can I change my script to read each line and store them ?

Do you have any ideas for that ?


Here is the script: (in two parts my message is too long)
It take the objects with a "Savable" tag and save their position etc...

Code (csharp):

1. using UnityEngine;
2. using MySql.Data;
3. using MySql.Data.MySqlClient;
4. using System;
5. using System.Data;
6. using System.Collections;
7. using System.Collections.Generic;
8.  
9. public class MySQL : MonoBehaviour
10. {
11.     // In truth, the only things you want to save to the database are dynamic objects
12.     // static objects in the scene will always exist, so make sure to set your Tag
13.     // based on the documentation for this demo
14.  
15.     // values to match the database columns
16.     string ID, Name, levelname, objectType;
17.     float posx, posy, posz, tranx, trany, tranz;
18.  
19.     bool saving = false;
20.     bool loading = false;
21.  
22.     // MySQL instance specific items
23.     string constr = "Server=mysql5.dns-systems.net; Port=3307;Database=icreateonline_2;User ID=icreateonline_2;Password=boris7;Pooling=true";
24.     // connection object
25.     MySqlConnection con = null;
26.     // command object
27.     MySqlCommand cmd = null;
28.     // reader object
29.     MySqlDataReader rdr = null;
30.     // error object
31.     MySqlError er = null;
32.     // object collection array
33.     GameObject[] bodies;
34.     // object definitions
35.     public struct data
36.     {
37.         public int UID;
38.         public string ID, Name, levelname, objectType;
39.         public float posx, posy, posz, tranx, trany, tranz;
40.     }
41.     // collection container
42.     List<data> _GameItems;
43.     void Awake()
44.     {
45.         try
46.         {
47.             // setup the connection element
48.             con = new MySqlConnection(constr);
49.  
50.             // lets see if we can open the connection
51.             con.Open();
52.             Debug.Log("Connection State: " + con.State);
53.         }
54.         catch (Exception ex)
55.         {
56.             Debug.Log(ex.ToString());
57.         }
58.  
59.     }
60.  
61.     void OnApplicationQuit()
62.     {
63.         Debug.Log("killing con");
64.         if (con != null)
65.         {
66.             if (con.State != ConnectionState.Closed)
67.                 con.Close();
68.             con.Dispose();
69.         }
70.     }
71.  
72.     // Use this for initialization
73.     void Start()
74.     {
75.  
76.     }
77.  
78.     // Update is called once per frame
79.     void Update()
80.     {
81.  
82.     }
83.  
84.  
85.     // gui event like a button, etc
86.     void OnGUI()
87.     {
88.         if (GUI.Button(new Rect(10, 70, 50, 30), "Save")  !saving)
89.         {
90.             saving = true;
91.             // first lets clean out the databae
92.             DeleteEntries();
93.             // now lets save the scene information
94.             InsertEntries();
95.             // you could also use the update if you know the ID of the item already saved
96.  
97.             saving = false;
98.         }
99.         if (GUI.Button(new Rect(10, 110, 50, 30), "Load")  !loading)
100.         {
101.             loading = true;
102.             // lets read the items from the database
103.             ReadEntries();
104.             // now display what is known about them to our log
105.             LogGameItems();
106.             loading = false;
107.         }
108.     }
109.  
110.     // Insert new entries into the table
111.     void InsertEntries()
112.     {
113.         prepData();
114.         string query = string.Empty;
115.         // Error trapping in the simplest form
116.         try
117.         {
118.             query = "INSERT INTO demo_table (ID, Name, levelname, objectType, posx, posy, posz, tranx, trany, tranz) VALUES (?ID, ?Name, ?levelname, ?objectType, ?posx, ?posy, ?posz, ?tranx, ?trany, ?tranz)";
119.             if (con.State != ConnectionState.Open)
120.                 con.Open();
121.             using (con)
122.             {
123.                 foreach (data itm in _GameItems)
124.                 {
125.                     using (cmd = new MySqlCommand(query, con))
126.                     {
127.                         MySqlParameter oParam = cmd.Parameters.Add("?ID", MySqlDbType.VarChar);
128.                         oParam.Value = itm.ID;
129.                         MySqlParameter oParam1 = cmd.Parameters.Add("?Name", MySqlDbType.VarChar);
130.                         oParam1.Value = itm.Name;
131.                         MySqlParameter oParam2 = cmd.Parameters.Add("?levelname", MySqlDbType.VarChar);
132.                         oParam2.Value = itm.levelname;
133.                         MySqlParameter oParam3 = cmd.Parameters.Add("?objectType", MySqlDbType.VarChar);
134.                         oParam3.Value = itm.objectType;
135.                         MySqlParameter oParam4 = cmd.Parameters.Add("?posx", MySqlDbType.Float);
136.                         oParam4.Value = itm.posx;
137.                         MySqlParameter oParam5 = cmd.Parameters.Add("?posy", MySqlDbType.Float);
138.                         oParam5.Value = itm.posy;
139.                         MySqlParameter oParam6 = cmd.Parameters.Add("?posz", MySqlDbType.Float);
140.                         oParam6.Value = itm.posz;
141.                         MySqlParameter oParam7 = cmd.Parameters.Add("?tranx", MySqlDbType.Float);
142.                         oParam7.Value = itm.tranx;
143.                         MySqlParameter oParam8 = cmd.Parameters.Add("?trany", MySqlDbType.Float);
144.                         oParam8.Value = itm.trany;
145.                         MySqlParameter oParam9 = cmd.Parameters.Add("?tranz", MySqlDbType.Float);
146.                         oParam9.Value = itm.tranz;
147.                         cmd.ExecuteNonQuery();
148.                     }
149.                 }
150.             }
151.         }
152.         catch (Exception ex)
153.         {
154.             Debug.Log(ex.ToString());
155.         }
156.         finally
157.         {
158.         }
159.     }
160.  

 

Code (csharp):

1.  
2.  
3.     // Update existing entries in the table based on the iddemo_table
4.     void UpdateEntries()
5.     {
6.         prepData();
7.         string query = string.Empty;
8.         // Error trapping in the simplest form
9.         try
10.         {
11.             query = "UPDATE demo_table SET ID=?ID, Name=?Name, levelname=?levelname, objectType=?objectType, posx=?posx, posy=?posy, posz=?posz, tranx=?tranx, trany=?trany, tranz=?tranz WHERE iddemo_table=?UID";
12.             if (con.State != ConnectionState.Open)
13.                 con.Open();
14.             using (con)
15.             {
16.                 foreach (data itm in _GameItems)
17.                 {
18.                     using (cmd = new MySqlCommand(query, con))
19.                     {
20.                         MySqlParameter oParam = cmd.Parameters.Add("?ID", MySqlDbType.VarChar);
21.                         oParam.Value = itm.ID;
22.                         MySqlParameter oParam1 = cmd.Parameters.Add("?Name", MySqlDbType.VarChar);
23.                         oParam1.Value = itm.Name;
24.                         MySqlParameter oParam2 = cmd.Parameters.Add("?levelname", MySqlDbType.VarChar);
25.                         oParam2.Value = itm.levelname;
26.                         MySqlParameter oParam3 = cmd.Parameters.Add("?objectType", MySqlDbType.VarChar);
27.                         oParam3.Value = itm.objectType;
28.                         MySqlParameter oParam4 = cmd.Parameters.Add("?posx", MySqlDbType.Float);
29.                         oParam4.Value = itm.posx;
30.                         MySqlParameter oParam5 = cmd.Parameters.Add("?posy", MySqlDbType.Float);
31.                         oParam5.Value = itm.posy;
32.                         MySqlParameter oParam6 = cmd.Parameters.Add("?posz", MySqlDbType.Float);
33.                         oParam6.Value = itm.posz;
34.                         MySqlParameter oParam7 = cmd.Parameters.Add("?tranx", MySqlDbType.Float);
35.                         oParam7.Value = itm.tranx;
36.                         MySqlParameter oParam8 = cmd.Parameters.Add("?trany", MySqlDbType.Float);
37.                         oParam8.Value = itm.trany;
38.                         MySqlParameter oParam9 = cmd.Parameters.Add("?tranz", MySqlDbType.Float);
39.                         oParam9.Value = itm.tranz;
40.                         MySqlParameter oParam10 = cmd.Parameters.Add("?UID", MySqlDbType.Int32);
41.                         oParam10.Value = itm.UID;
42.  
43.                         cmd.ExecuteNonQuery();
44.                     }
45.                 }
46.             }
47.         }
48.         catch (Exception ex)
49.         {
50.             Debug.Log(ex.ToString());
51.         }
52.         finally
53.         {
54.         }
55.     }
56.  
57.     // Delete entries from the table
58.     void DeleteEntries()
59.     {
60.         string query = string.Empty;
61.         // Error trapping in the simplest form
62.         try
63.         {
64.             // optimally you will know which items you want to delete from the database
65.             // using the following code and the record ID, you can delete the entry
66.             //-----------------------------------------------------------------------
67.             // query = "DELETE FROM demo_table WHERE iddemo_table=?UID";
68.             // MySqlParameter oParam = cmd.Parameters.Add("?UID", MySqlDbType.Int32);
69.             // oParam.Value = 0;
70.             //-----------------------------------------------------------------------
71.             query = "DELETE FROM demo_table WHERE iddemo_table";
72.             if (con.State != ConnectionState.Open)
73.                 con.Open();
74.             using (con)
75.             {
76.                 using (cmd = new MySqlCommand(query, con))
77.                 {
78.                     cmd.ExecuteNonQuery();
79.                 }
80.             }
81.         }
82.         catch (Exception ex)
83.         {
84.           Debug.Log(ex.ToString());
85.         }
86.         finally
87.         {
88.         }
89.     }
90.  
91.     // Read all entries from the table
92.     void ReadEntries()
93.     {
94.         string query = string.Empty;
95.         if (_GameItems == null)
96.             _GameItems = new List<data>();
97.         if (_GameItems.Count > 0)
98.             _GameItems.Clear();
99.         // Error trapping in the simplest form
100.         try
101.         {
102.             query = "SELECT * FROM view_demo";
103.             if (con.State != ConnectionState.Open)
104.                 con.Open();
105.             using (con)
106.             {
107.                 using (cmd = new MySqlCommand(query, con))
108.                 {
109.                     rdr = cmd.ExecuteReader();
110.                     if(rdr.HasRows)
111.                     while (rdr.Read())
112.                     {
113.                         data itm = new data();
114.                         itm.UID = int.Parse(rdr["iddemo_table"].ToString());
115.                         itm.ID = rdr["ID"].ToString();
116.                         itm.levelname = rdr["levelname"].ToString();
117.                         itm.Name = rdr["Name"].ToString();
118.                         itm.objectType = rdr["objectType"].ToString();
119.                         itm.posx = float.Parse(rdr["posx"].ToString());
120.                         itm.posy = float.Parse(rdr["posy"].ToString());
121.                         itm.posz = float.Parse(rdr["posz"].ToString());
122.                         itm.tranx = float.Parse(rdr["tranx"].ToString());
123.                         itm.trany = float.Parse(rdr["trany"].ToString());
124.                         itm.tranz = float.Parse(rdr["tranz"].ToString());
125.                         _GameItems.Add(itm);
126.                     }
127.                     rdr.Dispose();
128.                 }
129.             }
130.         }
131.         catch (Exception ex)
132.         {
133.             Debug.Log(ex.ToString());
134.         }
135.         finally
136.         {
137.         }
138.     }
139.  
140.     /// <summary>
141.     /// Lets show what was read back to the log window
142.     /// </summary>
143.     void LogGameItems()
144.     {
145.         if (_GameItems != null)
146.         {
147.             if (_GameItems.Count > 0)
148.             {
149.                 foreach (data itm in _GameItems)
150.                 {
151.                     Debug.Log("UID: " + itm.UID);
152.                     Debug.Log("ID: " + itm.ID);
153.                     Debug.Log("levelname: " + itm.levelname);
154.                     Debug.Log("Name: " + itm.Name);
155.                     Debug.Log("objectType: " + itm.objectType);
156.                     Debug.Log("posx: " + itm.posx);
157.                     Debug.Log("posy: " + itm.posy);
158.                     Debug.Log("posz: " + itm.posz);
159.                     Debug.Log("tranx: " + itm.tranx);
160.                     Debug.Log("trany: " + itm.trany);
161.                     Debug.Log("tranz: " + itm.tranz);
162.                 }
163.             }
164.         }
165.     }
166.  
167.     /// <summary>
168.     /// This method prepares the data to be saved into our database
169.     ///
170.     /// </summary>
171.     void prepData()
172.     {
173.         bodies = GameObject.FindGameObjectsWithTag("Savable");
174.         _GameItems = new List<data>();
175.         data itm;
176.         foreach (GameObject body in bodies)
177.         {
178.             itm = new data();
179.             itm.ID = body.name + "_" + body.GetInstanceID();
180.             itm.Name = body.name;
181.             itm.levelname = Application.loadedLevelName;
182.             itm.objectType = body.name.Replace("(Clone)", "");
183.             itm.posx = body.transform.position.x;
184.             itm.posy = body.transform.position.y;
185.             itm.posz = body.transform.position.z;
186.             itm.tranx = body.transform.rotation.x;
187.             itm.trany = body.transform.rotation.y;
188.             itm.tranz = body.transform.rotation.z;
189.             _GameItems.Add(itm);
190.         }
191.         Debug.Log("Items in collection: " + _GameItems.Count);
192.     }
193. }
194.  

 

I really need to know how to write and read in/a specific row of my array. That's my main problem.

 

Here is what I got:

- I'm connected to my database.

- I'm using a table wich include an "ID" (int 0-198), a UNumber" = room number (int) (301-345, 401-433,501-533......901-933), "Buyer" = the buyer's name (varchar), "Sold" bool or int (1/0).

- I can retrieve my ID UNumber Buyer and Sold, and store it in my variables.

- I don't know how to update in a specific row. I can add stuff but not update a previous one.


So basicaly what i need to do is:

Second application:

Fill in the form wich include : textfield1 = Room Number (UNumber - Int), textfield2 = Buuyer's name (Buyer), tickbox (Sold bool or int between 1/0). and a send button to send these information to the Row with the right "UNumber".

First application;

Done !

 

Hi,

Still have a problem to update in a specific row. I can add stuff but not update a previous one....

And everything only works in the editor... I can't create an EXE because of the MySql.Data.dll...

Help