|
|
|
|
|
|
| |
Hi All,
The most tedious part of coding any db interaction is the defining/initialization of the variables that interact with the db. All those POST/GET elements, the global elements and the development of the sql statements.
This code takes the work out of that. Simply fill in the form to point it to a database and table and run it...the output shown is to :
1. initialize all db variables
2. generate the POST/GET values from the forms.
3. generate the global variables
4. generate the insert statement
5. generate the update statement
It will look for primary keys, adapt to place quotes only around the text elements, and fill in the default db values where needed.
There is always room for improvement, but this really reduces the workload when working on pages that map to one table.
You can copy the code generated from the screen, or for a little more formatted code, view source, copy and remove the <br> tags
enjoy,
bastien
bastien koert
Aug 2004
www.bastienkoert.net
This code writes out all the needed DB fields for insert / update
statements as well as
generating the $global code, the post/get code and the initialization code
with defaults from
the db tables
| <?php
//control code
if(!$_POST['submit']){
show_form();
}else{
generate_scripts();
}//end if
//------------------------------------------------------------------------
// show form function
//------------------------------------------------------------------------
function show_form()
{
echo "
<html><body>
<form action=".$_SERVER['PHP_SELF']." method=post>
<table>
<tr><td>Table Name:</td><td><input type='text' name='tablename'
size='25'></td></tr>
<tr><td>DB Name:</td><td><input type='text' name='dbname'
size='25'></td></tr>
<tr><td>User Name:</td><td><input type='text' name='uname'
size='25'></td></tr>
<tr><td>Password:</td><td><input type='text' name='pass'
size='25'></td></tr>
<tr><td>Host:</td><td><input type='text' name='host'
size='25'></td></tr>
<tr><td>Get / Post:</td><td><select name='gp_type'>
<option value='_POST'>Post
<option value='_GET'>Get
</select>
</td></tr>
<tr><td>Require addslashes / stripslashes:</td><td><input
type='checkbox' alt='click to add' name=slashes value='yes'>
<tr><td colspan=2><input type='submit' name='submit' value='generate
scripts'></td></tr>
</table>
</form>
</body>
</html>";
}
//------------------------------------------------------------------------
// generate code function
//------------------------------------------------------------------------
function generate_scripts()
{
global $dbname;
//initialize variables
$table_name = '';
$dbname = '';
$uname = '';
$pass = '';
$host = '';
$type = '';
$slashes = '';
$pk_id = 0;
$pk_num = 0;
$sql = '';
$update_query = '';
$insert_query = '';
$cnt = 0;
$my_global = 'global ';
//get form data
$table_name = $_POST['tablename'];
$dbname = $_POST['dbname'];
$uname = $_POST['uname'];
$pass = $_POST['pass'];
$host = $_POST['host'];
$type = $_POST['gp_type'];
$slashes = $_POST['slashes'];
$numeric_field_types_array = array('int','tin','flo','dec','big,
dou','sma','med');
//sql statement
$sql = "show columns from $dbname.$table_name";
//connection info
if (!($conn=mysql_connect($host, $uname, $pass))) {
printf("error connecting to DB by user = $uname and pwd=$pass");
exit;
}
$db=mysql_select_db($dbname,$conn) or die("Unable to connect to
database1");
//run query
$result = mysql_query($sql, $conn)or die("Unable to query local database
<b>". mysql_error()."</b><br>$sql");
if (!$result){
echo "database query failed. try again";
show_form();
die();
}// end if
//do the results and generate the code
while ($rows = mysql_fetch_array($result)){
//get the data set and stick into a set of arrays
$fields[] = $rows[0];
$types[] = $rows[1];
$keys[] = "". $rows[3];
$nulls[] = "". $rows[2];
$defaults[] = "". $rows[4];
$extras[] = "". $rows[5];
}
$cnt = count($fields);
//get the primary key for the table
foreach($keys as $key => $value){
if ($value=="PRI"){
$pk_id = $key;
if (strtolower(substr($types[$pk_id], 0, 6)) != "varcha"){
$pk_num = true;
}else{
$pk_num = false;
}// end if
} // endfor
}// end foreach
//get the initial variabales
echo "\n\n<p><h3>setting initial variables</h3><br>\n";
for ($x=0; $x < $cnt; $x++){
echo "\$$fields[$x] = \"$defaults[$x]\";<br>\n";
}
echo "\n\n<p><h3>setting post/get values</h3><br>\n";
for ($x=0; $x < $cnt; $x++){
if ($slashes=="yes"){
echo "\$$fields[$x] =
addslashes(@\$".$type."['$fields[$x]']);<br>\n";
}else{
echo "\$$fields[$x] =
@\$".$type."['$fields[$x]'];<br>\n";
}// end if
} // end for
//get the insert statement
echo "\n\n<br><p><h3>setting insert statement</h3><br>\n";
$insert_query = " insert into $table_name (";
for ($x=0; $x < $cnt; $x++){
$insert_query .= "$fields[$x], ";
}// end for
$insert_query .= ") values (";
for ($x=0; $x < $cnt; $x++){
if (in_array(substr($types[$x],0,3), $numeric_field_types_array)){
$insert_query .= "\$$fields[$x], ";
}else{
$insert_query .= "'\$$fields[$x]', ";
}// end if
}// end for
$insert_query = substr($insert_query, 0, strlen($insert_query)-2) . ");";
echo $insert_query;
//get the update statement
echo "\n\n<br><p><h3>setting update</h3><br>\n";
$update_query = "update $table_name set ";
for ($x=0; $x < $cnt; $x++){
if (in_array(substr($types,0,3), $numeric_field_types_array)){
$update_query .= "$fields[$x]=\$$fields[$x], ";
}else{
$update_query .= "$fields[$x]='\$$fields[$x]', ";
}// end if
}// end for
$update_query = substr($update_query, 0, strlen($update_query)-2);
//rows id'd by pprimary key
if ($pk_num == true){
$update_query .= " where $fields[$pk_id] = \$$fields[$pk_id]";
}else{
$update_query .= " where $fields[$pk_id] = '\$$fields[$pk_id]'";
}//end if
echo $update_query;
//get the primary key for the table
echo "\n\n<br><p><h3>setting global variables</h3><br>\n";
for ($x=0; $x < $cnt; $x++){
$my_global .= "\$$fields[$x], ";
} // end for
$my_global = substr($my_global,0,strlen($my_global) - 2);
echo "$my_global;";
//get the editable values from the db
echo "\n\n<br><p><h3>getting edit variables</h3><br>\n ";
for ($x=0; $x < $cnt; $x++){
if ($slashes=="yes"){
echo "\$$fields[$x] \t\t= stripslashes(\$row['$$fields[$x]']);<br>\n
";
}else{
echo "\$$fields[$x] \t\t= \$row['$$fields[$x]'];<br>\n ";
}// end if
}// end for
}// end function
?> | |
|
|
| This program allows you to upload an ODBC ressource - i.e. an MS-Access database to a MySQL server.
Categories : Databases, MySQL, Complete Programs, PHP, Databases | | | bookmarker - PHP, PHPLIB, MySQL WWW based bookmark manager
Categories : MySQL, PHP, MySQL, Complete Programs, Databases | | | Accepts a database & hostname from a user and then HTTP username and password. Uses this to connect to a MySQL database. Produces a form based on the tables it finds there to allow the user to do SELECTs, INSERTs, and DELETEs.
Categories : Databases, PHP, MySQL, Complete Programs | | | phpAds, a complete banner and ad management system with detailled tracking and stats.
Categories : MySQL, Complete Programs, Ecommerce, PHP, Databases | | | Point and Click Interface ala MS Access for creating SQL statements.
Categories : MySQL, Complete Programs, General SQL, PHP, Databases | | | Message of the Day - Random Message (Needs MySQL!)
Categories : Databases, HTML and PHP, PHP, MySQL | | | email new items in db
Categories : PHP, Email, Databases, MySQL, Beginner Guides | | | Alternating background color for HTML table rows
Categories : PHP, Databases, MySQL, HTML and PHP | | | color codes for positive and negative numbers
Categories : PHP, MySQL, Databases, HTML | | | Authorize Me! An authentication script.
Categories : MySQL, Databases, Authentication, PHP | | | A very simple way to build and do a hierarchical html categories browser without javascript , just using html php and mySql
Categories : HTML and PHP, Databases, Algorithms, PHP, MySQL | | | Linked comboboxes with php-mysql & javascript
Categories : PHP, Java Script, Databases, MySQL | | | mysql_escape_string
Categories : PHP, MySQL, Databases, Strings | | | Automatically printing the contents of an sql table in MySQL.
Categories : MySQL, PHP, HTML and PHP, Databases | | | usercounter class
Categories : PHP, PHP Classes, Databases, MySQL, Environment Variables | |
| | | | bastien koert wrote : 1177
It seems there may be some confusion about who this script is for and what its intended use was. This is totally a development tool, not something to be left lying around on a server somewhere for the general vistor to the site.
Since its use is by developers, security in the script is pretty much absent, for logical reasons.
bastien
| | | | bastien koert wrote : 1179
Use the attached file, it contains some corrections to the above code.
| | | | matthew waygood wrote : 1220
I see what you mean about security risk (showing everyone your username and password)
//connection info
if (!($conn=mysql_connect($host, $uname, $pass))) {
printf("error connecting to DB by user = $uname and pwd=$pass");
exit;
}
I did something myself, so I can change the database structure and/or forms without it causing errors in the code. I have also included scope for NULL values, and forced compatibility matching of data to field type. So putting "x1" in an INT field wont cause errors.
I also allowed the creation of SEARCH queries, using matching wildcard comparisons and not exact matching. eg WHERE name="bob" -> WHERE name="%bob%"
| | | | matthew waygood wrote :1221
What would happen if i tried to submit a text value to a numberic field?
field--type---test value
name---text---bob
age----int----bob
dod----date---now()
| |
|
|
|
