Query "WHERE string is blank and date is at least 6 months ago.

[Ailis]

I'm getting this error "Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/westiehi/public_html/groupBOS.php on line 36"

 

for this code:

$query_rs_specialty = "SELECT * FROM specialty WHERE Group_Place <> '' and Date>= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY Date DESC";

 

I've tried everything to get this to take records where the one field is blank and the date is greater than a date 6 months ago but nothing seems to work.  Help would sure be appreciated!!

[xyph]

When I copy and paste that code into my editor, I get no error.

 

If we can't see line 36, along with other relevant code, it's hard to help you.

[Psycho]

If that PHP error is due to that query failing then you need to view the error generated by the query. Echo mysql_error() to the page after runnign that query. You might have a typo in a field name, maybe the 'Date' field in the database isn't an actual date type, or ???

[Ailis]

I was concerned about my script being too long - sorry!  Here's the entire code

 

<?php require_once('Connections/LocalConnect.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_LocalConnect, $LocalConnect);
$query_rs_specialty = 
"SELECT specialty.Group_Total, specialty.`Date`, specialty.Sponsors, specialty.Superintendent, specialty.Event_Type, specialty.Judge, specialty.City, specialty.`State`, specialty.BIS_Title, specialty.Best_of_Show, specialty.Group_Title, specialty.Group_Place  
FROM specialty WHERE specialty.Group_Place <> '' Date>= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) 
ORDER BY specialty.`Date`";
$rs_specialty = mysql_query($query_rs_specialty, $LocalConnect) or die(mysql_error());
$row_rs_specialty = mysql_fetch_assoc($rs_specialty);
$totalRows_rs_specialty = mysql_num_rows($rs_specialty);
?>

 

I tried the 'echo php error' but probably did not have it right.  It did not produce anything.

 

With the exception of "WHERE specialty.Group_Place <> '' Date>= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ", the code was all created by Dreamweaver.

 

Thanks again!!

[Psycho]

The only line I see with mysql_query() is this one:

$rs_specialty = mysql_query($query_rs_specialty, $LocalConnect) or die(mysql_error());

 

The $query_rs_specialty variable is just a string. If it was an invalidly formatted query it wouldn't generate the error. SO, the problem must with $LocalConnect. I assume that connection variable is set within the include file 'LocalConnect.php'. So, I'm guessing you are running a mysql_connect() function in there and assigning the result to this variable. So, open that file and see where you are doing that and add some error handling. E.g.

$LocalConnect = mysql_connect('database', 'username', 'password') or die(mysql_error());

[Ailis]

That was what was causing the error - thank you SOOO MUCH!!

 

But the query is not limiting the records shown.  I will keep trying!

 

I truly appreciate your help!!!

[Ailis]

Just in case anyone else is looking for the proper syntax - here it is:

 

mysql_select_db($sys_dbname, $conn);
$query_rs_specialty = "SELECT specialty.Group_Total, specialty.`Date`, specialty.Sponsors, specialty.Superintendent, specialty.Event_Type, specialty.Judge, specialty.City, specialty.`State`, specialty.BIS_Title, specialty.Best_of_Show, specialty.Group_Title, specialty.Group_Place  FROM specialty WHERE specialty.Group_Place <> '' AND Date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY specialty.`Date` DESC";

$rs_specialty = mysql_query($query_rs_specialty, $conn) or die(mysql_error());
$row_rs_specialty = mysql_fetch_assoc($rs_specialty);
$totalRows_rs_specialty = mysql_num_rows($rs_specialty);