Wednesday 11 August 2010

Review - mySQL Ajax Table Editor

This is much more like what I need to create a user-friendly data entry/editing system for lay users.

Documentation is on the terse side (I could have used some examples) but it does look like I will be able to get it to do odd bits of post-entry/edit data manipulation.

On user-friendliness, it scores modestly well. It is very easy to specify a user-friendly title for a data item.

BUT - it doesn't cope at all with data that includes an apostrophe.

PHPmyEdit: doesn't work with WAMP?

Looks like a very comprehensive offering, but ...

Installed it under WAMP and got:

Notice: Undefined index: db in C:\webserver\Billing\phpMyEdit\phpMyEditSetup.php on line 65

Notice: Undefined index: tb in C:\webserver\Billing\phpMyEdit\phpMyEditSetup.php on line 66

Notice: Undefined variable: db in C:\webserver\Billing\phpMyEdit\phpMyEditSetup.php on line 189

Notice: Undefined variable: tb in C:\webserver\Billing\phpMyEdit\phpMyEditSetup.php on line 192

Searching suggests that others have hit this with WAMP. There also seem to be some current issues (Aug 2010) with compatability with PHP 5.

The search goes on

PHPmySQL - review of demo




Simple enough to use, but documentation is very poor: a description of each screen in the sequence. At a first look, it appeared as only the connection data can be saved: if you want to modify an existing set of pages, you have to specify your requirements from scratch again.

When you are finished, the "Generate" action copies a bunch of files to your chosen folder. Only "database.php" and "config.php" have the current date - all the others are much older, which confused me: I eventually twigged that I needed to open "index.php" to see the resulting application.

The result is competent enough, I am sure (demo does not allow use of foreign-key lookup), but very much the IT-person's data entry experience: not suitable for lay users, in my view: there didn't seem to be any option to give data fields user-friendly display names, for example.

Monday 9 August 2010

Working with Multiple Databases under MySQL, PHP and phpMyAdmin

I couldn't find clear and concise guidance, so here is my offering (after much miserable experimentation):

Establish a Connection, eg

$glob_dbConnection = mysql_connect($db_host, $db_user , $db_pass) or die ('D5518 Error connecting to mysql');


Unless you are doing anything fancy, you don't actually need to refer to $glob_dbConnection again, as this becomes the default.

This connection then allows access to as many databases as $db_user has privileges for, so you can specify queries like this:

$sql="SELECT databaseCommon.products.*, databaseCustomer.purchases.* from databaseCommon.products INNER JOIN databaseCustomer.purchases ON ...

But setting the Privileges is a bit of a pain, and (to me) far from obvious.

In phpMyAmin, click on the link to "Server: localhost" to get at all the top-level settings.

Clicking on the Privileges link will take you to the User Overview. This allows you to Add an extra user, and lists existing users, and tells what Global Privileges each has, but it says nothing about which databases the user can access.  To fix that, click on Edit Privileges, and find the second section, which is marked "Database-specific privileges". There is a select box marked "Add privileges on the following database" - with a default of "Use text field".
Using the default, you can type in the database name, but it is safer to open the Select Box and choose the database name from there.

Having done this, press Go. The page reloads and you can now specify which actions are allowed for this user for this database (eg Select+Insert+Update but not Delete, perhaps). Tick the boxes and press Go. You may well get an error message, eg
#1141 - There is no such grant defined for user 'billinguser' on host 'localhost'
which appears to be spurious. Click on the link (eg 'billinguser'@'localhost') and it will now offer you the chance to edit Global privileges at the top of the page; further down, it lists the Database-specific privileges and you will see the specific permissions that you have added.



Good Luck

Sunday 8 August 2010

Chrome may fire OnKeyUp event when you give focus to a SELECT box

Trying to create an select-as-you-type text box using Javascript, I wanted to open a SELECT box listing matches for text typed so far: no problem.

The challenge came when I wanted use of down-arrow from the text box to result in the first item in the select box being displayed and selected. In Chrome, setting the focus to the SELECT box was fine for an instant, but also resulted in the "onkeyup" event being fired and the focus moving away from the SELECT box. That isn't correct: onkeyup should only fire if the user has clicked on the SELECT box, and I had moved there programatically.

The solution was to use the onkeyup event to call a function that would specify thisObj.selectedIndex=0; - this brought focus back to the SELECT box. Of course, that then needed a boolean value setting in the onKeyDown so that Icould distinguish between normal and "phantom" keyup events.


... select id='monikerSelect' onkeyup='handleSelectKeyUp();'  ...


function handleSelectKeyUp(){
//CHROME triggers this immediately after passing focus.
    //We cannot stop it firing, so we need to try and make it put right what it mucks up

if (glob_selectDownHasBeenRun){
    //this is a keyup which follows a keydown: normal use
} else {
    //this is an orphan keyup that Chrome has invented for itself: THE PROBLEM CASE
    document.getElementById("monikerSelect").selectedIndex=0;
}
glob_selectDownHasBeenRun=false;
}