Jump to content

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


falkencreative

Recommended Posts

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 records

Online demo:
http://www.falkencreative.com/forum/records-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");
}

?>
  • Like 1
Link to comment
Share on other sites

  • 3 months later...

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

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

  • 4 months later...
Guest Koos Mooij

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

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

  • 7 months later...
  • 2 months later...
  • 2 weeks later...

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

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

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

  • 5 weeks later...

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

  • 1 month later...

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

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

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

  • 3 weeks later...

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

  • 2 weeks later...

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

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

  • 3 weeks later...

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

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

Guest
This topic is now closed to further replies.
×
×
  • Create New...