≡ Menu

How to create a simple dynamic php/mysql web page

I am always creating simple php websites that get data from a mysql db. The first step is to set up the db. You can do this in your control panel. Once you do this make a note of the db info. This will be db name, db password. Next you need to go into mysql and create a table. I normally do this with phpmyadmin. Most web hosts provide this for you.

Next you need to create a config.php file where you store the connection information. You will include this on any pages that access the db later. Most people stick this in a includes sub directory.

#most of the time this is localhost but some hosts like pair.com will assign you a db server name
$db_server ="localhost";
$db_name = "name_of_db";
$username = "user_name";
$password = "password";
$dbh = @mysql_connect($db_server,$username,$password) or die ("Connection failed."); $db = @mysql_select_db($db_name) or die ("Connection made. But database '$db_name' was not found.");
?>

Every page that you want to access the db from put this line of code at the top

Now you need to create a page. There are different types of pages. Some pages will show a list that comes from a db search and others that will show just one record. First lets create a page that creates a list of records from the db. Each one of the items in single quotes can also be a php variable. You can get this from something declared before on the page or you can get it from a var passed from the url. When you pass a variable you access by using $val = $_GET['word']; . If you have the URL http://www.example.com/page.php?myvar=test Then $val will contain “test”.

Create a sql search to get your data. What this does is pull the data from the db.

$query = "SELECT count(*) FROM `table_name` WHERE Row_Name = 'some_text_your_looking_for' ORDER BY Row_Name";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
?>

Create a loop to display the data.

while ($row = mysql_fetch_assoc($result)) {
echo $row['Row1_Name'];
echo "
";
echo $row['Row2_Name'];

#you can also create links
echo 'Anchor Text';
}
?>

Next you can create a page that returns the data from a specific record. If you create a link like above you can create a page that uses the variable set in the url.

$query = "SELECT * FROM Table_Name WHERE Row_Name = 'search_text' ";
$result=mysql_query($query);
$row=mysql_fetch_assoc($result);
echo $row['Row1_Name'];
echo $row['Row2_Name'];
?>

This is a very simple tutorial you may need a lot more depending on your needs. Sometimes you might have to search the db for data that does not exactly match. When this happens you can use the wildcard %.

SELECT * FROM Table_Name WHERE DISTNAME LIKE '%ISD'

What this does is return all the rows that end in ISD. You can also use AND and OR like if you want to pull city and state. You would have to do that because a city name is not unique to a state there would be several rows with the same city but with different states.

{ 0 comments… add one }

Leave a Comment