PHP script to query Mysql database from url

[jamjam]

Hello

 

I am new to the world of creating dynamic web pages using PHP and Mysql.

I want to create a new web site.

I could just create this website, the good old way using a html editor and upload it to a webserver.

Because this website will contain a huge amount of pages and it would be impossible to maintain using static html,

such approach will also make it difficult to do regular updates.

I want to store the sites information on a database, hence why I am exploring the dynamic route.

Unlike most dynamic websites.

I do not want the sites visitors to fill in forms, select drop boxes or anything like that to be able to access the database.

 

Suppose my site was a travel-site that display’s information to visitors when they click a city link. Like his.

 

http://www.travel.com/london

http://www.travel.com/paris

http://www.travel.com/tokyo

 

Using static html I would simply upload a page for these cities to the correct location and that’s it.

I want to achieve the same thing dynamically with the cities information been retrieved from the database depending on which link was clicked.

So basically the PHP code should query and return a result from the Mysql database using the city name in the url.

 

Is this possible? and if so how would I go about achieving this.

If anyone can provide a PHP script that I can adapt that would be awesome.

 

Thanks

 

[Muddy_Funster]

It's very possible.  And, given a few days and a couple hundred green backs I'd post you your code up   Seriously though - if you are going to administer the site you really need to get your hands dirty and build it yourself.  It is the best way to get to know what's going on and learn what you will need to fix anything that breaks or change/expand the site when need or desire come along.  By all meens we will help when you get stuck, or want to try something tricky and want advice, but you sould have a go at the ground work on your own.  There are lots of resources out there that can teach you php and SQL some in conjunction with each other and some not.  If there is some specific aspect of the progect that you are looking for help with ask away.

[jamjam]

Thanks for the quick reply

 

I am currently studing PHP and Mysql basics. I am confident of administering the site when it is up running.

 

The reason I asked this question is that. It is critical to the workings of my site.

 

I do not want my visitors to fill any forms or select check boxes. I just need them to click links.

 

I can't find a reference to this solution so I thought I ask you guys.

 

Please give more pointers into how a such solution would work.

 

I will really Appreciate it.

 

Thanks 

[Muddy_Funster]

Effectivly the

include "";

statement will be your bigest friend.  You will have a top page that will query your database for formating information.  this will simply be raw html stored in a text field in your database.  You will then echo this out.  when you echo raw html it's just the same as if you coded the page directly.  Within the formating you can then use the include "content1.php"; to auto load the content of each formated area.  This will be a php page that runs another query on your database and echos out the contents for that format section be it a table, heading, frame or even an image.  All the information that you would normaly type into the flat html pages can be entered into the database and called back using a query in any php page at any point when building the end page. Structure your database and index.php correctly and you will even be able to seamlesly add new pages with only having to enter the content that it will display.  Something you may want to note though - you will still need to use forms for your input if you are planning on entering data through php instead of directly into the database.

[jamjam]

Thanks Muddy_Funster

 

Although I don't understand everything you said as I am so new to PHP.

 

I now know that my original request is possible.

 

So I am going to study hard to figure how to implement your solution.

 

I will be back if I get stuck though.

 

Thanks again

 

[Muddy_Funster]

You are very welcome and best of luck

[siric]

I would recommend using a Content Management System (CMS).  This will give you the framework and you can build your own pages, etc to suit.  Joomla is good but in my option a bit too complex esp. to the beginner.  I use PHP Fusion (www.php-fusion.co.uk).  Simple to learn and very flexible.

 

 

[jamjam]

Thanks for the recommendation.

 

But I don't think I will be using a CMS like the ones you suggested.

 

I plan to use Dreamweaver CS4 to maintain the parts of the website that are static html and

 

once I have figured out a solution to my problem I will update the database using MySQL Workbench.

 

Do think this is good approach?

[Muddy_Funster]

Purely personal point of view here but MySQL Workbench blows.  If you have direct access to the database server and you want to use something that is gui I would say to try Navicat Lite.  Although you could always code some input pages just for your own use.

[jamjam]

Noted

 

I have been comparing Workbench and Navicat.

 

I was leaning towards Workbench as its community supported and free application but you as you mentionend Navicat.

I shall consider that again.

 

 

[Muddy_Funster]

Navicat lite is a free cut down version of Navicat for MySQL.  You don't get the bells and whistles, but you still get enough functionality to manipulate the data in the database.  So long as you have a rough grasp of SQL (mainly in table and index creation) it should do what you need it to.

[ignace]

You'll need a .htaccess with Rewrite rules to create (search (or ask) the Apache forum)

 

http://www.travel.com/london
http://www.travel.com/paris
http://www.travel.com/tokyo

 

This will pass london/paris/tokyo to a pre-defined script (eg city.php?name=london) Query the DB and display the result.

[ignace]

this will simply be raw html stored in a text field in your database

 

Store HTML in a database? Your data should be format-independent. Your entire explanation makes me wonder if you have ever heard of a template (engine)?

 

@siric I have seen websites that deployed a CMS system where they should have clearly used a tailored solution eg http://www.mijn-recepten.be/hoofdgerechten - great if you are looking for a recipe that starts with the letter K

[Muddy_Funster]

@ignace: my reasoning for storing the html in the database was to enable even the format to be dynamic for each page and not restricted to a fixed template...

[jamjam]

 

The page that displays that city information will be have the same format for all cities.

 

So a  basic html template will be ideal for the solution.

 

Muddy_Funster I am interested to know how this will change your original solution.

 

 

 

[Muddy_Funster]

Not at all really, it's just one less thing you will need to put in the database.  Run the php through a template file for formating rather than calling the format from the database.

[ignace]

@ignace: my reasoning for storing the html in the database was to enable even the format to be dynamic for each page and not restricted to a fixed template...

 

That technique has some disadvantages like:

 

1) Before you are able to edit the data you need to strip all HTML, and afterwards again apply the template before you insert it into the database. Which means that you have each template twice (once on file, and once in your DB).

2) If the user wants to export the data to some different format (eg PDF, XML) all data has to be stripped prior to the PDF generation. Which makes your data inflexible.

 

Basically, your technique renders your DB completely useless and you would have been better off if you would have just when the user submits his data, apply the template on the data and store it into a .html file instead of passing through the DB (re-doing the entire process every time the user edits the page data).

 

A setup like eg

 

templates (id, name, path)
contents (id, template_id, title, summary, ..)

 

would have been more beneficial.

[jamjam]

OMG!! I'm totally confused now.

 

Muddy_Funster, ignace

 

I currently have developer environment set up on my local pc.

 

Using the http://localhost/cityname as in example.

 

Will one you guys create a php script combined with a basic html template.

 

I don't mean to bother you with this kind of request, but I now think it will take me from ever to find my own solution .

 

I will be enternal grateful.

 

[ignace]

I'm secretly hijacking your thread

 

Will one you guys create a php script combined with a basic html template

 

Your .htaccess should something like:

 

Options +FollowSymlinks
RewriteEngine On
RewriteRule (.*) city.php?name=$1

 

Your PHP would then be something like:

 

if (empty($_GET['name'])) {
  header('Location: ..');
  exit(0);
}

function filterCityName($cityName) {
  return preg_replace('/[^a-z]/i', '', $cityName);
}

$name = filterCityName($_GET['name']);
$query = "SELECT .. FROM cities WHERE name = '$name'";
$result = mysql_query($query);
if (false === $result) {
  trigger_error("Query '$query' failed and returned " . mysql_error());
  header('Location: ..');
  exit(0);
}

$row_count = mysql_num_rows($result);
if (0 === $row_count) {
  //unknown city name provided
  //what should be the procedure?
}

//what should be displayed?

[jamjam]

Hey ignace. Hijack all you want. I encourage it.

 

Your guys have been great help

 

I will now look and see if can implement that sloution.

 

Will report back.

 

Properly to ask even more questions.

 

Thanks again