Jump to content


Photo

Basic PHP System: View, Edit, Add, Delete records with MySQLi


  • Please log in to reply
58 replies to this topic

#1 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 09 May 2010 - 04:50 PM

This is a revised version of my previous tutorial (http://www.killersit...eteadd-records/) which uses MySQLi rather than regular MySQL to connect to the database. MySQLi, often called MySQL Improved, has several advantages over regular MySQL, including support for prepared statements (which helps prevent SQL injection, a common security issue) and object-oriented code. I've also provided a modified view.php file that shows one way to do basic pagination.

I have also recorded a 8 part video tutorial (a bit over an an hour worth of video) showing how to build this system and explaining it as I go. It's available in the KillerSites University (http://www.killersites.com/university - subscription required) under PHP > PHP CRUD Videos.

---

(Anyone with PHP knowledge is welcome to comment on the code. If there are issues I haven't noticed, please let me know. Do realize that it is intended for beginners, so I didn't want to do anything too advanced that might lead to confusion. Yes, I realize I could use OOP, or could separate some of these out into methods, etc. etc.)

OK... Here's some code for you to play with. It's a basic system that allows you to:
-- view existing records
-- edit existing records
-- delete existing records
-- add new records

Online demo:
http://www.falkencre...mysqli/view.php

Basically, just imagine that you are in charge of a sports team, and you want to keep a list of all your player's contact information. The code I've created could be a starting point for that (it only includes fields for their first name/last name, but could obviously could be expanded to use more fields).

This is just a basic starting point for projects that require view/edit/delete functionality. I know it may seem a lot to understand at first, but read all the comments in the code -- I try to explain what I am doing step by step. I'm also happy to help with any questions (please post questions in a new topic.)

How to create a system that allows a user to add/edit/remove data in a database seems to be a commonly asked topic, so I may adapt this into an actual tutorial at some point in the future.

DATABASE:
-- You'll need to create a database (I named mine 'records' but it can be changed) using PHPMyAdmin
-- Save the included sql file on your desktop as a .txt file
-- Once you've created the database, make sure the database is selected, then click the "import" tab
-- Select the .txt file on your desktop, and import it into your database. PHPMyAdmin will create all of the necessary tables/import some test data for you to play with

SQL file:
--
-- Table structure for table `players`
--

CREATE TABLE `players` (
 `id` int(11) NOT NULL auto_increment,
 `firstname` varchar(32) NOT NULL,
 `lastname` varchar(32) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `players`
--

INSERT INTO `players` VALUES(1, 'Bob', 'Baker');
INSERT INTO `players` VALUES(2, 'Tim', 'Thomas');
INSERT INTO `players` VALUES(3, 'Rachel', 'Roberts');
INSERT INTO `players` VALUES(4, 'Sam', 'Smith');


Save these php files all in the same folder in a place where you can run them using your server (I'm assuming you are using something like WAMP for the server? I'm not sure if Dreamweaver includes something like that by default.)

connect-db.php
<?php

// server info
$server = 'localhost';
$user = 'root';
$pass = 'root';
$db = 'records2';

// connect to the database
$mysqli = new mysqli($server, $user, $pass, $db);

// show errors (remove this line if on a live site)
mysqli_report(MYSQLI_REPORT_ERROR);

?>

view.php (non-paginated -- will just display one long list of members)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
        <head>  
                <title>View Records</title>
                <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
        </head>
        <body>
                
                <h1>View Records</h1>
                
                <p><b>View All</b> | <a href="view-paginated.php">View Paginated</a></p>
                
                <?php
                        // connect to the database
                        include('connect-db.php');
                        
                        // get the records from the database
                        if ($result = $mysqli->query("SELECT * FROM players ORDER BY id"))
                        {
                                // display records if there are records to display
                                if ($result->num_rows > 0)
                                {
                                        // display records in a table
                                        echo "<table border='1' cellpadding='10'>";
                                        
                                        // set table headers
                                        echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th><th></th><th></th></tr>";
                                        
                                        while ($row = $result->fetch_object())
                                        {
                                                // set up a row for each record
                                                echo "<tr>";
                                                echo "<td>" . $row->id . "</td>";
                                                echo "<td>" . $row->firstname . "</td>";
                                                echo "<td>" . $row->lastname . "</td>";
                                                echo "<td><a href='records.php?id=" . $row->id . "'>Edit</a></td>";
                                                echo "<td><a href='delete.php?id=" . $row->id . "'>Delete</a></td>";
                                                echo "</tr>";
                                        }
                                        
                                        echo "</table>";
                                }
                                // if there are no records in the database, display an alert message
                                else
                                {
                                        echo "No results to display!";
                                }
                        }
                        // show an error if there is an issue with the database query
                        else
                        {
                                echo "Error: " . $mysqli->error;
                        }
                        
                        // close database connection
                        $mysqli->close();
                
                ?>
                
                <a href="records.php">Add New Record</a>
        </body>
</html>

view-paginated.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
        <head>  
                <title>View Records</title>
                <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
        </head>
        <body>
                
                <h1>View Records</h1>
                
                <?php
                        // connect to the database
                        include('connect-db.php');
                        
                        // number of results to show per page
				        $per_page = 3;
				        
				        // figure out the total pages in the database
				        if ($result = $mysqli->query("SELECT * FROM players ORDER BY id"))
				        {
				        	if ($result->num_rows != 0)
				        	{
				        		$total_results = $result->num_rows;
				        		// ceil() returns the next highest integer value by rounding up value if necessary
					        	$total_pages = ceil($total_results / $per_page);
					        	
		        				// check if the 'page' variable is set in the URL (ex: view-paginated.php?page=1)
						        if (isset($_GET['page']) && is_numeric($_GET['page']))
						        {
						                $show_page = $_GET['page'];
						                
						                // make sure the $show_page value is valid
						                if ($show_page > 0 && $show_page <= $total_pages)
						                {
						                        $start = ($show_page -1) * $per_page;
						                        $end = $start + $per_page; 
						                }
						                else
						                {
						                        // error - show first set of results
						                        $start = 0;
						                        $end = $per_page; 
						                }               
						        }
						        else
						        {
						                // if page isn't set, show first set of results
						                $start = 0;
						                $end = $per_page; 
						        }
						        
						        // display pagination
						        echo "<p><a href='view.php'>View All</a> | <b>View Page:</b> ";
						        for ($i = 1; $i <= $total_pages; $i++)
						        {
						        	if (isset($_GET['page']) && $_GET['page'] == $i)
						        	{
						        		echo $i . " ";
						        	}
						        	else
						        	{
						        		echo "<a href='view-paginated.php?page=$i'>$i</a> ";
						        	}
						        }
						        echo "</p>";
						        
						        // display data in table
						        echo "<table border='1' cellpadding='10'>";
						        echo "<tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th></th> <th></th></tr>";
						
						        // loop through results of database query, displaying them in the table 
						        for ($i = $start; $i < $end; $i++)
						        {
						        	// make sure that PHP doesn't try to show results that don't exist
					                if ($i == $total_results) { break; }
					                
						        	// find specific row
						        	$result->data_seek($i);
   	 								$row = $result->fetch_row();
   	 								
   	 								// echo out the contents of each row into a table
					                echo "<tr>";
					                echo '<td>' . $row[0] . '</td>';
					                echo '<td>' . $row[1] . '</td>';
					                echo '<td>' . $row[2] . '</td>';
					                echo '<td><a href="records.php?id=' . $row[0] . '">Edit</a></td>';
					                echo '<td><a href="delete.php?id=' . $row[0] . '">Delete</a></td>';
					                echo "</tr>";
						        }

						        // close table>
						        echo "</table>";
				        	}
				        	else
				        	{
				        		echo "No results to display!";
				        	} 
				        }
				        // error with the query
				        else
				        {
				        	echo "Error: " . $mysqli->error;
				        }
                                                
                        // close database connection
                        $mysqli->close();
                
                ?>
                
                <a href="records.php">Add New Record</a>
        </body>
</html>
</html>

records.php (create a new record/edit existing records)
<?php
	/*
		Allows the user to both create new records and edit existing records
	*/

	// connect to the database
	include("connect-db.php");

	// creates the new/edit record form
 	// since this form is used multiple times in this file, I have made it a function that is easily reusable
	function renderForm($first = '', $last ='', $error = '', $id = '')
	{ ?>
		<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
		<html>
			<head>	
				<title>
					<?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?>
				</title>
				<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
			</head>
			<body>
				<h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1>
				<?php if ($error != '') {
					echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error
						. "</div>";
				} ?>
				
				<form action="" method="post">
				<div>
					<?php if ($id != '') { ?>
						<input type="hidden" name="id" value="<?php echo $id; ?>" />
						<p>ID: <?php echo $id; ?></p>
					<?php } ?>
					
					<strong>First Name: *</strong> <input type="text" name="firstname"
						value="<?php echo $first; ?>"/><br/>
					<strong>Last Name: *</strong> <input type="text" name="lastname"
						value="<?php echo $last; ?>"/>
					<p>* required</p>
					<input type="submit" name="submit" value="Submit" />
				</div>
				</form>
			</body>
		</html>
		
	<?php }



        /*

           EDIT RECORD

        */
	// if the 'id' variable is set in the URL, we know that we need to edit a record
	if (isset($_GET['id']))
	{
		// if the form's submit button is clicked, we need to process the form
		if (isset($_POST['submit']))
		{
			// make sure the 'id' in the URL is valid
			if (is_numeric($_POST['id']))
			{
				// get variables from the URL/form
				$id = $_POST['id'];
				$firstname = htmlentities($_POST['firstname'], ENT_QUOTES);
				$lastname = htmlentities($_POST['lastname'], ENT_QUOTES);
				
				// check that firstname and lastname are both not empty
				if ($firstname == '' || $lastname == '')
				{
					// if they are empty, show an error message and display the form
					$error = 'ERROR: Please fill in all required fields!';
					renderForm($firstname, $lastname, $error, $id);
				}
				else
				{
					// if everything is fine, update the record in the database
					if ($stmt = $mysqli->prepare("UPDATE players SET firstname = ?, lastname = ?
						WHERE id=?"))
					{
						$stmt->bind_param("ssi", $firstname, $lastname, $id);
						$stmt->execute();
						$stmt->close();
					}
					// show an error message if the query has an error
					else
					{
						echo "ERROR: could not prepare SQL statement.";
					}
					
					// redirect the user once the form is updated
					header("Location: view.php");
				}
			}
			// if the 'id' variable is not valid, show an error message
			else
			{
				echo "Error!";
			}
		}
		// if the form hasn't been submitted yet, get the info from the database and show the form
		else
		{
			// make sure the 'id' value is valid
			if (is_numeric($_GET['id']) && $_GET['id'] > 0)
			{
				// get 'id' from URL
				$id = $_GET['id'];
				
				// get the recod from the database
				if($stmt = $mysqli->prepare("SELECT * FROM players WHERE id=?"))
				{
					$stmt->bind_param("i", $id);
					$stmt->execute();
					
					$stmt->bind_result($id, $firstname, $lastname);
					$stmt->fetch();
					
					// show the form
					renderForm($firstname, $lastname, NULL, $id);
					
					$stmt->close();
				}
				// show an error if the query has an error
				else
				{
					echo "Error: could not prepare SQL statement";
				}
			}
			// if the 'id' value is not valid, redirect the user back to the view.php page
			else
			{
				header("Location: view.php");
			}
		}
	}



        /*

           NEW RECORD

        */
	// if the 'id' variable is not set in the URL, we must be creating a new record
	else
	{
		// if the form's submit button is clicked, we need to process the form
		if (isset($_POST['submit']))
		{
			// get the form data
			$firstname = htmlentities($_POST['firstname'], ENT_QUOTES);
			$lastname = htmlentities($_POST['lastname'], ENT_QUOTES);
			
			// check that firstname and lastname are both not empty
			if ($firstname == '' || $lastname == '')
			{
				// if they are empty, show an error message and display the form
				$error = 'ERROR: Please fill in all required fields!';
				renderForm($firstname, $lastname, $error);
			}
			else
			{
				// insert the new record into the database
				if ($stmt = $mysqli->prepare("INSERT players (firstname, lastname) VALUES (?, ?)"))
				{
					$stmt->bind_param("ss", $firstname, $lastname);
					$stmt->execute();
					$stmt->close();
				}
				// show an error if the query has an error
				else
				{
					echo "ERROR: Could not prepare SQL statement.";
				}
				
				// redirec the user
				header("Location: view.php");
			}
			
		}
		// if the form hasn't been submitted yet, show the form
		else
		{
			renderForm();
		}
	}
	
	// close the mysqli connection
	$mysqli->close();
?>

delete.php
<?php

	// connect to the database
	include('connect-db.php');
	
	// confirm that the 'id' variable has been set
	if (isset($_GET['id']) && is_numeric($_GET['id']))
	{
		// get the 'id' variable from the URL
		$id = $_GET['id'];
		
		// delete record from database
		if ($stmt = $mysqli->prepare("DELETE FROM players WHERE id = ? LIMIT 1"))
		{
			$stmt->bind_param("i",$id);	
			$stmt->execute();
			$stmt->close();
		}
		else
		{
			echo "ERROR: could not prepare SQL statement.";
		}
		$mysqli->close();
		
		// redirect user after delete is successful
		header("Location: view.php");
	}
	else
	// if the 'id' variable isn't set, redirect the user
	{
		header("Location: view.php");
	}

?>

  • 0
Benjamin Falk
Falken Creative : Twitter

#2 deekay

deekay

    Member

  • Member
  • PipPip
  • 18 posts
  • LocationAmsterdam, The Netherlands

Posted 19 August 2010 - 10:02 AM

My host doesnt support MySQLi, only MySQL. In what way do I have to the change the code, so it will work with MySQL? So far I copy/pasted all your files to my host and I only get this output:

View Records

View All | View Paginated

Error: Add New Record


So there is some kind of error.

But on my localhost I get this, just like in your video:

View Records

View All | View Paginated

ID First Name Last Name
1 Bob Baker Edit Delete
2 Tim Thomas Edit Delete
3 Rachel Roberts Edit Delete
4 Sam Smith Edit Delete
Add New Record


I just copy/pasted the text, but the table appears and the buttons are in blue in the browser.

My host does have PHPmyAdmin and I also made the same database 'records' with table 'players'. So that's not the problem. Another difference is that my local host is running Xamp with PHP5.3.1 and my host has PHP5.2.4. I don't know if that matters.
  • 0

#3 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 19 August 2010 - 10:12 AM

Your localhost probably supports MySQLi, whereas your web hosting doesn't. That would explain why it works on your local machine but not on your hosting.

I did do a regular MySQL version of this tutorial, which you can look at here: http://www.killersit...eteadd-records/
  • 0
Benjamin Falk
Falken Creative : Twitter

#4 deekay

deekay

    Member

  • Member
  • PipPip
  • 18 posts
  • LocationAmsterdam, The Netherlands

Posted 19 August 2010 - 12:50 PM

Cool! now it works. thanks :)
  • 0

#5 Guest_Koos Mooij

Guest_Koos Mooij
  • Guests

Posted 26 December 2010 - 08:02 PM

Great application, thank you.

I guess in the "view-paginated.php" the link to: "edit.php" should be "records.php" ?

I suggest to include in the delete function to include "Are you sure?" to avoid accidently lost of records.
  • 0

#6 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 27 December 2010 - 12:29 AM

I guess in the "view-paginated.php" the link to: "edit.php" should be "records.php" ?

I suggest to include in the delete function to include "Are you sure?" to avoid accidently lost of records.

Correct - that should be "records.php" - I've updated the code above.

Yes, a more fully functional system might include some sort of confirmation functionality to prevent records from being accidentally deleted. I was trying to keep this system as simple as possible, so I haven't included everything.
  • 0
Benjamin Falk
Falken Creative : Twitter

#7 Guest_ashuweb

Guest_ashuweb
  • Guests

Posted 02 August 2011 - 12:40 PM

Thanxxxxxxxxxxxxxxxx For PHP&sql Script :)
  • 0

#8 itsover9000

itsover9000

    New member

  • New Members
  • 1 posts

Posted 21 October 2011 - 07:20 PM

in the page with the name "view-paginated.php"
there is a line of code that goes like this
echo '<td><a href="records.php?id=' . $row[0] . '">Edit</a></td>';

what does the
?id=' . $row[0] .
do?
  • 0

#9 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 21 October 2011 - 08:01 PM

the $row[0] is a variable that holds the id of the record, creating a url that includes the id, like this: "records.php?id=12". On the records page, you can use $_GET[] to get the id and know which record you want to edit.
  • 1
Benjamin Falk
Falken Creative : Twitter

#10 Ajoe

Ajoe

    New member

  • New Members
  • 3 posts

Posted 05 November 2011 - 01:50 PM

Great little tutorial, was just what i needed. How would you do pagination for big amounts of information, like you for instance do on this very forum.
(Where you don't necessarily have every page from 1 to 100 listed, but only the first couple of pages and then the jump to last button?)

Im sorry if you have already covered this in one of your video tutorials and i just missed it.
  • 0

#11 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 07 November 2011 - 10:14 PM

Great little tutorial, was just what i needed. How would you do pagination for big amounts of information, like you for instance do on this very forum.
(Where you don't necessarily have every page from 1 to 100 listed, but only the first couple of pages and then the jump to last button?)

Im sorry if you have already covered this in one of your video tutorials and i just missed it.

It would just be a matter of counting the total number of pages and if it was over a certain number, choosing to only display a limited number of pagination items. I don't believe it's something that I covered, but shouldn't be that hard to implement with the code I've provided.
  • 0
Benjamin Falk
Falken Creative : Twitter

#12 Ajoe

Ajoe

    New member

  • New Members
  • 3 posts

Posted 12 November 2011 - 08:40 AM

Sry for late reply. Thx for the tip managed to figure it out :).
  • 0

#13 metabee

metabee

    New member

  • New Members
  • 9 posts

Posted 16 November 2011 - 03:35 AM

Can you add a email reminder function?

e.g. Users can insert their information, then set a time for a reminder before the event. The reminder is triggered at that time and sent to their default email or to their friends.
  • 0

#14 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 19 November 2011 - 03:57 PM

Is it possible? Yes. But that's a bit out of the scope of this tutorial. It's something you'll probably need to do a bit of research on, and probably involves cron jobs.
  • 0
Benjamin Falk
Falken Creative : Twitter

#15 metabee

metabee

    New member

  • New Members
  • 9 posts

Posted 21 November 2011 - 07:29 PM

Here is the source code for importing csv file into database.

Hello, Ben
Please combine this code with your basic php system.


uploadcsv.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR...nsitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Upload Record By CSV</title>
</head>

<body>
<p><form id="form1" action="" method="post" enctype="multipart/form-data">
<input type="file" name="file" id="file" size="30" />
<br/>
<input type="Submit" value="Upload" name="submitBtn">
</form>
</p>
</body>
</html>

<?php
if (isset($_POST['submitBtn'])) { //read csv file

$cnx = mysql_connect("localhost","root","") or die("Error!");
mysql_select_db("record2",$cnx);

$num = 0;
if (trim($_FILES["file"]["name"]) == "") {
print "Please select file to upload.<br>";
} else { //read file
$fp = fopen($_FILES["file"]["tmp_name"], 'r') or die("can't open file");
$strTmp = "";
while ($csv_line = fgetcsv($fp, 1024)) {
print '<tr>';
for ($i = 0, $j = count($csv_line); $i < $j; $i++) {
$strTmp .= "'".$csv_line[$i]."',";
}
$strTmp = substr($strTmp,0,strlen($strTmp)-1); //remove last character (,)
$sql1 = "insert into players(firstname,lastname) values({$strTmp})"; //******/
$result1 = mysql_query($sql1) or die("error! {$sql1}");

$num++;
$strTmp = "";
}

fclose($fp) or die("can't close file");
print "\n{$num} records created...";
}
}
?>




uploadplayers.csv

John, Robert
Jane, Watson
Venon, Eddie
  • 0

#16 mrsamrsa

mrsamrsa

    New member

  • New Members
  • 1 posts

Posted 21 December 2011 - 07:22 AM

Hi, I use some text to be written in the mysql-database that contains amongst others ä, ü ö etc., but these are not saved that way?
In your script you use the utf-8 (<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
)
which should show these characters as they are, but it does not? How can I change this?

In your sql-file you use

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

I chose as CHARSET utf8, but this does not do the trick?

Hope to get some help, thanx!


Best regards
Mrsa
  • 0

#17 xSerenity

xSerenity

    New member

  • New Members
  • 1 posts

Posted 25 January 2012 - 11:39 PM

Thanks for the great help.
  • 0

#18 CLU

CLU

    Member

  • Member
  • PipPip
  • 24 posts

Posted 29 January 2012 - 05:17 PM

This looks good, nice work ben. Do you think i could take the view code and put it into a class. Then call the class to display a database?
  • 0

#19 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 29 January 2012 - 07:38 PM

This looks good, nice work ben. Do you think i could take the view code and put it into a class. Then call the class to display a database?

To be honest, I'd handle this code very differently, now that I have had more experience with PHP. I would actually split this up into multiple files so it follows the MVC pattern, splitting it up into a file for the model (which would control all access to the database), view (which would probably be a couple different files for the different possible views) and controller (which would handle choosing when to access the database and which views to display.)
  • 0
Benjamin Falk
Falken Creative : Twitter

#20 metabee

metabee

    New member

  • New Members
  • 9 posts

Posted 06 February 2012 - 04:15 AM

Hello, I want ask about how to use explode to split timestamp? into 3 array variable so that

3 textboxes will combine and insert together into one fieldname which is schedule_time with | as the delimiter.

Please help me solve this explode function problem.

insert_ac.php
<?php

$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="1"; // Database name 
$tbl_name="test_mysql"; // Table name 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Get values from form 
$name=$_POST['name'];
$lastname=$_POST['lastname'];
$email=$_POST['email'];
$schedule_time = $_POST['schedule_time'];


$val = "000|0000-00-00 00:00:00|0";

$ary = explode("|", $val);

$a = $ary[0];
$b = $ary[1];
$c = $ary[2];

$abc = $a . " " . $b .  $c;





// Insert data into mysql 
$sql="INSERT INTO $tbl_name(name, lastname, email, schedule_time)VALUES('$name', '$lastname', '$email', '$abc')";
$result=mysql_query($sql);

// if successfully insert data into database, displays message "Successful". 
if($result){
echo "Successful";
echo "<BR>";
echo "<a href='insert.php'>Back to main page</a>";
}

else {
echo "ERROR";
}

// close connection 
mysql_close();
?>


insert.php
<table width="300" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td><form name="form1" method="post" action="insert_ac.php">
<table width="100%" border="0" cellspacing="1" cellpadding="3">
<tr>
<td colspan="3"><strong>Insert Data Into mySQL Database </strong></td>
</tr>
<tr>
<td width="71">Name</td>
<td width="6">:</td>
<td width="301"><input name="name" type="text" id="name"></td>
</tr>
<tr>
<td>Lastname</td>
<td>:</td>
<td><input name="lastname" type="text" id="lastname"></td>
</tr>
<tr>
<td>Email</td>
<td>:</td>
<td><input name="email" type="text" id="email"></td>
</tr>


<!--one textbox submit-->
<!--<tr>-->
<!--<td>Schedule Time</td>-->
<!--<td>:</td>-->
<!--<td><input name="schedule_time" type="text" id="schedule_time"></td>-->
<!--</tr>-->



<!--three textboxes submission of schedule_time  000|0000-00-00 00:00:00|0  -->
<td>Schedule Time</td>
<td>:</td>
<td>
<input type="text" name="schedule_time" id="schedule_time" value="<?php echo $ary[0]; ?>"> <!--000-->
<input type="text" name="schedule_time" id="schedule_time" value="<?php echo $ary[1]; ?>"> <!--0000-00-00 00:00:00-->
<input type="text" name="schedule_time" id="schedule_time" value="<?php echo $ary[2]; ?>"> <!--0-->
</td>
</tr>

<tr>
<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</form>
</td>
</tr>
</table>

Attached Files


  • 0

#21 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 06 February 2012 - 02:18 PM

When you use the above code, what do you get? Errors? If so, what specific error messages are you getting?
  • 0
Benjamin Falk
Falken Creative : Twitter

#22 metabee

metabee

    New member

  • New Members
  • 9 posts

Posted 06 February 2012 - 07:38 PM

When you use the above code, what do you get? Errors? If so, what specific error messages are you getting?


I always get this 000 0000-00-00 00:00:000 format

but not this 000|0000-00-00 00:00:00|0 format

For example I type 100 2000-10-02 06:00:10 6 in the three textboxes,

100 -> textbox 1
2000-10-02 06:00:10 -> textbox 2
6 -> textbox 3

it still remain the same 000 0000-00-00 00:00:000.

I want to get this data 100|2000-10-02 06:00:10|6 in sql database.

Please help me to solve.


textbox codes found in insert.php
<!--one textbox submit-->
<!--<tr>-->
<!--<td>Schedule Time</td>-->
<!--<td>:</td>-->
<!--<td><input name="schedule_time" type="text" id="schedule_time"></td>-->
<!--</tr>-->



<!--three textboxes submission of schedule_time  000|0000-00-00 00:00:00|0  -->
<td>Schedule Time</td>
<td>:</td>
<td>
<input type="text" name="schedule_time" id="schedule_time" value="<?php echo $ary[0]; ?>"> <!--000-->
<input type="text" name="schedule_time" id="schedule_time" value="<?php echo $ary[1]; ?>"> <!--0000-00-00 00:00:00-->
<input type="text" name="schedule_time" id="schedule_time" value="<?php echo $ary[2]; ?>"> <!--0-->
</td>
</tr>



insert_ac.php
// Get values from form  
$name=$_POST['name']; 
$lastname=$_POST['lastname']; 
$email=$_POST['email']; 
$schedule_time = $_POST['schedule_time']; 
 
 
$val = "000|0000-00-00 00:00:00|0"; 
 
$ary = explode("|", $val); 
 
$a = $ary[0]; 
$b = $ary[1]; 
$c = $ary[2]; 
 
$abc = $a . " " . $b .  $c; 
 
 
 
 
 
// Insert data into mysql  
$sql="INSERT INTO $tbl_name(name, lastname, email, schedule_time)VALUES('$name', '$lastname', '$email', '$abc')"; 
$result=mysql_query($sql); 
 
// if successfully insert data into database, displays message "Successful".  
if($result){ 
echo "Successful"; 
echo "<BR>"; 
echo "<a href='insert.php'>Back to main page</a>"; 
} 
 
else { 
echo "ERROR"; 
} 
 
// close connection  
mysql_close(); 
?>



database file for import
--
-- Database: `1`
--

-- --------------------------------------------------------

--
-- Table structure for table `test_mysql`
--

CREATE TABLE IF NOT EXISTS `test_mysql` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(65) NOT NULL DEFAULT '',
  `lastname` varchar(65) NOT NULL DEFAULT '',
  `email` varchar(65) NOT NULL DEFAULT '',
  `schedule_time` varchar(25) CHARACTER SET utf8 NOT NULL DEFAULT '000|0000-00-00 00:00:00|0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `test_mysql`
--

INSERT INTO `test_mysql` (`id`, `name`, `lastname`, `email`, `schedule_time`) VALUES
(1, 'john', 'lere', 'yhh@hotmail.com', '000|0000-00-00 00:00:00|0'),

  • 0

#23 Ansir

Ansir

    New member

  • New Members
  • 1 posts

Posted 28 February 2012 - 07:01 AM

Hi, Great tutorial. I got every thing to work except the Edit record part. I am using the non-paginated version. The php file is attached.

Attached Files


  • 0

#24 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 28 February 2012 - 10:18 AM

Hi, Great tutorial. I got every thing to work except the Edit record part. I am using the non-paginated version. The php file is attached.

Can you specify what issues you are having? Are you getting any errors? What are you expecting, and what are you getting?
  • 0
Benjamin Falk
Falken Creative : Twitter

#25 chuckmorris

chuckmorris

    New member

  • New Members
  • 1 posts

Posted 06 March 2012 - 01:17 PM

Hi,

I'm new to php and I'm having some trouble getting your tutorial working.

the view-paginated screen isn't working, could be my initial php setup that is at fault. Checked phpinfo() and from what I can see everything is setup.

Here is a snippet from my log.

[06-Mar-2012 18:08:59] PHP Warning: include(connect-db.php) [<a href='function.include'>function.include</a>]: failed to open stream: No such file or directory in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\ttt\view-paginated.php on line 13

[06-Mar-2012 18:08:59] PHP Warning: include() [<a href='function.include'>function.include</a>]: Failed opening 'connect-db.php' for inclusion (include_path='.;C:\php5\pear') in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\ttt\view-paginated.php on line 13

[06-Mar-2012 18:08:59] PHP Notice: Undefined variable: mysqli in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\ttt\view-paginated.php on line 19

[06-Mar-2012 18:08:59] PHP Fatal error: Call to a member function query() on a non-object in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\ttt\view-paginated.php on line 19

Edit - Fixed the problem now. An absolute rookie mistake.
  • 0

#26 GIRISH

GIRISH

    New member

  • New Members
  • 2 posts

Posted 16 March 2012 - 02:19 AM

Hi ADMIN,

Great tutorial. I m using it right now for my project. Actually I replaced the text boxes of First and last name by TEXTAREA. Actually I want to input information like one page or more. Bt I am unable to do it. Please help me out. I edited Records.php to replace textboxes by textarea. I am able to insert small amonut of data like 2-3 line. But when I am trying insert more its not happening. I am not able to Insert and Edit the large amount of data. Please help me out If anyone knows how to do it. I attached the Edited records.php file. Other files are use as its is.

Attached Files


  • 0

#27 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 16 March 2012 - 07:09 AM

GIRISH, the problem isn't in your code -- it's most likely in your database setup. This is the code used from my original example to create the database:

CREATE TABLE `players` (
`id` int(11) NOT NULL auto_increment,
`firstname` varchar(32) NOT NULL,
`lastname` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

The "varchar(32)" indicates the type of the column in the database, and it's currently limited to 32 characters. I would suggest using PHPMyAdmin or similar to modify the database, changing those "varchar" columns to use a "text" type instead that doesn't have character limits.
  • 0
Benjamin Falk
Falken Creative : Twitter

#28 Nasrullah

Nasrullah

    New member

  • New Members
  • 1 posts

Posted 04 April 2012 - 03:21 AM

Bros !
Here i m uploading the code for inserting the record with date of current day So Enjoy @@@@
You can use attached files freely and insert ur data into mysql with the current date......

Attached Files


  • 0

#29 TomTom

TomTom

    New member

  • New Members
  • 5 posts

Posted 07 April 2012 - 01:04 PM

Hi Ben,
I am totally new to php & found this which has helped me greatly to understand it.Thanks.
I have used your MySQL first example in MySQL basic to see how it all works but am having issues with the "delete" & the "edit".
I was hoping you could help me with it?

The "delete" is just not doing anything when it is clicked.
<?php

// connect to the database 
include('connect-db.php');  
// check if the 'id' variable is set in URL, and check that it is valid 
if (isset($_GET['cust_no']) && is_numeric($_GET['cust_no'])) 
{ 
// get id value 
$cust_no = $_GET['cust_no'];  
// delete the entry 
$result = mysql_query("DELETE FROM customer WHERE id=$cust_no") 
or die(mysql_error());   
// redirect back to the view page header("Location: view.php"); 
} 
else 
// if id isn't set, or isn't valid, redirect back to view page 
{ 
header("Location: view.php"); 
} 
?>

In the "edit.php" I am getting a:
Parse error: syntax error, unexpected T_ELSE on line 96, which would appear to be the last "else" in the code below.
(Hope I have laid this out right & in the proper order, if not, my appologies.
the table is: customer.
the elements of the table are: cust_no, name,address, phone_no.

<?php
// creates the edit record form 
function renderForm($cust_no, $name, $address, $phone_no, $error) 
{ 
?> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> 
<html> 
<head> 
<title>Edit Record</title> 
</head> 
<body> 
<?php  
// if there are any errors, display them 
if ($error != '') 
{ 
echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>'; 
} 
?>
<form action="" method="post"> 
<input type="hidden" name="cust_no" value="<?php echo $cust_no; ?>"/> 
<div> 
<p><strong>Customer No:</strong> <?php echo $cust_no; ?></p> 
<strong>Name: *</strong> <input type="text" name="name" value="<?php echo $name; ?>"/><br/> 
<strong>Address: *</strong> <input type="text" name="address" value="<?php echo $address; ?>"/><br/> 
<strong>Phone No: *</strong> <input type="text" name="phone_no" value="<?php echo $phone_no; ?>"/><br/> 
<p>* Required</p> 
<input type="submit" name="submit" value="Submit"> 
</div> 
</form>  
</body> 
</html>  
<?php 
} 
// connect to the database 
include('connect-db.php');  
// check if the form has been submitted. If it has, process the form and save it to the database 
if (isset($_POST['submit'])) 
{  
// confirm that the 'id' value is a valid integer before getting the form data 
if (is_numeric($_POST['cust_no'])) 
{ 
// get form data, making sure it is valid 
$cust_no = $_POST['cust_no']; 
$name = mysql_real_escape_string(htmlspecialchars($_POST['name'])); 
$address = mysql_real_escape_string(htmlspecialchars($_POST['address']));  
$phone_no = mysql_real_escape_string(htmlspecialchars($_POST['phone_no']));
// check that  fields are filled in 
if ($name == '' || $address == '' || $phone_no == '') 
{ 
// generate error message 
$error = 'ERROR: Please fill in all required fields!'; 
 //error, display form 
renderForm($cust_no, $name, $address, $phone_no, $error); 
} 
else 
{ 
// save the data to the database 
mysql_query("UPDATE customer SET name='$name', address='$address', phone_no='$phone_no' WHERE cust_no='$cust_no'") 
or die(mysql_error()); 
// once saved, redirect back to the view page 
header("Location: view.php");  
} 
} 
else 
{ 
// if the 'id' isn't valid, display an error 
echo 'Error!'; 
} 
} 
else 
// if the form hasn't been submitted, get the data from the db and display the form 
{  
// get the 'id' value from the URL (if it exists), making sure that it is valid (checking that it is numeric/larger than 0) if (isset($_GET['cust_no']) && is_numeric($_GET['cust_no']) && $_GET['cust_no'] > 0) 
{ 
// query db 
$cust_no = $_GET['cust_no']; 
$result = mysql_query("SELECT * FROM customer WHERE cust_no=$cust_no") 
or die(mysql_error());  
$row = mysql_fetch_array($result); 
// check that the 'id' matches up with a row in the databse 
if($row) 
{  
// get data from db 
$name = $row['name']; 
$address = $row['address']; 
$phone_no = $row['phone_no'];  
// show form 
renderForm($cust_no, $name, $address, $phone_no, ''); 
} 
else 
// if no match, display result 
{ 
echo "No results"; 
} 
} 
else
// if the 'id' in the URL isn't valid, or if there is no 'id' value, display an error 
{ 
echo 'Error'; 
} 
}
?>

If you can help with these issues, I would be very thankful.
  • 0

#30 Ben

Ben

    Administrator

  • Administrators
  • 5,641 posts
  • LocationChico, CA

Posted 08 April 2012 - 11:12 AM

To help you out with deleting a record, I would probably need to see your view file. Most likely the link to the delete file is incorrect and doesn't include the right "cust_no" in the URL.

For the edit record, I think this line is the problem:

// get the 'id' value from the URL (if it exists), making sure that it is valid (checking that it is numeric/larger than 0) if (isset($_GET['cust_no']) && is_numeric($_GET['cust_no']) && $_GET['cust_no'] > 0)
Looks like it is one long line, and the "//" at the start of the line means that it is all commented out. You need to have the "if" statement on its own line, like this:

// get the 'id' value from the URL (if it exists), making sure that it is valid (checking that it is numeric/larger than 0) 
if (isset($_GET['cust_no']) && is_numeric($_GET['cust_no']) && $_GET['cust_no'] > 0) 
also, I believe you have an extra, unnecessary "}" at the end of the file.
  • 0
Benjamin Falk
Falken Creative : Twitter




1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users