Jump to content
Killersites Community
falkencreative

Basic Php System: View/edit/delete/add Records

Recommended Posts

Check out our new Interactive Web Developer course (created in 2016) that covers this, and much more: shop.killervideostore.com

 

(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");

}



?>
  • Upvote 1

Share this post


Link to post
Share on other sites

Nice work. This should help those wishing to learn some php. It is also a good start to the Admin side of small applications.

Share this post


Link to post
Share on other sites
Guest mynameiscoffey

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/mysqlTutorial/mysql-php-sql-injection.php

Edited by mynameiscoffey

Share this post


Link to post
Share on other sites
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().

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

The reason why my field was empty after using

$message = mysql_real_escape_string($_POST['message']);

was because all my variables were after the <?php tag but before the mysql_connect codes; as soon as I put the variable after the mysql_connect codes like you have, the field was filled with data.

 

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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:-

".stripslashes($row['club'])."

 

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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/od/mysqlcommands/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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Guest cjmiles

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

Share this post


Link to post
Share on other sites

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/manual/en/function.preg-match.php)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

$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.

Share this post


Link to post
Share on other sites

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;
}

Share this post


Link to post
Share on other sites
Guest jason

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

Share this post


Link to post
Share on other sites

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.)

Share this post


Link to post
Share on other sites
Guest daydreamer

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×