Question for database dev people, SQL PHP
Discussion
I'm having a website built by a freelancer which is basically for people to query my database. I have had this up and running some years ago using the same Datatables table framework and SQL database but I stopped updating it, the domain expired which lost all my files and the original dev wasn't answering the phone when I tried to get it resurrected.
Anyway.. the process I used back then was to edit locally in DB broswer for SQlite which has all the schema and such already configured after setting up your database, and saves as a nice.db file. The dev "wired" it all up so all I needed to do was drag the .db file to my FTP, which overwrote the last version and 10 seconds after it had finished uploading, the fresh data was ready to query on my website.
I gave this new dev a sample file of records to work with and he initially avoided the SQL database route, said it wasn't required, and just made it so it was a simple query of a csv export which I then uploaded to my FTP. This worked fine, but it was only when we uploaded the full csv database of half a million records did we discover this wasn't going to work as queries were now taking 20+ seconds to show the results
. In fairness to him, he didn't complain and said he would create the SQL database instead, which he did. All good there, but wants me to export my database to csv each day, then log into my cPanel each time, go to phpmyadmin, clcik the database and import the fresh csv. This is too much f
king around for me. I'm prepared to drag the .db to my FTP like I did before, or at the very most, export it to csv and do it. He says he doesn't think it's possible when I know it clearly is.
He is complaining he is getting a time-out from my host :
"This is what happens if I try to sync csv to database on your website." followed by a screenie from the server operations screen showing a request time-out, contact your host to increase your connection time-out level.
I'm not sure what he's doing.
"On your cpanel, i was able to see max execution time is just 2 minutes. I think i have to increase it."
I replied back saying that surely .db file should 'work' with a SQL database directly without needing to mess around with csv files.
"CSV is read-able for PHP that's why we are using CSV."
I don't have the technical knowledge to understand what's going on here. Can any db devs shed some light please?
Anyway.. the process I used back then was to edit locally in DB broswer for SQlite which has all the schema and such already configured after setting up your database, and saves as a nice.db file. The dev "wired" it all up so all I needed to do was drag the .db file to my FTP, which overwrote the last version and 10 seconds after it had finished uploading, the fresh data was ready to query on my website.
I gave this new dev a sample file of records to work with and he initially avoided the SQL database route, said it wasn't required, and just made it so it was a simple query of a csv export which I then uploaded to my FTP. This worked fine, but it was only when we uploaded the full csv database of half a million records did we discover this wasn't going to work as queries were now taking 20+ seconds to show the results
. In fairness to him, he didn't complain and said he would create the SQL database instead, which he did. All good there, but wants me to export my database to csv each day, then log into my cPanel each time, go to phpmyadmin, clcik the database and import the fresh csv. This is too much fking around for me. I'm prepared to drag the .db to my FTP like I did before, or at the very most, export it to csv and do it. He says he doesn't think it's possible when I know it clearly is.He is complaining he is getting a time-out from my host :
"This is what happens if I try to sync csv to database on your website." followed by a screenie from the server operations screen showing a request time-out, contact your host to increase your connection time-out level.
I'm not sure what he's doing."On your cpanel, i was able to see max execution time is just 2 minutes. I think i have to increase it."
I replied back saying that surely .db file should 'work' with a SQL database directly without needing to mess around with csv files.
"CSV is read-able for PHP that's why we are using CSV."
I don't have the technical knowledge to understand what's going on here. Can any db devs shed some light please?
Edited by r3g on Thursday 17th August 19:14
Your dev is using MySQL, which is a service built into your server, and what any dev would use for SQL unless they knew they needed to do otherwise.
Your old site must have used SQLLite access, which is built into PHP ( point them at: https://www.php.net/sqlite3 ).
If they remove their MySQL specific code and re-write it to use the SQLLite code pointing to your db file then it should work as before, you should be able to just FTP the new version of the file onto the server.
The other option is for the dev to write an import routine that will read the CSV file and insert it into the MySQL server - this is the approach we use.
Sounds like your dev has made a lot of extra work for themselves as it was a 5 minute job to check that PHP supported SQLLite files directly. To be fair the MySQL approach will almost certainly run a lot faster than SQLLite but if it's going from half a second to a quarter it's not exactly important.
Oh, and the idea of reading the CSV file every time was really stupid, assuming you told them how many rows were involved.
Your old site must have used SQLLite access, which is built into PHP ( point them at: https://www.php.net/sqlite3 ).
If they remove their MySQL specific code and re-write it to use the SQLLite code pointing to your db file then it should work as before, you should be able to just FTP the new version of the file onto the server.
The other option is for the dev to write an import routine that will read the CSV file and insert it into the MySQL server - this is the approach we use.
Sounds like your dev has made a lot of extra work for themselves as it was a 5 minute job to check that PHP supported SQLLite files directly. To be fair the MySQL approach will almost certainly run a lot faster than SQLLite but if it's going from half a second to a quarter it's not exactly important.
Oh, and the idea of reading the CSV file every time was really stupid, assuming you told them how many rows were involved.
maffski said:
Your dev is using MySQL, which is a service built into your server, and what any dev would use for SQL unless they knew they needed to do otherwise.
Your old site must have used SQLLite access, which is built into PHP ( point them at: https://www.php.net/sqlite3 ).
If they remove their MySQL specific code and re-write it to use the SQLLite code pointing to your db file then it should work as before, you should be able to just FTP the new version of the file onto the server.
The other option is for the dev to write an import routine that will read the CSV file and insert it into the MySQL server - this is the approach we use.
Sounds like your dev has made a lot of extra work for themselves as it was a 5 minute job to check that PHP supported SQLLite files directly. To be fair the MySQL approach will almost certainly run a lot faster than SQLLite but if it's going from half a second to a quarter it's not exactly important.
Oh, and the idea of reading the CSV file every time was really stupid, assuming you told them how many rows were involved.
Thanks for the intel, I think I understand now. So it seems that it's not a straightforward process as I had assumed, getting a .db file to "talk" efficiently with MySQL.Your old site must have used SQLLite access, which is built into PHP ( point them at: https://www.php.net/sqlite3 ).
If they remove their MySQL specific code and re-write it to use the SQLLite code pointing to your db file then it should work as before, you should be able to just FTP the new version of the file onto the server.
The other option is for the dev to write an import routine that will read the CSV file and insert it into the MySQL server - this is the approach we use.
Sounds like your dev has made a lot of extra work for themselves as it was a 5 minute job to check that PHP supported SQLLite files directly. To be fair the MySQL approach will almost certainly run a lot faster than SQLLite but if it's going from half a second to a quarter it's not exactly important.
Oh, and the idea of reading the CSV file every time was really stupid, assuming you told them how many rows were involved.
The dev had been informed that it was 520k records in the initial RFP but perhaps forgot about it once I presented him with the 100 record csv.
He's now made me a 'sync database' tab on my website which, after dragging the exported csv file to a folder on my FTP, I have to press to sync this new fresh version of my database. In theory this should be a slick operation, but the reality is that it sits there for a good 5 minutes as it slowly chugs through the data, record by record. It's only a 25 MB file. I have tried it doing it via the convulted route through cPanel, phpmyadmin, but it still takes the same time. I posted to him your comments about writing a bit of code and pointing it at the relevant .db file , but he said this would massively slow down the website and make the searches take forever. :shrug:
TonyRPH said:
Is the process replacing all the data in the database each time?
Are you adding new records, or updating existing records?
If updating, then you could be providing the updates in .sql format which should be somewhat quicker.
Tony, I'm not sure what actually happens during the process. Given the time it takes I'm assuming it overwrites everything. All the editing is done locally in DB4S program. No editing is done on the server/through the website. All I'm doing locally is adding new records, maybe a couple of hundred per day and amending maybe 100 or so existing ones.Are you adding new records, or updating existing records?
If updating, then you could be providing the updates in .sql format which should be somewhat quicker.
All I know is that it's taking far too long and a quicker way is required, without me needing to jump through a bunch of hoops.
Just so I understand...
You're editing an SQLite file in db4s, and then exporting that to .csv and then importing it to MySQL on the server?
How many columns (not rows - already know that) does this database have?
I'm just pondering - if there's not that many columns, creating a simple editor in PHP shouldn't be too onerous - then you can operate on the database directly.
Obviously working this way would mean taking a daily backup (which hopefully you're doing already anyway).
You're editing an SQLite file in db4s, and then exporting that to .csv and then importing it to MySQL on the server?
How many columns (not rows - already know that) does this database have?
I'm just pondering - if there's not that many columns, creating a simple editor in PHP shouldn't be too onerous - then you can operate on the database directly.
Obviously working this way would mean taking a daily backup (which hopefully you're doing already anyway).
TonyRPH said:
Just so I understand...
You're editing an SQLite file in db4s, and then exporting that to .csv and then importing it to MySQL on the server?
How many columns (not rows - already know that) does this database have?
I'm just pondering - if there's not that many columns, creating a simple editor in PHP shouldn't be too onerous - then you can operate on the database directly.
Obviously working this way would mean taking a daily backup (which hopefully you're doing already anyway).
Despite its name, it's not an SQLite file. The file saves as .db not .sqlite and the project file name to keep the column width settings in the editing screen saves as .sqbpro. As mentioned, all I did was drag the .db file to my FTP and whatever configurations the original dev put in place on the server end were able to "talk" to that file without issue.You're editing an SQLite file in db4s, and then exporting that to .csv and then importing it to MySQL on the server?
How many columns (not rows - already know that) does this database have?
I'm just pondering - if there's not that many columns, creating a simple editor in PHP shouldn't be too onerous - then you can operate on the database directly.
Obviously working this way would mean taking a daily backup (which hopefully you're doing already anyway).
I realise that the done thing these days is to do all the editing online, thus eliminating these issues, but having tried various applications (including editing directly in phpmyadmin) I have not gelled with any of them. The front-end online editors have all had annoyances, mainly the small delay when saving edits/new records. It's typically only a second or so, but it irritates me when I'm a fast typer and used to breezing across Excel sheets at lightning speed.
DB4S does have a 'export database to SQL' button with various format and configuration options which I would be willing to do and drag to my FTP, but for some reason the dev seems to only function with csv.
.db is fine for an SQLite file.
PHP includes an SQLite extension by default and the PDO data-access abstraction layer makes it dead simple to switch access between database types.
Your developer is making really hard work of this. There is no reason why you can't do as before and just ftp an updated .db file and have PHP read straight from it.
Converting your 500k row SQLite database to CSV and then dumping that into MySQL is clearly the work of a madman, but not in the style of a mad genius. Just simply mad.
PHP includes an SQLite extension by default and the PDO data-access abstraction layer makes it dead simple to switch access between database types.
Your developer is making really hard work of this. There is no reason why you can't do as before and just ftp an updated .db file and have PHP read straight from it.
Converting your 500k row SQLite database to CSV and then dumping that into MySQL is clearly the work of a madman, but not in the style of a mad genius. Just simply mad.
r3g said:
I realise that the done thing these days is to do all the editing online, thus eliminating these issues, but having tried various applications (including editing directly in phpmyadmin) I have not gelled with any of them. The front-end online editors have all had annoyances, mainly the small delay when saving edits/new records. It's typically only a second or so, but it irritates me when I'm a fast typer and used to breezing across Excel sheets at lightning speed.
This is the route you should be considering - apart from anything else, having data in two places and then over-writing it in the way you are is far from good data practice - it is also a painful process as you are discovering...dump the whole lot in MySQL and then either:
- get your dev. to write you an admin system
- use SqlYOG - which is a superb bit of PC software which allows you to edit tables / data etc. - it is fast and slick and a million miles away from PHPMyAdmin which is clunky and horrible...
I wonder if something like https://retool.com/ would done the job.
I've not used it, be see loads of ads for it and similar solutions.
I've not used it, be see loads of ads for it and similar solutions.
akirk said:
it is also a painful process as you are discovering...
Only because his developer is fumbling about in the dark.akirk said:
dump the whole lot in MySQL and then either:
- get your dev. to write you an admin system
- use SqlYOG - which is a superb bit of PC software which allows you to edit tables / data etc. - it is fast and slick and a million miles away from PHPMyAdmin which is clunky and horrible...
Which might be the way to go for a completely fresh project, but it's over complicating things imho.- get your dev. to write you an admin system
- use SqlYOG - which is a superb bit of PC software which allows you to edit tables / data etc. - it is fast and slick and a million miles away from PHPMyAdmin which is clunky and horrible...
r3g had a process that he's comfortable with. He's happy using DB Browser and whilst doing everything "in the cloud" might be fashionable, I can think of several benefits of working on the data locally, especially if your internet connection can be a little moody. I'm not convinced that creating an admin system to replicate DB Browser on the desktop is a worthwhile investment.
Agree on PHPMyAdmin.
I just downloaded DB Browser, inserted 1000 test records, created a test site, and scripted a page in PHP to display the data straight from the SQLite .db file generated by DB Browser. That took about 15 minutes to do, so what the hell is his developer doing?
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff