Jump to content


Photo

Basic PHP System: View/Edit/Delete/Add Records


  • Please log in to reply
190 replies to this topic

#1 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 08 October 2009 - 06:00 PM

(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...ecords/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
/* 
 CONNECT-DB.PHP
 Allows PHP to connect to your database
*/

 // Database Variables (edit with your own server information)
 $server = 'localhost';
 $user = 'root';
 $pass = 'root';
 $db = 'records';
 
 // Connect to Database
 $connection = mysql_connect($server, $user, $pass) 
 or die ("Could not connect to server ... \n" . mysql_error ());
 mysql_select_db($db) 
 or die ("Could not connect to database ... \n" . mysql_error ());


?>

view.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
	<title>View Records</title>
</head>
<body>

<?php
/* 
	VIEW.PHP
	Displays all data from 'players' table
*/

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

	// get results from database
	$result = mysql_query("SELECT * FROM players") 
		or die(mysql_error());  
		
	// display data in table
	echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View Paginated</a></p>";
	
	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
	while($row = mysql_fetch_array( $result )) {
		
		// echo out the contents of each row into a table
		echo "<tr>";
		echo '<td>' . $row['id'] . '</td>';
		echo '<td>' . $row['firstname'] . '</td>';
		echo '<td>' . $row['lastname'] . '</td>';
		echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit</a></td>';
		echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>';
		echo "</tr>"; 
	} 

	// close table>
	echo "</table>";
?>
<p><a href="new.php">Add a new record</a></p>

</body>
</html>	

view-paginated.php
Same as view.php above, except that results are split up into separate pages
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
	<title>View Records</title>
</head>
<body>

<?php
/* 
	VIEW-PAGINATED.PHP
	Displays all data from 'players' table
	This is a modified version of view.php that includes pagination
*/

	// 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
	$result = mysql_query("SELECT * FROM players");
	$total_results = mysql_num_rows($result);
	$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++)
	{
		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; }
	
		// echo out the contents of each row into a table
		echo "<tr>";
		echo '<td>' . mysql_result($result, $i, 'id') . '</td>';
		echo '<td>' . mysql_result($result, $i, 'firstname') . '</td>';
		echo '<td>' . mysql_result($result, $i, 'lastname') . '</td>';
		echo '<td><a href="edit.php?id=' . mysql_result($result, $i, 'id') . '">Edit</a></td>';
		echo '<td><a href="delete.php?id=' . mysql_result($result, $i, 'id') . '">Delete</a></td>';
		echo "</tr>"; 
	}
	// close table>
	echo "</table>"; 
	
	// pagination
	
?>
<p><a href="new.php">Add a new record</a></p>

</body>
</html>

new.php
<?php
/* 
 NEW.PHP
 Allows user to create a new entry in the database
*/
 
 // creates the new 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)
 {
 ?>
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
 <html>
 <head>
 <title>New 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">
 <div>
 <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; ?>" /><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, start to process the form and save it to the database
 if (isset($_POST['submit']))
 { 
 // get form data, making sure it is valid
 $firstname = mysql_real_escape_string(htmlspecialchars($_POST['firstname']));
 $lastname = mysql_real_escape_string(htmlspecialchars($_POST['lastname']));
 
 // check to make sure both fields are entered
 if ($firstname == '' || $lastname == '')
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';
 
 // if either field is blank, display the form again
 renderForm($firstname, $lastname, $error);
 }
 else
 {
 // save the data to the database
 mysql_query("INSERT players SET firstname='$firstname', lastname='$lastname'")
 or die(mysql_error()); 
 
 // once saved, redirect back to the view page
 header("Location: view.php"); 
 }
 }
 else
 // if the form hasn't been submitted, display the form
 {
 renderForm('','','');
 }
?>

edit.php
<?php
/* 
 EDIT.PHP
 Allows user to edit specific entry in database
*/

 // creates the 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($id, $firstname, $lastname, $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="id" value="<?php echo $id; ?>"/>
 <div>
 <p><strong>ID:</strong> <?php echo $id; ?></p>
 <strong>First Name: *</strong> <input type="text" name="firstname" value="<?php echo $firstname; ?>"/><br/>
 <strong>Last Name: *</strong> <input type="text" name="lastname" value="<?php echo $lastname; ?>"/><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['id']))
 {
 // get form data, making sure it is valid
 $id = $_POST['id'];
 $firstname = mysql_real_escape_string(htmlspecialchars($_POST['firstname']));
 $lastname = mysql_real_escape_string(htmlspecialchars($_POST['lastname']));
 
 // check that firstname/lastname fields are both filled in
 if ($firstname == '' || $lastname == '')
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';
 
 //error, display form
 renderForm($id, $firstname, $lastname, $error);
 }
 else
 {
 // save the data to the database
 mysql_query("UPDATE players SET firstname='$firstname', lastname='$lastname' WHERE id='$id'")
 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 (checing that it is numeric/larger than 0)
 if (isset($_GET['id']) && is_numeric($_GET['id']) && $_GET['id'] > 0)
 {
 // query db
 $id = $_GET['id'];
 $result = mysql_query("SELECT * FROM players WHERE id=$id")
 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
 $firstname = $row['firstname'];
 $lastname = $row['lastname'];
 
 // show form
 renderForm($id, $firstname, $lastname, '');
 }
 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!';
 }
 }
?>

delete.php
<?php
/* 
 DELETE.PHP
 Deletes a specific entry from the 'players' table
*/

 // 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['id']) && is_numeric($_GET['id']))
 {
 // get id value
 $id = $_GET['id'];
 
 // delete the entry
 $result = mysql_query("DELETE FROM players WHERE id=$id")
 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");
 }
 
?>

  • 1
Benjamin Falk
Falken Creative : Twitter

#2 jlhaslip

jlhaslip

    Advanced Member

  • Member
  • PipPipPipPip
  • 530 posts
  • LocationGolden, BC

Posted 09 October 2009 - 09:26 AM

Nice work. This should help those wishing to learn some php. It is also a good start to the Admin side of small applications.
  • 0
My signature goes here --> X

#3 Guest_mynameiscoffey

Guest_mynameiscoffey
  • Guests

Posted 22 December 2009 - 04:35 PM

You should add some validation to your script, as it stands in the sample code above is easily injectable.


results in all names being blown out of each record:
EDIT PAGE
FIRSTNAME: '#
LASTNAME: anything


You could just as easily throw a delete or drop statement in there instead of just commenting off the remainder of the sql statement. This might not be as critical on an internal site for yourself only but its a big deal to worry about when dealing with public facing sites.

mysql_real_escape_string() can solve these problems pretty painlessly - just as a heads up to anyone looking here for learning purposes.


Edit:
here is an article discussing in more detail what I am talking about: http://www.tizag.com...l-injection.php

Edited by mynameiscoffey, 22 December 2009 - 04:37 PM.

  • 0

#4 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 22 December 2009 - 05:37 PM

You should add some validation to your script, as it stands in the sample code above is easily injectable.

mysql_real_escape_string() can solve these problems pretty painlessly - just as a heads up to anyone looking here for learning purposes.


Good point -- I had overlooked that. I've updated the tutorial and the live code to include mysql_real_escape_string().
  • 0
Benjamin Falk
Falken Creative : Twitter

#5 Wickham

Wickham

    Advanced Member

  • Advanced Member
  • PipPipPipPip
  • 1,732 posts
  • LocationSalisbury UK

Posted 23 December 2009 - 04:26 AM

I've been experimenting with mysql_real_escape_string() and found that it deletes all content in a field, so where I had
$message = ($_POST['message']);
in the php file to process a form I got an entry in a database but when I edited the form php to
$message = mysql_real_escape_string($_POST['message']);
it sent the form data to the database with all the other fields but the message field was empty.

It did this whether I used Tizag.com's example of ' OR 1' inside ' which is supposed to be escaped and also when I just entered normal text.

Additionally for other fields I have
elseif (empty($day) || empty($month) || empty($year) ||
empty($starttime) || empty($endtime) || empty($who) || empty($email) ||
empty($phone)) {
header( "Location: $errorurl" );
}

and if I edit a variable to
$phone = mysql_real_escape_string($_POST['phone']);

the form produces an error because it thinks the field is empty because the mysql_real_escape_string() has made it empty.

Tizag's example for using mysql_real_escape_string() seems to be for use where you are extracting data from a database using
$name_bad = "' OR 1'";

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection:
" . $query_bad . "
";

but when inputting data from a form it seems to delete the normal data which you don't want.

There must be something I've missed.
  • 0

#6 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 23 December 2009 - 10:04 AM

I've been experimenting with mysql_real_escape_string() and found that it deletes all content in a field, so where I had
$message = ($_POST['message']);
in the php file to process a form I got an entry in a database but when I edited the form php to
$message = mysql_real_escape_string($_POST['message']);
it sent the form data to the database with all the other fields but the message field was empty.

It doesn't do that for me... if you check out the online example, which I updated, it works just fine. I'm not sure what's going on there for you...

Tizag's example for using mysql_real_escape_string() seems to be for use where you are extracting data from a database using $name_bad = "' OR 1'";

From PHP.net:

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.


mysql_real_escape_string() is intended to prevent a single or double quote from accidentally closing a SQL query and giving someone else the ability to insert their own code.

For example, say a person enters this into an input named "username":

' OR 1"

and this was the PHP that you used to capture that data:

$var = $_GET['username'];
$query = "SELECT * FROM customers WHERE username = '$var'";

Without fixing that code in any way, here's what the query will look like:

$query = "SELECT * FROM customers WHERE username = '' OR 1";

The extra quote in there will end the query early, and then adds an additional clause, meaning that the statement will always be true, so every single entry in the "customers" table would be selected by this statement. Using this method, someone could insert and run additional code, even deleting tables or dropping the entire database. The mysql_real_escape_string() escapes those potentially malicious characters so they don't affect the query.
  • 0
Benjamin Falk
Falken Creative : Twitter

#7 Wickham

Wickham

    Advanced Member

  • Advanced Member
  • PipPipPipPip
  • 1,732 posts
  • LocationSalisbury UK

Posted 24 December 2009 - 02:10 AM

The reason why my field was empty after using
$message = mysql_real_escape_string($_POST['message']);
was because all my variables were after the
Having used 'OR 1' in submitting the form I notice that it is still displayed as 'OR 1' in the database, so I'm still trying to work out whether the escape string has done what it should. I thought it was supposed to show as \'OR 1\' in the database but perhaps it will work if I use the field in code like SELECT FROM

I also note that php.net says before sending a query to MySQL but I was using it to send data from a form into the database; I'm not using that field for a query to SELECT FROM; that's probably why the database hasn't put a \ before ' (so if someone enters an address like Joe's farm into a web form, you wouldn't want the ' to be preceded by \ as the ' is supposed to be there).

With your example, if someone's name is D'Arcy Wentworth Thompson, (a real name) does that show as D'Arcy Wentworth Thompson in the database or as D\'Arcy Wentworth Thompson and when you view the name on a web page is it D\'Arcy Wentworth Thompson which would look odd?

Edited by Wickham, 24 December 2009 - 11:17 AM.

  • 0

#8 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 24 December 2009 - 11:44 AM

Having used 'OR 1' in submitting the form I notice that it is still displayed as 'OR 1' in the database, so I'm still trying to work out whether the escape string has done what it should. I thought it was supposed to show as \'OR 1\' in the database but perhaps it will work if I use the field in code like SELECT FROM

As far as I know, that means it is working correctly. For example, on the live version of this tutorial (before I added mysql_real_escape_string()), if I entered

' or 1

into an input and tried to add a new record, I'd get an error saying my SQL query was malformed (caused by the text I entered into the input.) With the mysql_real_escape_string() in place, I can enter that same text in the input and it automatically escapes it and I get no errors. Yes, it shows up in the database, but it can no longer affect the SQL query.

I also note that php.net says before sending a query to MySQL but I was using it to send data from a form into the database;

Well, you are using it before your SQL query. As long as you are using it to wrap your $_GET or $_POST, and then using that value within your query, you are using it before sending a query to MySQL. Using it to escape get or post data before adding it to a database is the correct use of the function.

With your example, if someone's name is D'Arcy Wentworth Thompson, (a real name) does that show as D'Arcy Wentworth Thompson in the database or as D\'Arcy Wentworth Thompson and when you view the name on a web page is it D\'Arcy Wentworth Thompson which would look odd?

It appears without any slashes... but it has been escaped so it doesn't cause problems.
  • 0
Benjamin Falk
Falken Creative : Twitter

#9 Wickham

Wickham

    Advanced Member

  • Advanced Member
  • PipPipPipPip
  • 1,732 posts
  • LocationSalisbury UK

Posted 24 December 2009 - 12:45 PM

I've now got my version of a booking form with output to a table on a webpage working as I want. It was all set up and working before I saw this topic and tried adding mysql_real_escape_string(). My code structure is totally different from your's.

I found that it either made an empty field if it was before the connection to the database, or when I put the variable inside the mysql section it created an error if a name or text had ' in it (which needs to be kept).

I've now got a clumsy code where the first part of the php code before the mysql_connect takes the form data:-
$club = $_POST['club'] ;

and as that didn't work with mysql_real_escape_string I've now created another set of variables after the mysql_connect including htmlspecialchars which you had and I hadn't included before:-
$club =  mysql_real_escape_string(htmlspecialchars($club));

So it works with text and names which have ' and presumably has the injection protection and I expect I can make the code a bit simpler.

Edit: It didn't work online and after messing about I realised that my hosting service gives me MySQL 4.1.22-standard but I think that mysql_real_escape_string() requires MySQL 5 to work properly. I have PHP 5.2.4 which should be good enough.

NOTE: I've just uploaded all your player files to my host and found that ' and " are both escaped (" with \" and ' with just a \) in the database and the \ shows on a web page table, so it must be my MySQL version which is not sufficiently up to date, but I can edit out the " and \ . I suppose it means that my MySQL version is protected from sql injection because it operates the escape but the older MySQL fails to delete the \ when shown in the database or on a web page.

As I can't get my host to update my MySQL today, I've looked for an alternative. I've just added this into your new.php page:-
$lastname = stripslashes($lastname);
after $lastname = mysql_real_escape_string(htmlspecialchars($_POST['lastname']));
and it works to maintain " or ' in the database text without the \ and shows " and ' in a web page without the \ , but I wonder if it has disabled the escape and therefore left me open to sql injection !!

mysql_real_escape_string() doesn't show a \ in up to date MySQL or after retrieval to a web page but does in my older MySQL version so I've put stripslashes only into the retrieval code for display on a web page, I don't mind if the \ shows in the database; it's probably should be there for sql injection protection.

In php.net it says for htmlspecialchars ''' (single quote) becomes ''' only when ENT_QUOTES is set. So I've used entities ENT_QUOTES instead of htmlspecialchars. First the mysql_real_escape_string() which causes a \ in my old MySQL version, then ENT_QUOTES to convert " to " and ' to ' :-
$club =  mysql_real_escape_string(htmlentities($club, ENT_QUOTES));

and then stripslashes in the retrieval code to get rid of the \ in the web page table:-
<td class='clubtd'>".stripslashes($row['club'])."</td>

The database still shows the \" and \' but the web page shows " and ' which is what I want; but my newer MySQL version in WampServer doesn't show the \ in the database.

If all " and ' are converted to " and ' before entering the database, why is there a need for mysql_real_escape_string() if htmlspecialchars or ENT_QUOTES is used? Can the ' be processed if it's ' ? Google has lots of posts that mysql_real_escape_string() is not as good as it makes out to be and in my case it would stop the \ if I just relied on htmlspecialchars like you did originally or ENT_QUOTES to convert ' as well as "?

The up to date MySQL seems to give no problems; the above is just for my old MySQL version.

Edited by Wickham, 26 December 2009 - 07:09 AM.

  • 0

#10 TLH

TLH

    New member

  • New Members
  • 2 posts

Posted 30 March 2010 - 02:49 PM

The Basic PHP System: View/Edit/Delete/Add Records forum appears to be corrupt.

I am unable to view the code correctly.

Any ideas?
  • 0

#11 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 30 March 2010 - 04:22 PM

The Basic PHP System: View/Edit/Delete/Add Records forum appears to be corrupt.


Thanks for pointing that out. In case you didn't realize, we switched forums recently and some of the code samples that were transferred have issues. Take another look -- I've fixed the post.
  • 0
Benjamin Falk
Falken Creative : Twitter

#12 TLH

TLH

    New member

  • New Members
  • 2 posts

Posted 01 April 2010 - 05:22 PM

Thanks for pointing that out. In case you didn't realize, we switched forums recently and some of the code samples that were transferred have issues. Take another look -- I've fixed the post.


Thanks This is really a nice set of scripts
  • 0

#13 SirNessyUK

SirNessyUK

    New member

  • New Members
  • 5 posts

Posted 29 April 2010 - 03:50 AM

Hello,

Just a quick question, what happens if you have hundreds of records, is there anyway to limit them and display the other records in the next page.
  • 0

#14 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 29 April 2010 - 04:10 AM

Just a quick question, what happens if you have hundreds of records, is there anyway to limit them and display the other records in the next page.


Yes, it is possible, though it obviously isn't built in at the moment. Probably the easiest way to do it would be to add a variable to the url on the view page:

view.php?page=2

Say you wanted to show ten records per page. You could then get the page variable from the URL using $_GET and then modify the MySQL command so it limits the results (http://php.about.com...g/Limit_sql.htm)

I haven't tested this, but you may be able to do something similar to this:

if (isset($_GET['id']) && is_numeric($_GET['id']))
{
$start = $_GET['id'] * 10;
}
else
{
$start = 10;
}
$result = mysql_query("SELECT * FROM players LIMIT $start, 10")

This should give you a rough place to start, though I imagine it will need some tweaking to make sure the number isn't invalid. For example, you'll not only need to make sure it's a numeric value, but also that the number isn't negative and that it won't try to display results that aren't in the database (for example, what if the id value is "3", meaning that the query would show results 30-40, but there are only 20 records in the database table?).

I am considering turning this tutorial into a video tutorial, so perhaps I'll look into adding this feature if/when I do that.
  • 0
Benjamin Falk
Falken Creative : Twitter

#15 SirNessyUK

SirNessyUK

    New member

  • New Members
  • 5 posts

Posted 29 April 2010 - 04:14 AM

Hello,

Thanks for the info, i will take a look at that and see how i get on.
  • 0

#16 J Stern

J Stern

    Advanced Member

  • Member
  • PipPipPipPip
  • 103 posts

Posted 29 April 2010 - 10:02 AM

Falken that would be pretty awesome. I know I could use a lot more info on something like this. Could you do it using the Zend Framework?
  • 0

#17 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 29 April 2010 - 10:21 AM

Could you do it using the Zend Framework?

To be honest, probably not. I haven't used Zend much, so I'm not sure I'm authorized to teach. :P I could do it in CodeIgniter though.
  • 0
Benjamin Falk
Falken Creative : Twitter

#18 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 29 April 2010 - 11:26 AM

To anyone who is interested, I am going to do a video tutorial on this topic that should explain how I go about this a little better than the code I have provided. It'll probably be out sometime next week.

I may also do a separate non KillerSites version showing some of the basics of CodeIgniter as well... I'll have to see if I have enough free time.
  • 0
Benjamin Falk
Falken Creative : Twitter

#19 J Stern

J Stern

    Advanced Member

  • Member
  • PipPipPipPip
  • 103 posts

Posted 29 April 2010 - 12:36 PM

whats the skinny on CodeIgniter? Is it a php editor or more like a framework??
  • 0

#20 Stefan

Stefan

    Stefan Mischook

  • Administrators
  • 4,251 posts
  • LocationMontreal Canada

Posted 29 April 2010 - 01:37 PM

whats the skinny on CodeIgniter? Is it a php editor or more like a framework??

CodeIgniter is a PHP framework and from what I've seen, it is pretty good.

I decided to back the Zend Framework thought because I figured it would become the predominant PHP framework over time - as it has.

Stefan
  • 0
StudioWeb makes teaching web design and programming easy: StudioWeb

#21 J Stern

J Stern

    Advanced Member

  • Member
  • PipPipPipPip
  • 103 posts

Posted 29 April 2010 - 03:47 PM

From everything Ive learned along the way so far, I'm gonna have to agree. Its what were using at work as well so until I've 'mastered' it, I'd rather not confuse myself with others...yet

Either way, I plan on checking out that tutorial, I'm just not sure if I can follow along.
  • 0

#22 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 29 April 2010 - 11:47 PM

Just a quick question, what happens if you have hundreds of records, is there anyway to limit them and display the other records in the next page.


I played with the files a bit... check my original post in this topic -- I've added code for a revised version of view.php that incorporates some basic pagination. The view-paginated.php page is set up to show 3 results per page (just for this demo - I realize you'd probably want 10+ per page), but that can be adjusted by changing the $per_page variable near the top of the view-paginated.php file.
  • 0
Benjamin Falk
Falken Creative : Twitter

#23 Guest_cjmiles

Guest_cjmiles
  • Guests

Posted 19 May 2010 - 10:22 PM

Cool tutorial. Can you suggest a way to incorporate a "search" function on this? Thanks.
  • 0

#24 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 20 May 2010 - 12:07 AM

Cool tutorial. Can you suggest a way to incorporate a "search" function on this? Thanks.


To search the records? Probably. I'll add that to the list of ideas, and maybe that will make it into a screencast.

As far as the actual code goes, you'd probably want to do something like this (though this isn't something I've done before, so it may require some experimentation):
-- create a search form to get data from the user
-- get the text to search for using $_POST[]
-- search through the database and return all the results that include the search term (using preg_match? http://php.net/manua....preg-match.php)
  • 0
Benjamin Falk
Falken Creative : Twitter

#25 jmb272

jmb272

    Member

  • Member
  • PipPip
  • 20 posts
  • LocationSouth Yorkshire, UK

Posted 20 May 2010 - 09:05 AM

I've recently built a search function into my website.

This works for me.

$criteria = strtoupper($_POST['search_criteria']);
SELECT * FROM `tbl_name` WHERE UPPER(`field_name`) LIKE '%'.mysql_real_escape_string($critera).'%'

I've converted the search criteria and table field values to uppercase to make the query case insensitive.
  • 0

#26 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 20 May 2010 - 11:35 PM

$criteria = strtoupper($_POST['search_criteria']);
SELECT * FROM `tbl_name` WHERE UPPER(`field_name`) LIKE '%'.mysql_real_escape_string($critera).'%'

Good call -- doing the searching within the MySQL query makes a lot more sense than the way I posted above.
  • 0
Benjamin Falk
Falken Creative : Twitter

#27 jmb272

jmb272

    Member

  • Member
  • PipPip
  • 20 posts
  • LocationSouth Yorkshire, UK

Posted 21 May 2010 - 03:57 AM

Good call -- doing the searching within the MySQL query makes a lot more sense than the way I posted above.


Thanks. Yeah it will do, saves you having to fetch all the records and preg_match them. :)

When you have your results, if you want to highlight the criteria in the results you could do something like..

while ($row = @mysql_fetch_array($results, MYSQL_ASSOC))
{
   $field = $row['field_name'];
   $field = str_replace($_POST['search_criteria'], '<font color="#336699">'.$_POST['search_criteria'].'</font>', $field);

  echo $field;
}

  • 0

#28 Guest_jason

Guest_jason
  • Guests

Posted 08 November 2010 - 12:15 PM

Great script. Is there a way to add code that will purge data that is older than 60 days?
  • 0

#29 Ben

Ben

    Administrator

  • Administrators
  • 5,658 posts
  • LocationChico, CA

Posted 08 November 2010 - 12:23 PM

Great script. Is there a way to add code that will purge data that is older than 60 days?

If you have an understanding of PHP and MySQL, that should be possible. You would need to add some sort of "date" column in the database, and then build a page that would select all records with a date over 60 days old and delete them (which is just a standard mysql delete statement with a "where" clause to specify only records over a certain age.)
  • 0
Benjamin Falk
Falken Creative : Twitter

#30 Guest_daydreamer

Guest_daydreamer
  • Guests

Posted 10 November 2010 - 10:27 AM

Hello All,

Just some question here, I'm having doubt about deleting a records from the database, is it possible to add a confirmation function before before delete it. Correct me if I'm wrong, thank in advance.
  • 0




2 user(s) are reading this topic

0 members, 2 guests, 0 anonymous users