falkencreative Posted May 9, 2010 Report Share Posted May 9, 2010 Check out our new Interactive Web Developer course (created in 2016) that covers this, and much more: shop.killervideostore.com This is a revised version of my previous tutorial (http://www.killersites.com/community/index.php?/topic/1969-basic-php-system-vieweditdeleteadd-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.webmentor.org - 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 recordsOnline demo:http://www.falkencreative.com/forum/records-mysqli/view.phpBasically, 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 withSQL 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"); } ?> 1 Link to comment Share on other sites More sharing options...
deekay Posted August 19, 2010 Report Share Posted August 19, 2010 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. Link to comment Share on other sites More sharing options...
falkencreative Posted August 19, 2010 Author Report Share Posted August 19, 2010 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.killersites.com/community/index.php?/topic/1969-basic-php-system-vieweditdeleteadd-records/ Link to comment Share on other sites More sharing options...
deekay Posted August 19, 2010 Report Share Posted August 19, 2010 Cool! now it works. thanks Link to comment Share on other sites More sharing options...
Guest Koos Mooij Posted December 27, 2010 Report Share Posted December 27, 2010 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. Link to comment Share on other sites More sharing options...
falkencreative Posted December 27, 2010 Author Report Share Posted December 27, 2010 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. Link to comment Share on other sites More sharing options...
Guest ashuweb Posted August 2, 2011 Report Share Posted August 2, 2011 Thanxxxxxxxxxxxxxxxx For PHP&sql Script Link to comment Share on other sites More sharing options...
itsover9000 Posted October 22, 2011 Report Share Posted October 22, 2011 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? Link to comment Share on other sites More sharing options...
falkencreative Posted October 22, 2011 Author Report Share Posted October 22, 2011 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 Link to comment Share on other sites More sharing options...
griffer Posted November 5, 2011 Report Share Posted November 5, 2011 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. Link to comment Share on other sites More sharing options...
falkencreative Posted November 8, 2011 Author Report Share Posted November 8, 2011 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. Link to comment Share on other sites More sharing options...
griffer Posted November 12, 2011 Report Share Posted November 12, 2011 Sry for late reply. Thx for the tip managed to figure it out . Link to comment Share on other sites More sharing options...
metabee Posted November 16, 2011 Report Share Posted November 16, 2011 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. Link to comment Share on other sites More sharing options...
falkencreative Posted November 19, 2011 Author Report Share Posted November 19, 2011 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. Link to comment Share on other sites More sharing options...
metabee Posted November 22, 2011 Report Share Posted November 22, 2011 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/xhtml1/DTD/xhtml1-transitional.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 Link to comment Share on other sites More sharing options...
mrsamrsa Posted December 21, 2011 Report Share Posted December 21, 2011 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 Link to comment Share on other sites More sharing options...
xSerenity Posted January 26, 2012 Report Share Posted January 26, 2012 Thanks for the great help. Link to comment Share on other sites More sharing options...
CLU Posted January 29, 2012 Report Share Posted January 29, 2012 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? Link to comment Share on other sites More sharing options...
falkencreative Posted January 30, 2012 Author Report Share Posted January 30, 2012 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.) Link to comment Share on other sites More sharing options...
metabee Posted February 6, 2012 Report Share Posted February 6, 2012 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> insert_ac.zip Link to comment Share on other sites More sharing options...
falkencreative Posted February 6, 2012 Author Report Share Posted February 6, 2012 When you use the above code, what do you get? Errors? If so, what specific error messages are you getting? Link to comment Share on other sites More sharing options...
metabee Posted February 7, 2012 Report Share Posted February 7, 2012 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'), Link to comment Share on other sites More sharing options...
Ansir Posted February 28, 2012 Report Share Posted February 28, 2012 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. records.php Link to comment Share on other sites More sharing options...
falkencreative Posted February 28, 2012 Author Report Share Posted February 28, 2012 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? Link to comment Share on other sites More sharing options...
chuckmorris Posted March 6, 2012 Report Share Posted March 6, 2012 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. Link to comment Share on other sites More sharing options...
GIRSH Posted March 16, 2012 Report Share Posted March 16, 2012 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. records.php Link to comment Share on other sites More sharing options...
falkencreative Posted March 16, 2012 Author Report Share Posted March 16, 2012 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. Link to comment Share on other sites More sharing options...
Nasrullah Posted April 4, 2012 Report Share Posted April 4, 2012 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...... insert.php insert_ac.php table.php Link to comment Share on other sites More sharing options...
TomTom Posted April 7, 2012 Report Share Posted April 7, 2012 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. Link to comment Share on other sites More sharing options...
falkencreative Posted April 8, 2012 Author Report Share Posted April 8, 2012 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. Link to comment Share on other sites More sharing options...
Recommended Posts