Making a Directory

[ronnyd8719r]

Hi
I am new to php. I have been reading and learning php commands and also mysql commands. I have the book PHP and Mysql by Visual
Your visual blueprint for creating dynamic, database-driven web sites.
I am learning,but I don't see what I would like to do.
I would like to have a database with States, Citys, Businesses. The businesses will have the address and phone number to go with each. I have a database with all the states, citys and buisnesses . I have each state in its own table. Then I put the businesses names as a primary key with the business name in one column, the address in a column, the description in one column and phone number in a column and the zip code in the last column. The columns are listed like this.
Business name, City, address, zip code, description

Then the table is the State name.

I would like to have a webpage that the visitor will have a form to do a search by State and city. It would take you to template that would put the Business name in the template in a marked area, then the address in a marked area, and phone it one area. The visitors would not see the marked areas. They would see a new page come up with the list of business in the city they choose. Each business would have its information put in a webpage from the database.
I hope I explained well.
I need to know if this is possible are would it be better to do all of it in html and build 100's of 1000's of pages.
Thanks for any help you can give me.
Also do you suggest me purchasing phpbuilder to help me with the project.

[impactdesignpro]

I would recommend instead of having 50 tables each with its own state name. Do one table and have one of the fields as state. On your form you can put a drop down menu with each of the of the values predefined (all 50 states). When they submit the form the data will be entered into the field called "state".
Then if you have a seach feature on your site you confine the search to one table/one field.

Does this help?

[ronnyd8719r]

Thanks for the the reply. How would I do that. I am new. I don't understand. Also can I build a directory like I want.

[impactdesignpro]

Ok - I have taken the liberty to write a quick mysql create file.

Copy all the code and save in one file as "setup.php" and remember to delete it after successful set up.
First part of the code is to connect to your mySQL.

<?php
$con = mysql_connect("localhost","user_name","password"); // change "localhost","user_name","password" to match your mysql requirements
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

Now the first thing we need to do is create a database.

// Create database
if (mysql_query("CREATE DATABASE business",$con))
  {
  echo "business Database has been created<br>Remove files: setup.php from your directory";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

Now that we created a database titled "business", we need to now create a table to enter our fields.
our table will be called "contact"

  // Create table in my_db database
mysql_select_db("business", $con);
$sql ="CREATE TABLE `contact` 

Now we have our databse "Business" and we have created a table called "contact" we need to create the fields that will be inserted in the table."

( 
`id` int(4) NOT NULL auto_increment,
`b_name` varchar(40) NOT NULL default '',
`b_address` varchar(40) NOT NULL default '',
`b_phone` varchar(20) NOT NULL default '',
`b_city` varchar(20) NOT NULL default '',
`b_state` varchar(15) NOT NULL default '',
`b_zip` varchar(12) NOT NULL default '',
PRIMARY KEY (`id`))";
mysql_query($sql,$con);
mysql_close($con);
?> 

Does this help? You can add more fields like: website address, email address, etc.

Now when someone does a search - the query is confined to one table "contact" and can be limited to "city","state"

[ronnyd8719r]

Thanks impactdesignpro

I have my database all setup for two states. I did two so I don't waste time if I can't do this with php. I need to know how to get the information from my database to the marked spots in the web page. I will have a table made and I would like to put code in the table that would go and get the address, phone number, and URL, Description.

Can I do this with PHP and would php builder help me??
Do I need phpbuilder to do this??

Thanks Ron

[impactdesignpro]

The problem that I see with creating a table for every state is that you will have to duplicate all the fields for each state (business name, phone, address, city... etc.) Then displaying the data will be quite difficult because you would have to query each table and so if you have 50 tables your query will be quite lengthy. Not only that but posting the data will be quite cumbersome because you are trying to communicate with 50 tables.

Your select script would read something like this...
$table1="Alaska";
$table2="Alabama"
$table3="Arkansas"
and so on....(all 50 states)

$sql="SELECT * FROM $table1, $table2, $table3 ";

Personally, I think it would be to much scripting. Keep it simple and you'll have less chance for errors.

Now you asked how to display your data...

To create a display page that will display all the data in the fields of your table edit this script in your html editor and save as display.php

you can tweek it later and add a search query

<?php 
$host="localhost"; // Host name 
$username=" "; // Mysql username 
$password=" "; // Mysql password 
$db_name=" "; // Database name 
$tbl_name=" "; // Table name

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);

?>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Display Page</title>
</head>

<body>
<table width="100%" border="1" cellspacing="0" cellpadding="0">
  <tr>
    <td width="20%"><div align="right"><strong>Business Name:</strong></div></td>
    <td width="80%"><? echo $rows['field1']; // change field1  to match the field that contains the businesses name ?></td>
  </tr>
  <tr>
    <td><div align="right"><strong>Phone:</strong></div></td>
    <td><? echo $rows['field2']; // change field2  to match the field that contains the businesses phone ?></td>
  </tr>
  <tr>
    <td><div align="right"><strong>Address:</strong></div></td>
    <td><? echo $rows['field3'];// change field3  to match the field that contains the businesses address ?></td>
  </tr>
  <tr>
    <td><div align="right"><strong>City:</strong></div></td>
    <td><? echo $rows['field4'];// change field4  to match the field that contains the businesses City ?></td>
  </tr>
  <tr>
    <td><div align="right"><strong>State:</strong></div></td>
    <td><? echo $rows['field5']; // change field5  to match the field that contains the businesses state ?></td>
  </tr>
  <tr>
    <td><div align="right"><strong>Zip:</strong></div></td>
    <td><? echo $rows['field6']; // change field6  to match the field that contains the businesses zip  ?></td>
  </tr>
</table><?php
mysql_close();
?>
</body>
</html>

You'll notice where it say's <? echo $rows['field5']; ?> this code displays the data that is in this field. It calls for the data and inputs the data into your html table. You can make your own html table and insert the "echo command" where you need them.

[ronnyd8719r]

Thanks so much. I was making it hard becuase I didn't know it was that easy to call up data from my database.
Thanks Ron

[ronnyd8719r]

What I want to do is the first page will have a form and search for the state they would like. From That they will do a search for city. The a list of all the business in the city will come you and they will choose the business they want to look into and There will be a web page for that business with all the information.
Thanks alot I did not under stand how to start getting data and it is so easy.

[impactdesignpro]

Because you confine all your data to one table you can search by multiple fields. For example let's say you have a search form called "search.php" this form would execute a search query on your database.

Lets say I'm on your site and I input in your search fields "Florida" - the php script would search your database -> table->field (you would assign the field to match state entries) for any matches for florida. If there are any matches it will output all the data entries for Florida regardless of city or have it display a statment that say's "Sorry no matches"
Let's say I want to narrow my search to a particular city... So I enter in the "State" field - Flordia, and in the "City" field - Boynton Beach. Now the php script will search for matches in two fields - "State", "City".

The results from your search will be displayed on the display page.
You can also hyperlink the "name" of the business to open up another page (let's call this page - "business.php" or whatever you want to call it) which would have additional information or you can add a [more info] link next to the business name.

You would place this code in your html table.

<a href="business.php?id=<? echo $rows['id']; ?>" >[More Info]</a>

in the business.php file you would need the following code:

<?php
include("includes/config.inc.php");

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar
$id=$_GET['id'];


// Retrieve data from database 
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>

Remember in the design phase - make a field in your table for each section of data that you wish to compile.

Hope this makes sense.

[impactdesignpro]

Also you can have the first page "search.php" with just the state they want to search. It will execute another page called "search_city.php" this will display all the cities located on in your table field "city" that match under the search for State.

You can assign a radio button that people can select for a specific city, when they click on the sumbit button it will execute another page (we'll call that page "search_business.php" that will display all the businesses located in that city.
You can also do this with types of businesses (Stores, Travel, etc.)

[ronnyd8719r]

Thanks you have been a very big help. I hope get to know the code as well as you one day.