Add/Delete/Update records to MySQL tables Via PHP

**mainul** · Mar 4 '07, 09:49 AM

Originally posted by nungo

Hi Members
I have been able to add records to MySQL table using PHP the problem is I don't know how to write a code that will allow users to Add/Update/Delete records in one Form.

Any suggestions or Ideas are warmly welcome. Thanks
Regards,
Papa Nungo

send the code and the database that you have developed so far.

best regards
Mainul

**nungo** · Mar 4 '07, 11:09 AM

Hi Mainul,
I have spents hours trying to work this out. I want to be able to add, delete, update records in MySQL database using PHP. Below are all the codes I have created so far.
Thanks alot for your spirit to help . Have a good day.
Regards,
Nungo

MySQL Code

Code:

CREATE TABLE student
(
studentNo 	numeric,
name 		char(20), 
age 		numeric, 
CONSTRAINT student_PK PRIMARY KEY(studentNo)
)ENGINE=InnoDB;

INSERT INTO student (studentNo,name,age) VALUES ('031', 'Paul','21');
INSERT INTO student (studentNo,name,age) VALUES ('042', 'Peter','19');
INSERT INTO student (studentNo,name,age) VALUES ('043', 'Kirsty','18');
INSERT INTO student (studentNo,name,age) VALUES ('064', 'Mary','17');
INSERT INTO student (studentNo,name,age) VALUES ('085', 'Allan','21');
INSERT INTO student (studentNo,name,age) VALUES ('096', 'Kanta','22');

Code for displaying My data From MySQL

Code:

<?php
$username = "root";
$password = "babati";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
  or die("Unable to connect to MySQL");
//echo "Connected to MySQL<br>";
//select a database to work with
$selected = mysql_select_db("roscte",$dbhandle) 
  or die("Could not select roscte");


// This script retrieves all the records from the users table.
// This new version allows the results to be sorted in different ways.
$page_title = 'student';
include ('./includes/header.html');
// Page header.
echo '<h1 id="mainhead">student</h1>';
// Number of records to show per page:
$display = 10;
// Determine how many pages there are. 
if (isset($_GET['np'])) { // Already been determined.
	$num_pages = $_GET['np'];
} else { // Need to determine.
 	// Count the number of records
	$query = "SELECT COUNT(*) FROM student ORDER BY name ASC";
	$result = @mysql_query ($query);
	$row = mysql_fetch_array ($result, MYSQL_NUM);
	$num_records = $row[0];
	// Calculate the number of pages.
	if ($num_records > $display) { // More than 1 page.
		$num_pages = ceil ($num_records/$display);
	} else {
		$num_pages = 1;
	}
} // End of np IF.
// Determine where in the database to start returning results.
if (isset($_GET['s'])) {	$start = $_GET['s'];
} else {
	$start = 0;
}
// Default column links.
$link1 = "{$_SERVER['PHP_SELF']}?sort=lna";
$link2 = "{$_SERVER['PHP_SELF']}?sort=fna";
$link3 = "{$_SERVER['PHP_SELF']}?sort=dra";
// Determine the sorting order.
if (isset($_GET['sort'])) {
	// Use existing sorting order.
	switch ($_GET['sort']) {
		case 'lna':
			$order_by = 'name ASC';
			$link1 = "{$_SERVER['PHP_SELF']}?sort=lnd";
			break;
		case 'lnd':
			$order_by = 'name ASC';
			$link1 = "{$_SERVER['PHP_SELF']}?sort=lna";
			break;
		case 'fna':
			$order_by = 'name ASC';
			$link2 = "{$_SERVER['PHP_SELF']}?sort=fnd";
			break;
		case 'fnd':
			$order_by = 'name ASC';
			$link2 = "{$_SERVER['PHP_SELF']}?sort=fna";
			break;
		case 'dra':
			$order_by = 'name ASC';
			$link3 = "{$_SERVER['PHP_SELF']}?sort=drd";
			break;
		case 'drd':
			$order_by = 'name ASC';
			$link3 = "{$_SERVER['PHP_SELF']}?sort=dra";
			break;
		default:
			$order_by = 'name ASC';
			break;
	}
	// $sort will be appended to the pagination links.
	$sort = $_GET['sort'];
} else { // Use the default sorting order.
	$order_by = 'name ASC';
	$sort = 'drd';
}
// Making the query.
$query = "SELECT studentNo, name, age  FROM student ORDER BY $order_by LIMIT $start, $display";		
$result = @mysql_query ($query); // Run the query.

// Table header.
echo '<table class="datatable" align="center" cellspacing="0" cellpadding="5">
<tr>
	
	<td align="left"><b>StudentNo.</b></td>
	<td align="left"><b>Name</b></td>
	<td align="left"><b>Age</b></td>
</tr>
';

// Fetch and print all the records.
$bg = '#eeeeee'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switch the background color.
	echo '<tr bgcolor="' . $bg . '">
		
		<td align="left">' . $row['studentNo'] . '</td>
		<td align="left">' . $row['name'] . '</td>
		<td align="left">' . $row['age'] . '</td>
	</tr>
	';
}

echo '</table>';

mysql_free_result ($result); // Free up the resources.	

mysql_close(); // Close the database connection.

// Makes the links to other pages, if necessary.
if ($num_pages > 1) {
	
	echo '<br /><p>';
	// Determine what page the script is on.	
	$current_page = ($start/$display) + 1;
	
	// If it's not the first page, make a Previous button.
	if ($current_page != 1) {
		echo '<a href=http://localhost/roscte/student.php?s=' . ($start - $display) . '&np=' . $num_pages . '&sort=' . $sort .'">Previous</a> ';
	}
	
	// Make all the numbered pages.
	for ($i = 1; $i <= $num_pages; $i++) {
		if ($i != $current_page) {
			echo '<a href=http://localhost/roscte/student.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '&sort=' . $sort .'">' . $i . '</a> ';
		} else {
			echo $i . ' ';
		}
	}
	
	// If it's not the last page, make a Next button.
	if ($current_page != $num_pages) {
		echo '<a href=http://localhost/roscte/student.php?s=' . ($start + $display) . '&np=' . $num_pages . '&sort=' . $sort .'">Next</a>';
	}
	
	echo '</p>';
	
} // End of links section.
	
include ('./includes/footer.html'); // Include the HTML footer.
?>

Code for adding the records

Code:

<table width="270" border="0" cellpadding="0" cellspacing="0" align="center">
	<tr style="background: url(file:///E|/images/BackTop.png)">
		<td height="20" width="270"><span class="style1">Adding Student </span></td>
	</tr>
</table>
<table width="270" border="0" cellpadding="0" cellspacing="0" align="center" bgcolor="#9ABCD6">
	<form  action="InsertStudent.php" name="Student_INSERT" method="post">
		<tr>
			<td width="4">&nbsp;</td>
			<td width="113">&nbsp;</td>
			<td width="10">&nbsp;</td>
			<td width="149">&nbsp;</td>
		</tr>
		
		<tr>
			<td height="27">&nbsp;</td>
			<td style="text-align: left; vertical-align: middle">StudentNo</td>
			<td>&nbsp;</td>
			<td>
				<input type="text" name="studentNo" size="20" />
			</td>
		</tr>
		<tr>
			<td height="27">&nbsp;</td>
			<td style="text-align: left; vertical-align: middle">Student</td>
			<td>&nbsp;</td>
			<td>
				<input type="text" name="Student" size="20" />
			</td>
		</tr>
		<tr>
			<td height="27">&nbsp;</td>
			<td style="text-align: left; vertical-align: middle">Age </td>
			<td>&nbsp;</td>
			<td>
				<input type="text" name="age" size="20" />
			</td>
		</tr>
		
		
		<tr>
			<td height="37">&nbsp;</td>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
			<td>
				<input type="submit" name="submit" age="Submit" />
			</td>
		</tr>
	</form>
</table>
<table width="270" border="0" cellpadding="0" cellspacing="0" align="center">
	<tr style="background: url(file:///E|/images/BackBottom.png)">
		<td height="20" width="270">&nbsp;</td>
	</tr>
</table>

**marcusoh** · Mar 4 '07, 03:45 PM

Hello,
Nobody else seems to be helping. I am new to php too!
But...

I had a very similar problem and was able to adjust the details in this affiliate management system tutorial to suit my needs, it is a complete system being shown here, and if studied carefully, can be adjusted to suit - you may be able to do the same.

Complete Affiliate Management System
I hope this tutorial really helps you as it did for me

Marcus

**marcusoh** · Mar 4 '07, 03:56 PM

PS to the above.

You will find more tutorials on the left under "Tutorials" > "PHP-MySQL" to make the affiliate Management System run very smoothly indeed!

Marcus

**Atli** · Mar 4 '07, 08:22 PM

Hi.

Anything you need to do to a database table can be done with the mysql_query / mysqli_query functions.
You just need to adjust the query you pass to it.

Btw.

Code:

"SELECT COUNT(*) FROM student ORDER BY name ASC";
// The order by is kind of redundant, all your doing is counting the rows.

"SELECT COUNT(*) FROM student";
// Does the exact same thing.

**nungo** · Mar 4 '07, 09:11 PM

Hi Marcus,
Thanks alot for your help.

I have tried to customize the Complete affiliates management system code to suite mine but all I get is error message not connected to the MySQL database.

I will sleep over it and perherps try and work on it tomorrow morning while my mind is Fresh. I have worked on this stuff for two solid days....... thats all my weekend gone.

Have a good evening and once again thanks for your help.
Regards,
nungo

**nungo** · Mar 4 '07, 09:17 PM

Hi everyone,

I have spent 2 solid days on trying to sort out this code.......... thats it all my weekend gone.

If any Member has an Idea of how to sort out this code Please help, you will have made my Monday morning.

Otherwise all the best and thanks for your advice and help.

Regards,
Nungo

**mainul** · Mar 5 '07, 05:00 AM

Just copy and paste the code and change the database name and password.

Code:

<script type="text/javascript">
function confirmDelete()
{
    var agree=confirm("Are you sure you wish to delete this entry?");
    if (agree)
        return true;
    else
        return false;
} 
</script>
<?php
$username = "root";
$password = "passw0rd";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
  or die("Unable to connect to MySQL");
//echo "Connected to MySQL<br>";
//select a database to work with
$selected = mysql_select_db("test1",$dbhandle) 
  or die("Could not select test1");
 

if($Submit){
	if($_REQUEST['edit'])
		$sql = "UPDATE student set studentNo='$studentNo', name='$name', age='$age' WHERE studentNo= '".$_REQUEST['edit']."'"; 
	else
		$sql="INSERT INTO student (studentNo,name,age) VALUES ('$studentNo','$name','$age')";
	if(!mysql_query($sql)){
		echo "Data Insertion Fail<br>";
		echo $sql;
	}
		//mysql_free_result($sql);
}
?>


<table width="800" height="860" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFF">
  
  <tr bgcolor="#330000">
    <td width="800" height="20" bgcolor="#FFFFFF"><div align="right" class="style28"><a href="#">Home</a> |<? if($_REQUEST['all']=="all"){ ?><a href="test_u.php">Add student<? }else{?> </a> <a href="test_u.php?all=all">All Students</a><? }?>
    </div></td>
  </tr>
  
  <tr valign="top">
    <td height="175"><div align="center">
      <br />
      <form name="form1" method="post" action="">
	  <? 
	if($_REQUEST['all']=="all"){
	$data = "select * from student order by name ASC";
	//$query = display_page($data);
	//$result = mysql_query($query);*/
		$allStudent = mysql_query($data);
	if($_REQUEST['delete']=="delete")
		{
		$del="Select from student where studentNo='delete'";
		}
	?> 
	
	<table width="700" border="0" cellspacing="0" cellpadding="0" align="center">
	<tr bgcolor="#666666">
		<td width="250" valign="top"><font color="#FF9900"><b>Student ID</b></font></td>
		<td width="175" valign="top"><font color="#FF9900"><b>Name</b></font></td>
		<td width="175" valign="top"><font color="#FF9900"><b>Age</b></font></td>
		<td width="175" valign="top"><font color="#FF9900"><b>Options</b></font></td>
	</tr>

	<? while($res1 = mysql_fetch_assoc($allStudent)){?>

	<tr>
		<td width="250" valign="top"><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
		<? echo $res1['studentNo'];?>
		</font></td>
		<td width="150" valign="top"><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
		<? echo $res1['name'];?>
		<td width="100" valign="top"><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
		<? echo $res1['age'];?>
		
		<br>
		<td width="100" valign="top"><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
		
		<a href="test_u.php?edit=<?=$res1['studentNo']?>">Edit</a>&nbsp;|&nbsp; 

	<?	echo "<a href=\"del.php?studentNo=".$res1["studentNo"]."\" onclick='return confirmDelete();'> Delete</a> "; ?>
	</td>
	</tr>
	<tr><td colspan="4"><hr width="700" noshade size="1"></td></tr>
	<? } ?>
	</table>
	<? }
	else
	{
	?> 
	<?
	if($_REQUEST['edit']){
	$con = mysql_query("select * from student where studentNo ='".$_REQUEST['edit']."'");
	$detCon = mysql_fetch_assoc($con);
	}
	?>
        <table width="424" height="0" border="0" cellpadding="0" cellspacing="0">
          <tr>
            <td><div align="right" class="style18">Student Number  </div></td>
            <td width="266"><label>
              <input name="studentNo" type="text" id="studentNo" size="35" value="<?=$detCon['studentNo']?>">
            </label></td>
          </tr>

          <tr>
            <td><div align="right" class="style18">Name</div></td>
            <td><label>
              <input name="name" type="text" id="name" size="35" value="<?=$detCon['name']?>">
            </label></td>
          </tr>

          <tr>
            <td><div align="right" class="style18">Age</div></td>
            <td><label>
              <input name="age" type="text" id="age" size="35" value="<?=$detCon['age']?>">
            </label></td>
          </tr>
          <tr>
            <td colspan="3"><div align="center">
              <input type="submit" name="Submit" value="Submit" />
              <input type="reset" name="Submit2" value="Reset" />
            </div></td>
            </tr>
        </table>
        <? }?>
      </form>
      </div></td>
  </tr>

</table>

save the the below file as del.php

Code:

<title>Delete</title>
<?
$username = "root";
$password = "passw0rd";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
  or die("Unable to connect to MySQL");
//echo "Connected to MySQL<br>";
//select a database to work with
$selected = mysql_select_db("test1",$dbhandle) 
  or die("Could not select test1");
 
mysql_query("DELETE FROM student WHERE studentNo=".$_GET['studentNo']);
echo "<center>Information Deleted</center><br><br>";
//echo "<center><br><a href='#'>Back</a></center>";
?>

best regards
Mainul

**savanm** · Mar 5 '07, 12:12 PM

Hi guy

Check this one

Code:

<?php

$conn = ("localhost","username","password") or die("cannot connect");

mysql_connect($conn);

mysql_select_db("Here the databse name");

if(isset($_POST["btnINS"]))

{

$usertxt = $_POST["$usertxt"];

$userpass = $_POST["$userpass"];

$sql= insert into tablename values('$usertxt','$userpass');

mysql_query($sql);

}

if(isset($_POST["btnDEL"]))

{

$usertxt = $_POST["$usertxt"];

$userpass = $_POST["$userpass"];

$sql= delete from tablename where username='$usertxt';

mysql_query($sql);

}

if(isset($_POST["btnUPD"]))

{

$usertxt = $_POST["$usertxt"];

$userpass = $_POST["$userpass"];

$sql= update tablename SET username='$usertxt' where password='$userpass';

mysql_query($sql);

}
<form name="admin" method="POST/GET" action="<? echo $_SERVER['PHP_SELF']; ?>">

<input type="text" name="usertxt" maxlength="12">

<input type="text" name="userpass" maxlength"12">

<input type="submit" name="btnINS" value="ADD">

<input type="submit" name="btnDEL" value="DELETE">

<input type="submit" name="btnUPD" value="UPDATE">

Regards

Navas.M

**nungo** · Mar 5 '07, 04:26 PM

Hi Mainul,
You are a star!!! I have just woken up to some good code! Will give it ago and let you know how it goes.

Thanks a bunch Mate.
Regards,
Nungo

**ronverdonk** · Mar 5 '07, 04:55 PM

Nice piece of code mainul! Very useful to our members.

Ronald :cool:

**mainul** · Mar 6 '07, 03:52 AM

Originally posted by ronverdonk

Nice piece of code mainul! Very useful to our members.

Ronald :cool:

Thanks Ronald.

**amcs** · Dec 1 '08, 08:47 PM

Great Code Mainul

Thank you for posting that code, it is very helpful, however I'm having issues with it updating the database. When you click submit nothing happens, and copied and pasted the code directly and setup a test database called the same as yours.

**nathj** · Dec 2 '08, 03:51 PM

Hi,

When I first started with PHP (18months ago) I found an article here on Bytes really useful:

Creating a Data Abstraction Layer

I then built such an object that I could use the execute my SQL queries as and when I wanted to - as long as the class was instantiated.

Have a play around with that idea and see where you get, feel free to post back with any errors or questions.

Finally, I noticed you used PHP tags for you code these don't seem to work at present so please use simple code tags.

Cheers
nathj

Add/Delete/Update records to MySQL tables Via PHP

Add/Delete/Update records to MySQL tables Via PHP

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment