Creating a dynamic query for Google Fusion Tables

hi,

The following fusion tables chart uses an sql query to populate the chart:

http://enex.net/autorecon/fusion/GC_network.html

How can i edit the query to filter for say keyword "bingo" ? Ideally as a variable so as a follow-up question/project I can link this to a input search box and start developing the page.

<iframe width="1000" height="600" scrolling="no" frameborder="no" src="https://fusiontables.google.com/embedviz?containerId=googft-gviz-canvas&amp;viz=GVIZ&amp;t=GRAPH&amp;gc=true&amp;gd=true&amp;sdb=1&amp;rmax=100000&amp;q=select+col1%2C+col2%2C+col0+from+1Gk2Gpw70hvXqjDoXVcAu8G_7xTwIv4jrhsHiEkL7&amp;qrs=+where+col1+%3E%3D+&amp;qre=+and+col1+%3C%3D+&amp;qe=&amp;uiversion=2&amp;state=%7B%22ps%22%3A%221_7_-16_-f_v_cz_4_w_cg_-q_n_-6v_-1f_o_-6r_-27_a_8g_1_9_8r_-m_30_-a_1_4_-c7_-1f_5_-bs_-1t_3x_-df_-34_3y_-d0_-3i_5p_4y_1p_5o_52_29_50_-m_-11_37_8v_-3z_36_89_-3w_3a_6o_o_3b_6t_1c_1p_-c_-o_1j_-9d_-15_1k_-97_-h_3j_-da_4_3k_-dt_-g_3_-e9_1e_2_-dk_1w_5r_-1n_-19_cv_-1h_i_e_ds_-3h_d_da_-3x_3f_f0_1a_7e_-11_-1g_iw_f6_-1q_iv_ex_-2i_q_-75_1u_p_-7o_27_24_7z_32_23_87_3n_16_56_-24_17_63_-26_az_-1z_b_gb_-26_-u_5z_-e1_-3t_5y_-dn_-4d_6m_ab_3i_6n_ag_2s_in_ci_-48_io_d0_-4h_5w_-ap_2t_5v_-9r_28_s_-l_-1f_jd_-10_q_lm_3a_-26_ll_34_-2y_3v_62_-48_3u_6n_-3y_li_-4s_-3p_kj_3x_-17_ki_3b_-1l_2d_bb_-3k_2e_aq_-3j_i2_-n_r_lh_-46_-3z_5q_-1d_-1q_j1_d9_49_j0_dq_48_1b_-83_3e_1a_-88_2p_3e_f8_-5_m1_-7w_3u_m0_-8e_45_cq_-bz_30_cr_-cl_2r_6z_-2k_3v_6y_-19_42_cp_8u_4a_co_8b_47_3c_ew_32_3d_eb_3f_1c_-e_-1q_m2_-6_q_1r_6o_2y_1q_67_22_g0_-11_15_2v_e3_2d_2w_dg_2t_32_40_a_33_3y_-8_ez_-1j_-45_ey_-p_-41_mw_-6p_4e_mv_-66_4i_nd_-9j_30_ne_-a3_3f_4s_f9_f_dt_-1a_-21_51_-c_10_88_-2q_1_a5_-at_-d_a4_-af_-t_a9_-5v_b_a8_-5t_-7_e4_7_-1f_m8_0_-1p_57_-m_18_ur_7c_-2t_uq_7e_-3b_ty_-48_4l_tz_-4g_49_le_-2f_-9_1e_-6_-1z_tx_-17_1f_u6_-o_-27_7v_aj_2a_7u_az_2e_gq_-f_-24_ay_-1h_1d_pz_93_-33_q0_9e_-2p_8h_-e1_28_8i_-eg_2p_i8_-v_1h_c3_-16_-2c_vz_6w_-2h_w0_6e_-1q_18_4o_-2x_19_45_-30_p6_6k_-3d_p5_71_-3k_cd_-1r_-1v_b0_-2g_4_c1_-ag_10_c2_-9v_13_9e_-2k_-u_10_-2b_o_nk_-1m_1j_6p_-8p_3o_6o_-8l_37_8l_dh_38_8m_bu_3o_ka_-2m_-g_aa_-1j_-2e_ev_-1y_1h_ma_f7_-14_m9_f2_-i_22_-2f_-18_ga_-25_z_a7_5r_-i_a6_6a_-c_g1_3o_2v_g2_34_2r_hf_-bq_-4l_tw_-cj_-4k_o5_-2b_-26_ou_-9e_y_ov_-%22%2C%22cx%22%3A21%2C%22cy%22%3A-33%2C%22sw%22%3A1236.1335687288026%2C%22sh%22%3A474.362769038141%2C%22z%22%3A0%7D&amp;gco_forceIFrame=true&amp;gco_hasLabelsColumn=true&amp;width=1000&amp;height=600"></iframe>

Open in new window


Here is the documentation, but i'm such a newbie here not sure where to start! A WHERE clause needs to added but not sure of the syntax here, and how to add this as a variable in say javascript.
https://developers.google.com/fusiontables/docs/v2/sql-reference

Thanks
xeniumAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Where would the keyword bingo exist - I would need to know what table / column to look in.
xeniumAuthor Commented:
Let's say column 1 for now, there seem to be 3 columns in the SQL query, i would want to be able to filter on either of the first 2, which I think is what generates the network chart, but trial and error would confirm this. I assume a start would be to edit the SQL query, but I'm not sure how it's encoded, or how to decode it:

q=select+col1%2C+col2%2C+col0+from+1Gk2Gpw70hvXqjDoXVcAu8G_7xTwIv4jrhsHiEkL7&amp;qrs=+where+col1+%3E%3D+&amp;qre=+and+col1+%3C%3D+&amp;qe=&amp

EDIT2: just found https://www.url-encode-decode.com/ and got this

q=select col1, col2, col0 from 1Gk2Gpw70hvXqjDoXVcAu8G_7xTwIv4jrhsHiEkL7&qrs= where col1 >= &qre= and col1 <= &qe=&
xeniumAuthor Commented:
I've tried fiddling with the WHERE clause and don't seem to get any change. However changing the select clause is having an impact, so i know i'm looking at the right file at least!
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Julian HansenCommented:
I just checked the docs and you appear to be using Fusion Tables 1 which was deprecated August 1 - Version 2 seems a bit easier to work with have you considered moving to that - it requires a Google API key
xeniumAuthor Commented:
I've done some digging and it appears that the charts feature is not yet available in the "new look"

The data was however imported using the "new look" interface:

https://fusiontables.google.com/DataSource?docid=1Gk2Gpw70hvXqjDoXVcAu8G_7xTwIv4jrhsHiEkL7

If there's a workaround for now that'd be great otherwise may have to wait/hope for the feature to be included in v2
xeniumAuthor Commented:
Ah, the interface offers a filter option which does appear in the HTML offered when publishing:

where+col2+contains+ignoring+case+&#39;bingo&#39;&amp;qrs=+and+col1+%3E%3D+&amp;qre=+and+col1+%3C%3D+&amp

and shows in the preview:
https://fusiontables.google.com/DataSource?docid=1Gk2Gpw70hvXqjDoXVcAu8G_7xTwIv4jrhsHiEkL7#chartnew:id=4

But does not appear to render in the published HTML:

<iframe scrolling="no" frameborder="no" src="https://fusiontables.google.com/embedviz?containerId=googft-gviz-canvas&amp;viz=GVIZ&amp;t=GRAPH&amp;gc=true&amp;gd=true&amp;sdb=1&amp;rmax=100000&amp;q=select+col1%2C+col2%2C+col0+from+1Gk2Gpw70hvXqjDoXVcAu8G_7xTwIv4jrhsHiEkL7+where+col2+contains+ignoring+case+&#39;bingo&#39;&amp;qrs=+and+col1+%3E%3D+&amp;qre=+and+col1+%3C%3D+&amp;qe=&amp;uiversion=2&amp;state=%7B%22ps%22%3A%221_a_67_u_d_3z_-2m_e_4v_-10_17_6i_-2d_16_66_-2u_1k_-5n_-2_1r_62_31_1q_56_2o_1c_o_-1g_1p_2_e_n_-65_-14_w_3c_f_9_1q_5_1e_1f_-21_s_1_-s_10_-2e_1o_7_-1s_k_5_-57_-2a_4_-4j_-1u_19_2s_-2x_18_4x_-36_o_-41_n_p_-4n_-l_3_-53_1c_2_-47_1l_22_-3g_-1s_v_33_15_1j_-53_-1f_1a_-61_-2b_1b_-5q_-1s_q_-5u_1j_23_2g_-2c_24_2n_-v_%22%2C%22cx%22%3A21%2C%22cy%22%3A-33%2C%22sw%22%3A1236.1335687288026%2C%22sh%22%3A474.362769038141%2C%22z%22%3A0%7D&amp;gco_forceIFrame=true&amp;gco_hasLabelsColumn=true&amp;width=500&amp;height=300"></iframe>

Open in new window

xeniumAuthor Commented:
Julian HansenCommented:
I can't answer your latest question - I can assist with getting the query setup on the URL for version 2 - beyond that I am unable to assist.
xeniumAuthor Commented:
Ok thanks I'll put this on hold and maybe post an aside to find a work-around for now.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fusion

From novice to tech pro — start learning today.