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

Discussion in 'Android Questions' started by mangojack, Jun 29, 2015.

  1. mangojack

    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 ...

    Code:
    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 ..
    Code:
    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: Jun 29, 2015
  2. Erel

    Erel Administrator Staff Member Licensed User

    I recommend you to switch to RDC if you can. It will be faster, safer and more reliable.
     
    Peter Simpson likes this.
  3. mangojack

    mangojack Well-Known Member Licensed User

    How did I know you were going to say that ... ;) Thanks.
     
  4. mangojack

    mangojack Well-Known Member Licensed User

    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: Jun 29, 2015
  5. LucaMs

    LucaMs Expert Licensed User

    I'm a newbie to PHP.

    Why $id is not in quotation marks?

    Also, is it an autoincrement field?
     
  6. mangojack

    mangojack Well-Known Member Licensed User

    PHP Newbie as well .

    Because its numeric value .. not string

    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 ??
     
  7. LucaMs

    LucaMs Expert Licensed User

    I thought the quotes were used to indicate to use the contents of the variable, not to indicate that it is of type string.
     
  8. sorex

    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
     
  9. LucaMs

    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 :)).
     
  10. mangojack

    mangojack Well-Known Member Licensed User

    just working on found examples and reading PHP sites ..

    here is snippet by @DonManfred
    Code:
    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");
     
  11. LucaMs

    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.
     
  12. mangojack

    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 ???
     
  13. LucaMs

    LucaMs Expert Licensed User

    8 record x 4 fields... I don't think of size limits (any kind)
     
  14. sorex

    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.
     
  15. sorex

    sorex Expert Licensed User

    $_GET is limites in size since that's just added right after the url as ?parameter=whatever
     
  16. LucaMs

    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
     
  17. mangojack

    mangojack Well-Known Member Licensed User

    Sorry @sorex ... I'm now confused !
    are your replies pointing to me or @LucaMs
     
  18. sorex

    sorex Expert Licensed User

    to you, both and everyone else :)
     
    LucaMs likes this.
  19. LucaMs

    LucaMs Expert Licensed User

  20. sorex

    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.
     
Loading...