homepage Welcome to WebmasterWorld Guest from 54.235.16.159
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL Query Builder
NickCoons




msg:4134371
 11:57 pm on May 17, 2010 (gmt 0)

I want to create a page or a set of pages that will be populated with the structure of a given database some that someone can use a GUI interface to execute SELECT statements. In other words, I want a layperson to be able to have a GUI interface to pull data from a database based on their own criteria at the moment.

Let's say I have three tables: clients, client_phones, and client_addresses.

So the first page would show a list of these tables and ask them to select the table or tables from which they want information. Then, they would be presented with the available columns within their selections so they could choose which columns they wanted returned. Next, they would be presented with options to choose the conditions (i.e. WHERE condition is true). Finally, they'd be able to choose the order of the results. From the data they've input, the script would be able to built the SQL query in the form "SELECT columns FROM tables WHERE conditions ORDER BY criteria".

Does anyone know if there is something out there that already does this? Or, does anyone know of a better approach that I should take? Basically, I want my users to be able to select data from the database without having to be SQL literate, and without me having to pre-create every report that they could possibly want.

 

coopster




msg:4134381
 12:37 am on May 18, 2010 (gmt 0)

Have you considered phpMyAdmin?

NickCoons




msg:4134392
 12:56 am on May 18, 2010 (gmt 0)

As far as I know, phpMyAdmin doesn't actually do any of the things that I am wanting to do. It will let you run SQL SELECT queries, but you have to build the queries yourself rather than choose the options from a GUI interface.

Unless I'm missing something?

rocknbil




msg:4134437
 2:54 am on May 18, 2010 (gmt 0)

I think it does, I remember seeing it somewhere, but something tells me you don't want your users accessing your PHPmyAdmin. :-)

This is not that difficult, it's tedious, but I've done similar stuff.

First, you write a function that returns all fields for the selected table and store it in an array. Write it anonymously so it can query any table and return this list. In this function comes a little tedium - you most likely want to skip any autoincrement fields, and internal usage only fields, or sensitive fields. Though the approach below may not be the best and there may be a more efficient way, I just popped it off for concept:

So at the top of your program, you have

$valid_tables = Array('table1','table2','table3');
if (in_array($_POST['tablename'],$valid_tables)) { $field_list = fields($_POST['tablename']); }
else { die("Uhh, only valid tablenames allowed"); }

Note we're only using defined table names, so this will allow us to use the post value directly. (In truth you should at least alias these.)

The function:


function fields($table) {
if (! isset($table)) { die("No table in field name function"); }
$fields = Array ();
$counter=0;
$result = mysql_query("show columns from $table");
// Remove mysql_error when deployed
if (!$result) { die("Could not get table names: " . mysql_error()); }
while ($row = mysql_fetch_array($result)) {
if (($row[0]=='id') or ($row[0]=='password')) { continue; }
$fields[$counter]=$row[0];
$counter++;
}
return $fields;
}


Now we're going to change that a little. :-)

Next, for your various forms, you'll need an array that associates the field type with a form input type: you want select lists for data, integer values, text inputs for varchar, text, etc.

Note the above uses $row[0] for the table field name. On investigation, you'll find that one of the other indices will contain the data type: int, varchar, text, etc. So what you'll do is something like this, eliminating the need for $counter in a list array:

$fields[$row[0]] = $row[1];

Giving you an associative array:

'fname' => 'varchar',
'description' => 'text',
'created' => 'date',
'quantity' => 'int'

So now you can generate your form so it will output the appropriate form controls based on the type:


foreach ($fields as $fld=>$type) {
switch $type {
case 'int':
$form .= create_int_dropdown($_POST['tablename'],$fld);
break;
case 'varchar':
case 'text':
$form .= create_text_field($_POST['tablename'],$fld);
break;
//etc
}
}


Your functions will output form controls based on the table and the type of field to be queried. Textual ones are easy, dates are not too hard. You may have to write conditionals for integer ones though.

This will take some fiddling but this will get you the right elements in the forms. Important to consider for the next step is when you generate select lists and radio buttons, make sure the default value (top value in selects) is empty. You'll see why that's important when we build the where.

When submitted, you'll still have the table and it's field list at hand. A select statement that is just "select * from $table" will display all records, so your $where will be built only based on the form values. If there's no input, it displays all records. This is why it's so important that the form doesn't submit any values for something that was not intentionally selected.

Warning: presuming you've already cleansed $_POST!


$table = $_POST['tablename'];
$where = null; // squelch concatenation errors
//
foreach ($fields as $fld => $type) {
if (isset($_POST[$fld]) and (! empty($_POST[$fld)) {
if ($where) { $where .= ' and'; } // not needed first time
if ($type=='int') { $comp = "$fld=$_POST[$fld]"; }
else if ($type=='text') {
if ($_POST['txt_type']=='starts_with') { $comp = "$fld like '%$_POST[$fld]'"; }
else if ($_POST['txt_type']=='ends_with') { $comp = "$fld like '$_POST[$fld]%'"; }
else { $comp = "$fld like '%$_POST[$fld]%'"; }
}
// etc., for each data type
$where .= " $comp"; // NOTE SPACE!
}
}


Note the little surprise I threw in there for text you'll have to consider. The same is true of int and date, you'll want to compose >, >=, <, <= or = operators based on the form input for int's and dates.

All that's left is to build the select, order, and limit.

list($order,$limit) = your_order_limit_function($table);

$select = "select * from $table";
if ($where) { $select .= " where $where"; } // also note space
$select .= " $order $limit";

If you want specific columns, what you do is generate these as checkboxes in the form based on the selected table, and instead of doing select * from . . . you use the checkboxes to built a comma separated list.

You'll encounter lots of challenges, and I left a lot of blanks you'll need to fill out (like the order/limit which has a lot to do with pagination links) . . . but that's a starter on how I approach these. When you get to apply the entire concept on relational tables with joins, it gets really fun. :-)

NickCoons




msg:4135002
 11:41 pm on May 18, 2010 (gmt 0)

Thank you! As I begin working on this project, I think your input and code examples will come in handy.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved