Make Your Own Phpmyadmin

hippiman

Apr 9 2007, 05:26 PM

First of all, I'm letting you know that this is nowhere near as good as PHPMyAdmin. It's just a tutorial on how to view all your databases, tables, and what's in the tables, and be able to edit the tables. I haven't even put in a way to add databases, tables, or columns (yet). This was just a test thing I made up to see if I knew what I was doing with PHP and MySql.

Feel free to ask any questions. That's why I'm here. If I don't know the answer, I'll be glad to look it up or ask someone that knows. I need credits!!!

Now, as you've probably seen in other tutorials, you need to create a "config.php". It will have connect you to MySql.

CODE

<?php
  $dbserver="localhost";
  $user="root";
  $passwd="password";
  $connect=mysql_connect($dbserver,$user, $passwd);
?>

Once you have that, you need to make your "index.php", or whatever you want to call it. Put it in the same directory as your config file.

Now, you need to have it show your databases. (If you don't have permission to do "SHOW DATABASES", then this part wont work.)

CODE

echo "<center><h1>Databases:</h1>";

$sql = "SHOW DATABASES;";
$result = mysql_query($sql, $connect);

?><form method='POST'><select name='dbselect' size='5'>"<?php    //the select tag is a drop down list to select the database.
for($i=0;$i<mysql_numrows($result);$i++) {
  $data = mysql_result($result,$i,"database");
  if($data!="information_schema" && $data != "mysql")  //Those two databases are MySql's, I don't want to mess with them.
    if($_POST['dbselect']==$data) echo "<option selected>" . $data . "</option>";
    else echo "<option>" . $data . "</option>";    //If there's already a DB selected, it will stay selected when you reload.
}
echo "</select><input type='submit' value='USE DATABASE'></form>";//the input is the button to select the DB

Next, if there is a DB selected(in the POST), then it will call our getTables function.

CODE

if($_POST['dbselect']) getTables($_POST['dbselect'],$connect);
function getTables($db, $connect) {
  mysql_select_db($db);   //select the DB
  $sql="SHOW TABLES;";
  $result=mysql_query($sql,$connect);

  echo "<h1>" . $db . ":</h1>"; //display the name of the DB
  echo "<form method='POST'><input name='dbselect' type='hidden' value='" . $db . "'><input name='tbselect' type='hidden' value='" . $_POST['tbselect'] . "'><select name='tbselect' size=5>"; //Makes sure when you select the table, the DB is selected
  for($i=0;$i<mysql_numrows($result);$i++) {
    $data= mysql_result($result, $i, "tables_in_" . $db);
    if($_POST['tbselect']==$data) echo "<option selected>" . $data . "</option>";  //keeps the table selected
    else echo "<option>" . $data . "</option>";
  }
  echo "</select><input type='submit' value='SELECT * FROM'></from>"; //button to select everything from the table.
}

Once you've selected the table, you need to display the data.

CODE

if($_POST['tbselect']) selectAll($_POST['dbselect'],$_POST['tbselect'],$connect);
function selectAll($db, $tb, $connect) {
  mysql_select_db($db);
  $sql="SHOW COLUMNS FROM " . $tb . ";";
  $result=mysql_query($sql,$connect);

  echo "<h1>" . $tb . ":</h1>";  //show the name of the selected table.
  echo "<form method='POST'><input name='dbselect' type='hidden' value='" . $db . "'><input name='edited' type='hidden' value='1'>";  //Keep the DB and table selected
  echo "<table border='1'><tr>";
  for($i=0;$i<mysql_numrows($result);$i++) {  //shows the names of the columns at the top of the table
    echo "<td>" . mysql_result($result, $i, "field") . "</td>";
    $colnames[$i]=mysql_result($result, $i, "field");
  }
  $numcols=$i;
  echo "</tr>";

  $sql="SELECT * FROM " . $tb . ";";
  $result=mysql_query($sql,$connect);
  $numrows=mysql_numrows($result);

  for($y=0;$y<$numrows;$y++) { //these loops make txtBoxes so you can change the data in the table.
    for($x=0;$x<$numcols;$x++) {
      if(!$x) echo "<td>" . mysql_result($result, $y, $colnames[$x]) . "</td>"; //echoes the ID without a txtBox.
      else echo "<td><input name='" . ($x + $y*$numcols) . "' type='text' value='" . mysql_result($result, $y, $colnames[$x]) . "'></td>";  //($x+$y*numcols) is which TD it is from left to right, then top to bottom(like reading)
    }
    echo "</tr>";
  }
  echo "</form><form method='POST'><input type='hidden' name='newrow' value='asdf'><input type='hidden' name='dbselect' value='" . $db . "'><input type='hidden' name='tbselect' value='" . $tb . "'><tr>";//form to add a row
  for($x=0;$x<$numcols;$x++){
    if($x) echo "<td><input type='text' name='" . $x . "'></td>";
    else echo "<td><input type='Submit' value='Add New Row'</td>";
  } //makes an extra row so you can add a row if you need to.
  echo "</form></table>";
  echo "<form method='POST'>Delete row by id:<input type='text' name='delete'><input type='hidden' name='dbselect' value='" . $db . "'> <input type='hidden' name='tbselect' value='" . $tb . "'></form>"; //form that deletes a row

}

Next, you need to be able to Update the table. If they submit the update form, it will change the values in the DB.

CODE

if($_POST['edited']) {
  updateTable($_POST['dbselect'], $_POST['tbselect'], $connect);
  echo "<br>Table Updated.";
}
function updateTable($db, $tb, $connect) {
  mysql_select_db($db);
  $sql="SHOW COLUMNS FROM " . $tb . ";";
  $result=mysql_query($sql, $connect);
  $numcols=mysql_numrows($result);

  for($i=0;$i<$numcols;$i++) {
    $colname[$i]=mysql_result($result, $i, "field");
  } //gets the names of the columns

  $sql="SELECT * FROM " . $tb . ";";
  $result=mysql_query($sql, $connect);
  $numrows=mysql_numrows($result);

  for($y=0;$y<$numrows;$y++) { //these loops make the update string in the format "UPDATE table SET column = value WHERE //id=idOfRow (One for every row)
    $sql="UPDATE " . $tb . " SET ";
    for($x=0;$x<$numcols-1;$x++){//$numcols-1 because we're not changing the ID
      $sql .= $colname[$x+1] . "='" . $_POST[($x+$y*$numcols+1)] . "'";
      if($x!=$numcols-2) $sql .= ","; //adds a comma if there's another column to add
    }
    $sql .= " WHERE id=" . ($y+1) . ";";
    mysql_query($sql,$connect);
  }
}

Next, (we're almost done) you need to make a way to add a row. If they submitted the addRow form, it will add the row.

CODE

if($_POST['newrow']) addRow($_POST['dbselect'],$_POST['tbselect'],$connect);
function addRow($db, $tb, $connect) {
  mysql_select_db($db);
  $sql="SHOW COLUMNS FROM " . $tb . ";";
  $result=mysql_query($sql, $connect);
  $numcols=mysql_numrows($result);
  for($i=0;$i<$numcols;$i++) {
    if(mysql_result($result, $i, "field")!="id") $colname[$i]=mysql_result($result, $i, "field");
  }  //gets the names of the columns
  $sql="INSERT INTO " . $tb . "(";
  for($x=0;$x<$numcols;$x++){
    if($colname[$x]) $sql .= $colname[$x];
    if($x!=$numcols-1 && $x) $sql .= ",";
  } //doesn't insert into the id column, it's auto_increment
  $sql .= ") VALUES (";
  for($x=0;$x<$numcols;$x++){
    if($x) $sql .= "'" .  $_POST[$x] . "'";
    if($x!=$numcols-1 && $x) $sql .= ",";
  }
  $sql .=");";
  mysql_query($sql,$connect);
}

Finally, the last function. A function to delete a row by its ID. If they've submitted the delete form, it deletes the row they entered.

CODE

if($_POST['delete']) {
  mysql_select_db($_POST['dbselect']);
  $sql="DELETE FROM " . $_POST['tbselect'] . " WHERE id=" . $_POST['delete'] . ";";
  mysql_query($sql,$connect);
}

Here is my entire code:

CODE

<head><title>LOCALHOST</title></head>
<center><a href='tradedvds'>Trade Dvds</a></center>
<center><a href='forum'>Forum</a></center>
<?
require_once("config.php");

echo "<center><h1>Databases:</h1>";

$sql = "SHOW DATABASES;";
$result = mysql_query($sql, $connect);

?><form method='POST'><select name='dbselect' size='5'>"<?
for($i=0;$i<mysql_numrows($result);$i++) {
  $data = mysql_result($result,$i,"database");
  if($data!="information_schema" && $data != "mysql")
    if($_POST['dbselect']==$data) echo "<option selected>" . $data . "</option>";
    else echo "<option>" . $data . "</option>";
}
echo "</select><input type='submit' value='USE DATABASE'></form>";

function getTables($db, $connect) {
  mysql_select_db($db);
  $sql="SHOW TABLES;";
  $result=mysql_query($sql,$connect);

  echo "<h1>" . $db . ":</h1>";
  echo "<form method='POST'><input name='dbselect' type='hidden' value='" . $db . "'><input name='tbselect' type='hidden' value='" . $_POST['tbselect'] . "'><select name='tbselect' size=5>";
  for($i=0;$i<mysql_numrows($result);$i++) {
    $data= mysql_result($result, $i, "tables_in_" . $db);
    if($_POST['tbselect']==$data) echo "<option selected>" . $data . "</option>";
    else echo "<option>" . $data . "</option>";
  }
  echo "</select><input type='submit' value='SELECT * FROM'></from>";
}

function selectAll($db, $tb, $connect) {
  mysql_select_db($db);
  $sql="SHOW COLUMNS FROM " . $tb . ";";
  $result=mysql_query($sql,$connect);

  echo "<h1>" . $tb . ":</h1>";
  echo "<form method='POST'><input name='dbselect' type='hidden' value='" . $db . "'><input name='edited' type='hidden' value='1'>";
  echo "<table border='1'><tr>";
  for($i=0;$i<mysql_numrows($result);$i++) {
    echo "<td>" . mysql_result($result, $i, "field") . "</td>";
    $colnames[$i]=mysql_result($result, $i, "field");
  }
  $numcols=$i;
  echo "</tr>";

  $sql="SELECT * FROM " . $tb . ";";
  $result=mysql_query($sql,$connect);
  $numrows=mysql_numrows($result);

  for($y=0;$y<$numrows;$y++) {
    for($x=0;$x<$numcols;$x++) {
      if(!$x) echo "<td>" . mysql_result($result, $y, $colnames[$x]) . "</td>";
      else echo "<td><input name='" . ($x + $y*$numcols) . "' type='text' value='" . mysql_result($result, $y, $colnames[$x]) . "'></td>";
    }
    echo "</tr>";
  }
  echo "</form><form method='POST'><input type='hidden' name='newrow' value='asdf'><input type='hidden' name='dbselect' value='" . $db . "'><input type='hidden' name='tbselect' value='" . $tb . "'><tr>";
  for($x=0;$x<$numcols;$x++){
    if($x) echo "<td><input type='text' name='" . $x . "'></td>";
    else echo "<td><input type='Submit' value='Add New Row'</td>";
  }
  echo "</form></table>";
  echo "<form method='POST'>Delete row by id:<input type='text' name='delete'><input type='hidden' name='dbselect' value='" . $db . "'> <input type='hidden' name='tbselect' value='" . $tb . "'></form>";

}
function updateTable($db, $tb, $connect) {
  mysql_select_db($db);
  $sql="SHOW COLUMNS FROM " . $tb . ";";
  $result=mysql_query($sql, $connect);
  $numcols=mysql_numrows($result);

  for($i=0;$i<$numcols;$i++) {
    $colname[$i]=mysql_result($result, $i, "field");
  }

  $sql="SELECT * FROM " . $tb . ";";
  $result=mysql_query($sql, $connect);
  $numrows=mysql_numrows($result);

  for($y=0;$y<$numrows;$y++) {
    $sql="UPDATE " . $tb . " SET ";
    for($x=0;$x<$numcols-1;$x++){
      $sql .= $colname[$x+1] . "='" . $_POST[($x+$y*$numcols+1)] . "'";
      if($x!=$numcols-2) $sql .= ",";
    }
    $sql .= " WHERE id=" . ($y+1) . ";";
    mysql_query($sql,$connect);
  }
}
function addRow($db, $tb, $connect) {
  mysql_select_db($db);
  $sql="SHOW COLUMNS FROM " . $tb . ";";
  $result=mysql_query($sql, $connect);
  $numcols=mysql_numrows($result);
  for($i=0;$i<$numcols;$i++) {
    if(mysql_result($result, $i, "field")!="id") $colname[$i]=mysql_result($result, $i, "field");
  }
  $sql="INSERT INTO " . $tb . "(";
  for($x=0;$x<$numcols;$x++){
    if($colname[$x]) $sql .= $colname[$x];
    if($x!=$numcols-1 && $x) $sql .= ",";
  }
  $sql .= ") VALUES (";
  for($x=0;$x<$numcols;$x++){
    if($x) $sql .= "'" .  $_POST[$x] . "'";
    if($x!=$numcols-1 && $x) $sql .= ",";
  }
  $sql .=");";
  mysql_query($sql,$connect);
}

if($_POST['dbselect']) getTables($_POST['dbselect'],$connect);
if($_POST['edited']) {
  updateTable($_POST['dbselect'], $_POST['tbselect'], $connect);
  echo "<br>Table Updated.";
}
if($_POST['delete']) {
  mysql_select_db($_POST['dbselect']);
  $sql="DELETE FROM " . $_POST['tbselect'] . " WHERE id=" . $_POST['delete'] . ";";
  mysql_query($sql,$connect);
}
if($_POST['newrow']) addRow($_POST['dbselect'],$_POST['tbselect'],$connect);
if($_POST['tbselect']) selectAll($_POST['dbselect'],$_POST['tbselect'],$connect);

mysql_close();

?>

You should just be able to copy it, but I don't know if the Code thing made stuff go onto other lines. It should still work though. You can change use this code however you want. It took me forever to figure out, but it works. If anyone has any suggestions on how to make my code any shorter, they're welcome, but this is as short as I could get it. I might add more functionality to it later. I hope it's helpful.

mallumedia

Apr 10 2007, 02:18 PM

this is lot to do...anyway thanx man

Damen

Apr 10 2007, 07:15 PM

Yes I agree it is a lot to do but it is very helpful. Me and hippiman and rl friends and I showed him everything he knows...lol well not really. But we figured lots of stuff out by ourselves.

html

Oct 10 2007, 04:17 AM

Thanks hippiman for sharing a phpmyadmin scripts this is good for the web deveopler
Thanks for such a superb tutorial

ShaggytheClown

Apr 26 2008, 07:05 AM

Nice tutorial hippiman

lailai

Apr 28 2008, 03:27 AM

Wow! Nice tutorial! Thank you for sharing how to make a phpmyadmin...
I coudn't understand most parts, but anyway thanks

games4u

Apr 30 2008, 11:56 AM

This is an excellent tutorial. I could see that hippiman is sad that this is not as good as phpMyAdmin.
An equally good phpMyAdmin can be created.
I learnt the basics of php and MySql at w3schools and it gives me a good idea to create a good phpMyAdmin combining php, MySql, and XML.
So any one interested may visit the site.

ADD REPLY / Got an Opinion!	Remove these ADs!	RAPID SEARCH!	Free Web Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE