Solved

How to read URL variable from URL to MYSQL Statement

Posted on 2010-09-12
13
314 Views
Last Modified: 2012-05-10
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
Comment
Question by:colonelblue
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 1

Expert Comment

by:zasad
ID: 33659937
I'am not used to PHP so forgive me if i am wrong :)

"SELECT * FROM......WHERE city= ".$_GET["city"];
0
 
LVL 48

Expert Comment

by:hernst42
ID: 33660008
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
 
LVL 48

Expert Comment

by:hernst42
ID: 33660014
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
 
LVL 7

Expert Comment

by:haijerome
ID: 33660859
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
 
LVL 7

Expert Comment

by:haijerome
ID: 33660869
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33662383
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:colonelblue
ID: 33665892
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33666619
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
 

Author Comment

by:colonelblue
ID: 33668147
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
 

Author Comment

by:colonelblue
ID: 33668162
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
 
LVL 7

Accepted Solution

by:
haijerome earned 400 total points
ID: 33670104
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
 
LVL 7

Expert Comment

by:haijerome
ID: 33670130
And also i added a for each loop for multiple variables in the above code....Kindly Find It...

Regards,
Jerome Dennis D
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 33672990
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Encode a password 2 39
Multiple methods, one main controller in Codeigniter 6 23
Paging Using PHP 7 34
regex expression 9 23
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now