Check if record exists in the database if not then insert value

  • 1
  • Question
  • Updated 5 years ago
  • Doesn't Need an Answer
Hello there,

I'm fetching external JSON from the URL. JSON is nothing but a Facebook feeds. I want to check by the feed ID. So, If the feed ID doesn't exists on the Sqlite database table then it will insert the value if that. If feed ID of does exists then it will ignore it.
I have written a code but it doesn't work.!! It's duplicating the temp_str variable everytime even if it is in the loop. Database is already opened.

<script type="text/javascript" charset="utf-8">
$.getJSON( "https://graph.facebook.com/334718749959224/posts?access_token=1612844388968138|npOfcrXPkussrfVJZOiq7imr1Tc&limit=15", function( getdata )
{

var len = getdata.data.length;

for(var i=0;i<len;i++)
{
var temp_str = getdata.data[i].id;

database.transaction(function(tx)
{
tx.executeSql("select count(post_id) as cnt from posts_data where post_id=?",[temp_str],function(tx,res)
{

if(res.rows.item(0).cnt == 0)
{

tx.executeSql("insert into posts_data values(?)",[temp_str],function(tx,res) {
console.log("inserted");
});
}

},function(e){
console.log("ERROR: " + e.message);
});
});
}

});

</script>

Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes

Posted 5 years ago

  • 1
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
Someone please help !!!! :-(
Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
I would expect
if(res.rows.item(0).cnt == 0)

to be
if(res.rows[0].cnt == 0)
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
I have printed the value of i using alert. It's always 15 !! I can not access variable i's value inside the database.transaction function. Why ?



<script type="text/javascript" charset="utf-8">
$.getJSON( "https://graph.facebook.com/334718749959224/posts?access_token=1612844388968138|npOfcrXPkussrfVJZOiq7imr1Tc&limit=15", function( getdata )
{

var len = getdata.data.length;

for(var i=0;i<len;i++)
{
var temp_str = getdata.data[i].id;

database.transaction(function(tx)
{
tx.executeSql("select count(post_id) as cnt from posts_data where post_id=?",[temp_str],function(tx,res)
{
//printed i here and it shows it's 15
alert(i);
if(res.rows.item(0).cnt == 0)
{

tx.executeSql("insert into posts_data values(?)",[temp_str],function(tx,res) {
console.log("inserted");
});
}

},function(e){
console.log("ERROR: " + e.message);
});
});
}

});

</script>

Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
I don't see why you would want the value of i at that spot.
The main problem, though, is that you are mixing up asynchronous function logic with a synchronous for loop. You can't do that in javascript!

See http://stackoverflow.com/questions/42...
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
Thank you! Is there any other way to do so ? I mean to get the json response and check whether that record exists in the table ? Can you suggest a tutorial ?
Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
Well, the Stackoverflow-article already contains a conceptual solution to synchronize the process.

For your particular case, I have a feeling that I would take a different approach, but I may not know all requirements. It sounds as if you simply want to store your JSON data persistently (maybe to allow for offline use, in which case your application logic would be based on the local, persistent data). It also sounds as if the live data from the server would be a matter of merely several kilobytes.
If that is the case, I would not use a database (yours appears to have one table, no primary keys, no constraints and no relationships, so it seems no 'real database') at all. What would happen if you serialized the JSON data and stored them in localStorage - even without checking whether or not data already exist?
You could simply retrieve those data from localStorage whenever needed, without using transactions or multiple nested loops.

OTOH, if this is a homework assignment where you must use a 'database', OR the requirements and goals go way further than painted above, then the above is useless.
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
Sir, To be frank this is not the part of my assignment. I'm on the project. I'm in the last year of B.E(here B.S== B.E). We have to choose 1 project guide. But here no one know what Phonegap or apache cordova is. So everything I need to do by my own. No one is here in my university to help since they don't know anything about Phonegap. This is the reason I'm posting in this forum. I hope you understand :-)
If you don't mind can I describe the project structure ?

Thank you!
Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
If you don't mind can I describe the project structure ?
Certainly. Please, do.
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
Regarding to the project. It does the following things.

MySQL server table fields.
Person has Username,First name,Middle Name,Last Name, Primary Email, Secondary Email, Address, Phone Number, Password, unique ID( increments on each register),Home address, Office Address, Facebook URL, Twitter URL.
Each person is identified by the Phone Number.

Person can change the information from the website or from phone App.

Now when user open ups the App. He/she will have to enter the phone number then he/she will receive the code to verify the phone number.

After that I will check if that phone number has already registered. If phone number is registered then user will be redirected to the login.html file if not then he/she has to register with username and password. After the registration process user has to enter the details.

Once the registration process is done, local phone number sync process comes into the picture. It means send all the local phone numbers and get the response(e.g select * from users where phone_num in[phone numbers] ) and store it on the local sqlite database(users still can access the data offline). So the people who have registered with the phone number on website that information will be stored on that user's local database(sqlite).

Additionally, user can see the list of users in the list view. ListView is retrieved from the sqlite database as mentioned above.

When user hits the "Refresh" button then we have the registered numbers on the local database. So send all those numbers and get the response. According to the response update the current records in the local database. i.e If user has changed number or email or address or if user has added the facebook url etc etc.
Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
Thanks for this.

If you really want to store this in a database, you could loop through your JSON and directly perform a INSERT or REPLACE INTO query for each JSON data entry.
http://stackoverflow.com/questions/41...
This requires a local database with an identifying primary key column!

Now, the question is which field really identifies your users. Here are several questions:
1. Is a person suddenly a new user if he changes his phone number?
2. Is a person multiple users if he has more than 1 phone number?
3. If users have to register with username and password, can the username of two users be identical at any time?
4. If users are identified by phone number, what do you need the unique ID field (autoincrement) for?
5. How do users revoke their registration, and how do you make sure that users who unregistered get deleted from local databases?
6. A user must be online to register and he must be online to log in (if I understand correctly). Why is it necessary for him to see the data offline? Remember that these data might now be old (not updated, not valid anymore, etc.)
7. If the average user has 70 friends, and the average data record for a user is 1.5KB, each local database would be about 100KB. This is really small, so you might as well use a storage model that is more convenient for you. One option would be to simply store the whole JSON object in localStorage (which, however, could have drawbacks if you want to change its structure server-side, unless you send a javascript file with the data handling logic along with the JSON data).
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
Sorry my bad!! A person will be identified by the unique ID column.

1) Username is unique if it is used then it's not available anymore.
2) ID column's value will be same on the server side and client side(sqlite db).So the INSERT or Replace INTO can be applied.
3) Multiple users can not have the same phone number. While registering the phone number if the phone number is registered then App will redirect to the login page. If user doesn't remember the credentials then App will send an email to the registered email.
4) Yes, User must be online to register or log in. User can see the old data however when user has connection, we will do the same process of Sync. As you told INSERT or REPLACE INTO.
5) Well, I have 2780 contacts, So, I think it's better to use SQLite.
6) INSERT or REPLACE INTO is good but what is I want to show the notification(with local notification plugin) that user has changed his phone number or user has added facebook account ?

Thank you for your efforts! :-)
Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
1) Do you enforce this server side in your database, or in your application logic only?
2) If a user has multiple phone numbers, must he have multiple user names?

5) You have 2780 contacts server side. But how many users will have more than 100 contacts in the local database? That's what counts!
6) You decide that a user has changed his data on the server, not in the client. Then create a separate JSON file, which is loaded by the app. with a list of users that have changed their data. Locally, one or more notifications are generated.

7) What is your emergency scenario if you have to roll back your server side database to a previous backup? How do you roll back all those local databases?
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
0) I'm using Laravel framework on server side which will provide the JSON output.It will handle posting phone numbers,adding user's data, changing user's data stuff etc etc.
1) A user can't have more than one phone number. 1 username per 1 phone number.
2) Why did you say "You have 2780 contacts server side ?" I meant I have 2780 locally. :-)
3) User can change the data both from Website and the App. If user updates his/her data in the local App then his/her information will be stored locally as well as on the server side too.
4) Regarding the rollback database I don't know how will I do it. :/

Lets say you are in my contact list and your phone number is 1234567890(assuming that you are registered user). Now I'm a new user. I download the App and I register my phone number. After registration you are in my contacts list so it will fetch your all data. In case in future if you change your data then I will use "Refresh" button to get those data's(Using INSERT OR REPLACE INTO method as you suggested).
Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
1) You never heard of dual sim smartphones? Nowadays, it's quite common for people to have several phone numbers.
2) Ah. OK, 2780 contacts locally. But you have them all in your contacts, already. Why would you need them in another local database as well?
3) I understand. You are synchronizing the local database and the server database. I wonder what happens if those data conflict. What if a user changes his secondary mail address locally and his primary mail address on the website?
4) That would probably be end-of-app. Unless you always make a full refresh of the local database (e.g.: if JSON data are imported, first empty the database, then insert all data from the JSON. Never update data)

Note:
if your app sends all data from the contacts list to your server, I must make sure to never install your app. Do you have a privacy statement? What if someone in your database demands to be deleted from your bunch of data?

In case in future if you change your data then I will use "Refresh" button to get those data's(Using INSERT OR REPLACE INTO method as you suggested)
You are not required to hit that refresh button, so if you don't, you are walking around with old data, which are not the actual data anymore. Twice (in your contacts list and in your local database).
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
1) I have two phone numbers but I will use 1 for registration and another phone number will be added as an extra number.
2) Not all 2780 database has to be stored on the local database. After sync 2780 contacts only contacts with an account(as we are sending contact number to the server)will be stored.
3) Well, Everytime user will open an App. There will be two operation will be performed. Get the current user's data from the server and update it locally. and refresh the other user's data so the "Refresh" button will not be required.
4) Regarding to sending contact number, Nowadays every chatting app like Whatsapp, Facebook messenger, Hike, WeChat, Line, Telegram and many other App uses this approach. It's quite common. Still I will add it in the terms and conditions.
Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
Nowadays every chatting app like Whatsapp, Facebook messenger, Hike, WeChat, Line, Telegram and many other App uses this approach.
Probably the reason why I have none of them.

Still, the question remains: what if a user demands deletion of his data from your database?

Anyway, I hope you have now enough information for the next days of app work.
Best of luck!
:-)
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
Sir I didn't get one answer.

1) INSERT OR REPLACE INTO approach is good. But what If I want to show the local notification that this user has changed name, something ?
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
For example I get this JSON from the server.

{
"id":"100",
"first_name":"Abhishek1",
"last_name":"Deshkar1",
"address":"new address",
}

In the local SQLITE table has following record :

id -> 100
first_name -> Abhishek
last_name-> Deshkar
address -> Old address
facebook-> www.fb.com/abhi.pro
twitter -> @abhishek
email-> abhi.alone@ymail.com
phone_number -> 1234567890

Now how will I write the query ? Kindly provide one example :-)
Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
It depends on how you want this to be interpreted. Has this user removed his facebook, twitter, email and phone?
If not, why aren't these data present in the JSON object?
If so, why does the server application allow deletion of phone number, as that was required?

Speaking of which: how would your JSON object indicate that user-100 was deleted from the server and must now be deleted from the local database?
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
That was just an example. I will not allow users to delete the phone number. Yes the user has deleted the facebook,email.

Speaking of which: how would your JSON object indicate that user-100 was deleted from the server and must now be deleted from the local database?

Apparently, I don't know what should I do for this!! Can you suggest me a solution ?
I have a solution in my mind that I will add another field in the table that is active. I will set active value to 0.

Thank you ! :-)
Photo of Petra V.

Petra V., Champion

  • 7794 Posts
  • 1391 Reply Likes
1.
INSERT OR REPLACE 

INTO posts_data (id, fname, lname, address)
VALUES (100, 'Abhishek1', 'Deshkar1', 'new address');

The other fields will get their default value (which is usually null, '' or 0, depending on the filed type)

2.
You can set the active value to 0, but you must also delete the contents of all user fields (except the primary key, of course).

My feeling is, that you are making this way too complicated. You don't need to "insert or update". You don't need to delete specific records. And you don't need a active=0 value (unless you desperately want to notify the user of removals).
As I wrote earlier:
[Unless you] always make a full refresh of the local database (e.g.: if JSON data are imported, first empty the database, then insert all data from the JSON. Never update data)
Photo of Abhishek Deshkar

Abhishek Deshkar

  • 48 Posts
  • 0 Reply Likes
Okay then I will choose full refresh method. But I also want to show the notification that this user has changed his address or user has added something. When I'm doing full refresh how would I able to do this ? Do I need to make separate table to store changes done by the user?
Also I'm planning to make phone number as a Unique ID. So no ID column is longer required. Will it be okay ?
Thank you!