Make Your Own Phpmyadmin

[hippiman 0]

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.

<?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.)

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.

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.

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.

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.

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.

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:

<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.
Edited April 11, 2007 by hippiman (see edit history)

[mallumedia 0]

this is lot to do...anyway thanx man

[Damen 0]

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 0]

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

[ShaggytheClown 0]

Nice tutorial hippiman

Edited April 26, 2008 by ShaggytheClown (see edit history)

[lailai 0]

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

[games4u 0]

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.