[SOLVED] complex SELECT and...

[Grant Holmes]

I have a successful query that is pulling date(s).

 

I then compare them to display today, or a range using a calendar script (below what is posted). All that is working. However, I now want to modify the SELECT to not only pull that data, but ONLY when another field is "active" by the field having a "1" (its an TINYINT field). If the field contains a "0" (zero), I don't want the record. I've tried modifying the code below several ways and am not making progress.

 

So the other SELECT would be [ where Active='1' ]

 

I'm not a "programmer", but am trying to learn the logic. Your help is appreciated.

====My Current SELECT====

include("dbinfo.inc.php");
$tDay = date("m-d");
$fromDay = $_REQUEST["f_date"];
$toDay = $_REQUEST["t_date"];
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
if (!empty($_REQUEST["t_date"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where DATE_FORMAT(Cbirthdate,"%m-%d") >= "'.$fromDay.'" AND DATE_FORMAT(Cbirthdate,"%m-%d") <= "'.$toDay.'"';
} else if (!empty($_REQUEST["showall"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays';
} else if (!empty($_REQUEST["id"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where id='.$_REQUEST["id"];  
} else {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where DATE_FORMAT(Cbirthdate,"%m-%d") = "'.$tDay.'"';
}

 

modified per request. Thanks!

[papaface]

Use [*code][*/code] without *'s

Easier to read that way.

[Zane]

just add

 

AND `active` = 1

to whatever query you want it on.

 

assuming it already has the where clause used once

 

if not then just use WHERE active = 1

[Grant Holmes]

Zanus, I think I understood the logic that you gave, I have no idea where to insert it in my code.

 

When I look at the code, I see four SELECT statements. Where do I put the [ AND `active` = 1 ]  and do I do it in each of the four statements?

 

Or there are three WHERE statements. Do I only insert in those? (and where do I plop it in)

 

That is my issue.

[Zane]

at the end

 

the syntax of WHERE in a nutshell

 

WHERE col1 = 'something' AND col2 = 'this' OR blah = 'something else'

pretty straight forward

[Grant Holmes]

Sorry. So in one of my statements:

  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where DATE_FORMAT(Cbirthdate,"%m-%d") >= "'.$fromDay.'" AND DATE_FORMAT(Cbirthdate,"%m-%d") <= "'.$toDay.'"';

 

I think the most basic issue is WHERE to insert it. I've done other statements and used "AND", but in this one, there are so many quotes (single and double) and greater/than and () that I'm not sure. This is where I think I'm failing.

 

So, using "XX " can you point to where I would put it? like:

  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where DATE_FORMAT(Cbirthdate,"%m-%d") >= "'.$fromDay.'" XX AND DATE_FORMAT(Cbirthdate,"%m-%d") <= "'.$toDay.'"';

 

Thanks again

 

[Grant Holmes]

I used a guess and place the "new" SELECT before the date and it works. Thanks for helping me with thinking this through.

 

include("dbinfo.inc.php");
$tDay = date("m-d");
$fromDay = $_REQUEST["f_date"];
$toDay = $_REQUEST["t_date"];
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
if (!empty($_REQUEST["t_date"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where `Active` = 1 AND DATE_FORMAT(Cbirthdate,"%m-%d") >= "'.$fromDay.'" AND DATE_FORMAT(Cbirthdate,"%m-%d") <= "'.$toDay.'"';
} else if (!empty($_REQUEST["showall"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays';
} else if (!empty($_REQUEST["id"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where `Active` = 1 AND id='.$_REQUEST["id"];  
} else {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where `Active` = 1 AND DATE_FORMAT(Cbirthdate,"%m-%d") = "'.$tDay.'"';
}

[Grant Holmes]

Well, I thought I had it fixed. I use this code in two places. In a "backend" page where the user can make the record active/not. The code works there as posted last above.

 

However, when I use it to display publicly, it returns nothing.

 

sigh  :'(    At this point, I'm not sure what help to ask for.

[Grant Holmes]

Last post by me. In the weirdest of events, on the "public" page (all logic to select Active is the same otherwise) If I change to look for "0" instead of "1" then it works.

 

Basically the opposite of the other page.

 

Go figure.