session causing select between probs

sessions

[Orangutang]

Hi,

Apologise if I've posted this in incorrect forum but it involves php an mysql.

I'm trying to retrieve quotes placed between 2 dates but I'm using session stipulation as the where clause.

I've managed to retrieve the quotes sent on a particular day ok with the following statement.

$query = "SELECT * FROM `quotes` WHERE (`username` = '".$_SESSION['user']."') AND `datesent` = '".($_POST['finddatesent'])."'
ORDER BY quoteid DESC LIMIT 100";

But when I try to add another AND it all goes wrong. I've constructed the below based on the above but I'm obviously doing something wrong.

$query = "SELECT * FROM `quotes` WHERE (`username` = '".$_SESSION['user']."') AND `datesent` BETWEEN '".($_POST['findbetqdatestart'])."' AND '".($_POST['findbetqdateend'])."' ORDER BY quoteid DESC LIMIT 100";

I'm not sure if the construction of my statement is incorrect or even if what I'm trying to do is possible so any help/pointers would be much appreciated.

Many thanks.

[impact]

I am not so expert so cant comment on what you are asking but may I suggest some thing?

why dont you make things little simpler by doing it in the tradational wasys?

such as this

session_start();
$username = $_session['user'];
$findDateSent = $_POST['finddatesent'];

$getQuotes = mysql_query("SELECT * FROM quotes WHERE username = '$username' AND datesent = '$findDateSent' ORDER BY quoteid DESC LIMIT 100");


Hope this help!

[rocknbil]

The first thing I notice is (if you're not already) that you have to unset session user after your query, or at least be sure to re-set it before the next query or it will always continue to query the same user (unless that's the intent, as for the logged in user.)

I wouldn't use between. Try

if (! isset($_SESSION['user']) or
(isset($_SESSION['user']) and ! preg_match('/^[\w\d]+$/',$_SESSION['user'])) {
// Or some other condition, maybe it's numeric,
// error checking is always important
die ("Invalid user queried.");
}


$query = "SELECT * FROM `quotes` WHERE `username` = '".$_SESSION['user']."' AND `datesent` >= '".($_POST['findbetqdatestart'])."' AND `datesent` <= '".($_POST['findbetqdateend'])."' ORDER BY quoteid DESC LIMIT 100";

Be sure the posted values are in mysql date format: YYYY-MM-DD. I'd use select lists, then you can do this, allowing you to use >= or <= alone. Make sure each select has an empty first value:

<select name="syear" id="syear">
<option value="">Select</option>
...

$start=$end=null; 
$where = "`username` = '".$_SESSION['user']."'"; 
// 
if (preg_match('/\d{4}/',$_POST['syear']) and  
 preg_match('/\d{2}/',$_POST['smonth']) and  
 preg_match('/\d{2}/',$_POST['sday'])) { 
 $start = $_POST['syear'] . '-' $_POST['smonth'] . '-' $_POST['sday']; 
} 
if (preg_match('/\d{4}/',$_POST['eyear']) and  
 preg_match('/\d{2}/',$_POST['emonth']) and  
 preg_match('/\d{2}/',$_POST['eday'])) { 
 $end = $_POST['eyear'] . '-' $_POST['emonth'] . '-' $_POST['eday']; 
} 
// 
$query = "SELECT * FROM `quotes` WHERE $where"; 
// 
// Note the spaces before AND, important 
if ($start) { 
 $query .= " AND `datesent` >= '$start'"; 
} 
if ($end) { 
 $query .= " AND `datesent` <= '$end'"; 
} 
$query .= " ORDER BY `quoteid` DESC LIMIT 100";  

This will give you one of four selects.

all records

SELECT * FROM `quotes` WHERE `username` = 'rocknbil1234';

Anything after a date

SELECT * FROM `quotes` WHERE `username` = 'rocknbil1234' AND `datesent` >= '2010-08-01' ORDER BY `quoteid` DESC LIMIT 100;

Anything before a date

SELECT * FROM `quotes` WHERE `username` = 'rocknbil1234' AND `datesent` <= '2010-09-01' ORDER BY `quoteid` DESC LIMIT 100;

Anything between the two.

SELECT * FROM `quotes` WHERE `username` = 'rocknbil1234' AND `datesent` >= '2010-08-01' AND `datesent` <= '2010-09-01' ORDER BY `quoteid` DESC LIMIT 100;

[Orangutang]

Hi Impact,

Thanks for the advice, I've been wondering why my select statements looked different that what I've seen. I've amended and now looks like this.

session_start();
$username = (isset($_SESSION['user']) ? $_SESSION['user'] : 'Log in');
$findDateSent = rtrim($_POST['finddatesent']);

$query = "SELECT * FROM `quotes` WHERE `username` = '$username' AND `datesent` = '$findDateSent' ORDER BY quoteid DESC LIMIT 100";

/////////

Hi rocknbil,

Brills, thanks for the help. I'll probably take a while to study and implement your post. I notice preg_match which I knew I'd have to get into at some point, may as well be now. :-)

Also, please don't be offended Impact because I've learnt a lot from your post but I was wondering what rocknbils opinion was of the construction of the select statement because I notice you've kept it as I had it. IE:

$query = "SELECT * FROM `quotes` WHERE `username` = '".$_SESSION['user']."' AND `datesent` >= '".($_POST['findbetqdatestart'])."' AND `datesent` <= '".($_POST['findbetqdateend'])."' ORDER BY quoteid DESC LIMIT 100";

Many thanks guys.

[Orangutang]

Hi Guys,

I'm having problems understanding due to newbie but may I ask a couple of questions: Below are examples from testing site, no sanitizing just wrote for this point.

Method 1
a - js calander to populate date fields on form. Looks like this after choice 2010-09-14 which it should do to match date field in mysql db.

b - PHP page:
session_start();
$username = (isset($_SESSION['user']) ? $_SESSION['user'] : 'Log in');

$findbetqdatestart = $_POST['findbetqdatestart'];
$findbetqdateend = $_POST['findbetqdateend'];

if (! isset($_SESSION['user']) or (isset($_SESSION['user']) and ! ($_SESSION['user'] > 0)))
{
$query = "SELECT * FROM `quotes` WHERE (`username` = '".$_SESSION['user']."') AND `datesent` BETWEEN '".($_POST['findbetqdatestart'])."' AND '".($_POST['findbetqdateend'])."' ";

Question:
Is there no way to reconstruct the above query to produce the result I need. Quotes placed between 2 dates.

///////////////////////////
If there is no way and php is better to use as not everyone enables js.
//////////////////////////

Method 2
a - I changed form to select lists

<form method="POST" action="findquotebetdatesent.php">
<select name="sday" id="sday">
<option value="">Select</option>
<option value='01'>01</option>
........

b - PHP page
session_start();
$username = (isset($_SESSION['user']) ? $_SESSION['user'] : 'Log in');

Create variables ?
Like? $sday = $_POST['sday']; (How get findbetqdatestart)

to use in this
if (! isset($_SESSION['user']) or (isset($_SESSION['user']) and ! ($_SESSION['user'] > 0)))
{
$query = "SELECT * FROM `quotes` WHERE `username` = '".$_SESSION['user']."' AND `datesent` >= '".($_POST['findbetqdatestart'])."' AND `datesent` <= '".($_POST['findbetqdateend'])."' ORDER BY quoteid DESC LIMIT 100";

Then where would this go
$start=$end=null;
$where = "`username` = '".$_SESSION['user']."'";
//
if (preg_match('/\d{4}/',$_POST['syear']) and
preg_match('/\d{2}/',$_POST['smonth']) and
preg_match('/\d{2}/',$_POST['sday'])) {
$start = $_POST['syear'] . '-' $_POST['smonth'] . '-' $_POST['sday'];
}
if (preg_match('/\d{4}/',$_POST['eyear']) and
preg_match('/\d{2}/',$_POST['emonth']) and
preg_match('/\d{2}/',$_POST['eday'])) {
$end = $_POST['eyear'] . '-' $_POST['emonth'] . '-' $_POST['eday'];
}

to enable me to use below select options.

//
$query = "SELECT * FROM `quotes` WHERE $where";
//
// Note the spaces before AND, important
if ($start) {
$query .= " AND `datesent` >= '$start'";
}
if ($end) {
$query .= " AND `datesent` <= '$end'";
}
$query .= " ORDER BY `quoteid` DESC LIMIT 100";

Many thanks :-(

[rocknbil]

On method 1:

js calander to populate date fields on form. Looks like this after choice 2010-09-14 which it should do to match date field in mysql db.

Of course, this now makes the application Javascript dependent, but if this is the case, this should work but there's some errors in logic, I think. (?)

I don't get this:
if (! isset($_SESSION['user']) or (isset($_SESSION['user']) and ! ($_SESSION['user'] > 0)))

You're saying " if username has NOT been set or if it HAS been set and is > 0". But you previously set $username, so why are you using session in your query? See change below.

Next,

$username = (isset($_SESSION['user']) ? $_SESSION['user'] : 'Log in');

Follow your logic here . . . this will cause it to do a query on username='Log In'.

$query = "SELECT * FROM `quotes` WHERE (`username` = 'Log In') AND . . .

What you probably want is

session_start(); 
// Presuming, of course, you have an include to connect to db, etc. 
$username=null; 
if (isset($_SESSION['user'] and is_numeric($_SESSION['user']) and ($_SESSION['user'] > 0)) { 
 $username = $_SESSION['user']; 
 // Using the logic from my post, we will presume if they get past that,  
 // $username has been set. 
 // 
 $where=$findbetqdatestart=$findbetqdateend=null; 
 if (isset($_POST['findbetqdatestart']) and 
 preg_match('/\d{4}\-\d{2}\-\d{2}/',$_POST['findbetqdatestart'])) { 
 $findbetqdatestart = $_POST['findbetqdatestart']; 
 } 
 if (isset($_POST['findbetqdateend']) and 
 preg_match('/\d{4}\-\d{2}\-\d{2}/',$_POST['findbetqdateend'])) { 
 $findbetqdateend = $_POST['findbetqdateend']; 
 } 
 // 
 if ($findbetqdatestart) { 
 $where .= " `datesent` >= '$findbetqdatestart'"; 
 } 
 if ($findbetqdateend) { 
 // If where has been set, we need an AND 
 if ($where) { $where .= ' and'; } 
 $where .= " `datesent` <= '$findbetqdateend'"; 
 } 
 // If where has been set, we need an AND 
 if ($where) { $where .= ' and'; } 
 $where .= " `username` = '$username'"; 
 // 
 $query = "SELECT * FROM `quotes` where $where order by `quoteid` desc limit 100";  
 // execute your query HERE 
} END if $username 
else { 
// redirect to log in script or log in 
} 

On method 2:

$username = (isset($_SESSION['user']) ? $_SESSION['user'] : 'Log in');

See notes in method 1.

Create variables ?
Like? $sday = $_POST['sday']; (How get findbetqdatestart)

You don't need to, you're overthinking it. :-) Look:

if (preg_match('/\d{4}/',$_POST['syear']) and
preg_match('/\d{2}/',$_POST['smonth']) and
preg_match('/\d{2}/',$_POST['sday'])) {
$start = $_POST['syear'] . '-' $_POST['smonth'] . '-' $_POST['sday'];
}

See that? We don't need $sday, $smonth, $syear, we're checking all three at the same time and getting a start date. But I did have an error in it - missing a concatenation dot, it's your job to debug my examples (fixed below). :-)

Then where would this go

I think the previous post had it, a revision combining parts of method 1 but with select lists. If you look closely, this is exactly the same as method 1, I've changed my "$start" and "$end" variables to your variable names so you can see. All that's different is what incoming variables we look for.

session_start(); 
// Presuming, of course, you have an include to connect to db, etc. 
$username=null; 
if (isset($_SESSION['user'] and is_numeric($_SESSION['user']) and ($_SESSION['user'] > 0)) { 
 $username = $_SESSION['user']; 
 $where=$findbetqdatestart=$findbetqdateend=null; 
 // 
 // Note the extra dots around '-' - oops, fixed here 
 if (preg_match('/\d{4}/',$_POST['syear']) and 
 preg_match('/\d{2}/',$_POST['smonth']) and 
 preg_match('/\d{2}/',$_POST['sday'])) { 
 $findbetqdatestart = $_POST['syear'] . '-' . $_POST['smonth'] . '-' . $_POST['sday']; 
 } 
 if (preg_match('/\d{4}/',$_POST['eyear']) and 
 preg_match('/\d{2}/',$_POST['emonth']) and 
 preg_match('/\d{2}/',$_POST['eday'])) { 
 $findbetqdateend = $_POST['eyear'] . '-' . $_POST['emonth'] . '-' . $_POST['eday']; 
 } 
 if ($start) { 
 $where .= " `datesent` >= '$findbetqdatestart'"; 
 } 
 if ($end) { 
 // If where has been set, we need an AND 
 if ($where) { $where .= ' and'; } 
 $where .= " `datesent` <= '$findbetqdateend'"; 
 } 
 // If where has been set, we need an AND 
 if ($where) { $where .= ' and'; } 
 $where .= " `username` = '$username'"; 
 // 
 $query = "SELECT * FROM `quotes` where $where order by `quoteid` desc limit 100";  
 // execute your query HERE 
} END if $username 
else { 
// redirect to log in script or log in 
} 

[Orangutang]

Hi rocknbil,

Thanks for the help, understand a lot more now especially what I was doing with the session sentences. Appreciate the time you've spent on the Javascript method but I've decided to ditch all js. PHP is the core engine and its what does the work and it can't be turned off and its relevant to every user. Good enough for me. If I could materialise you a beer by thinking it I would but for now I'll just have to say thanks. ,-)

I'll study and implement your post and hopefully get it working but something tells me I'm still going need some more help.

Thanks again, its definitely starting to sink in :-)