Welcome Guest ( Log In | Register)



 
 Reply to this topicStart new topic
> Make Your Own Phpmyadmin
hippiman
post Apr 9 2007, 05:26 PM
Post #1


Advanced Member
*******

Group: Members
Posts: 149
Joined: 9-April 07
From: Nebraska
Member No.: 41,301
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.

This post has been edited by hippiman: Apr 11 2007, 03:28 AM

(list)

Go to the top of the page
 
+Quote Post
mallumedia
post Apr 10 2007, 02:18 PM
Post #2


Newbie
*

Group: Members
Posts: 1
Joined: 10-April 07
Member No.: 41,394
this is lot to do...anyway thanx man

(list)

Go to the top of the page
 
+Quote Post
Damen
post Apr 10 2007, 07:15 PM
Post #3


Super Member
*********

Group: [HOSTED]
Posts: 235
Joined: 9-April 07
From: Nebraska
Member No.: 41,342
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.

(list)

Go to the top of the page
 
+Quote Post
html
post Oct 10 2007, 04:17 AM
Post #4


Newbie [Level 2]
**

Group: Members
Posts: 31
Joined: 28-September 07
Member No.: 50,771
Thanks hippiman for sharing a phpmyadmin scripts this is good for the web deveopler
Thanks for such a superb tutorial

(list)

Go to the top of the page
 
+Quote Post
ShaggytheClown
post Apr 26 2008, 07:05 AM
Post #5


Newbie
*

Group: Members
Posts: 6
Joined: 24-April 08
From: England
Member No.: 61,279
Nice tutorial hippiman smile.gif

This post has been edited by ShaggytheClown: Apr 26 2008, 07:07 AM

(list)

Go to the top of the page
 
+Quote Post
lailai
post Apr 28 2008, 03:27 AM
Post #6


Newbie [Level 3]
***

Group: Members
Posts: 42
Joined: 8-December 07
Member No.: 54,414
Wow! Nice tutorial! Thank you for sharing how to make a phpmyadmin...
I coudn't understand most parts, but anyway thanks

(list)

Go to the top of the page
 
+Quote Post
games4u
post Apr 30 2008, 11:56 AM
Post #7


Newbie
*

Group: Members
Posts: 7
Joined: 27-April 08
Member No.: 61,384
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. smile.gif