Phpmyadmin not working. Need ideas
Discussion
Hi.
Upon a recommendation I recently started using phpmyadmin for my 2+ million records which I've had in Excel for years, getting progressively slower and slower as expected. I just can't get on with it. It is too 'technical' for a simpleton like me. Whilst Excel has its flaws it is quick and easy to add and change records, also very easy to add bulk data in various formats with the in-built import tools.
In phpmyadmin you can't easily do any of that and you either have to import the data from csv or start writing 'INSERT INTO' sql statements and this is not for me. I want quick. I want easy. I don't want to be messing around with back-end code to perform basic data entry and editing.
I had an MS Access db back in 2003 with around 50k records and an .asp look-up page on my site and it worked great. I want something with the same sort of WYSIWYG GUI and ease of use but don't want to be tied in to MS and at a future date third party websites will be automatically referencing my database which is probably why I was sent down the sql path as that seems to be the 'default' for databases these days. I don't care how the data is stored or what format it's in, but I need a noob friendly GUI like Excel or Access to add and edit the records.
What other options are out there please, and preferably free? Also being able to edit locally rather than always through the server would be preferred. If it's anything that involves writing code or sql statements beyond the initial set-up I'm not interested in it!
Thanks
Upon a recommendation I recently started using phpmyadmin for my 2+ million records which I've had in Excel for years, getting progressively slower and slower as expected. I just can't get on with it. It is too 'technical' for a simpleton like me. Whilst Excel has its flaws it is quick and easy to add and change records, also very easy to add bulk data in various formats with the in-built import tools.
In phpmyadmin you can't easily do any of that and you either have to import the data from csv or start writing 'INSERT INTO' sql statements and this is not for me. I want quick. I want easy. I don't want to be messing around with back-end code to perform basic data entry and editing.
I had an MS Access db back in 2003 with around 50k records and an .asp look-up page on my site and it worked great. I want something with the same sort of WYSIWYG GUI and ease of use but don't want to be tied in to MS and at a future date third party websites will be automatically referencing my database which is probably why I was sent down the sql path as that seems to be the 'default' for databases these days. I don't care how the data is stored or what format it's in, but I need a noob friendly GUI like Excel or Access to add and edit the records.
What other options are out there please, and preferably free? Also being able to edit locally rather than always through the server would be preferred. If it's anything that involves writing code or sql statements beyond the initial set-up I'm not interested in it!
Thanks
not necessarily free... but, sqlyog
https://www.webyog.com/
brilliant software assuming you can ideally SSH to your server (usually possible)
you can very easily edit in the software, or import directly from csv excel files
we use it the whole time, it is one of our most essential pieces of software...
on a mac Sequel Pro is the equivalent, also something we use...
https://www.webyog.com/
brilliant software assuming you can ideally SSH to your server (usually possible)
you can very easily edit in the software, or import directly from csv excel files
we use it the whole time, it is one of our most essential pieces of software...
on a mac Sequel Pro is the equivalent, also something we use...
Lemming Train said:
In phpmyadmin you can't easily do any of that and you either have to import the data from csv or start writing 'INSERT INTO' sql statements and this is not for me. I want quick. I want easy. I don't want to be messing around with back-end code to perform basic data entry and editing.
You know there's an "Insert" link that presents each field with an appropriate input box so you can manually insert new records? And you can edit any record in that same form just by clicking the "edit" link if you have it in "Browse" mode. 
Input screen
What kind of bulk input do you want, other than CSV? Is it possible you're on a very old version? Mine seems to allow other formats, including XML and "OpenDocument Spreadsheet", whatever that is.
Note - I'm not saying it wouldn't be nicer to have something that covers all the back room stuff up - and phpmyadmin isn't intended to do that. By it's nature, it is an admin tool to make it easy to admin a MySQL database without having to use the command line.
Edited by droopsnoot on Monday 8th April 11:01
I find that phpMyAdmin is just not very intuitive for those who are not coders - and also for those who are!
sqlyog makes it intuitive as does Sequel Pro
we also use other software like Adminer - not very good...
totally get the need to have a process which lets you do what you want without having to overthink it...
the only alternative would be a small simple admin system written to let you admin the database...
we do that for a number of clients as it also lets us manage what they can / can't access / damage!
sqlyog makes it intuitive as does Sequel Pro
we also use other software like Adminer - not very good...
totally get the need to have a process which lets you do what you want without having to overthink it...
the only alternative would be a small simple admin system written to let you admin the database...
we do that for a number of clients as it also lets us manage what they can / can't access / damage!
droopsnoot said:
You know there's an "Insert" link that presents each field with an appropriate input box so you can manually insert new records? And you can edit any record in that same form just by clicking the "edit" link if you have it in "Browse" mode.
Yes, I am of course aware of those buttons as those are the default ones for adding new records. But try adding 50 or 100 records without writing an sql statement and see how you get on! That's the problem. In Excel (or even Access) I could do that in 5 mins as clusters of cells have sequential data from the one above so some left clicking and dragging with the mouse completes the tasks in seconds.akirk said:
not necessarily free... but, sqlyog
https://www.webyog.com/
brilliant software assuming you can ideally SSH to your server (usually possible)
you can very easily edit in the software, or import directly from csv excel files
we use it the whole time, it is one of our most essential pieces of software...
on a mac Sequel Pro is the equivalent, also something we use...
Thanks, unfortunately at $300 well out of my budget for the simplicity of what I want to do.https://www.webyog.com/
brilliant software assuming you can ideally SSH to your server (usually possible)
you can very easily edit in the software, or import directly from csv excel files
we use it the whole time, it is one of our most essential pieces of software...
on a mac Sequel Pro is the equivalent, also something we use...
akirk said:
I find that phpMyAdmin is just not very intuitive for those who are not coders - and also for those who are!
sqlyog makes it intuitive as does Sequel Pro
we also use other software like Adminer - not very good...
totally get the need to have a process which lets you do what you want without having to overthink it...
the only alternative would be a small simple admin system written to let you admin the database...
we do that for a number of clients as it also lets us manage what they can / can't access / damage!
I understand the difference in what I want. I get that mysql is the "engine" (for want of a better word) of the database and if you were a mechanic you'd be happy as a pig in ssqlyog makes it intuitive as does Sequel Pro
we also use other software like Adminer - not very good...
totally get the need to have a process which lets you do what you want without having to overthink it...
the only alternative would be a small simple admin system written to let you admin the database...
we do that for a number of clients as it also lets us manage what they can / can't access / damage!
t playing about with it and getting oiled up to the eyeballs because you understand it. That's the same way that a coder looks at mysql - they know what they're doing. But I'm not a coder, I'm the driver of the car and aren't interesting in the engine. All I want is all the info from the engine to be displayed to me on a nice simple screen called the dashboard display ie. the front-end, where I can make changes and additions from the comfort of my seat without having to go crawling underneath the engine with a set of spanners getting oily. I use AppGini - it's not free but not that pricy
https://bigprof.com/appgini/
Easy to use and should give you what you need
https://bigprof.com/appgini/
Easy to use and should give you what you need
Lemming Train said:
I understand the difference in what I want. I get that mysql is the "engine" (for want of a better word) of the database and if you were a mechanic you'd be happy as a pig in st playing about with it and getting oiled up to the eyeballs because you understand it. That's the same way that a coder looks at mysql - they know what they're doing. But I'm not a coder, I'm the driver of the car and aren't interesting in the engine. All I want is all the info from the engine to be displayed to me on a nice simple screen called the dashboard display ie. the front-end, where I can make changes and additions from the comfort of my seat without having to go crawling underneath the engine with a set of spanners getting oily.
which is why you need a PC based or web based interface - it is either one of those out there - which vary in intuitiveness or the writing of a control panel which will cost you considerably more...t playing about with it and getting oiled up to the eyeballs because you understand it. That's the same way that a coder looks at mysql - they know what they're doing. But I'm not a coder, I'm the driver of the car and aren't interesting in the engine. All I want is all the info from the engine to be displayed to me on a nice simple screen called the dashboard display ie. the front-end, where I can make changes and additions from the comfort of my seat without having to go crawling underneath the engine with a set of spanners getting oily. https://alternativeto.net/software/sqlyog/ shows others - I have used Heidili SQL - and there are others which are free - a lot depends on how you like to work
using your analogy though - you want to have the infotainment system which talks to the engine - however you currently have one which doesn't work the way you do (familiar story in cars!) so you either need to find another head unit / interface, or or learn to code for the canbus etc. - the problem is that most interfaces are written by those with a technical bent so they tend not to be so friendly to the non-technical...
stepej said:
I use AppGini - it's not free but not that pricy
https://bigprof.com/appgini/
Easy to use and should give you what you need
Thanks. Just spent 30 mins playing around with the trial. Close but no cigar. There is no way to add/paste in multiple records unless you do it in another program like Excel and then import the csv file and reading the FAQ there is no facility to set user view permissions which is something that I must have. https://bigprof.com/appgini/
Easy to use and should give you what you need
akirk said:
which is why you need a PC based or web based interface - it is either one of those out there - which vary in intuitiveness or the writing of a control panel which will cost you considerably more...
https://alternativeto.net/software/sqlyog/ shows others - I have used Heidili SQL - and there are others which are free - a lot depends on how you like to work
using your analogy though - you want to have the infotainment system which talks to the engine - however you currently have one which doesn't work the way you do (familiar story in cars!) so you either need to find another head unit / interface, or or learn to code for the canbus etc. - the problem is that most interfaces are written by those with a technical bent so they tend not to be so friendly to the non-technical...
Yup that about sums it up. I was reading some stack overflow posts about this as some guy using Access had the same problem and was looking for alternatives. All sorts were mentioned like Heidi, SQLite, PHPmyAdmin etc and received large amounts of upvotes until someone came along and pointed out that these were just the engine component and all lack a proper UI which is something that Access excels at, comparatively speaking. Unfortunately the discussion fizzled out without any solution.https://alternativeto.net/software/sqlyog/ shows others - I have used Heidili SQL - and there are others which are free - a lot depends on how you like to work
using your analogy though - you want to have the infotainment system which talks to the engine - however you currently have one which doesn't work the way you do (familiar story in cars!) so you either need to find another head unit / interface, or or learn to code for the canbus etc. - the problem is that most interfaces are written by those with a technical bent so they tend not to be so friendly to the non-technical...
Lemming Train said:
stepej said:
I use AppGini - it's not free but not that pricy
https://bigprof.com/appgini/
Easy to use and should give you what you need
Thanks. Just spent 30 mins playing around with the trial. Close but no cigar. There is no way to add/paste in multiple records unless you do it in another program like Excel and then import the csv file and reading the FAQ there is no facility to set user view permissions which is something that I must have. https://bigprof.com/appgini/
Easy to use and should give you what you need
user access - you might arguably be able to do some of this at a server level, but generally this is managed in a front end - so we are back to bespoke front ends...
nyt said:
If you are familiar with Access then you can use that as a front end to your MySQL database.
You can link the two using ODBC (and probably more modern mechanisms)
not sure that is a route I would be taking - we are currently moving clients away from Access as it goes out of date in 2020 and effectively any support is switched off...You can link the two using ODBC (and probably more modern mechanisms)
Lemming Train said:
akirk said:
which is why you need a PC based or web based interface - it is either one of those out there - which vary in intuitiveness or the writing of a control panel which will cost you considerably more...
https://alternativeto.net/software/sqlyog/ shows others - I have used Heidili SQL - and there are others which are free - a lot depends on how you like to work
using your analogy though - you want to have the infotainment system which talks to the engine - however you currently have one which doesn't work the way you do (familiar story in cars!) so you either need to find another head unit / interface, or or learn to code for the canbus etc. - the problem is that most interfaces are written by those with a technical bent so they tend not to be so friendly to the non-technical...
Yup that about sums it up. I was reading some stack overflow posts about this as some guy using Access had the same problem and was looking for alternatives. All sorts were mentioned like Heidi, SQLite, PHPmyAdmin etc and received large amounts of upvotes until someone came along and pointed out that these were just the engine component and all lack a proper UI which is something that Access excels at, comparatively speaking. Unfortunately the discussion fizzled out without any solution.https://alternativeto.net/software/sqlyog/ shows others - I have used Heidili SQL - and there are others which are free - a lot depends on how you like to work
using your analogy though - you want to have the infotainment system which talks to the engine - however you currently have one which doesn't work the way you do (familiar story in cars!) so you either need to find another head unit / interface, or or learn to code for the canbus etc. - the problem is that most interfaces are written by those with a technical bent so they tend not to be so friendly to the non-technical...
the issue you have (being blunt!
) is that there are two options:- learn the technical (and therefore use one of the readily available options)
- spend the money (and have a bespoke front end written)
but there is no non-techie friendly front end I know of which requires no spend...
do PM me / send me more details if you wish - happy to talk off line if you don't wish to share more details on here - but it would help to understand a little bit more about exactly what you are trying to do and with what data...
I wasn't aware that Access was ending! 
The new data either comes to me via email from various sources dotted all around the globe or my own research. One recent email provided 63 new codes which are generally a mix of random numbers and letters but often many run sequential. Rough example :
ABC1234
ABC1235
ABC1236
ABC1237
DEF0011
DEF0012
GHI9876
JKL5678
JKL5679
JKL5680
JKL5681
JKL5682
JKL5683
MNO4321
MNO4322
etc
I then add the full details of what these codes "belong to" from another source and add them to the database. That data is also in a similarly matching sequential pattern. They don't to come to me in an easily copy and pastable format so it's a manual entry job but to add these in, say, Excel is a quick process as the sequential batches of them can be added quickly once I've done the first one, simply by left click and drag down. If Excel worked like a table/database rather than a spreadsheet it would be fine, but it doesn't and with 2m+ records it's become predictably painful to use plus there's no direct way for users to connect to it via a web page query form.
I find it hard to believe in the year 2019 that a stand-alone application doesn't exist for simpleton MS Access/Excel users that require the noob-friendly interface. I mean how hard can it be? :
1. MS Access-esque interface with query box to quick reference the table data and usual column sorting etc.
2. Button to convert/save data in SQL format.
3. Screen containing a selection of search box templates to choose from and customise, generate the code and paste it on your web page with quick install button to populate the database name, folder location, path, port etc.
From my research this doesn't exist. Every app I've found which will do this in a much more basic way all want you on a monthly subscription model which is fine if you're hosting the database with them, but a complete piss take if you're hosting it on your own site.
The new data either comes to me via email from various sources dotted all around the globe or my own research. One recent email provided 63 new codes which are generally a mix of random numbers and letters but often many run sequential. Rough example :
ABC1234
ABC1235
ABC1236
ABC1237
DEF0011
DEF0012
GHI9876
JKL5678
JKL5679
JKL5680
JKL5681
JKL5682
JKL5683
MNO4321
MNO4322
etc
I then add the full details of what these codes "belong to" from another source and add them to the database. That data is also in a similarly matching sequential pattern. They don't to come to me in an easily copy and pastable format so it's a manual entry job but to add these in, say, Excel is a quick process as the sequential batches of them can be added quickly once I've done the first one, simply by left click and drag down. If Excel worked like a table/database rather than a spreadsheet it would be fine, but it doesn't and with 2m+ records it's become predictably painful to use plus there's no direct way for users to connect to it via a web page query form.
I find it hard to believe in the year 2019 that a stand-alone application doesn't exist for simpleton MS Access/Excel users that require the noob-friendly interface. I mean how hard can it be? :
1. MS Access-esque interface with query box to quick reference the table data and usual column sorting etc.
2. Button to convert/save data in SQL format.
3. Screen containing a selection of search box templates to choose from and customise, generate the code and paste it on your web page with quick install button to populate the database name, folder location, path, port etc.
From my research this doesn't exist. Every app I've found which will do this in a much more basic way all want you on a monthly subscription model which is fine if you're hosting the database with them, but a complete piss take if you're hosting it on your own site.
Edited by Lemming Train on Monday 8th April 15:55
Lemming Train said:
I wasn't aware that Access was ending!
The new data either comes to me via email from various sources dotted all around the globe or my own research. One recent email provided 63 new codes which are generally a mix of random numbers and letters but often many run sequential. Rough example :
ABC1234
ABC1235
ABC1236
ABC1237
DEF0011
DEF0012
GHI9876
JKL5678
JKL5679
JKL5680
JKL5681
JKL5682
JKL5683
MNO4321
MNO4322
etc
I then add the full details of what these codes "belong to" from another source and add them to the database. That data is also in a similarly matching sequential pattern. They don't to come to me in an easily copy and pastable format so it's a manual entry job but to add these in, say, Excel is a quick process as the sequential batches of them can be added quickly once I've done the first one, simply by left click and drag down. If Excel worked like a table/database rather than a spreadsheet it would be fine, but it doesn't and with 2m+ records it's become predictably painful to use plus there's no direct way for users to connect to it via a web page query form.
I find it hard to believe in the year 2019 that a stand-alone application doesn't exist for simpleton MS Access/Excel users that require the noob-friendly interface. I mean how hard can it be? :
1. MS Access-esque interface with query box to quick reference the table data and usual column sorting etc.
2. Button to convert/save data in SQL format.
3. Screen containing a selection of search box templates to choose from and customise, generate the code and paste it on your web page with quick install button to populate the database name, folder location, path, port etc.
From my research this doesn't exist. Every app I've found which will do this in a much more basic way all want you on a monthly subscription model which is fine if you're hosting the database with them, but a complete piss take if you're hosting it on your own site.
Access 2010 goes off support - 2016 goes off mainstream support - but will still have support for a few years - but general view in the industry is to treat Access as dying in 2020 for lots of technical reasons...The new data either comes to me via email from various sources dotted all around the globe or my own research. One recent email provided 63 new codes which are generally a mix of random numbers and letters but often many run sequential. Rough example :
ABC1234
ABC1235
ABC1236
ABC1237
DEF0011
DEF0012
GHI9876
JKL5678
JKL5679
JKL5680
JKL5681
JKL5682
JKL5683
MNO4321
MNO4322
etc
I then add the full details of what these codes "belong to" from another source and add them to the database. That data is also in a similarly matching sequential pattern. They don't to come to me in an easily copy and pastable format so it's a manual entry job but to add these in, say, Excel is a quick process as the sequential batches of them can be added quickly once I've done the first one, simply by left click and drag down. If Excel worked like a table/database rather than a spreadsheet it would be fine, but it doesn't and with 2m+ records it's become predictably painful to use plus there's no direct way for users to connect to it via a web page query form.
I find it hard to believe in the year 2019 that a stand-alone application doesn't exist for simpleton MS Access/Excel users that require the noob-friendly interface. I mean how hard can it be? :
1. MS Access-esque interface with query box to quick reference the table data and usual column sorting etc.
2. Button to convert/save data in SQL format.
3. Screen containing a selection of search box templates to choose from and customise, generate the code and paste it on your web page with quick install button to populate the database name, folder location, path, port etc.
From my research this doesn't exist. Every app I've found which will do this in a much more basic way all want you on a monthly subscription model which is fine if you're hosting the database with them, but a complete piss take if you're hosting it on your own site.
Edited by Lemming Train on Monday 8th April 15:55
Lemming Train said:
I mean how hard can it be?
Not very hard...https://www.designtoo.com/PHdemo
follow the instructions...
so I used this as a test in Excel
ABC1234 green
ABC1235 blue
ABC1236 red
ABC1237 brown
DEF0011 black
DEF0012 yellow
GHI9876 pink
JKL5678 orange
copy from excel
paste on page
page takes the clipboard / reads in the format / splits on end of line & horizontal tabs (which is how clipboard comes out of Excel) and then adds each record to a database - then as this is all ajaxed - it simply looks into the database, pulls out all records and squirts them out in table format into the area below...
once you realise that you can dump data from Excel, then you can add all the fripperies around it that you want...
as I said above - no-one provides this nicely for you - but - for a bespoke system, it is not overly complex - putting all of this together as the code code has been done since your post above... not a huge amount of time needed - clearly to do it properly you might then wish to add in prettiness to the page / log in system / etc. etc. - and you could add in options to autogenerate lists of numbers or codes - lots of possibilities - but it is bespoke...
and that is never dirt cheap - so you need to decide what its value is to you...
Edited by akirk on Monday 8th April 18:45
Bibbs said:
I think the fact that 2016 and 2019 have the same end of life says it all... Microsoft announced the end of access some time ago... of course the reality is that it will carry on working for much longer in the same way that companies are using xp still, but I wouldn’t be relying on it...anyway, it is a slight red-herring as the op wants access to MySQL in a method like his previous use of Access, he doesn’t want Access as the solution and says he doesn’t want to rely on microsoft...
Lemming Train said:
Thanks. Just spent 30 mins playing around with the trial. Close but no cigar. There is no way to add/paste in multiple records unless you do it in another program like Excel and then import the csv file and reading the FAQ there is no facility to set user view permissions which is something that I must have.
The admin area allows you to set permissions by group to Insert/View/Edit/Delete records in a tableGassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff