Android Question SOLVED - Problem Updating mySQL db with array / JSON string via PHP .

mangojack

Well-Known Member
Licensed User
In the process of learning MySQl, PHP etc. I am attempting to export data from a local SQL db to mySQL via PHP script following examples in this thread by @KMatle Httputils2 send large arrays as JSON string via job download2 and PHP

I cannot send the entire (small) SQL table in one hit. I can send certain amount of records but there appears to be a maximum limit .. 6 Records.
Have run tests and scoured the forum to no avail.

Is there something I'm Missing .. JSON string size (doubted that .. but now reading threads raising this as possibility ...) , MySQL / Host settings etc

Many Thanks ...

B4X:
Sub btnSendBrowse_Click

   Dim JSONList As List
   JSONList.Initialize

   'get all records and populate map ... TABLE Browse_Links (ID INTEGER PRIMARY KEY, Tag TEXT , Title TEXT, url TEXT)")
   'There is 8 Records in this Table

   cursor1 = SQL1.ExecQuery("SELECT * FROM  Browse_Links")

   Dim numRows As Int = cursor1.RowCount  
   Log (numRows)     '### logs 8 records

   'For  i = 0 To numRows -1       '###  FAIL  mySQL db NOT updated !

   'For i = 0 To 5      '##  OK 6 records inserted
   'For i = 1 To 6     '##    OK 6 records inserted
   'For i = 2 To 7     '##  FAIL  Nil records inserted  ##
   For i = 3 To 7     '##    OK 5 records inserted

     Dim mapData As Map
     mapData.Initialize

     cursor1.Position = i
     mapData.put("ID", cursor1.GetInt("ID"))
     mapData.put("Tag",cursor1.GetString("Tag") )
     mapData.put("Title", cursor1.GetString("Title"))
     mapData.put("Url",cursor1.GetString("url"))

     JSONList.add(mapData)

     '###  all records listed correctly
     lvwData.AddSingleLine(cursor1.GetInt("ID") & "  " & cursor1.GetString("Tag") & "  " & cursor1.GetString("Title") & "  " & cursor1.GetString("url"))
   Next

   cursor1.Close

   Dim JSONGen1 As JSONGenerator
     JSONGen1.Initialize2(JSONList)

   Dim JSONstring As String
     JSONstring = JSONGen1.ToString

   Log (JSONstring)  '### log confirms all 8 records included

   Dim InsertJob As HttpJob
  InsertJob.Initialize("InsertBrowse", Me)
  InsertJob.download2("http://myhost.net/scripts/recipes/insert_script.php", Array As String ("Action", "InsertBrowse", "MyJSON", JSONstring))

End Sub
PHP ..
B4X:
case "InsertBrowse":

   $json = $_GET["MyJSON"];
  $jsall = array();
  $jsone = array();
  $jsall=json_decode($json, true);
  $x = 0;
  while($x < count($jsall)) {
      $jsone=$jsall[$x];

      $id=$jsone["ID"];
      $tag=$jsone["Tag"];
      $title=$jsone["Title"];
      $url=$jsone["Url"];

  
       $q = mysql_query("INSERT INTO browse_tbl (table_id, tag, title, url) VALUES ($id, '$tag', '$title', '$url')");
  
  $x++;
  }
     print json_encode("Inserted Records");
   break;
 
Last edited:

mangojack

Well-Known Member
Licensed User
Installing RDC on a remote server requires some administration knowledge. It also requires SSH access if it is a linux server. You will usually not be able to install it with a shared hosting account.
Trying to get a grip on installing RDC .. as the database is hosted on HostPapa.com ( a Shared Hosting Account ) does that rule out installing RDC.
I cannot see anything useful regarding this etc in the KB's

Plus .. As this is purely a learning exercise is there any compelling need forthe RDC approach
versus attempting to nut out why the initial problem occurred ( or is that method completely unreliable ?)

Any thoughts / comments welcome
 
Last edited:

mangojack

Well-Known Member
Licensed User
PHP Newbie as well .

Why $id is not in quotation marks?
Because its numeric value .. not string

Also, is it an autoincrement field?
Yes.

But I dont believe these are the issue ... If I send 3 / 4 / 5 ? / 6 records all OK .. any more than that a consistent Fail .

I'm Reading JSON string size can be limited to 8kb ??
 

sorex

Expert
Licensed User
that's the good thing about php, Luca.

you can insert variables into string without the ".$xxx." concat misery.

types faster and it reads a lot better like that
 

LucaMs

Expert
Licensed User
Well, I have an insert query in a php script in which also numerical variables has quotes and it works.

Maybe MySQL converts it from string to number.
(I dedicated only one day to PHP, sorry :)).
 

mangojack

Well-Known Member
Licensed User
just working on found examples and reading PHP sites ..

here is snippet by @DonManfred
B4X:
case "InsertNewPerson":
  #$name = $_GET["name"];
  if (isset($_REQUEST['name'])){name=utf8_decode($_REQUEST['name']);} else {name="";}
  #$age = $_GET["age"];
  if (isset($_REQUEST['age'])){$age=intval($_REQUEST['age']);} else {$age=0;}
  $q = mysql_query("INSERT INTO Persons (name, age) VALUES ('$name', $age)");
  print json_encode("Inserted");
 

LucaMs

Expert
Licensed User
My last stupid sentence (ehm... not stupid, only ignorant ;))

I also thought I need to test isset before use $_GET, to know if that field was passed.
 

mangojack

Well-Known Member
Licensed User
I will continue to read .. but in the meantime I'm hopeful someone possibly has a reason post #1 does not work.
Under the circumstances I have a workaround (do the initial export in batches ...) but it would be good to know the reason..
some sort of string size limit PHP setting ???
 

sorex

Expert
Licensed User
your problem is the use of $_GET.

change it to $_POST and post in the B4A aswell and it will work.
 

sorex

Expert
Licensed User
$_GET is limites in size since that's just added right after the url as ?parameter=whatever
 

LucaMs

Expert
Licensed User
This is what I love: use POST instead of GET :confused: (I know, this is not PHP).

Use Set, Let instead of Get, Read :D

Or Download2 to execute a query !

I go crazy very quickly ... no, maybe this has already happened :p
 

sorex

Expert
Licensed User
the lenght of GET depends on the browser so there is not valid size limit. on IE it can be 2048 chars and on firefox 1024 just to give an example.

not sure what it on on android.
 
Top