Hi all, back into Buzztouch after a bit of a break. I have a php/MySQL question that has had me stumped for a couple of days now. My app runs a php/MySQL query (thanks @chris1 and @smugwimp), pulls the external data in and gives me nice red pins and callouts with all the info returned from the query. When I click the callout bubble for a given pin, the app loads up a new screen using the 'loadScreenObject" function, which calls a "BT_screen_htmlDoc" plugin, which is populated by a second php script that should provide further info on each unique record. I've called that second php file 'metamap.php'. The mechanics work well and I have metamap.php serving up content... but at present it is just placeholder html content, not dynamically generated. My challenge is with the php. I need metamap.php to serve up only additional data that relates specifically to the particular callout/pin that gave rise to it in the first place. Here's the php from the first script (well, the relevant bits anyway): $SQL = "SELECT * FROM location WHERE postal_code='60601'"; $result = mysql_query($SQL); //Create the pins and callouts displayed on the mapscreen using "location", titles "title" and their subtitles "subTitle"; put it into JSON for BT: $outputString = '{"childItems":['; $childItemString = ""; $i=0; while ( $db_field = mysql_fetch_assoc($result) ) { $i++; $childItemString .= '{"itemid":"location' . $i . '","itemType":"BT_mapLocation","latitude":"' . $db_field["latitude"] . '","longitude":"' . $db_field["longitude"] . '","title":"' . $db_field["id"] . '","subTitle":"' . $db_field["street"] . ' ' . $db_field["city"] . ' ' . $db_field["postal_code"] . '", "loadScreenObject":{"itemId":"26E5DD65713801C423C182B", "itemType":"BT_screen_htmlDoc", "itemNickname":"metamap.php", "navBarTitleText":"metamap screen", "navBarRightButtonType":"home", "navBarRightButtonTapLoadScreenItemId":"goHome", "dataURL":"http://www.website.com/metamap.php"}},'; } You can see the 'loadScreenObject' function above, calling metamap.php to serve its data to the BT_screen_htmlDoc plugin. I now need to grab the value of the table field "id", which has already been used above as the map pin's 'title', and using this unique id, present some further data from other tables within the database. So, when I tap the callout, metamap.php needs to take the unique "id" value for that location, run another query with it and serve up the results. So I THINK my question is, how to set a variable here in this php (or in metamap.php) that lets the unique identifier in the "id" field be re-used, and keep the cascade of information true. Remembering that there are multiple records returned from this query, so each 'cascade' will be unique. I've tried a bunch of things from eg $newid = $row['id'] all the way to re-querying the same table again and trying to define this unique "id" value as equal to the result of the query. I have a 'require' connection between metamap.php and the initial php/MySQL query file so it should be able to use the same query results and pass them through when metamap.php is called. I'm not expecting anyone to give me code, but would be extremely grateful if anyone could suggest an appropriate method to use or place to go looking. The googleverse seems to be very good at hiding the solution from me, which probably just means that as a php newbie I don't know what I should be searching for in the first place. I'm hoping someone in BT land must have had the same challenge before! All thoughts much appreciated. Cheers Paddy.

It should be reasonable to add additional 'identifiers' onto your metamap.php URL… like: ..."dataURL":"http://www.website.com/metamap.php?otherID=someotheruniquevalue"}},; And in your next php just use the 'some other unique value' as part of your query $someotheruniquevalue = $_GET['otherID']; result = mysql_query("SELECT * FROM dbrecords WHERE fieldvalue LIKE '$someotheruniquevalue'") or something along those lines? Cheers! -- Smug

Wow, I had no idea you could do that Smug. I'll have a play and try to get my head around the seemingly circular logic - thanks for the new toy! Cheers Paddy.

Paddy, As Smug says, you can easily parse data within the url call to be used in the next php page. eg. if you look at the http call for this forum page, http://www.buzztouch.com/forum/thread.php?fid=A553CC9FD378EA7FBB68E2A&tid=A553CC9FD378EA7FBB68E2A you see that the variables fid & tid were assigned data that was used to locate this topic in the BT database. From your code above I assume your database is indexed by the locations title which is the 'id' variable so your dataurl would be something like: http://www.website.com/metamap.php?otherID=$db_field["id"] then in your metamap.php you just need to use the sql search $result = mysql_query("SELECT * FROM dbrecords WHERE id=$otherID") to get the required data and then display it. Regards, Paul

Back into it again after an unfortunately unavoidable 3mth break. And 4 hours (plus 3 months!) later, I might have made some progress. Can I ask that someone has a scan over this and see why it's not coming through? OK, I have two php files talking to each other using the dynamic variable suggestions made earlier and when I run them in a browser they give me the sort of output I need (which is JSON, to send to the app plugin, right?) but the iOS simulator tells me that the JSON is not well-formed. My config file's JSON checks out ok in JSONLint so it must be in what the php is sending to the simulator. If you run this http://www.noshplanet.com/metamap.php?otherID=$db_field["lid"] in your browser you can see what it's giving me. (That metamap.php requires a previous result www.noshplanet.com/testchild.php which itself gets access to the dbase from a config.php file. So they appear to be cascading nicely. Just not showing up on the simulator.) Now, when I throw the output of www.noshplanet.com/metamap.php?otherID=$db_field["lid"] into JSONLint it tells me that I have this error: Parse error on line 17: ...?otherID=$db_field["lid"]" } -----------------------^ Expecting '}', ':', ',', ']' - but when I try to correct that in the php's JSON formatting, it doesn't seem to make any difference. Any ideas much appreciated!

It looks like it might be freaking out over all the " characters… JSON kinda has to be in that "key":"value" format only, or it might just balk at you. You might try substituting a single ' rather than a double, and see if that helps. $db_field['lid'] Also, try leaving it off entirely $db_field[lid] a lot of systems can handle it, and the worst thing that can happen is it won't work; you won't 'break' anything, you just might not 'get' anything… It's all worth a shot :) Cheers! -- Smug

Thanks Smug, I'll give it a shot. So far removing the " completely from "lid" hasn't changed any browser output so I'm, hopeful. (and re-publishing and downloading the whole thing has now given a new JSON error and an odd repetition of item definitions - will post separately.)

OK, so it WAS the " thingys in $db_field["lid"] that were messing up the JSON after all, and losing them doesn't seem to have affected what the php sends to my browser. However I'm still not getting a single 'thread' of output from these info buttons on the pin callouts in the app or browser... I have no doubt at all that it's my poor php. Would you (or anyone!) be kind enough to take a look at it and see where I might need to concentrate on learning a bit more about what I'm doing wrong? Thanks!

Can I see a bit of your JSON output? Cheers! -- Smug

Sure can, thanks Smug. Here's what comes out at the end of it all in the BT_screen_htmlDoc. (The BT_screen_htmlDoc is what 'happens' when the info button is tapped on the callout.) It's displaying all the JSON that relates to the original set of pins/records and is filling in their details according to the query. This is what appears on the app screen: {"childItems":[{"itemid":"location1","itemType":"BT_mapLocation","latitude":"41.886879","longitude":"-87.623817","title":"118","subTitle":"233 North Michigan Avenue Chicago 60601", "loadScreenObject":{"itemId":"26E5DD65713801C423C182B", "itemType":"BT_screen_htmlDoc", "itemNickname":"metamap.php", "navBarTitleText":"metamap screen", "navBarRightButtonType":"home", "navBarRightButtonTapLoadScreenItemId":"goHome", "dataURL":"http://www.noshplanet.com/metamap.php?otherID=$db_field[lid]"}},{"itemid":"location2","itemType":"BT_mapLocation","latitude":"41.888195","longitude":"-87.624013","title":"127","subTitle":"111 East Wacker Drive Chicago 60601", "loadScreenObject":{"itemId":"26E5DD65713801C423C182B", "itemType":"BT_screen_htmlDoc", "itemNickname":"metamap.php", "navBarTitleText":"metamap screen", "navBarRightButtonType":"home", "navBarRightButtonTapLoadScreenItemId":"goHome", "dataURL":"http://www.noshplanet.com/metamap.php?otherID=$db_field[lid]"}},{"itemid":"location3","itemType":"BT_mapLocation","latitude":"41.884538","longitude":"-87.620775","title":"128","subTitle":"200 E Randolph Drive Chicago 60601", "loadScreenObject":{"itemId":"26E5DD65713801C423C182B", "itemType":"BT_screen_htmlDoc", "itemNickname":"metamap.php", "navBarTitleText":"metamap screen", "navBarRightButtonType":"home", "navBarRightButtonTapLoadScreenItemId":"goHome", "dataURL":"http://www.noshplanet.com/metamap.php?otherID=$db_field[lid]"}}]}$db_field[lid] Compare this with the JSON in the php that runs the initial dbase query: $childItemString .= '{"itemid":"location' . $i . '","itemType":"BT_mapLocation","latitude":"' . $db_field["latitude"] . '","longitude":"' . $db_field["longitude"] . '","title":"' . $db_field["lid"] . '","subTitle":"' . $db_field["street"] . ' ' . $db_field["city"] . ' ' . $db_field["postal_code"] . '", "loadScreenObject":{"itemId":"26E5DD65713801C423C182B", "itemType":"BT_screen_htmlDoc", "itemNickname":"metamap.php", "navBarTitleText":"metamap screen", "navBarRightButtonType":"home", "navBarRightButtonTapLoadScreenItemId":"goHome", "dataURL":"http://www.noshplanet.com/metamap.php?otherID=$db_field[lid]"}},'; and you can see that all this is somehow being passed along to the next stage of the flow after being executed. I've used the extra identifiers in that last dataURL to try and isolate the unique location id ("lid") in the final php file, which does this: $uniqueinfo = $_GET["otherID"]; $result = mysql_query("SELECT * FROM dbrecords WHERE lid=$otherID"); echo $uniqueinfo; echo $result; ... but neither of those echoes (I'm trying multiples!!) are giving me anything. What I am hoping for is simply one unique number that corresponds to the dbase field "lid" for the particular dbase record. (eg for the first record above, the number would be 118. I know it needs to be formatted with css eventually but for now I'm just hoping to see a bare number.) Instead it seems to be grabbing the field values for each record (in this case there are 3) and displaying them all in this JSON format rather than a html format. I haven't worked out how to single them out which is why I'm pretty sure it's my php code that's wonky. Thanks for taking a look. Cheers Paddy

edit: removed duplicate

@Paddy, maybe try this! Change your url call from "dataURL":"http://www.noshplanet.com/metamap.php?otherID=$db_field[lid]"}},'; to this "dataURL":"http://www.noshplanet.com/metamap.php?otherID=' . $db_field["lid"] . '"}},'; and cross your fingers! The main problem I can see is that it's not assigning an actual value to 'otherID', it is just assigning the text $db_field[lid] as the 'otherID' value whereas the $db fields above it seem to be being assigned correctly. Paul :)

@Paul, you're a genius mate! Thank so much for that - it works beautifully. I'm just gonna sit here and run that little simulator repeatedly for fun until it smokes!! Could I trouble you to explain why that actually worked? What do those dots before and after the $db_field["lid"] variable do to this? Next step: to use that otherID as bait to fish out other (different) fields, concatenate them and make the output actually interesting. I really appreciate the help from you and Smug on this. (This is really a much nicer place than stack overflow :[ - chalk & cheese.) Cheers Paddy.

Oops, spoke too soon. It's ALMOST there... I tap a callout's info button and I definitely get the correct 'lid' number due to Paul's genius. But now when I go back a screen and tap a different info button, I get the first one's number again. If I refresh the app though, it will work correctly for whichever info button I tap first, but it's carrying that value through to the others if I don't refresh. I guess it's something to do with needing to clear that value and re-running the script again... tonight's goal!! cheers Paddy

@Paddy, to be totally honest, No, I cannot fully explain why it worked!!! As I suggested, I noticed your syntax in previous code for setting the other json variables was working and that the syntax you had for the dataURl variable was different. My php ain't that flash also but what I do know is that it is picky sometimes with the use of the ' and the " and minor syntax errors can result in many lost hours! As for the . - as far as I know, like in other codes, they are what I refer to as 'joiners' - joining or connecting strings, variables etc. Why they are in your code? No idea, maybe if you delete them it will still work! A suggestion for the refresh problem is to change the plugins document behaviour. Set it to force refresh so each time the page is accessed it will reload again rather than refreshing the whole app. Regards, Paul :)

Thanks Paul. Forcing a refresh worked eventually - I say eventually b/c there's something interesting about the relationship between the JSON in the BT_config.txt and the JSON that the php is creating 'on the fly'. It goes like this: Even if BT_config.txt has this updated setting (ie "forceRefresh":"1") it doesn't actually do that refresh UNLESS the updated setting is also in the JSON that is being created in the php that's serving it through the dataURL. And playing around with various other settings, I can add things to the php (eg change the nav bar colour) WITHOUT changing the BT_config.txt and that will override/ignore/add to whatever is in the BT_config.txt. Conversely, if I make a change in the BT_config.txt but it isn't also changed in the php, then the php (with nothing updated in it) still overrides the BT_config.txt, and nothing changes. So it looks like the JSON generated by the php is definitely dominant over the JSON in BT_config.txt. Interesting. Moral of the story: when changing settings in BT for a screen that's fed by a dataURL, if the php that's sending the data includes JSON formatting, make sure you change the php's JSON (manually) to match the BT_config settings for that object. Feeling 0.0000001% more confident now. :) Cheers Paddy

Sorry so late. Busy busy. But I stuck my head up for a diversion. Paul has it right. Or right-er. The syntax might be a little off, but not by much. Change your url call from "dataURL":"http://www.noshplanet.com/metamap.php?otherID=$db_field[lid]"}},'; to this "dataURL":"http://www.noshplanet.com/metamap.php?otherID=' . $db_field["lid"] . '"}},'; You never want 'php' as part of your URL Query. Bad juju. Whatever value you want to append on your URL, you need to have that figured out before the json gets created. the correct 'php' output for paul's suggestion would be something like: echo('\"dataURL\":\"http://www.noshplanet.com/metampa.php?otherID=\"' . $db_field["lid"] . '\"}},'; when dealing with quotes in php, you need to be careful. both ' and " can sometimes be interchanged incorrectly. Heaven only knows how they're really used, but I follow one basic rule: If the output requires a ' then I use " for my php. If the output requires " then I use ' instead. For instance: echo ('<a href=\"http://www.someplace.com\">a link</a>'); also remember to add reverse slashes preceding special characters. There was discussion and resolution of 'app refresh' here: https://www.buzztouch.com/forum/thread.php?fid=CD7F026EFACF2F3DCE0B4B2&tid=CD7F026EFACF2F3DCE0B4B2 If I've forgotten something please remind me. Are things working as you want, or moving in that direction? Hows things? Cheers! -- Smug

Smug, we are so lucky to have you in our community. You are a treasure!

Thanks Smug, I have it all working (for this baby step anyway!) - Paul's and your suggestion for "dataURL":"http://www.noshplanet.com/metamap.php?otherID=' . $db_field["lid"] . '"}},'; works beautifully. Even the refresh, once I added the forceRefresh to the php as well as to BT. But I'll go back and check on those special characters as well - no-one wants bad juju!! No doubt I'll break it in the process so I'll be back here before too long... I'm still trying to understand the relationship between the JSON in the BT_config.txt and the JSON that's being created within my php file. The thing that's doing my head in is that there are two places where the SAME dataURL is being called: firstly in BT, where I have it as the dataURL that's populating a BT_htmldoc screen (my meta-data about each map pin): { "itemId": "26E5DD65713801C423C182B", "itemType": "BT_screen_htmlDoc", "itemNickname": "metamap.php", "navBarTitleText": "data from metamap.php", "navBarBackgroundColor": "#FF9933", "navBarStyle": "solid", "navBarRightButtonType": "done", "navBarRightButtonTapLoadScreenNickname": "Mymap", "navBarRightButtonTapLoadScreenItemId": "2FCE8AD101DBEDB5D5C33F8", "navBarRightButtonTapTransitionType": "slideUp", "dataURL": "http://www.noshplanet.com/metamap.php?otherID=' . $db_field['lid'] . '", "forceRefresh": "1" } and second, in my php file, where it is called because it is part of the definition of the childItem that is the info button that calls that same BT_htmldoc screen! Here's that code: $result = mysql_query($SQL); $outputString = '{"childItems":['; $childItemString = ""; $i=0; while ($db_field = mysql_fetch_assoc($result) ) { $i++; $childItemString .= '{"itemid":"location' . $i . '","itemType":"BT_mapLocation","latitude":"' . $db_field["latitude"] . '","longitude":"' . $db_field["longitude"] . '","title":"' . $db_field["lid"] . '","subTitle":"' . $db_field["street"] . ' ' . $db_field["city"] . ' ' . $db_field["postal_code"] . '", "loadScreenObject":{"itemId":"26E5DD65713801C423C182B", "itemType":"BT_screen_htmlDoc", "itemNickname":"metamap.php", "navBarTitleText":"data from metamap.php", "navBarBackgroundColor":"#FF9933", "dataURL":"http://www.noshplanet.com/metamap.php?otherID=' . $db_field["lid"] . '", "forceRefresh": "1"}},'; } $outputString .= fnRemoveLastChar($childItemString, ','); $outputString .= "]}"; echo $outputString; Here's how I understand it: BT creates a map screen. BT's JSON (humour me, I know it's native code eventually) tells the app to go get that screen's content from a php file. The php file grabs data from the remote database and sends it to the map screen. But here's what's killing me: the data it sends is in JSON format, and part of that JSON includes a spec to use the SAME dataURL to find and supply data that will be used as child data. (And it's that dataURL that you're saying CAN be 'php', but the one in BT_config.txt shouldn't be, is that right?) It's a character flaw of mine to try to understand stuff to the nth degree, so if this is just witchcraft and I need to accept it as that, please let me know and put me out of my misery. Otherwise, if you have any idea as to why both are needed and what one does differently to the other, it would be awesome to understand. Next steps: I'm going to try and pull apart your 'search by proximity' tutorial and see if I can add that as a user option; I'm going to try and find a way to search the database by keywords entered into the app "eg find all food trucks in [type in your city]'; and then I'm going to try and concatenate MYSQL search results from different tables and display them on the same metadata screen. I reckon that should take me, oh, until about 2017! :) Thanks again - Susan's right! Cheers Paddy

https://www.youtube.com/watch?v=oFmNgiEgPoQ The thing that I keep looking at funny is the line in your HTML doc's 'dataURL' property… "dataURL": "http://www.noshplanet.com/metamap.php?otherID=' . $db_field['lid'] . '" In *my* mind, this is something that should have been figured out before it got to the JSON. In other words, the dataURL 'should' look more like this: "dataURL": "http://www.noshplanet.com/metamap.php?otherID=01234" 01234 being the desired ID of the record you're trying to get to. The dataURL should show up as a 'finished product'; either to the resource, or another valid dataURL… It should not contain PHP. ('should' not. I'm not saying it wont work). How does your script interpret the dataURL currently? specifically, when your HTMLDoc dataURL is called, how does your script interpret $db_field['lid'] ?? The word of the day is 'cascade'. Menus, and everything should 'flow' from one screen to another, without the PHP showing up in your JSON. So, your initial menu dataURL, on whatever screen you attach it to, should be calling for the next resource needed; either screen, or additional JSON via php script. So, the way it 'might' cascade would be something like this: The menu dataURL would populate the menuItems the menuItem dataURL would call the next screen the next Screens dataURL would populate the screen. Chew on that for a second, and let me know what questions pop in from that. Cheers! -- Smug

PaddyO--You have found the right place when you found Buzztouch. Buzztouch code is open source, so it is meant to be understood.

Hehe, thanks for the thinking music Smug! It's always the right time of day for a bit of ol' blue eyes! OK, I get what you're saying, it makes sense. Except that I can't have something like "dataURL": "http://www.noshplanet.com/metamap.php?otherID=01234" because that would always give a fixed result for the otherID value. It needs to be dynamically generated based on whichever map pin it pertains to. But that's all solved - thanks @bigPaul - no probs now. Here's how: based on what I saw happening with the forceRefresh parameter (ie, the JSON for that parameter as defined in the php file overrode JSON in BT_config.txt), I figured that as I already had "dataURL": "http://www.noshplanet.com/metamap.php?otherID=' . $db_field['lid'] . '" in the php file, maybe the BT_config.txt didn't need ANY dataURL ref at all for this screen, because its content is already coming from the php script (which is what you were saying, yes?). So I thought why not get rid of it completely? And guess what: it turns out that's entirely workable. I need virtually nothing in the BT_config.txt definition relating to that screen, except its itemId, itemType and itemNickname. Everything else is taken care of in the JSON created by the php. What I found really interesting was that I can't even style the nav bar for the screen in BT_config.txt, it all has to come from the JSON made in the php. So my BT_config.txt is much cleaner, and you'll be happy to see that it now has absolutely no php code in it whatsoever! BT_config for that screen is now simply: { "itemId": "26E5DD65713801C423C182B", "itemType": "BT_screen_htmlDoc", "itemNickname": "metamap.php" } and the php file tells it to do/look this way: $result = mysql_query($SQL); $outputString = '{"childItems":['; $childItemString = ""; $i=0; while ($db_field = mysql_fetch_assoc($result) ) { $i++; $childItemString .= '{"itemid":"location' . $i . '","itemType":"BT_mapLocation","latitude":"' . $db_field["latitude"] . '","longitude":"' . $db_field["longitude"] . '","title":"' . $db_field["lid"] . '","subTitle":"' . $db_field["street"] . ' ' . $db_field["city"] . ' ' . $db_field["postal_code"] . '", "loadScreenObject":{"itemId":"26E5DD65713801C423C182B", "itemType":"BT_screen_htmlDoc", "itemNickname":"metamap.php", "navBarTitleText":"data at metamap.php", "navBarRightButtonType": "infoLight", "navBarRightButtonTapLoadScreenNickname": "Mymap", "navBarRightButtonTapLoadScreenItemId": "2FCE8AD101DBEDB5D5C33F8", "navBarRightButtonTapTransitionType": "slideUp", "navBarBackgroundColor":"#FF9933", "dataURL":"http://www.noshplanet.com/metamap.php?otherID=' . $db_field["lid"] . '", "forceRefresh": "1"}},'; } $outputString .= fnRemoveLastChar($childItemString, ','); $outputString .= "]}"; echo $outputString; } To answer your question about how my script interprets the data URL correctly, the php code above is in the dataURL that puts the pins on the map. By giving each pin a URL that it points to, but making it a dynamic URL that's based on that otherID variable, the actual screen that loads and shows the supplementary info gets its data from script in the second (cascading!) php file, which simply asks for the appropriate value thus: $uniqueinfo = $_GET['otherID']; echo $uniqueinfo; and that's the content that shows up. I hope that makes sense! It definitely works. I'm so glad you posed that challenge Smug, I understand this teeny slice of my app so much better and my code is significantly cleaner now. Cheers Paddy

@Susan, you are so right! A great community, thank you.