Select data from MySQL DB based on the URL

[Tom926]

I am very new to php and I am looking to create dynamic pages.

In short - I need dynamic pages that has echo tags in the content that change depending on what page you're on.

Basically If someone lands on www.example.co.uk/essex.php I would like to be able to add an echo tag like <h1>Company In <?php echo $row['County']; ?> onto the page so that it would show as Company In Essex when Live and save time if I need to duplicate the page for another county or town.

So far I have setup a database in phpmyadmin with a table of every town and county in the UK

Picture of Table

My code so far to grab the table is;

$dbconnect = mysqli_connect("HOST", "USER", "PASSWORD", "DB");
if(mysqli_connect_errno()) {
echo "Connection Failed:".mysqli_connect_error();
exit;
}

$count_sql="SELECT * FROM areas";
$count_query=mysqli_query($dbconnect, $count_sql);
$count_rs=mysqli_fetch_assoc($count_query);

 

and then on /essex.php I have added the follow code 

<?php
do {
echo $count_rs['County'];
} while ($count_rs=mysqli_fetch_assoc($count_query))

?>

But where I have gone wrong is it pulls all the data from the table for County where I only wish it to pull the county of Essex at the momenent but will need code so that it changes based on what page you are on.

Please forgive me If I have explained this badly.

[benanamen]

You don't need a separate page for each County. You need to use a WHERE clause and GET with the ID and then foreach over the results.

 

SELECT column_name FROM table WHERE id = $_GET['id']

 

mydomain.com/county.php?id=2

 

You should also think about using PDO and your database could use a little bit of normalization. Additionally, do not output internal system errors to the user. The information is only good to hackers.

Edited July 11, 2017 by benanamen

[ginerjm]

And don't use $_GET['id'] in your where clause without first ensuring that there is no sql injection going on.

[Jacques1]

There are several fundamental problems with your approach, and I'm not sure if this is a good project for somebody who has neither worked with PHP nor SQL before.

 

First, you're storing plenty of redundant data. The information that Bedfordshire is in England is repeated over and over again, which leads to anomalies and is a waste a space. This may not look like a big problem right now, but when you start adding more data and more complex relations, it will quickly turn into an unfixable mess.

 

So before you do anything, you must understand the basics of database design, especially the concept of normalization. However, it's not even clear what kind of information you're trying to store. So Bedford is in Bedfordshire, and Bedfordshire is in England. Now what? Where is the information which is actually useful?

 

Then you appareantly keep duplicating the same PHP script for each county. This is, again, a fundamental design flaw which will quickly turn the project into a nightmare. And it really misses the point of a dynamic website.

 

Like I said, this task seems a bit too ambitious for your current level of knowledge. Why not start with a more realistic project?

[Tom926]

This is for an Adwords nationwide website hence why I need every town and county in the UK In the database. In past projects I have done this before but used code that is now deprecated which is why I was trying to rewrite it.

 

******************************

 

$row = url2content();

extract($row);

 

/**

* @return array return an associative arrays of all fields from a database table based on the current URL:

* array(

* 'field1_name' => 'field1_value',

* 'field2_name' => 'field2_value', 

* ........

* );

*/

function url2content($url = NULL){

if(is_null($url)){

if(isset($_SERVER["REQUEST_URI"]) && $_SERVER["REQUEST_URI"]){

$url = $_SERVER["REQUEST_URI"];

}elseif(isset($_SERVER["PATH_INFO"]) && $_SERVER["PATH_INFO"]){

$url = $_SERVER["PATH_INFO"];

}

if (substr($url,0,1) == '/'){

$url = substr($url,1);

}

}

if (DEBUG_MODE){

echo ('URL requested: "'.$url.'"<br>');

}

 

$conn = mysql_pconnect(MYSQL_HOST,MYSQL_USER,MYSQL_PASS);

if (!$conn) {

printf("Unable to connect to mysql: %s\n", mysql_error());

exit();

}

$res = mysql_select_db(MYSQL_DB,$conn);

if (!$res) {

printf("Unable to select ".MYSQL_DB." database: %s\n", mysql_error());

exit();

}

 

$res = mysql_query("select * from ".MYSQL_TABLE." where ".URL_FIELD_NAME." = '".$url."'");

if(!$res){

printf("Unable to query ".MYSQL_TABLE." table: %s \n", mysql_error());

exit();

}

if(mysql_affected_rows($conn))

$return = mysql_fetch_assoc($res);

else

$return =array();

 

if (DEBUG_MODE){

if( $return != array())

echo ('Matched row: '.print_r($return,1).'<br>');

else

echo ('No matched rows found <br>');

}

return $return;

} 

 

 

/**

* @param string county 

* @return string a list of all towns in a county wrapped by <li>...</li> in a random order

*/

 

function get_towns($county){

$query = sprintf("select townName from " . AREAS_TABLE . " where countyName = '%s'  order by rand() LIMIT 0,24" ,mysql_real_escape_string($county));

$res = mysql_query($query);

if(!$res){

printf("Unable to query ".AREAS_TABLE." table: %s \n", mysql_error());

}

$html="";

while($row = mysql_fetch_assoc($res)){

$html.="<li>".stripslashes($row["townName"])."</li>\n";

}

return $html;

}

 

/**

* @param string table name

* @return string a random testimonial from a specified table 

*/

function get_random_testimonial($table_name){

$query = "select testimonial from $table_name order by rand()";

$res =  mysql_query($query);

if(!$res){

printf("Unable to query ".$table_name." table: %s \n", mysql_error());

}

$row = mysql_fetch_assoc($res);

if(isset($row["testimonial"])){

return stripslashes($row["testimonial"]);

}

}

?>

 

 

On the php pages I would have tags like 

 

<?php echo $row["TownName"]; ?>

<?php echo $row["PostCode"]; ?>

<?php echo $row["PhoneNumber"]; ?>

<?php echo $row["County"]; ?>

<?php echo get_towns($County) ?> 

 

within content.

 

I am very bad at explaining things... 

[Jacques1]

Use code tags. This is the second time you post a wall of unformatted code, and it's getting rather annoying.

 

In any case, everything I said about database design still applies. You should actually repair your database before you start writing code.

[Tom926]

Always one dickhead

[Jacques1]

Is this a request for a ban?

[Tom926]

Do what you want I came here for help and all you seem do on this forum is slate peoples questions 

[Jacques1]

I gave you a very clear answer. If you were actually interested in finishing this project, then you would roll up your sleeves and get busy.

 

But obviously you prefer to avoid work. Instead, you whine and complain for no reason and insult those who helped you. You can do that. But it makes you look like a complete idiot and demonstrates your fundamental inability to get a job done.

 

To be honest, however, I expected something like this. Your whole story sounded fishy right from the beginning. You clearly don't know anything about programming or databases, yet at the same time you're writing complex commercial applications. This just doesn't add up.

[Moorcam]

There is certainly always 1, which is yourself mate. You ask for help and when someone tries to guide you in the right direction, you ignore and abuse.

Best of luck with your project. You will need it.