SunQuest
           Tutorials
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Codewalkers Forums > Other > Tutorials
Reload this Page

PHP Script For Creation and Manipulation of MySQL Databases

Discuss PHP Script For Creation and Manipulation of MySQL Databases in the Tutorials forum on Codewalkers.
PHP Script For Creation and Manipulation of MySQL Databases Please post all concerns and questions regarding any tutorials on the site here. If you have a request for a tutorial please post it here and start your title with REQUEST:

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
You eat, breathe and sleep innovation. Build your mobile intelligence with BlackBerry® experts this July. Register Today!
  #1  
Old September 1st, 2004, 01:49 PM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
PHP Script For Creation and Manipulation of MySQL Databases
Well this is my first post, and since this idea seems to save ALOT of work i decided i might as well post it.

I started learning php arround a week ago and i noticed from day one how ANNOYING is what you want to create a table with 20 fields, just to find out that you missed a "," and your whole query goes to the toilet.

So if someone asked you if there is a way to add, and delete tables, or even a whole database in a easy way, without any third party program, in the good old Win32 interface, without having to type a single command, wouldnt you think that guy is nuts?

Well there is a easy (and rather simple) way to do it, make a php script do all the dirty work for you by using the POST method, simple uh? Ok lets get started.I wont get too deep on explaining the html code and ill try to explain the php code to my best.

In this example you need a .html form wich has the fields to insert the username, password and location of the host (u know, root, pass, localhost)so the script can connect into MySQL.

Now in order to add a database we need a field to type in the name of the database we are going to create, and to delete a database we need another field to type in the name of the database to delete.

And finally, if we want to add a table, we need:
A textarea for typing the name of the table to create
A textarea for typing the name of the database where the table will be created.
N number of textareas for the table fields

in This example Im using 7 textareas to introduce a maxium of 7 fields into the table.

Remember to add the "<form>" tag before any textarea, and a submit button at the end of all this mess.

Heres my html file(this one is tangled in a web of html tables, so the html might look a little complicated,
the important parts here are the <textarea> tags):


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>MySQL database handler</title>

</head>
<body bgcolor="#999999" text="#000000" link="#0000ee" alink="#0000ee"
vlink="#551a8b">

<div align="center">MySQL Database Manipulation<br>
<br>

<table cellpadding="2" cellspacing="2">
<tbody>
<tr>
<td valign="top">Usernameussually root)<br>
</td>
<td valign="top"><form name="postab" action="postab.php" method="post"><textarea name="username" wrap=""
style="height: 20px; width: 100px;"></textarea><br>
</td>
</tr>
<tr>
<td valign="top">Password:<br>
</td>
<td valign="top"><textarea name="password" wrap=""
style="height: 20px; width: 100px;"></textarea><br>
</td>
</tr>
<tr>
<td valign="top">Host: (ussually localhost)<br>
</td>
<td valign="top"><textarea name="hostname" wrap=""
style="height: 20px; width: 100px;"></textarea><br>
</td>
</tr>

</tbody>
</table>

<table cellpadding="2" cellspacing="0" border="0">
<tbody>
<tr align="center">
<td valign="top" rowspan="1" colspan="2"
bgcolor="#666666" width="150">Create database</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">Database
name<input type="submit" name="PostD" value="Submit"><br>
</td>
<td valign="top"><textarea name="dataname" wrap=""
style="height: 20px; width: 400px;">DONT</textarea><br>
</td>
</tr>
<tr align="center">
<td valign="top" rowspan="1" colspan="2"
bgcolor="#666666" width="150">Delete database (All tables inside the database
will be lost!)<br>
</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">Database
name:<input type="submit" name="DelD" value="Delete"><br>
</td>
<td valign="top"><textarea name="deldata" wrap=""
style="height: 20px; width: 400px;">DONT</textarea><br>
</td>
</tr>
<tr align="center" bgcolor="#666666">
<td valign="top" rowspan="1" colspan="2"
bgcolor="#666666" width="150">Create table: (with all fields as text)<br>
</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">Table name:<br>
</td>
<td valign="top"><textarea name="tablename" wrap=""
style="height: 20px; width: 400px;">DONT</textarea><br>
</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">Field1 name:<br>
</td>
<td valign="top"><textarea name="field1" wrap=""
style="height: 20px; width: 400px;"></textarea><br>
</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">Field2 name:<br>
</td>
<td valign="top"><textarea name="field2" wrap=""
style="height: 20px; width: 400px;"></textarea><br>
</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">field3 name<br>
</td>
<td valign="top"><textarea name="field3" wrap=""
style="height: 20px; width: 400px;"></textarea><br>
</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">Field4 name<br>
</td>
<td valign="top"><textarea name="field4" wrap=""
style="height: 20px; width: 400px;"></textarea><br>
</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">Field 5 name<br>
</td>
<td valign="top"><textarea name="field5" wrap=""
style="height: 20px; width: 400px;"></textarea><br>
</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">Field6 name<br>
</td>
<td valign="top"><textarea name="field6" wrap=""
style="height: 20px; width: 400px;"></textarea><br>
</td>
</tr>
<tr>
<td valign="top" width="150" bgcolor="#666666">Field7 name<br>
</td>
<td valign="top"><textarea name="field7" wrap=""
style="height: 20px; width: 400px;"></textarea><br>
</td>
</tr>
<tr>
<td valign="top" bgcolor="#666666"><input type="submit"
name="postT" value="Create table">Pick database:<br>
</td>
<td valign="top"><textarea name="datsel" wrap=""
style="height: 20px; width: 400px;"></textarea></form><br>
</td>
</tr>

</tbody>
</table>
<br>
</div>
<br>
<br>

</body>
</html>

Note that even though i used 3 submit buttons, only one is needed.

Now to the PHP script. So far we have a html that post all this info. First lets retrieve the posted data:
php Code:
Original - php Code 
  1.  
  2. <?php
  3. $US = $_POST['username'];
  4. $PW = $_POST['password'];
  5. $HO = $_POST['hostname'];
  6. $PD = $_POST['dataname'];
  7. $DD = $_POST['deldata'];
  8. $PT = $_POST['tablename'];
  9. $DS = $_POST['datsel'];
  10. $F1 = $_POST['field1'];
  11. $F2 = $_POST['field2'];
  12. $F3 = $_POST['field3'];
  13. $F4 = $_POST['field4'];
  14. $F5 = $_POST['field5'];
  15. $F6 = $_POST['field6'];
  16. $F7 = $_POST['field7'];
  17. ?>

Now we got 14 values that we will be using troughout the script. First lets use $US, $PW and $HO to connect to the sql server:
php Code:
Original - php Code 
  1.  
  2. <?php
  3. //connect to DB
  4. $dbcnx = mysql_connect( $HO , $US , $PW );
  5. if (!$dbcnx) {
  6. die( '<p><b>Unable to connect to database</p></b>'. mysql_error());
  7. }
  8. ?>

Great, we connected to MySQL by using the username and password we submitted earlier on.
Now lets make the first task: Create a database, since we dont always want to create a database
(maby we just want to add a table)we want to give the user the option to cancel this task, we do this by typing in the form "DONT":

php Code:
Original - php Code 
  1.  
  2. if ($PD != "DONT") { //If it aint "DONT" then it means the user wants to create a database
  3. $sql = "CREATE DATABASE $PD;";
  4. if ( @mysql_query($sql) ) {
  5.  echo('<p>Database successfully created!</p>');
  6. } else {
  7.  die($sql .'<p>Error creating Database. showing syntax: ' .
  8.      mysql_error() . '</p>');//add $sql so we can check if it was a syntax error(maby the user typed a ",")
  9. }
  10. }//end of database creation
  11.  


The same code applies for deleting a database, but instead or using the CREATE command we use the DROP command:

php Code:
Original - php Code 
  1.  
  2.  
  3. if ($DD != "DONT") { //user wants to delete a DATABASE
  4. $sql = "DROP DATABASE $DD;";
  5. if ( @mysql_query($sql) ) {
  6.  echo('<p>Database successfully Deleted!</p>');
  7. } else {
  8.  die($sql .'<p>Error Deleting Database. showing syntax: ' .
  9.      mysql_error() . '</p>');//add $sql so we can check if it was a syntax error
  10. }
  11. }//end of database deletion 
  12.  


Easy uh?, now lets do the "hardest" part of the code: Creating a table, we need to first select a database
php Code:
Original - php Code 
  1.  
  2. //select DB (THIS IS FOR TABLE CREATION)
  3. if(! mysql_select_db($DS) ) {
  4. echo( '<p><b>Unable to locate  the database.. </p></b>'. mysql_error() );
  5. }

Once selected, we can send the sql query with all the data recompiled from the form. As before, if user typed "DONT" in the form, the following code wont execute. Also
i added error handlers wich will show the query in case it was a syntax error (maby user typed 2 "," etc...)
php Code:
Original - php Code 
  1.  
  2. if ($PT != "DONT") { //user wants to create a table 
  3. //Create table
  4. $sql = "CREATE TABLE $PT (
  5.         ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  6.         $F1
  7.         $F2
  8.         $F3
  9.         $F4
  10.         $F5
  11.         $F6
  12.         $F7
  13. )";
  14. if ( @mysql_query($sql) ) {
  15.  echo('<p>Table successfully created!</p>');
  16. } else {
  17.  die($sql .'<p>Error creating Table. showing syntax: ' .
  18.      mysql_error() . '</p>');//add $sql so we can check if it was a syntax error
  19. }
  20. }//end of table creation
  21.  
  22. ?>


Thats pretty much it, but there is one thing to note:
php Code:
Original - php Code 
  1.  
  2. //Create table
  3. $sql = "CREATE TABLE $PT (
  4.         ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  5.         $F1 //----there is no "," here
  6.         $F2 //----there is no "," here
  7.         $F3 //----there is no "," here
  8.         $F4 //----there is no "," here
  9.         $F5 //----there is no "," here
  10.         $F6 //----there is no "," here
  11.         $F7 //----there is no "," here
  12. )";


Tipically i would add commas to the query, but in this case, where the form will ALWAYS post 7 values for the fields,
if we dont type anything on one or more fields the sql query would be something like this:
----------------------------------------------
CREATE TABLE $PT (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
field1 text,
field2 text,
,//NOTE THE LONELY COMMAS
,)
----------------------------------------------
Wich would cause a syntax error, wich would mean we would have to add a 7 field table
for the script to work AND we would have to specify the field type in the script. To make it more flexible,
the user has to type the field name AND type followed by a comma.

For example, if we want to make a table with 2 fields, one named "NumbeR" (of type INT) and one named "ColoR" (of type TEXT)
we have to type in the form:
In the table creation field 1: "NumbeR INT,"
In the table creation field 2: "ColoR TEXT,"

This actually gives more flexibility, We could also do the following:

In the table creation field 1: "NumbeR INT, ColoR TEXT,"

Wich means we can add more than 7 fields into a table with the form, by typing them together.

Second thing to note, I add a field called "ID" wich i use to identify a item inside my tables, you can remove this from the query.

Pretty much thats it, you use the form to give the php script values and the script uses those values to create or delete a database or table.
You can also connect remotely to another sql server by typing the ip adress instead of "localhost" or you can upload this script
to computer running a sql server and edit its databases once given the proper password and user.

There is alot more i could add, remove from the script, but it works fine so far (arround 12 hrs of testing) But this example will give you an idea of how to implement this technique for updating tables, deleting tables, searching tables etc...
Here is the full php script,
php Code:
Original - php Code 
  1.  
  2. <?php
  3. $US = $_POST['username'];
  4. $PW = $_POST['password'];
  5. $HO = $_POST['hostname'];
  6. $PD = $_POST['dataname'];
  7. $DD = $_POST['deldata'];
  8. $PT = $_POST['tablename'];
  9. $DS = $_POST['datsel'];
  10. $F1 = $_POST['field1'];
  11. $F2 = $_POST['field2'];
  12. $F3 = $_POST['field3'];
  13. $F4 = $_POST['field4'];
  14. $F5 = $_POST['field5'];
  15. $F6 = $_POST['field6'];
  16. $F7 = $_POST['field7'];
  17.  
  18. //UNCOMMENT THE FOLLOWING LINES IF YOU WANT TO SEE ALL THE VARIABLES POSTED.
  19. //echo '<b>Variables:<br>';
  20. //echo $US;
  21. //echo '<br>';
  22. //echo $PW;
  23. //echo '<br>';
  24. //echo $HO;
  25. //echo '<br>';
  26. //echo $PD;
  27. //echo '<p>';
  28. //echo $DD;
  29. //echo '<br>';
  30. //echo $PT;
  31. //echo '<br>';
  32. //echo $DS;
  33. //echo '<br>';
  34. //echo $F1;
  35. //echo '<br>';
  36. //echo $F2;
  37. //echo '<br>';
  38. //echo $F3;
  39. //echo '<br>';
  40. //echo $F4;
  41. //echo '<br>';
  42. //echo $F5;
  43. //echo '<br>';
  44. //echo $F6;
  45. //echo '<br>';
  46. //echo $F7;
  47. //echo '<br></b>';
  48.  
  49.  
  50. ?>
  51.  
  52. <?php
  53. //connect to DB
  54. $dbcnx = mysql_connect( $HO , $US , $PW );
  55. if (!$dbcnx) {
  56. die( '<p><b>Unable to connect to database</p></b>'. mysql_error());
  57. }
  58.  
  59.  
  60. if ($PD != "DONT") { //user wants to create a DATABASE
  61. $sql = "CREATE DATABASE $PD;";
  62. if ( @mysql_query($sql) ) {
  63.  echo('<p>Database successfully created!</p>');
  64. } else {
  65.  die($sql .'<p>Error creating Database. showing syntax: ' .
  66.      mysql_error() . '</p>');//add $sql so we can check if it was a syntax error
  67. }
  68.  
  69.  
  70. }//end of database creation
  71. if ($DD != "DONT") { //user wants to delete a DATABASE
  72. $sql = "DROP DATABASE $DD;";
  73. if ( @mysql_query($sql) ) {
  74.  echo('<p>Database successfully Deleted!</p>');
  75. } else {
  76.  die($sql .'<p>Error Deleting Database. showing syntax: ' .
  77.      mysql_error() . '</p>');//add $sql so we can check if it was a syntax error
  78. }
  79. }//end of database deletion
  80.  
  81.  
  82.  
  83. //select DB (THIS IS FOR TABLE CREATION)
  84. if(! mysql_select_db($DS) ) {
  85. echo( '<p><b>Unable to locate  the database.. </p></b>'. mysql_error() );
  86. }
  87.  
  88. if ($PT != "DONT") { //user wants to create a table 
  89. //Create table
  90. $sql = "CREATE TABLE $PT (
  91.         ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  92.         $F1
  93.         $F2
  94.         $F3
  95.         $F4
  96.         $F5
  97.         $F6
  98.         $F7
  99. )";
  100. if ( @mysql_query($sql) ) {
  101.  echo('<p>Table successfully created!</p>');
  102. } else {
  103.  die($sql .'<p>Error creating Table. showing syntax: ' .
  104.      mysql_error() . '</p>')</