corsasport.co.uk
 

Corsa Sport » Message Board » Off Day » Geek Day » Google Sites/ Database query *Update*


New Topic

New Poll
  Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.


Author Google Sites/ Database query *Update*
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
19th May 14 at 13:46   View User's Profile U2U Member Reply With Quote

Dear web fanatics and IT boffins.


I am a complete n00b to all this web stuff and built my first website using Google Sites last year which has developed nicely.


However I have a database in Excel/Google Sheets format with a fair amount of entries on it, that I wish to make searchable and easy to use whilst not being monumentally slow, hosted on a webpage (or at least interactive/searchable)

The Google Doc is 31,000 entries and only 9 columns. This amount of entries will become smaller shortly but still expected to be at least half that.


I found a wicked example of what I want and how it could work here:

http://katiepiatt.blogspot.co.uk/2011/05/make-searchable-online-database-or-i.html

The trouble I have is two fold.

One, the database/sheet needs to be private. Two; I only have Google Sites which apparently doesn't allow php


I have read so much on this I am now cross-eyed and more confused than when I started. Anyone got any bright ideas what I could do?

I trialled loading the core spreadsheet in an iFrame and took about eleventy billion years to open, so Ctrl+F is not much of an option.


Please help

[Edited on 19-05-2014 by Jambo]

[Edited on 29-05-2014 by Jambo]
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
19th May 14 at 13:50   View Garage View User's Profile U2U Member Reply With Quote

31k rows is no problem at all.

How often will the data change?

Could import it in to to a MySQL db and put a little PHP front end on it, that would solve the problem but you'd still have the challenge of updating the data.

Might mean you need a more complicated PHP front end, or might mean you get familiar with phpMyAdmin.

Privacy can be sorted by authentication on the app, not five minutes but also quite possible if you have your own PHP. Need to decide whether you're going to go with a new set of user records each with a pass, or share one secret pass, or take your list of users from somewhere else.
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
19th May 14 at 14:07   View User's Profile U2U Member Reply With Quote

Data will change throughout the month, but only 2-3 entries at a time max. It remains largely unchanged just added to sporadically.


This is a little project for work that I was hoping I could have a stab at doing something basic. However it would appear that this is going to require an SQL license and PHP hosting... Meaning it will cost to much and be pushed back to the relevant department.


Balls.

PHP hosting much for a year with a domain etc?
VrsTurbo
Premium Member

Registered: 8th Jun 10
User status: Offline
19th May 14 at 19:20   View Garage View User's Profile U2U Member Reply With Quote

quote:
Originally posted by Jambo
Data will change throughout the month, but only 2-3 entries at a time max. It remains largely unchanged just added to sporadically.


This is a little project for work that I was hoping I could have a stab at doing something basic. However it would appear that this is going to require an SQL license and PHP hosting... Meaning it will cost to much and be pushed back to the relevant department.


Balls.

PHP hosting much for a year with a domain etc?


MYSQL is free for a small projects. MSSQL is stupid licence wise
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
19th May 14 at 19:24   View User's Profile U2U Member Reply With Quote

I won't go into details but I am involved in license infringement in one area of my job, double standards comes to mind
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
19th May 14 at 23:11   View Garage View User's Profile U2U Member Reply With Quote

No license with MySQL.

No hosting fee if I host it.

quote:
Originally posted by Jambo
PHP hosting much for a year with a domain etc?


http://my.vidahost.com/aff.php?aff=912

Don't forget your CS dizzy - CS10HOST

[Edited on 19-05-2014 by Ian]
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
20th May 14 at 07:13   View User's Profile U2U Member Reply With Quote

Cheers me dears. If it goes to requiring hosting etc, then its not impossible. But It really ideally needs to be done through Google sites as our corporate system runs through this and I can lock the sites to be viewable by anyone with a work.com email address.


I have read some things on there hinting that Google Apps Script can emulate/run (I don't know the proper lingo) PHP in an embedded object.

So I am looking into this, else it looks to be a fancy Excel spreadsheet or palming it off onto IT to sort out.

Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
20th May 14 at 10:31   View Garage View User's Profile U2U Member Reply With Quote

Can Google sites not iframe the faster externally hosted web site?

Will look like its coming from in there but in fact Google is bringing in the page content.
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
20th May 14 at 10:35   View User's Profile U2U Member Reply With Quote

Pretty sure it could, but it has a flaw. The iFrames never load until you confirm a security option on Chrome (The browser this will be used in)

Wont work smoothly enough for people here.


I am looking at using Google Apps script to see if I can perform searches, if not make elements to jazz up the spreadsheet.
Dom
Member

Registered: 13th Sep 03
User status: Offline
20th May 14 at 12:30   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by Ian
http://my.vidahost.com/aff.php?aff=912
Don't forget your CS dizzy - CS10HOST



Tsohost's ('same' company/datacentre) 'lite' hosting is cheaper at £15pa.


Jambo - If your company uses Google Apps (so i'm assuming everyone has a Google account), then why not use Google Sites (the link you've posted etc) and lock it to 'Private'?
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
20th May 14 at 12:36   View User's Profile U2U Member Reply With Quote

It is Dom, built a few of them.

issue being the lack of PHP for the database.

The issue is, I am doing this as a small project to be inexpensive. If it requires SQL and PHP stuff, it is totally possible but will be farmed out to a different department.

Wanted to get this done myself but its looking like the only real option I have is using the source Google Sheet and some filters, itl work but wanted something a bit cleaner with a good search function. Its basically a Libray database and users will be searching for different publications, the users need speed and simplicity. Google sheets is "fine" but will just be a bit clunky and messy.


Reading up on HTML database now, and using the built in Google search function on the google site, see if that helps.
Dom
Member

Registered: 13th Sep 03
User status: Offline
20th May 14 at 14:24   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by Jambo
It is Dom, built a few of them.

issue being the lack of PHP for the database.


My bad, thought publishing Google Spreadsheets (etc) as a 'site' integrated it with Google App Engine. Saying that, i believe Google offer free usage of their App Engine otherwise similarly Amazon offer a free package on their AWS service (you could deploy a small web server and use that to host the files).
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
20th May 14 at 15:28   View User's Profile U2U Member Reply With Quote

Just having a look at their app engine now (Google)

Says you can do PHP. But this will be an add on, not a seperate web page?

is there a way that could work if I put the code in there and make it an "app" and place that on the google site, using the Google doc as the database.

If that makes sense.
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
29th May 14 at 14:31   View User's Profile U2U Member Reply With Quote

Found another workaround

http://csessig.wordpress.com/2013/01/03/turning-excel-spreadsheets-into-searchable-databases-in-five-minutes/


Can upload the xml file to Google drive to host it, and in theory use it as a Gadget to make it work.


However, so far I just keep getting erorrs probably because I know bugger all about xml, Java and a teenie tiny amount of html


Frustrating. I tried loading it in the Google Apps editor and keep getting all sorts of xml syntax errors even though I am copying code from Google and other sources that are live and working! Probably the incorrect order, but I used the example above as a template and just changed the style sheets reference and datatables reference for their own cdn stuff which it recommends you do anyhow.

Looks like I am not going to be able to achieve this which is probably unsurprising, but nonetheless annoying!
Dom
Member

Registered: 13th Sep 03
User status: Offline
29th May 14 at 15:42   View User's Profile U2U Member Reply With Quote

Sounds like a complete bodge and arse-about way of doing things, especially as you'll have to continuously manually update the HTML table data. If you desperately want to use Google Spreadsheet's then you can publish (can fetch the data if the spreadsheet is private, just have to implement OAuth) the data as JSON and use that with one of the JQuery sortable table plugins (DataTables, Dynatable etc).

Still confused why you can't host this with the company (either internally on the intranet or on their internet hosting) or purchase some hosting or use Google Apps/Amazon AWS free tiers?


Edit - Similarly i'm confused why you're mentioning XML - there's no mention of it on the link you posted nor any need for it; all you're doing is a copy-pasta of the spreadsheet data and using 'Mr. Data Converter' to format it to HTML.

Edit 2 - Could you not use Access and forms? That way it could be stored on a network share.

[Edited on 29-05-2014 by Dom]
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
29th May 14 at 15:52   View User's Profile U2U Member Reply With Quote

We can Dom, but it wouldnt be me who sorted that kind of thing. The idea was with my limited knowledge I could knock up a basic searchable database online hosted on our Google Site to keep it within our network (i.e not published to wider web and only employees with correct email domain can log in)

Its entirely possible to get this done by the dedicated IT department who would have the knowledge and tools etc, this is simply a chance to get the job done on the cheap and "quickly" although this is turning out to not be the case

Thanks for the input, very helpful

I have since found using CoffeeCup and preview that my xml is infact OK and its more than likely the fact Google sites wont display it, so looking to go back to option one and get a .php hosted page here with a username/password if not, then it gets farmed out.

Cheers
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
29th May 14 at 16:01   View Garage View User's Profile U2U Member Reply With Quote

JQuery stuff means 31k rows client side - you sure?
Dom
Member

Registered: 13th Sep 03
User status: Offline
29th May 14 at 16:07   View User's Profile U2U Member Reply With Quote

If you've got an intranet site, can you not just ask them to store an ASP (server-side scripting language like PHP) file and create a SQL DB (or worse case use a Access DB) and you do the leg work?


Edit - Again, you're not handling XML data, rather just converting spreadsheet data to HTML and using a JQuery table plugin to make it sortable and look 'pretty'.

[Edited on 29-05-2014 by Dom]
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
29th May 14 at 16:16   View User's Profile U2U Member Reply With Quote

Thats my plan Dom, have asked them. Seeing what they come back with.

And Ian, I have no idea, but that sounds like a bad one!
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
29th May 14 at 16:39   View Garage View User's Profile U2U Member Reply With Quote

Just that your browser needs to download, query and sort the entire data set. I suppose that's not a complete disaster as you're probably running fairly new machines etc. but perhaps I'm old school, that used to be an absolute no no when I was a lad.
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
29th May 14 at 16:52   View User's Profile U2U Member Reply With Quote

I know nothing mate. My machine is brand new with SSD/i7 and a TB broadband line but others will struggle I guess. Defaulting to the PHP idea if I can get the hosting if not not my problem any more so thanks for all the info you beauties
Ian
Site Administrator

Avatar

Registered: 28th Aug 99
Location: Liverpool
User status: Online
29th May 14 at 17:17   View Garage View User's Profile U2U Member Reply With Quote

You're probably OK even on older stuff. Don't forget I learnt this in the late 90s
Jambo
Member

Registered: 8th Sep 01
Location: Maidenhead, Drives: VXR Arctic
User status: Offline
30th May 14 at 08:08   View User's Profile U2U Member Reply With Quote

lol. Well I am self taught on all of it and know very little. I am enjoying learning though, Feel like I should have been this inspired at school tho

 
New Topic

New Poll

  Related Threads Author Forum Replies Views Last Post
PHPBB2 Question Tom J Geek Day 24 528
19th May 07 at 11:39
by Tom J
 
Right, I have my hosting I have my domain mattk Geek Day 22 845
21st Aug 10 at 17:23
by Andrew
 
Website builders........ Jenko_Sport Geek Day 32 1916
13th Feb 12 at 17:39
by deano87
 
Friday Night MySQL Party ed Geek Day 3 368
12th Oct 12 at 21:52
by Baskey
 

Corsa Sport » Message Board » Off Day » Geek Day » Google Sites/ Database query *Update* 29 database queries in 0.0961218 seconds