• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

How to read URL variable from URL to MYSQL Statement

I have a page that displays a query from MYSQL.
I would like the Query to read
SELECT * FROM......
WHERE city= (The URL STRING)

For example the page is http://www.mydomain.com/index.php?city=hollywood

What is the correct syntax to have the MYSQL on that page read from the URL variable to filter the data?
0
colonelblue
Asked:
colonelblue
  • 4
  • 3
  • 3
  • +2
2 Solutions
 
zasadCommented:
I'am not used to PHP so forgive me if i am wrong :)

"SELECT * FROM......WHERE city= ".$_GET["city"];
0
 
hernst42Commented:
From http://www.php.net/mysql_fetch_assoc

if you use variable from _GET _POST _COKKIE or _REQUEST always use mysql_real_escape_string to prevent SQL injections!
$conn = mysql_connect("localhost", "mysql_user", "mysql_password");

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("mydbname")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}

$sql = "SELECT * FROM......
WHERE city=" . mysql_real_escape_string($_GET['city']);

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//       then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
    var_dump($row);
}

mysql_free_result($result);

Open in new window

0
 
hernst42Commented:
Ups. as city is a string it need to put into quotes. So use theis line:
$sql = "SELECT * FROM...... WHERE city='" . mysql_real_escape_string($_GET['city']) . "'";
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
haijeromeCommented:
Hi its pretty simple buddy...

 Kindly see the code below :
$city = mysql_real_escape_string($_GET['city']);
 
$city = htmlspecialchars(stripslashes($city));
$badWords = array("/delete/i", "/update/i","/union/i","/insert/i","/drop/i","/http/i","/--/i");
$city = preg_replace($badWords, "", $city);
$city = str_ireplace("script", "blocked", $city);
$city = mysql_real_escape_string($city);
        


$query =    "SELECT *
             FROM yourtablename
             WHERE city = '".$city."'"; 
 $result =  mysql_query($query,$connectionvariable) or die("Mysql Error ");

  while($rowset = mysql_fetch_array($result))
  {
    echo "Field1 : ".$rowset['field1'];
    
    /* Give your actual field / column names instead of field1*/

  }

Open in new window

0
 
haijeromeCommented:
In the above i provided maximum functions to prevent SQL injectoin and also a better way to extract data from MySQL db as per your filter...

But i recommend you to use MySQL i / PDO For good OO paradigms..

If you need it i will send you the sample code to access data using mySQLi and PDO ...
0
 
Ray PaseurCommented:
With this URL...
 http://www.mydomain.com/index.php?city=hollywood

You can see the contents of the URL arguments by using this code:
var_dump($_GET);

And you will find the word, "hollywood" in the variable named $_GET["city"].

To be safe about handling external input, you need to filter the information before using it in your code and escape it before using it in your data base queries.

Here is a teaching example that shows how to do the basics for your question.

Best regards, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$safe_city = mysql_real_escape_string($_GET["city"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE city='$safe_city'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
   echo "<br/>QUERY FOUND NO DATA: ";
   echo "<br/>$sql <br/>";
}
else
{
   echo "<br/>QUERY FOUND $num ROWS OF DATA ";
   echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
   // MAN PAGE: http://us2.php.net/manual/en/function.var-dump.php
   var_dump($row);
}

Open in new window

0
 
colonelblueAuthor Commented:
You guys are amazing and I appreciate the heads up for prevention of attacks.


Questions: haijerome

I placed them under the selected code for clarification.


Ray: Thank you. Daunting nonetheless, so much of it looks cryptic to me.
Again so that I may learn.


var_dump($_GET);
^^ Do I just declare that in the php or do I need to add to it? Is that like a session of sorts?
And you will find the word, "hollywood" in the variable named $_GET["city"].
^^ and then presumably just add
$_GET["city" to the WHERE clause, is that right?
Just trying to ascertain.

Again, my sincere gratitude.


And thank s to hernst42 and zasad.

$city = mysql_real_escape_string($_GET['city']);
 
$city = htmlspecialchars(stripslashes($city));
$badWords = array("/delete/i", "/update/i","/union/i","/insert/i","/drop/i","/http/i","/--/i");
$city = preg_replace($badWords, "", $city);
$city = str_ireplace("script", "blocked", $city);
$city = mysql_real_escape_string($city);
        
^^ For more than one variable , is there a way to loop that or should I just copy for each variable?



 $result =  mysql_query($query,$connectionvariable) or die("Mysql Error ");

  while($rowset = mysql_fetch_array($result))
  {
    echo "Field1 : ".$rowset['field1'];
    
    /* Give your actual field / column names instead of field1*/

^^ For the purpose of learning would you be so kind to demystify the above?
I was able to run the query just using the variable declarations and the addition of the WHERE clause to the SQL statement.

Open in new window

0
 
Ray PaseurCommented:
You're in luck, all of the PHP functions are described on the PHP web site.  Please read this page:
http://us.php.net/manual/en/function.var-dump.php

Next, before you try to write any more code, go buy this book:
http://www.sitepoint.com/books/phpmysql4/
Work through the examples in there and you will be light years ahead in your knowledge of PHP and MySQL.

Without too much modification, you should be able to install and run the code I posted above.  You will note that it DOES NOT add the $_GET element to the WHERE clause.  Please see line 42 and line 49.  If you do not understand what a PHP function does, please feel free to look it up in the online man pages and post questions here about what you read.

You might also want to look around for a PHP user group or a nearby colllege that teaches PHP classes.  Almost any learning opportunity would get you jump-started, and then you will be better equipped to begin asking questions on EE.

Best of luck with the project, ~Ray
0
 
colonelblueAuthor Commented:
Thank you.

I appreciate the point in the right direction and certainly want to and will learn it thoroughly.
That book looks great.
Not uncommon, everyone has to do 3 people's work and wish I had all the time to dedicate to study all at once.
But with deadlines and other non-related projects, I have to go with what I have. Coming from old school ASP Classic, I am familiar with what I can do but don't necessarily know how to translate them to PHP.
So I think I do do OK in "being equipped to ask questions". By and large it is like me trying to translate a paragraph from Spanish to English. I may know some Spanish but the grammar and structure is a bit different. To say I should learn to speak Spanish first in order to ask questions about it may seem intolerant.
0
 
colonelblueAuthor Commented:
One more question experts if I may.

How would you default the variable ' $city' to show all all cities if the  ' $city' value is null?
0
 
haijeromeCommented:
Hi

               I am really glad  and its my pleasure to help you again. Kindly find the comments preceded by "ANSWER :""NOTE:" inside the same code attached below.




Regards,Jerome Dennis D

<?php

/* QUESTION : For more than one variable , is there a way to loop that or should I just copy for each variable? */


/* ANSWER : Yes you can loop lke this for more than one GET variables*/

/*
 
   KINDLY NOTE : the below loop is coded in the sense if you are going to use 
   the get variables individually for each and every query. Instead of this if you are going to use 
   more than one VAriable in your select query then the logic shoul be quite different whats below here
   
   
*/

/* QUESTION : For the purpose of learning would you be so kind to demystify the above?
I was able to run the query just using the variable declarations and the addition of the WHERE clause to the SQL statement.

*/

foreach($_GET as $key => $value)
{
	/*ANSWER : Below Lines are Helpful to prevent SQL injection*/ 
    $city = mysql_real_escape_string($_GET[$key]);
    $city = htmlspecialchars(stripslashes($city));
    $badWords = array("/delete/i", "/update/i","/union/i","/insert/i","/drop/i","/http/i","/--/i");
    $city = preg_replace($badWords, "", $city);
    $city = str_ireplace("script", "blocked", $city);
    
	/* ANSWER : And here goes our query String*/
	$query =    "SELECT *
				 FROM yourtablename
				 WHERE city = '".$city."'";
	
	
	/* ANSWER : Here we are executing our query and store the result set in a variable And die statement will be called if there is any error with our query*/
	$result =  mysql_query($query,$connectionvariable) or die(mysql_error());
	
	/*	ANSWER : Here we're fetching the results as an array and store it in another array */
	/* 
	NOTE : 
	In mysql there are many ways of fetching the data from the database. The most common ways are : 
	1. mysql_fetch_array(), 
	2.mysql_fetch_row(), 
	3.mysql_fetch_object
	*/
	
	while($rowset = mysql_fetch_array($result))
	{
		echo "Field1 : ".$rowset['field1'];
		
		/* ANSWER : Give your actual field / column names instead of field1*/
	}
	
	
}
?>

Open in new window

0
 
haijeromeCommented:
And also i added a for each loop for multiple variables in the above code....Kindly Find It...

Regards,
Jerome Dennis D
0
 
Ray PaseurCommented:
How would you default the variable ' $city' to show all all cities if the  ' $city' value is null?

Something like this, to create a dynamic query...
$sql = "SELECT thing FROM table WHERE city = '" . mysql_real_escape_string($_GET["city"]) . "'";

if (empty($_GET["city"]))
{
    $sql = "SELECT thing FROM table";
}

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now