If/Else within my sql call?

PHP
1

I’m trying to create an news module and everything works allmost fine.

The issue is that when creating a news article you are ablle to set an startdate and an enddate for the news…If you don’t set the enddate the enddate would look like this in my db table:

0000-00-00 00:00:00

Thats OK I think, when I do this:

$thisdate = date("Y-m-d 00:00:00");
mysql_query("SELECT * FROM news WHERE startdate <= '$thisdate' AND enddate >= '$thisdate' AND user_id=".$user." ORDER BY id DESC");

I dont get the news out with no enddate set… How do I solve this?

Thanks in advance :wink:

2

is there some way you can prevent the end date from going into the table as zeros?

preferably it should be NULL

then the query is easy, you would use COALESCE to substitute some dummy date far into the future

i.e.

WHERE startdate <= '$thisdate' 
  AND COALESCE(enddate,'2099-12-31') >= '$thisdate'
3

Well… The table field is datetime. Not sure I can do so?

4

Try this:


	$user = '';
	$date_START = date("Y-m-d 00:00:00"); 
	$date_END    = date("Y-m-d 24:00:00"); // replaced with 24 hours
	$sql = 
	"
	  SELECT id, date, title 
	  FROM news
	  WHERE startdate >= '$date_START' 
	  AND 
	  enddate <= '$date_END' 
	  ORDER BY id 
	  DESC
	  LIMIT 0, 3
	";
  // AND #user_id=".$user." 
	$result		= mysql_query($sql);  

	$num_rows	= mysql_num_rows($result);
	
	// 
	if($num_rows)
	{
		echo '<br />', 'Rows: ', $num_rows;
		
		$rows		= mysql_fetch_array( $result );
		echo '<pre>';
			var_dump($rows);
			echo '<br />';
		echo '</pre>';
	}
	else
	{
		echo 'Yes we have no rows!!!'; 
	}	
	echo '<br /><br />$sql: <br />', $sql;
5

if you would please do a SHOW CREATE TABLE for the table, i can tell you…

6

THis is what I got:
435245415445205441424c452060657a5f6e6577736020280a20206069646020696e7428313129204e4f54204e554c4c20612e2e2e

7

I changed my jokes table.date from timestamp to datetime and ran this script:


	$user = '';
	$this_date	= '2012-01-26';
	$date_START = date("Y-m-d 00:00:00", strtotime( $this_date )); 
	$date_END		= date("Y-m-d 24:00:00", strtotime( $this_date )); 
	
	$sql = 
	"
	  SELECT id, xrl, date 
	  FROM jokes 
	  WHERE date >= '$date_START' 
	  AND 
	  date <= '$date_END' 
	  ORDER BY id 
	  DESC
	  LIMIT 0, 3
	";
	$result		= mysql_query($sql);  

	$num_rows	= mysql_num_rows($result);
	if($num_rows)
	{
		echo '<br />', 'Rows: ', $num_rows;
		$rows		= mysql_fetch_array( $result );
		echo '<pre>';
			var_dump($rows);
			echo '<br />';
		echo '</pre>';
	}
	else
	{
		echo 'Yes we have no rows!!!'; 
	}	
	echo '<br /><br />$sql: <br />', $sql;

and here is the routput:



Rows: 3

array(6) {
  [0]=>
  string(4) "2488"
  ["id"]=>
  string(4) "2488"
  [1]=>
  string(37) "Do-you-know-why-girls-are-like-apples"
  ["xrl"]=>
  string(37) "Do-you-know-why-girls-are-like-apples"
  [2]=>
  string(19) "2012-01-26 12:24:48"
  ["date"]=>
  string(19) "2012-01-26 12:24:48"
}

$sql:
SELECT id, xrl, date FROM jokes WHERE date >= '2012-01-26 00:00:00' AND date <= '2012-01-26 24:00:00' ORDER BY id DESC LIMIT 0, 3

//

8

i’m happy for you… i got a lot less than that :wink:

go to your sql window and run the following query:

SHOW CREATE TABLE news

and then look for, and copy here, the entire CREATE TABLE statement

this will show us the columns in the news table and their datatypes and default actions

9

Thats what I did… But here I write it:
id (int, ai)
fk_owner_id (int)
title (varchar,255)
content (text)
startdate (datetime)
enddate (datetime)

Hope this helps!

10

nope, doesn’t

i wanted to see the actual CREATE TABLE statement

11

Ok… Got it:

CREATE TABLE news (
id int(11) NOT NULL AUTO_INCREMENT,
fk_owner_id int(11) NOT NULL,
title varchar(255) COLLATE utf8_unicode_ci NOT NULL,
content text COLLATE utf8_unicode_ci NOT NULL,
startdate datetime NOT NULL,
enddate datetime NOT NULL,
img varchar(255) COLLATE utf8_unicode_ci NOT NULL,
imgtype int(11) NOT NULL,
active int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

12

@jmansa

Did you try this script?



 // REMOVE after testing
  $this_date = '2012-01-26';


  $date_START = date("Y-m-d 00:00:00", strtotime( $this_date )); 
  $date_END   = date("Y-m-d 24:00:00", strtotime( $this_date )); 

  mysql_query
  (
    "SELECT * 
     FROM news 
     WHERE startdate <= '$thisdate'    AND  enddate >= '$thisdate'    AND  user_id=".$user." 
     ORDER BY id 
     DESC"
  );

//edit : formatting

13

see that? that’s where your 0000-00-00 comes from

if you change it to NULL instead of NOT NULL, then you will be allowed to enter NULL as the value for enddate, for those items which have no enddate

then you can use the COALESCE function like i showed earlier

14

Working with datetimes that don’t allow null values is about as fun as playing in NYC traffic.

Course, what’s really fun is when you come across legacy code that expects 0000-00-00 to be the default, and setting things to null to gain sanity borks the hole system.

15

Now I realise why my suggestions were ONLY working locally.

I tested @jmansa’s submitted script on a table with a field set to TIMESTAMP with a default of TIMESTAMP and with attributes set to “on update to CURRENT_TIMESTAMP”. Date settings which seem sensible rather than a default to a meaningless none or NULL.

Surprisingly when the field was changed for testing to datetime the datetime values were retained. This is why my script worked locally.

Don’t you love computers :slight_smile:

//