How do I change SQL query without a new .php?

@evo190Feb 03.2009

Let’s say we have a web page about shopping. Since shopping is a very broad topic, within it you’d have a)clothes b)shoes c)hats, etc.

Currently, in order to retrieve shoe data, I have a file ‘shopping_shoes.php’ – ‘shopping_hats.php’ for hats, etc.

shopping_shoes.php
( “SELECT * FROM shopping WHERE ~~[COLOR=”Red”]~~type=’shoes’[/COLOR] ORDER BY name” )

shopping_hats.php
( “SELECT * FROM shopping WHERE ~~[COLOR=”Red”]~~type=’hats’[/COLOR] ORDER BY name” )

I really don’t want a million .php urls. Is there an easier way to retrieve this data?

Ideally, I’d like to have ‘shopping.php’ with a sub menu (clothes | shoes | hats) that re-queries within the same ‘shopping.php’ page.

Please help. TIA.

to post a comment

PHP

16 Comments(s) _↴

@skywalker2208Feb 03.2009 — #You could place it in the url like example.com?type='hats' and use the $_GET variable to extract it.

@evo190authorFeb 03.2009 — #I'm not sure how to use the $_get in this scenario - I've only used it for forms.

The shopping.php is 'type='shop0' with links HATS & SHOES

<a href="shopping.php?type='shop1'">hats</a>

<a href="shopping.php?type='shop2'">shoes</a>

Please explain the $_get part or give an example or site. Thanks, Skywalker, I'm getting close!

@skywalker2208Feb 03.2009 — #It is pretty much the same thing as using get in the form. When you use get in the form then it places the info in the url. The $_GET variable takes the variables from the url in my example the variable in $_GET is type and that will be the array key in the $_GET array. So to get the type from $_GET you would do $_GET['type'].

Also, remember if you are going to use the type in your query you will want to do some validation on it because you don't then you are prone to sql_injection.

@evo190authorFeb 03.2009 — #I tried a few different configurations maybe you can help.

[COLOR="Red"]SHOPPING.PHP[/COLOR]

<?php

$db="shopping";

$link = mysql_connect("#","#","#");

if (! $link)

die("Couldn't connect to MySQL");

mysql_select_db($db , $link)

or die("Couldn't open $db: ".mysql_error());

$result = mysql_query( "SELECT * FROM shopping WHERE type='shop0' ORDER BY name" )

or die(#);

while($row = mysql_fetch_assoc($result))

{

?>

[COLOR="Red"]LINKS[/COLOR]

<a href="shopping.php?type='shop1'">hats</a>

<a href="shopping.php?type='shop2'">shoes</a>

OK, I'm not sure where [COLOR="Red"]$_Get[/COLOR] goes in the PHP?? I keep getting errors with my newb attempts.

[COLOR="Red"]Keep in mind the initial request on SHOPPING.PHP should be 'type=shop0'[/COLOR]

@skywalker2208Feb 03.2009 — #If you are going to use it as part of your query then you probably want to it to a separate variable and perform validation on it and most likey use the mysql_real_escape_string function as well.

@auto_webFeb 04.2009 — #

[CODE]
 <?php
 //Contents of shopping.php
 $db="shopping";
 $link = mysql_connect("#","#","#");
 if (!$link)
 die("Couldn't connect to MySQL");
 mysql_select_db($db, $link)
 or die("Couldn't open $db: ".mysql_error());
 if(isset($_GET['type']))
 {
 $type = $_GET['type']; // hats or shoes or clothes
 $result = mysql_query( "SELECT * FROM shopping WHERE type='$type' ORDER BY name" ); 
 }
 else
 echo "Please select a valid product type"
 
 while($row = mysql_fetch_assoc($result))
 {
 ?>
 
 LINKS
 
 <a href="shopping.php?type=hats>hats</a>
 <a href="shopping.php?type=shoes>shoes</a>
 [/CODE]

I think this should work

@evo190authorFeb 04.2009 — #@AUTO WEB

Very nice job - it works!

Now let me ask you this, if I have two fields (type, top) how do edit the query part?

Example:

$type = $_GET['type'];

$result = mysql_query( "SELECT * FROM shopping [COLOR="Red"]WHERE type='$type' OR top=1[/COLOR] ORDER BY name" )

The problem with [COLOR="Red"]OR[/COLOR] is that I can't exclude [COLOR="Red"]type or top[/COLOR] on certain queries.

***SHOPPING.php should default to top=1***

So, when I click on SHOES (shopping.php?type=shoes) I get both type=shoes AND top=1, but I only want type=shoes.

Let me know what you think, I'll continue to search. TIA.

@skywalker2208Feb 04.2009 — #Just a heads up. By setting the variable directly into the query that opens you up for security risks like mysql injection. Devious users could change the url and actually drop your table or inject bad data into your database.

In your question do you want type that are shoes and are top 1?

@evo190authorFeb 04.2009 — #So how do I prevent the SQL injection?

SHOPPING.PHP is the ONLY URL

THE HOME PAGE (shopping.php)

I want [COLOR="DarkGreen"]top=1[/COLOR] only

SHOE QUERY (shopping.php?type=shoes)

I want [COLOR="DarkGreen"]type=shoes BUT NOT top=1[/COLOR]

HATS QUERY (shopping.php?type=hats)

[COLOR="DarkGreen"]type=hats BUT NOT top=1[/COLOR]

@skywalker2208Feb 04.2009 — #I provided a link for the sql injection in my previous post. It should help you understand and get you started.

If you are looking for shoes that does not have a type 1 then do

SELECT * FROM shopping WHERE type='$type' AND top <> 1 ORDER BY name"

@evo190authorFeb 04.2009 — #type='$type' works fine

AND top <> 1 does NOT work, it only pulls data with no TYPE or TOP noted.

@skywalker2208Feb 04.2009 — #I am not 100% what you are looking for because you gave an example saying [B]type=hats BUT NOT top=1[/B]. To me that translates to type = 'hats' AND top != 1. Maybe you can explain a little more and give me some example data.

@auto_webFeb 05.2009 — #to prevent the sql injection you can check the contents of the $_GET['type']

variable and check if it exists

for example

only proceed with the query if the contents match with predefined elements

like shoes, clothes, dress, etc.

for example you can define all elements inside a array

[code=php]$pretype = array();
 $pretype[] = 'shoes';
 $pretype[] = 'hats';
 $pretype[] = 'clothes';
 $pretype[] = 'chocolates';
 $type = $_GET['type'];[/code]

and check for

[code=php]if(!in_array($type,$pretype)){
 die('Wrong Type.'); return;}[/code]

i have edited it in the main source please try this

[code=php]<?php
 //Contents of shopping.php
 $db="shopping";
 $link = mysql_connect("#","#","#");
 if (!$link)
 die("Couldn't connect to MySQL");
 mysql_select_db($db, $link)
 or die("Couldn't open $db: ".mysql_error());
 
 $pretype = array();
 $pretype[] = 'shoes';
 $pretype[] = 'hats';
 $pretype[] = 'clothes';
 $pretype[] = 'chocolates';
 if(isset($_GET['type']))
 $type = $_GET['type']; // hats or shoes or clothes
 if(in_array($type,$pretype))
 $result = mysql_query( "SELECT * FROM shopping WHERE type='$type' ORDER BY name" ); 
 else
 die('Please select a valid product type');
 
 while($row = mysql_fetch_assoc($result))
 {
 ?>
 
 <a href="shopping.php?type=hats">hats</a>
 <a href="shopping.php?type=shoes">shoes</a>[/code]

and I don't understand what you need about the top part of the query

@evo190authorFeb 05.2009 — #1st, many thanks for all the help so far.

Sorry for not clearly explaining the two different fields (type, top). I'll try to keep this simple without being unclear.

The landing page for SHOPPING.PHP will incorporate a few items from each subcategory of shopping. For instance, if there are 5 links (hats, shirts, jeans, shoes, socks) I'd like the landing page (or root page) to pull 'favorites' from each subcategory. Let's say each subcategory has 30 items, I'd like to pull 5 popular items for the landing page, but also keep those items in there respective categories as well.

[B]LANDING (shopping.php) [COLOR="Red"]top=1[/COLOR][/B]

5 items from hats

5 items from shirts

5 items from jeans

5 items from shoes

5 items from socks

[B]HATS (shopping.php?type=hats) [COLOR="Red"]type=hats[/COLOR][/B]

30 items for hats (including the 5 from landing)

[B]SHIRTS (shopping.php?type=shirts) [COLOR="Red"]type=shirts[/COLOR][/B]

30 items for shirts (including the 5 from landing)

ETC...

Again, I have fields [COLOR="Red"]TOP [/COLOR]and [COLOR="Red"]TYPE [/COLOR]so I can place certain items in both the landing page (top) and the subcategory page (type). If I had only one field (type), I'd have to have the item in either the landing page or the subcategory page, not both (at least to my knowledge).

Maybe I can tweak the DB fields or something, but that is the general direction of this particular code and project.

@skywalker2208Feb 05.2009 — #I think you can use the query below for the landing page

SELECT * FROM shopping WHERE type IN ('hats', 'shirts', 'jeans', 'shoes', 'socks') AND top = 1 ORDER BY type, name

If you only have those five items in the table then you can get rid of the type IN (....), but if you have other items in there that you don't want like jackets then you will need that in there.

For the other query you should be able to use the same query you have been using.

@evo190authorFeb 08.2009 — #AutoWeb, Skywalker - Thanks for the help figuring this out.

I figured out how to incorporate both fields, and query both fields in a single .php file.

<?php

$db="db";

$link = mysql_connect("s","u","p");

if (!$link)

die("Couldn't connect to MySQL");

mysql_select_db($db, $link)

or die("Couldn't open $db: ".mysql_error());

[COLOR="Red"]// prevent sql injections[/COLOR]

$pretype = array();

$pretype[] = 'top';

$pretype[] = 'hats';

$pretype[] = 'shirts';

$pretype[] = 'shoes';

[COLOR="Red"]// define type[/COLOR]

if(isset($_GET['type']))

$type = $_GET['type'];

[COLOR="Red"]// prevent sql injections[/COLOR]

$pretop = array();

$pretop[] = '1';

$pretop[] = '2';

[COLOR="Red"]// define top[/COLOR]

if(isset($_GET['top']))

$top = $_GET['top'];

[COLOR="Red"]// defend[/COLOR]

if(in_array($type,$pretype,$top))

[COLOR="Red"]// pass through code[/COLOR]

$result = mysql_query( "SELECT * FROM shopping WHERE [COLOR="Red"]type ='$type' OR top ='$top'[/COLOR] ORDER BY name" );

else

die('DONT BE EVIL');

while($row = mysql_fetch_assoc($result))

{

?>

[B]

[SIZE="5"][COLOR="RoyalBlue"]HERE IS WHERE THE FUN HAPPENS[/COLOR][/SIZE][/B]

HTML LINKS, [COLOR="RoyalBlue"]actually the <a href= is the good part

[/COLOR]

MAIN NAVIGAION

#0

SHOPPING.PHP (LANDING or INDEX PAGE)

<a href="shopping.php?[COLOR="Red"]type=top&top=1[/COLOR]">[U]shopping[/U]</a>

[COLOR="SeaGreen"]//code note:[/COLOR] type=top excludes field "type" altogether since there's only entries "hats,shirts,shoes" in the 'type field' in the db.

[COLOR="SeaGreen"]//security note:[/COLOR] in the php above, defined "$pretop = array(); defend against sql injections.

SUBNAVIGATION

#1

SHOPPING.PHP (Hats Query/Link)

<a href="shopping.php?[COLOR="Red"]type=hats&top=2[/COLOR]">[U]hats[/U]</a>

[COLOR="SeaGreen"]//code note:[/COLOR] top=2 excludes field "top" altogether since there's only entry "1" in the 'top field' in the db.

[COLOR="SeaGreen"]//security note:[/COLOR] in the php above, defined "$pretype = array(); defend against sql injections

#2

SHOPPING.PHP (Shirts Query/Link)

<a href="shopping.php?[COLOR="Red"]type=shirts&top=2[/COLOR]">[U]shirts[/U]</a>

#3

SHOPPING.PHP (Shoes Query/Link)

<a href="shopping.php?[COLOR="Red"]type=shirts&top=2[/COLOR]">[U]shoes[/U]</a>

[B][I]Hope someone finds this helpful besides me - it racked my brain for a few days but it finally worked.[/I][/B]

Also in #PHP _↴

To generate a Certificate Signing Request (CSR)count in mysql - error Contact Management Script?

Success!

Help @evo190 spread the word by sharing this article on Twitter...

Tweet This

How do I change SQL query without a new .php?

16 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

How do I change SQL query without a new .php?

16 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

16 Comments(s) _↴

Also in #PHP _↴