speedftw Posted August 14, 2012 Share Posted August 14, 2012 I'm trying to use php and postgresql to generate a kml file (google earth) that have information of multiples places. So I have multiples queries to retrieve all the information that I need. Example: SELECT c.latitude, c.longitude FROM Circuit AS b, Instance AS c, InstanceNumber AS d WHERE c.id = d.Instance AND d.Circuit = b.id AND b.code = 'Z3D2' SELECT c.number, b.model, e.pollution FROM Asset AS a, Model AS b, TowerNumber AS c, Line AS d, Instance AS e WHERE COALESCE(a.Model,0)=b.id AND c.Asset=a.id AND a.Line=d.id AND e.Asset=a.id AND e.Source=1 AND d.name='Z3D2' The field "number" in both TowerNumber and InstanceNumber should be similar. I can't get all the information with just one query because some field are null for Instance.source and others are not. Besides that the foreign Keys that connect tables are not filled in all tables so I have to do different "ways" to get the information. 1st: Line->Asset->TowerNumber->Instance 2nd: Line->Circuit->InstanceNumber->Instance For Source='SD'which as 'latitude' and 'longitude' I have two use the 1st path. For the other information that have Source="HD" I've to use the 2nd path. I have these two queries as an example. What I want to do is get the information in this two queries and create and array like: number | model | pollution | latitude | longitude 0 | ZD1 | 7 | 48,48 | 2,22 1 | H3 | 6 | 48,49 | 2,24 ... | ... | ... | ... | ... And then I'll just write that information to the kml file where each place should have all the information and should look like: <Placemark> <name>$table[number]</name> <ExtendedData> <Data name=name_field_1> <value>$table[model]</value> </Data> <Data name=name_field_2> <value>$table[pollution]</value> </Data> ... ... <Point><coordinates>$table[longitude],$table[latitude]</coordinates></Point> </Placemark> Quote Link to comment https://forums.phpfreaks.com/topic/267071-do-and-write-a-table-with-results-from-multiples-queries-to-kml-file/ Share on other sites More sharing options...
btherl Posted August 14, 2012 Share Posted August 14, 2012 Ok that's fairly clear. What exactly is your question though? Quote Link to comment https://forums.phpfreaks.com/topic/267071-do-and-write-a-table-with-results-from-multiples-queries-to-kml-file/#findComment-1369455 Share on other sites More sharing options...
speedftw Posted August 15, 2012 Author Share Posted August 15, 2012 What I want to do is get the information in this two queries and create and array like: number | model | pollution | latitude | longitude 0 | ZD1 | 7 | 48,48 | 2,22 1 | H3 | 6 | 48,49 | 2,24 ... | ... | ... | ... | ... I don't know how to make a 2d array from the results of the queries. Quote Link to comment https://forums.phpfreaks.com/topic/267071-do-and-write-a-table-with-results-from-multiples-queries-to-kml-file/#findComment-1369527 Share on other sites More sharing options...
Christian F. Posted August 15, 2012 Share Posted August 15, 2012 From what I'm seeing there is no need for two queries, as you're trying to print out data which has a 1-1 relation. The data you're getting in your first query is even in one of the tables you're fetching in the second one, making it completely superfluous. Just add those to fields to the second query, and you should be set. PS: You will also want to rewrite your query, to specify the JOINs properly. Using intrinsic joins like this can seem easier to start with, but they're a lot harder to maintain. Also, it's quite easy to make a Cartesian product with them, which would increase the data retrieved exponentially. Quote Link to comment https://forums.phpfreaks.com/topic/267071-do-and-write-a-table-with-results-from-multiples-queries-to-kml-file/#findComment-1369592 Share on other sites More sharing options...
speedftw Posted August 15, 2012 Author Share Posted August 15, 2012 I can't get all the information with just one query because some field are null for Instance.source and others are not. Besides that the foreign Keys that connect tables are not filled in all tables so I have to do different "ways" to get the information. 1st: Line->Asset->TowerNumber->Instance 2nd: Line->Circuit->InstanceNumber->Instance For Source='SD'which as 'latitude' and 'longitude' I have two use the 1st path. For the other information that have Source="HD" I've to use the 2nd path. As I try to explain because the fields that I need for each point are not acessible from a single query. The Instance table has: - id - id_asset - id_model - source - latitude - longitude - pollution Example of instance: id | id_asset | id_model | source | latitude | longitude| pollution 1 100 NULL SD 42,4 2,2 NULL 2 100 3 HD NULL NULL Medium 3 22 12 HD NULL NULL High So, for the same asset (place) that I want to put in the kml file I have the information in the same table but in different rows. If I do as you said I get no results from the query. Besides, I have more queries that I want to do not just these two. Quote Link to comment https://forums.phpfreaks.com/topic/267071-do-and-write-a-table-with-results-from-multiples-queries-to-kml-file/#findComment-1369612 Share on other sites More sharing options...
Christian F. Posted August 15, 2012 Share Posted August 15, 2012 I can't get all the information with just one query because some field are null for Instance.source and others are not. That's what you have LEFT/RIGHT joins for. Besides that the foreign Keys that connect tables are not filled in all tables so I have to do different "ways" to get the information. This, I think, is the original problem. If you have two (or more) tables that are connected, the Foreign Key(s) should always be filled. If it is not, then you're stating that the rows are not associated with each other. You'd find that the solution to your problem would be quite easy, as long as you first fixed the missing foreign keys before putting a constraint on any future foreign keys. If that is not possible (and I mean impossible, not that you just don't want to do it), then you'll need to pull the data out from each table individually. Then use a function to loop through the results, and return the finished product per table. Concatenate it to the results from the previous tables, and you're set. Quote Link to comment https://forums.phpfreaks.com/topic/267071-do-and-write-a-table-with-results-from-multiples-queries-to-kml-file/#findComment-1369685 Share on other sites More sharing options...
speedftw Posted August 18, 2012 Author Share Posted August 18, 2012 If that is not possible (and I mean impossible, not that you just don't want to do it), then you'll need to pull the data out from each table individually. Then use a function to loop through the results, and return the finished product per table. Concatenate it to the results from the previous tables, and you're set. That's exactly what I need. But I don't know how to concatenate all the results from the queries. Quote Link to comment https://forums.phpfreaks.com/topic/267071-do-and-write-a-table-with-results-from-multiples-queries-to-kml-file/#findComment-1370396 Share on other sites More sharing options...
Christian F. Posted August 18, 2012 Share Posted August 18, 2012 You don't concatenate the results from the queries, you concatenate the finished output from the function that processes the results. Quote Link to comment https://forums.phpfreaks.com/topic/267071-do-and-write-a-table-with-results-from-multiples-queries-to-kml-file/#findComment-1370406 Share on other sites More sharing options...
speedftw Posted August 18, 2012 Author Share Posted August 18, 2012 Yes, I didn't explain well what I was trying to say. So, I do the queries and the do a loop which get the output from each query. Now, how do I concatenate the output? Quote Link to comment https://forums.phpfreaks.com/topic/267071-do-and-write-a-table-with-results-from-multiples-queries-to-kml-file/#findComment-1370553 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.