Horizontal Subnav w/ CSS & jQuery

MySQL/PHP Search Engine

Welcome to our search engine project! In this project we will go over how to interface html forms with php and mysql to create a searchable database where users will be able to enter entries into the database via a form. We will then implement low level security on the forms to prevent users from entering javascript and also only allowing users with granted access to access the form.

This project will be broken down into sections:
  1. GUI- html forms and css
  2. Database Creation/entry - HTML/SQL
  3. Enter and View the Database - PHP/SQL
  4. Image Handling
  5. The Search Query - PHP/SQL
  6. Basic Security - PHP


-All of the code will be will be avaliable for download FREE here . If you prefer to do this project through video tutorials they can also be found here (insert link to vids).

-This is a basic tutorial of search engines this is in no way optimized and provides a basic implementation of interfacing web languages to create a search engine. Optimized and increased functionality projects are in the process of being developed

-The GUI was designed and built by Thomas Ardnt, PHP/SQL was designed and built by Corey Kozak.

-Great! lets get started! This project will be modeled after This site

Section 1: GUI- html forms and css

This is our easiest section, using basic html and css we can create a search engine gui that is simple, clean, and efficient. Our gui is made of 3 sections, a top section which houses links, the main area which contains the logo and search bar and bottom which will contain the queried results.

Lets take a look at the top section

The top bar is controlled by 2 (really 3) classes.

.wrap class - Everything inside this class will be spanning the entire width of the page (all of the code). Think of this as the parent class everything in the body of the page will be inside this class.

.top class - This is the creation and control of the top bar. Calling this class creates the grey top bar, and whatever text we place inside this class will inheret the text attributes; in this case, the text will be aligned right, underlined, and colored white. We also use padding to give this element some additional space from the other elements on the page. Not necessarily needed for a simple site such as this, but is a best practice as things get more complex.

.top a class - This controls the anchor html tag (used to create links). We will see this implementation in the next step, but we are giving these the anchor tags within the top class these values. This way, if we want to add additional links to the bar we simply drop the links in and they will automatically inheret the attributes.

Next we will incorporate these classes to create the top bar

As you can see the implementation is very simple everything in the GUI will be inside of the master class "wrap" and inside of top we incorporate the top bar in which we have decided to place three links, add more if you wish! or less, thats fine too.

Moving On! That was simple wasn't it?

Our final task is to add a logo and a search bar again this will be controlled with CSS and implemented using simple HTML tags. So lets have a look!

The top bar is controlled by 2 (really 3) classes.

.div,img,form,body,html class - This class acts as a reset, with so many different browsers, screen resolutions, etc things

.form class - elements can have padding, borders, and margins. The margin is the outermost layer which defines the space around the element. We could have a border around the form if we want and a padding tag would determine the distance between the element and the border. Finally, the margin is outside of the border and contains the border, padding, and the element. We do not have a border so instead we simply define some margins for the element.

.label class - simply the class for the text on the button. We have defined it to be a block element similar to the paragraph tag and again we have our padding and our text indented to the left of the button.

.input class - This class controls any input tag we may use, in our case we only use one input field and that is a text input field, however if we would add more text fields or any other kind of input they would all inheret these properties. Take note, the overflow attribute is a stylistic choice on how we want to handle inputs that are larger than the text field. Again choose which ever one you want, I chose hidden because I like this style

Implementation of the above classes

Again, a very simple implementation the "bottom" class is everything below the top bar and the "main" class is the logo and the search bar. Here we incorporate our logo using the img src tag. The form action and input names are very important when we start to code the search functionality. "form action" is what happens when the "search" button is pressed.

Notice we are linking to the page we are on! Why? Because we are going to do our search queries right here and display them on the main page! Input name is important becuase this is essentially the variable name we are giving the input string, this will be used later on the actually preform the search.

And just like that! we have completed our GUI, simple right? like I said earlier, this is the easy and boring part, now lets have some fun and start to build our database so we can preform queries on it.

Section 2: Database Creation/entry - HTML/SQL

If you have not used databases for your website here is where you will get your taste, the SQL database is where we will house all of our user inputs. I have already made a video that goes into detail on how to setup the local host and creating database tables and fields. If you do not know how to setup your database watch the below tutorial, if you do you can continue through the tutorial. Also shown is the database fields we will be using for this project.

Next lets create a way for users to enter data

You should recognize the above code! We are just creating another form. I am not going to go over this in depth again. I will point out the form actions for each of the three forms we have created. as you can see each button is tied to a different action, so if someone enters a new entry then it will navigate to the code to enter that into the database same for the delete entry and upload image.

Section 3: Enter and View the Database - PHP/SQL

Do not get overwhelmed by this, the functions we are using are very simple and the logic is easy to follow. Our goal here is to connect to the database, pull all the entries and fields and display them on the page. So, in order to do this we need to preform the following steps:
  1. Connect to database table
  2. Preform a query to select all entries
  3. Display entries in table format

1. So the first thing we do is connect to the database. We do this by creating a connect variable. We can create variables in php by using "$" followed by the variable name. So on line 143 we are creating a variable that will connect to our xammp server (using the mysqli_connect() function). After connecting to the database we will then connect to the specified table we have created by using the mysqli_select_db() function which accepts two parameters a connection and a database name.

2. Next we will preform a query on the database to get all the entries and fields. We do this by again creating variables that will process our request. Similar to our connection protocol we create a variable again using the syntax $variable name = (Select all rows from the table in order). Note You do not have to select everything (everything being denoted by "*" if you wanted to only select ID then your syntax would be "SELECT ID...". Also, you can order it however you like, or not at all if you wanted to order it in decending order you could use "ORDER BY id DESC". If you would like to know more about this syntax W3 Schools is a great reference

Quickly, lets go over what we have just done. We have bridged a connection to our database and selected the table. Becuase we want to show all the fields and rows, we are using "SELECT *". The final thing we need to do is show the values.

3. We have defined $result to hold the data pulled from the database and we want to show all of its content by using the mysqli_num_rows() function we can determine whether or not there is data being pulled. As long as mysql_num_rows($result) > 0 (there is indeed data being called) then print or "echo" the fields of the entries.

3a. Now that we have entered our conditional to determine whether or not data is being called our next loop will display the fields. We define a variable $row to hold the string of values associated with our call. So now we have a string variable that hold all the information we need so now we just call whatever data we want. In our case everything, we echo out all the data in each entry.

Great! so now we know how to select all (or specific) entries in our table and display them to the page. If we do this now nothing will happen, so our next step is to send our form data to our database to then be viewed.

Do you remember when we created our user input form? I told you to keep track of the name values, method, and the form action? This is where it comes into play. The following code is a file called whatever your form action is targeted to. Lets look at the first part which if you look above is called "meme_in.php".

Some of this code will look familiar, but first we are going to take whatever string is entered into the text box and assign that to a variable. Our method is POST and our variable name is "name" so, using the method we first assign the value of the input string and id to PHP variables.

Next is the code we have seen, connect to the database and specified table, then run a query to select all the fields in the entries. However, here we have an additional qualifier. We want to make sure the entry ID is unique, in more advanced projects we will automate this, but this is a good learning expierence. Using SQL we select the "ID" field from the database and compare it to the "ID" entered by the user. Simply, if the ID entered already exists then echo an error, if not then we want to store our entry.

So how do we store our entry? Again, look at the form, all the text entry fields have unique names, using the POST methods we select these values and store them into the database. It is VERY important that you pull and send the data in the correct order (the same as the structure of your table).

Finally, we can have a simple check to make sure the data is entered by using the final conditional statements.

Section 4: Image Handling

W3 Schools has a very good tutorial for uploading images to your site using PHP. I essentially used their code for this project. Please check them out they are a great resource.

Section 5: The Search Query - PHP/SQL

Hey! Look at that we've nearly finished our search engine. Lets go over what we've done so far. First we created our GUI that was a simple form, logo, and a few links using css we were able to format it so that it is seamless across all browsers and operating systems. We next built our user input forms and used some basic PHP connect code to pull all the entries from our database. With the help of W3 schools we saw how to handle image input and some best practices when using images and SQL databases. Now all that is left is our search algorithm. This section is arguably the most difficult, that is, if we wanted to create a more advanced search function. For the purposes of this project, we will keep it simple. So lets see how we do this.

Again, this code should look very familiar to the previous code we have created! Do you see the pattern yet? Connect db and select table -> run some sql queries -> output data. Simple right! Quickly running though the first few lines. Connect to the database and select our table.

So now here is where the (somewhat) new code comes into play. We are passing the string entered through the function mysql_real_escape_string() this function simply adds an escape character (a backslash "/") before the string is passed to the SQL server, this acts as front end protection to our server (we will talk more about security in the next section).

Next, is a SQL query that is mostly review. We want to select all the rows from the database, but in this case we want to use the string the user entered as a search parameter. In SQL the "LIKE" command will find patterns within the specified column. By using encapsulation (placing the searchable string inside %% %$clean%) we are telling the database to look for ALL patterns either inside of a word or stand alone. As I mentioned earlier, our search algorithm is very basic and by using this we will show many more search results than you might want; however, in this case its better for us to pull more results than to miss results.

And finally, we through this query through a mysql_num_rows() function to say that as long as we keep finding entries that meet the search requirement then display the search in table format.

Section 6: Security

So, I am not going to even pretend that I know how to confidently protect a website, but I know a few things that will make your site more secure. You should not use these methods if you want to protect credit cards or personal user data, but if its something simple like this you should be fine. So, there are three "security" protocols we can implement here.

The first is passing all of the user inputs through mysql_real_escape_string(), I forgot to do this in the user input forms but based on the example I gave you should be able to incorporate this level of security into your forms. Again, all this will do is add a layer of protection for your database to prevent potentially dangerous characters in a string passed in to the function. Specifically, the prevented characters are \x00, \n, \r, \, ', " and \x1a.

The next thing we can do to protect our forms is to prevent special characters (and even specific ones) from being entered into forms. Again, I did not implement this into our project because I felt it was not needed but the following functions and implementation gives you an example of how to do this. (jscript security pic)

To be clear, the way to implement this is to create a function that looks at the input string. We have not talked about creating functions but here is some simple code that can help you learn to create and use functions in php. This acts as an intermediate step in processing our search query or form input. The string is passed through the function, which will check and make sure nothing suspicious is being entered then pass it to the database. Just an extra layer of security that can help prevent those pesky hackers.

I would say our best security is this layer. So, this was added after I made this project, but it will be incorporated into the project files. We are going to password protect the new entry form. We can do this very simply in php Once the new password was entered correctly, the user will be redirected to the new form input page. Here is where the security lies, this input page does not lie in the directory of the rest of our website i.e if you were actually building this, this form would NOT lie in public_html. Which means that the only way to get to this webpage is by entering the correct password and having the logic bring you to the page. This incorporated with restricting the use of special characters should make your site (this project) secure enough. Again, dont save credit card information or personal user data this way. If you need to do that, get a professional to do that.

Section 7: Conclusion

LOOK INTERNET WE DID IT! (picture of something silly). Hopefully, you have understood and learned something from this project. Keep in mind this is meant for a beginner web developer, in the future we will create better search algorithms better security, etc but for now this will do. If you have any comments or suggestions feel free to email us (here)