The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 21, 2019, 12:40:25 PM

Login with username, password and session length


Pages: [1] 2 3
  Print  
Author Topic: phpMyIDE 0.5a - Real Functionality. A First Look.  (Read 21495 times)
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« on: June 02, 2008, 06:22:42 PM »

I am enormously pleased to be releasing this version, because this is what I am actually using for my projects now. With this release, I got so much going in it that I jumped from 0.3c to 0.5a. I have not put the code up at the site yet, but it should go up by the end of tonight.

First off, a couple things you need to know:
  • phpMyIDE requires the json_encode and json_decode function in PHP. If you are using php 5.2.[n] then these functions came automatically, if you are using a previous version then the json package would have had to been installed via PEAR or PECL or manually. I’d imagine that most providers include this package, since it is a pretty durn important one.
  • mysqli_ enabled: phpMyIDE will run without the mysqli class library, but the command prompt will not.  Again, most hosts should have this installed because the mysql_ functions have pretty much been deprecated and lots of software now uses the mysqli library. PHP 4.1 came with the option to install mysqli, so this is nothing new. The PHP instance would have had to have the line –mysqli_enabled in the configuration directive at compile time – this is not something that you can “enable” at run time. Use phpinfo() in a web page or php –I from the command line and look for mysqli to make sure.
  • New Create SQL – there are a couple new tables that you’ll need to create for 0.5, so your best bet is to drop table phpmyide, create it again and let the create sql create the tables for you.
  • config.php file: In preparation for multiple connections, the config.php file (in the root of the project) has been restructured completely. The new file looks like this:
Code:
<?php

$siteHost 
'42.phpmyide';
$siteURL '/';
$sitePort 80;

$connections = array();
$connections[0]['name'] = 'Local (42)';
$connections[0]['host'] = '127.0.0.1';
$connections[0]['user'] = 'username';
$connections[0]['password'] = 'password';
$connections[0]['phpmyadmin'] = 'http://42.phpmyadmin';

?>

    … where the $site variables describe the location of THIS INSTANCE of phpMyIDE and $connections(n) describe the places that you connect to. Note that at this point, only connection zero
  • is ever used. The phpmyadmin variable describes where the phpMyAdmin instance is for the connected server.


New editing features: Views and Scratchpads
Not earthshaking, but certainly helpful, is the completion of editing Views and the addition of a “scratchpads” area of the editor.

Scratchpads are not saveable – they are simply an area for you to type/cut/paste etc. You may have as many open at one time as you like.


Views behave as you would expect – When creating a new one, however, you’ll note that I needed to tag the view to a living database and table, so the default View code when you request a new one is:
Code:
CREATE ALGORITHM=UNDEFINED
DEFINER=username@localhost
SQL SECURITY DEFINER
VIEW selectedDB.newviewname
AS select phpmyide.preferences.key AS key,phpmyide.preferences.value AS value from phpmyide.preferences
…note how the “as” pieces are tied to phpmyide – this is where you’d change the view to the structure of the view that you wish to implement. Views are backed up in the TimeMachine just like procedures, functions and triggers.



The Command Line
Far and away the biggest change is the implementation of the Command Line. I am really pleased with both its functionality as well as the GUI. I was going for an emulation of the shell MySQL interface, because I’m an old typer and I think it works well.


Some highlights:
  • It supports unlimited “roll back” ie., you can scroll back and see what you’ve typed and what was responded
  • a couple additions to the MySQL lexicon, including “clear” (clears the terminal buffer) use (un-uses a database directive like “use phpmyide”) show use (shows the database that is currently being used) show history (show the command history) and show connection (show what connection is currently in force, defined by the config.php file.)
  • The up and down arrows scroll through the command history, just like the Linux shell behavior

Here is a clip from an actual session:
Code:
show databases
----------------------
| Database           |
----------------------
| information_schema |
| emailer            |
| mysql              |
| phpmyide           |
| spidersite         |
----------------------


use phpmyide
OK

show tables
----------------------
| Tables_in_phpmyide |
----------------------
| long_commands      |
| long_responses     |
| preferences        |
| prefs              |
| timemachine        |
| triggers           |
----------------------


describe preferences
-------------------------------------------------------
| Field | Type         | Null | Key | Default | Extra |
-------------------------------------------------------
| key   | varchar(32)  | NO   | PRI |         |       |
| value | varchar(254) | NO   |     |         |       |
-------------------------------------------------------


select * from preferences
-------------------------------------
| key                  | value      |
-------------------------------------
| print_headertitle    | 1          |
| print_pagenumbers    | 1          |
| print_headerdate     | 1          |
| print_linenumbers    | 1          |
| print_fontsize       | 11         |
| print_fontname       | courier    |
-------------------------------------
Number of rows returned: 6


select count('x') from preferences
--------------
| count('x') |
--------------
| 6          |
--------------
Number of rows returned: 1


explain select * from preferences
----------------------------------------------------------------------------------------------
| id | select_type | table       | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------
| 1  | SIMPLE      | preferences | ALL  |               |     |         |     | 6    |       |
----------------------------------------------------------------------------------------------


call testdb.bighelloworld()
---------------------------
| Another hello world!    |
---------------------------
| Another hello world!    |
---------------------------
Number of rows returned: 1


---------------------------
| Another hello world!    |
---------------------------
| Another hello world!    |
---------------------------
Number of rows returned: 1


---------------------------
| Another hello world!    |
---------------------------
| Another hello world!    |
---------------------------
Number of rows returned: 1


---------------------------
| Another hello world!    |
---------------------------
| Another hello world!    |
---------------------------
Number of rows returned: 1

Note that even stored procedures with multiple responses come through to the command line perfectly.

The command line looks like a true persistent connection, but it is not. “use phpmyide” works because of a trick that I’m doing, not because you’ve got a handle to a persistent connection. So for example, a mutlti-line SQL command like this:
Code:
drop database if exists someDB;
create database someDB;
will work perfectly – the command line can execute multi-line statements. So two commands issued from the command line like this will fail:
Code:
insert into aTableWithAutoIncrement(afield) values('the value')

… then on the next line…

Code:
select LAST_INSERT_ID()

the LAST_INSERT_ID is undefined because it’s not a persistent connection. However, if you typed that last SQL into a scratchpad, copy & pasted it as multiple commands thus:

Code:
insert into aTableWithAutoIncrement(afield) values(‘the value’);
select LAST_INSERT_ID();
it will work, because both commands were executed on the same connection.

Select * from a ReallyBigTable will also work just fine, because of the way that I “collect” multiple responses at the server and then feed them back out to the client at about 128 lines per Ajax request. There is a “cancel” button if you selected from a table and want to stop the response. Here we can see me doing a selection against a spider IP table. The SQL was:

Code:
select id, engineid, address, useragent from spidertrack_spiders where useragent > ' ' order by useragent

which produces about 29K results. I pressed the Cancel button midway:




A Simple First Session
What I’d like to do now is actually run through the steps of implementing a stored procedure, trigger and view, using the features of phpMyIDE. In this example, we will create a new database, add two tables to it with a one-to-many relationship. Table 2 will contain rows of data that should be summed up into Table 1. We’ll use a procedure to do the actually summation, a trigger to fire it off and a view to display it. I’ll also demonstrate using phpMyAdmin inside of phpMyIDE.

So, off we go. Assuming we just started up, our window looks like this:


The first thing to do is create our database. Clicking on the Command Prompt tab we execute 2 SQL statements.
Clicking refresh on the Script Explorer shows us that the database we created does indeed exist.


Just for giggles, enter a couple SQL commands to see how the command prompt works:
  • use phpmyide
  • show tables
  • show databases
  • show status
  • select * from preferences
  • clear

Let’s use phpMyAdmin to add our two tables. Select the phpMyAdmin tab, select our new DB and create two tables as such:




Alternately, you could use the Command Prompt to execute this multi-line SQL:


use phptest;

CREATE TABLE tablea (
  id int(11) NOT NULL auto_increment,
  tablebsum int(11) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE tableb (
  id int(11) NOT NULL auto_increment,
  tablea_id int(11) NOT NULL,
  quantity int(11) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


Now when clicking on the new database in the Script Explorer we can see our tables:


Note that with the Database Explorer we can see the structure of the tables as well, which may be important later if we have the Editor or Command Prompt pane up and can’t see phpMyAdmin:


Now let's use a mutli-line SQL statement to insert a record into table A, and then table B (we know that the ID of table A is going to be 1, so this will work OK):

use phptest;
insert into tablea() values();
insert into tableb(tablea_id, quantity) values(1, 5);
insert into tableb(tablea_id, quantity) values(1, 7);
insert into tableb(tablea_id, quantity) values(1, 9);


Show that our work has borne fruit:


Time for our first procedure. Make sure the Script Explorer tab is open, click on our new DB, then the Procedures radio button. Click the “New” icon over the files area, and name our new procedure tablesum. You can see that the IDE has created the procedure, updated the Procedures window to show it’s existence and opened it up for you in the editing pane:


The essence of our stored procedure will be to accept an “ID” as a parameter, then do a sum for each row in Table B with that tablea_id and put it into the Table A sum field. Our stored procedure looks like the image below. Save the procedure by clicking on the Save icon in the Editor pane:


Click on the command tab and execute the stored procedure. Remember to supply the one parameter, the TableA id to the stored procedure. Then "select all" from the items from Table A and we can see that tableA was updated with the sum of rows in tableb:


Now it’s time for a trigger. What we want is anytime a row is modified in Table B, to fire our stored procedure to re-sum for Table A. From the Script Explorer, make sure you're on the phptest database then click on the Triggers radio button. We're putting a trigger on tableb because we want tablea updated anytime something happens to tableb. Let's start with the After Update trigger. Click on the New icon on the Trigger Grid to create a trigger placeholder and default code (Sorry the picture is a little squished):


Now let’s write the trigger code. Basically we’re going to tell MySQL to call our procedure with the ID of the row that was just modified. Remember to save the trigger by clicking on the Save icon:


Now you’ll need to put the trigger online, because so far it is only stored in the DB but not in the trigger position. Click the on/off switch in the trigger grid to enable the trigger. One of two things will happen: it will either “go green” meaning that the trigger is online, or it will stay red and you’ll get a syntax (or other) error. For now, we’ll assume that you’ve entered the trigger correctly and it is now online:


Go to the phpMyAdmin pane, browse Table B, edit one of the quantity records and save it. I'm going to change row 2 to 25. Then go to the Command Prompt and press the up arrow to bring back the “Select * from Table A” SQL line:


Whoohoo! When we edited in phpMyAdmin, the trigger called our procedure! Now let’s implement a trigger for After Insert. Just as we did before, click on the new icon for the After Insert trigger, add the exact same code to it, save it and put it online. Now we want to do the After Delete trigger, but there’s a little change – we cannot use the “NEW” keyword, because there isn’t a new record after a delete – only an insert or update. So we use the “OLD” keyword:


Put the Delete trigger online and you’re all set! With these 3 in the green, we know that we are enforcing our logic on every possible action against tableb:


Now let’s implement a view to show us Table A. Click on the Views radio button and then the New icon. Name the view, “coolness.” Again, the editor will create default code and open the file for you. Change the view so that the code looks like this:


Save the view. If you get no message, things are good. Go back to the Command Prompt and type “select * from coolness” :


That's about it. As I said, I should have the new code up by the end of the day or tomorrow at the latest. I'll post in Announcements when it's ready to go.

/perk
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #1 on: June 02, 2008, 06:39:37 PM »

brb, changing my shorts.
Logged

hai
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #2 on: June 03, 2008, 06:30:42 AM »

perk. Its seems you might need more clients?

nicely done. i will be using this for my next project, whenever I actually get going on it...
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: June 03, 2008, 08:58:55 AM »

LOL @ need more clients...

Actually, I started this because I'm doing a lot of SP work for my clients, figure others could use my tool as well.
Glad you like it, look forward to your feedback Nuts.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #4 on: June 03, 2008, 06:40:25 PM »

I'm salivating at the prospect of downloading this and getting it fired up tonight.

Great work perky. YOU ROCK!

Cheers,
td
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #5 on: June 03, 2008, 11:48:32 PM »

Great work perky. YOU ROCK!

Most kind, coming from a guy that spent almost 4 hours into the wee hours with me working through installing MySQL UDFs TD.

Rockage backatcha Wink
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #6 on: June 04, 2008, 01:28:58 AM »


Most kind, coming from a guy that spent almost 4 hours into the wee hours with me working through installing MySQL UDFs TD.

Rockage backatcha Wink

Nooooooooooooooooooooooooooooooooooo! Was it that long? Time flies when you're having fun, besides it was early evening here, wasn't even past my bedtime.  ROFLMAO

Cheers,
td
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #7 on: June 04, 2008, 10:11:38 AM »

Yeah TD had me up wicked late last night too. Actually it was really me who had me up late.
Logged

hai
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #8 on: June 04, 2008, 10:21:53 AM »

Yeah TD had me up wicked late last night too.

Shameless hussy.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
jairez
Expert
****
Offline Offline

Posts: 164


JTFC


View Profile
« Reply #9 on: July 16, 2008, 11:42:23 PM »

Quote
"...figure others could use my tool as well."

Huh, huh huh huh, huh huh huh .... you said tool!

I'm back!  (-ish) 

What a great utility.  I'm definitely going to have to check this out!  Thanks, Perk.  Hope you and everyone here's doing well.

   - JA

btw - love Cooper.  What a cutie.
Logged

Spontaneity has it's time and place.  [Sluggo, 1990-ish]
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #10 on: July 17, 2008, 01:06:50 AM »

Huh, huh huh huh, huh huh huh .... you said tool!
Is this where we sniff our thumb? (way old inside joke)


I'm back!  (-ish) 
Long time, my friend. I hope you and yours are all well.


btw - love Cooper.  What a cutie.
Nah, spawn of Satan ATM. There is nothing that escapes his teething wrath. But thanks Wink
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #11 on: July 17, 2008, 06:40:46 AM »

Nah, spawn of Satan ATM. There is nothing that escapes his teething wrath. But thanks Wink

same response I have about ashley...
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #12 on: July 17, 2008, 09:50:46 AM »

LOL
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
leden
n00b
*
Offline Offline

Posts: 8


View Profile
« Reply #13 on: September 26, 2008, 03:09:18 PM »

I like it.  It's exactly what I need (and in idle minutes had been thinking of writing something similar one day when I have time).  It doesn't quite work for me yet though.  By changing the config.php file to my login details I've got it pointing at the database (it shows tables, their structures, etc.)  Also shows a stored procedure I created from phpmyadmin.  My problems are:
  • the command line doesn't work for ordinary queries on my database - show tables works but select * from customers doesn't - it thinks for few seconds and then returns nothing (I do have data in my customers table)
  • I can call up my stored procedure in the Editor window but when I try to save I get a no database selected error
  • I haven't been able to work out how to actually execute a stored procedure yet (either in phpMyIDE or using phpMyAdmin ['PROCEDURE fss001.GetAllCustomers can't return a result set in the given context'] - which led me to find phpMyIDE)

I'm running under WAMP5 (localhost), and installed phpMyIDE by copying it into the www directory like a web site under development, which worked nicely (http://localhost/phpMyIDE/) once I'd meddled appropriately with config.php.

If you have any help or advice that would point me in the right direction I'd be very grateful.
Logged

No links in signatures please
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #14 on: September 26, 2008, 04:29:41 PM »

I like it.  It's exactly what I need (and in idle minutes had been thinking of writing something similar one day when I have time).  It doesn't quite work for me yet though.  By changing the config.php file to my login details I've got it pointing at the database (it shows tables, their structures, etc.)  Also shows a stored procedure I created from phpmyadmin.  My problems are:
  • the command line doesn't work for ordinary queries on my database - show tables works but select * from customers doesn't - it thinks for few seconds and then returns nothing (I do have data in my customers table)
  • I can call up my stored procedure in the Editor window but when I try to save I get a no database selected error
  • I haven't been able to work out how to actually execute a stored procedure yet (either in phpMyIDE or using phpMyAdmin ['PROCEDURE fss001.GetAllCustomers can't return a result set in the given context'] - which led me to find phpMyIDE)

I'm running under WAMP5 (localhost), and installed phpMyIDE by copying it into the www directory like a web site under development, which worked nicely (http://localhost/phpMyIDE/) once I'd meddled appropriately with config.php.

If you have any help or advice that would point me in the right direction I'd be very grateful.


Greets leden and welcome to The Cache.

First - did you run check testinstall.php, and what results did you get? The reason I ask is that it looks as though the mysqli_ functions are missing from your php instance. The reason I know this, is that show tables works, but long commands do not. "short" commands use the mysql functions, multi-response commands like select use mysqli. Now, if you run testinstall.php and it says that mysqli_ functions are available, then the problem is that your config is not set up correctly to "reference itself." The way that long commands work is by "poor man's threading" - the handling mechanism at the server fires off a web request to itself to collect all the results from the command. In either case, testinstall.php will let you know.

Point 2 - no database selected - have you run the buildsql.php? Is your config file pointing to a valid database in the MySQL instance, and do you have rights to it? Again, I believe testinstall.php should take care of you.

Point 3 - Stored procedures require that point 1 is taken care of (they require the long-command handler) - but essentially, when we get point 1 and 2 covered, then you'll go to the command prompt and simply enter "call myproc()" and press return. Whatever the result(s) of the command will be output to the command prompt page.

Ping back, let's get you online!

/perk
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1] 2 3
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!