Creating search function for website, confusion about MySQL

PHP
1

Hi all,

I’ve recently created a travel guide site using most hard coded HTML and CSS, and would like to implement a search function to search for city and country. I understand that I would first need to set up the Mysql DB, and as a newbie, I’m not sure how to do that/where to start from. I’ve watched YouTube videos about setting up MySQL but I don’t seem to get it.

This is currently how my files are organized in Public HTML. Would appreciate if someone could advise this beginner on what are the first steps I’ll need to do/create the search function?

2

  • Get some hosting that allows you to create MySQL databases.

  • Preferably host will provide a cpanel interface that gives access to a MySQL admin tool such as PHPMyAdmin.

  • Use PHPMyAdmin to create a new database.

  • In that database create a new Table with a unique name, such as Locations, which will contain your data, i.e. city and country names. Each row will have unique location id, city name, country name.

  • Populate your Locations table with data. Either import from an existing file or populate by manual input via web tool, or create an administration interface using PHP coding.

  • Write a PHP script that connects to the database and can search it. You need to know basic PHP and how to query MySQL via SQL statements to be able to do this.

I hope that helps.

3

recommendation: the PDO interface is more powerful and easier to use than the mySQLi one for accessing mySQL databases from PHP.

4

Thanks for the breakdown of steps, exactly what I was looking for.

About adding internal links to MySQL, if I want the result to link to a page in the site, do I then insert the link value as .php? or would it be "/.php?

Thanks!

5

Alternatively, you could add a search function to your website using Zoom Indexer

6

I think what you need to do is look for some tutorials about coding basic database functionality with PHP, i.e. CRUD functionality, create read, update, delete. YouTube may have some good tutorials.

You write a script, let’s say it’s called show-cities.php. This script will use PHP to connect to MySQL and run a query to retrieve data. Often you will pass the script a value via a querystring, e.g.

/show-cities.php?CityId=11

or

/show-cities.php?CityName=Copenhagen

You can then get the value from the querystring and use it in your MySQL query to retrieve the data related to that City from your database.

Rgds

7

I know the website sucks but perhaps you would be better served using sphider-plus rather than creating your own search from the ground-up. Sphider plus is fairly basic and easy to set-up. Its really not my favorite solution and the code quality leaves a lot to be desired but it is a simple solution to the problem. I would really prefer to use a dedicated search like sphinx, solr, elastic but those can be needlessly complex to set-up and for a simple, static site aren’t worth the work involved. Also those solutions won’t be realistic on shared hosting plans considering they require installing things on the server.

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.