PHP WHERE clause (how to initially pull all data)

evo190

evo190

sudo make money
May 12, 2009
308
3
0
Shitbridge NJ
I'm creating a landing page with only one file. Let's say index.php.

Index.php is able to alter its information by calling different values from a MySQL db, which has only two fields: "top" & "type".

I initially want to show ALL of the data. Links on the page will only sort specific data from either $top or $type.

I can sort the data by doing the following:

$top = $_GET['top'];
$type = $_GET['type'];
$result = mysql_query( "SELECT * FROM table WHERE top='$top' OR type='$type' ORDER BY name" )


The problem is the initial page doesn't pull ANY data. Once I click a link:

index.php?top=1
index.php?type=berries

... I get some data.


HOW do I pull ALL of the data when the page initially loads? I think it has something to do with an include() tag but nothing seems to be working.

TIA.
 


$top = $_GET['top'];
$type = $_GET['type'];

if(empty($top) AND empty($type)){
$result=mysql_query("SELECT * FROM table ORDER BY name");
}else{
$result = mysql_query( "SELECT * FROM table WHERE top='$top' OR type='$type' ORDER BY name" );
}
 
add something like:

if ( ! isset ( $_GET['top'] ) )
$_GET['top']= 'default value';

etc.
 
evo190 said:
I'm creating a landing page with only one file. Let's say index.php.

Index.php is able to alter its information by calling different values from a MySQL db, which has only two fields: "top" & "type".

I initially want to show ALL of the data. Links on the page will only sort specific data from either $top or $type.

I can sort the data by doing the following:

$top = $_GET['top'];
$type = $_GET['type'];
$result = mysql_query( "SELECT * FROM table WHERE top='$top' OR type='$type' ORDER BY name" )


The problem is the initial page doesn't pull ANY data. Once I click a link:

index.php?top=1
index.php?type=berries

... I get some data.


HOW do I pull ALL of the data when the page initially loads? I think it has something to do with an include() tag but nothing seems to be working.

TIA.
Click to expand...

could do it like this:

[high=PHP]if( isset($_GET['top']) && isset($_GET['type']) )
{
$top = $_GET['top'];
$type = $_GET['type'];
$result = mysql_query( "SELECT * FROM table WHERE top='$top' OR type='$type' ORDER BY name" );
}
else
{
$result = mysql_query( "SELECT * FROM table ORDER BY name" );
}[/high]
 
  • Like
Reactions: evo190
dhamma said:
not if you want to select all entries!
you have to alter the sql statement.
Click to expand...

ah, sorry...I wasn't really attentive when I first read his request.
I now re-read the OP's post and I figured my solution is of no use :p

what you're saying is correct - my bad :)
 
jryan21 said:
could do it like this:

if( isset($_GET['top']) && isset($_GET['type']) )
{
$top = $_GET['top'];
$type = $_GET['type'];
$result = mysql_query( "SELECT * FROM table WHERE top='$top' OR type='$type' ORDER BY name" );
}
else
{
$result = mysql_query( "SELECT * FROM table ORDER BY name" );
}
Click to expand...

You are the MAN! +rep

I'm having one small syntax error with my die code tho...

or die("SELECT Error: ".mysql_error());

Error: Parse error: syntax error, unexpected T_LOGICAL_OR in

Once I remove 'or die...' - it works the way I want.

Here's the full code:

if( isset($_GET['top']) && isset($_GET['type']) )
{
$top = $_GET['top'];
$type = $_GET['type'];
$result = mysql_query( "SELECT * FROM table WHERE top='$top' OR type='$type' ORDER BY name" );
}
else
{
$result = mysql_query( "SELECT * FROM table ORDER BY name" );
}



or die("SELECT Error: ".mysql_error());
while($row = mysql_fetch_assoc($result))
{

?>
 
GUYS:

By not sanitizing the url vars you're leaving your butts up in the air for a fucking.

Someone's gonna come along and fuck with your DB.

If you know the possible values for top and type compare against these.
If not do something like this:

$top = preg_replace("#[^a-zA-Z0-9]#s", "", $_GET['top']);

Will help stop the baddies coming to get you.
 
@evo190
you´re welcome

I´m not sure about the "or"
remove it in front of die("..)
or paste more code...

A logical "or" is only allowed within if(), while(), e.g. statements
if(A OR B){ do X }
while(A OR B true){ do Y}

SpamHat is right - always sanitize direct input!!
 
  • Like
Reactions: evo190
SpamHat said:
GUYS:

By not sanitizing the url vars you're leaving your butts up in the air for a fucking.

Someone's gonna come along and fuck with your DB.

If you know the possible values for top and type compare against these.
If not do something like this:

$top = preg_replace("#[^a-zA-Z0-9]#s", "", $_GET['top']);

Will help stop the baddies coming to get you.
Click to expand...

My last step will be to secure the db from injections... thanks for the tip. Here is the complete code if anyone would mind helping lock it down.


<?php

$db="database";

$link = mysql_connect("link","user","pass");
if (! $link)
die("Couldn't connect to MySQL");

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

if( isset($_GET['top']) && isset($_GET['type']) )
{
$top = $_GET['top'];
$type = $_GET['type'];
$result = mysql_query( "SELECT * FROM table WHERE top='$top' OR type='$type' ORDER BY name" );
}
else
{
$result = mysql_query( "SELECT * FROM table ORDER BY name" );
}



#or die("SELECT Error: ".mysql_error());

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

?>
 
This is it, assuming that you only want to allow alphanumeric url vars:


<?php

$db="database";

$link = mysql_connect("link","user","pass");
if (! $link)
die("Couldn't connect to MySQL");

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

if( isset($_GET['top']) && isset($_GET['type']) )
{
$top = preg_replace("#[^a-zA-Z0-9]#s", "", $_GET['top']);
$type = preg_replace("#[^a-zA-Z0-9]#s", "", $_GET['type']);
$result = mysql_query( "SELECT * FROM table WHERE top='$top' OR type='$type' ORDER BY name" );
}
else
{
$result = mysql_query( "SELECT * FROM table ORDER BY name" );
}



#or die("SELECT Error: ".mysql_error());

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

?>




You'll also probably want to do some checking to make sure what you get from mysql is really what you want, as well as building the query string dynamically to make sure that you don't have a where clause that's empty, for example.
 
SpamHat said:
You'll also probably want to do some checking to make sure what you get from mysql is really what you want, as well as building the query string dynamically to make sure that you don't have a where clause that's empty, for example.
Click to expand...

The page is really simple. Users will not be able to insert any data, the page will simply sort predefined data. First a user is able to see information on all products, then they can drill down to specifics.

Thanks again for the help everyone.

The last 'die' code is still giving me problems but overall it's working the way it should.

If anyone wants to security test the actual page send me a PM.
 
You must log in or register to reply here.
Top Bottom
Back