Solved

How to read URL variable from URL to MYSQL Statement

Posted on 2010-09-12
13
322 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 109

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
 

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 109

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 109

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
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.

770 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