Syntax and Mysql version??

Shamrox

Can anyone tell me what's wrong with the syntax here??

$select = "SELECT DISTINCT firstname, lastname, street1, street2, city, state, zipcode, zip4, country, countrycode, MAX(quantity) FROM moms_customer, moms_lists WHERE moms_customer.custnumber = moms_lists.custnumber AND pubid IN($display) GROUP BY lastname";

When i run it on a server with mysql version 4.0.25 it runs fine.

When I run it on a server with mysql version 4.1.8 I get the following error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUP BY lastname' at line 1

Lars_Berg

Check that the variable $display is not empty.

Shamrox

How?

Lars_Berg

echo  "Contents of display:" . $display;

Shamrox

Oh, yeah. I thought you meant something else.

Shamrox

Ok, it's def empty. when i adjusted the code, it output the same error. But only on the one version of mysql.

So, any ideas??

thorpe

where do you define $display ? post your code.

Shamrox

<?php
mysql_select_db($database_moms, $moms);
$query_rs_usernamematch = "SELECT moms_users.eid FROM moms_users WHERE moms_users.username ";
$rs_usernamematch = mysql_query($query_rs_usernamematch, $moms) or die(mysql_error());
$row_rs_usernamematch = mysql_fetch_assoc($rs_usernamematch);
$totalRows_rs_usernamematch = mysql_num_rows($rs_usernamematch);

mysql_select_db($database_moms, $moms);
$query_rs_username = "SELECT * FROM moms_users WHERE moms_users.username ='".$_SESSION['MM_Username']."'";
$rs_username = mysql_query($query_rs_username, $moms) or die(mysql_error());
$row_rs_username = mysql_fetch_assoc($rs_username);
$totalRows_rs_username = mysql_num_rows($rs_username);

mysql_select_db($database_moms, $moms);
$query_rs_pubid2 = "SELECT * FROM moms_pubtable ORDER BY listtitle ASC";
$rs_pubid2 = mysql_query($query_rs_pubid2, $moms) or die(mysql_error());
$row_rs_pubid2 = mysql_fetch_assoc($rs_pubid2);
$totalRows_rs_pubid2 = mysql_num_rows($rs_pubid2);
?>
<?php
mysql_select_db($database_moms, $moms);
$query_rs_pubid = "SELECT pubid, listtitle FROM moms_pubtable ORDER BY pubid ASC";
$rs_pubid = mysql_query($query_rs_pubid, $moms) or die(mysql_error());
$row_rs_pubid = mysql_fetch_assoc($rs_pubid);
$totalRows_rs_pubid = mysql_num_rows($rs_pubid);
?>
<? 

if(isset($_POST['ok']))    // submit button pressed 
{ 
    $_POST['display'] = trim($_POST['display']); 
    if($_POST['display'] == '') 
    { 
        die("nothing chosen."); 
    } 
    else  

    { 

header("Content-type: application/vnd.ms-excel"); 
header("Content-Disposition: attachment; filename=maillist.xls"); 
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
header("Expires: 0");
        $select = "SELECT DISTINCT firstname, lastname, street1, street2, city, state, zipcode, zip4, country, countrycode, MAX(quantity) FROM moms_customer, moms_lists WHERE moms_customer.custnumber = moms_lists.custnumber AND pubid IN($display) GROUP BY lastname"; 
$export = mysql_query($select, $moms) or die(mysql_error());
$row = mysql_fetch_assoc($export);
$totalRows = mysql_num_rows($export);
$fields = mysql_num_fields($export);

// BEGIN FOR LOOPING 
for ($i = 0; $i < $fields; $i++) { 
$header .= mysql_field_name($export, $i) . "\t"; 
} 
while($row = mysql_fetch_row($export)) { 
$line = ''; 
foreach($row as $value) { 
if ((!isset($value)) OR ($value == "")) { 
$value = "\t"; 
} else { 
$value = str_replace('"', '""', $value); 
$value = '"' . $value . '"' . "\t"; 
} 
$line .= $value; 
} 
$data .= trim($line)."\n"; 
} 
$data = str_replace("\r","",$data); 
echo $header."\n".$data; 
//IF STATMENT THAT IS PRODUCED IF NO DATA IS PRESENT 
if ($data == "") { 
$data = "n(0) Records Found!n"; 
} 

    die($sql); 
} 
} 

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<link href="moms.css" rel="stylesheet" type="text/css">
<script> 

function addToField(selbox) 
{ 
    selectedIndex = selbox.options.selectedIndex; 
    selectedValue = selbox.options[selectedIndex].value; 

if(selectedValue == "") 
    return; 

if(document.myForm.display.value != "") 
    document.myForm.display.value += ","; 

document.myForm.display.value += selectedValue; 
} 

</script> 
</head>
<body>
<p><?php adminToolbar(); ?></p>
<table width="800" height="233" border="1" align="center" cellpadding="5" cellspacing="0">
<td align="center">

<form name="myForm" method="POST" action="<?=$_SERVER['PHP_SELF'] ?>"> 
  <table width="600" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td>Select a Pub ID:</td>
      <td>Select a Pub Title: </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td><select size="1" name="select1" onChange="addToField(this);">
        <option value="">Choose:</option>
        <?php
do {  

?>
        <option value="<?php echo $row_rs_pubid['pubid']?>"><?php echo $row_rs_pubid['pubid']?>     <?php echo $row_rs_pubid['listtitle']?></option>
        <?php
} while ($row_rs_pubid = mysql_fetch_assoc($rs_pubid));
  $rows = mysql_num_rows($rs_pubid);
  if($rows > 0) {
      mysql_data_seek($rs_pubid, 0);
	  $row_rs_pubid = mysql_fetch_assoc($rs_pubid);
  }
?>
      </select></td>
      <td><select size="1" name="select2" onChange="addToField(this);">
        <option value="">Choose:</option>
        <?php
do {  

?>
        <option value="<?php echo $row_rs_pubid2['pubid']?>"><?php echo $row_rs_pubid2['listtitle']?></option>
        <?php
} while ($row_rs_pubid2 = mysql_fetch_assoc($rs_pubid2));
  $rows = mysql_num_rows($rs_pubid2);
  if($rows > 0) {
      mysql_data_seek($rs_pubid2, 0);
	  $row_rs_pubid2 = mysql_fetch_assoc($rs_pubid2);
  }
?>
      </select></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td colspan="2">List of Pub ID's: 
        <input name="display" type="text" size="30">
        <input type="submit" name="ok" value="Run Mailing List">
        <input type="reset" name="Reset" value="Reset"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <br /> 
  <br /> 
</form>    <p>&nbsp;</p></td>
</table>
<p>&nbsp;</p>
<p><?php echo $row_rs_username['name']; ?></p>
<p>
</p>
<p>&nbsp; </p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>
<?php
mysql_free_result($rs_pubid);

mysql_free_result($rs_pubid2);
?>

mysqlautobackup

Please ensure the $display is not empty first, and then please insert a space between IN to ( symbol.

AND pubid IN ($display) GROUP BY lastname

Shamrox

I checked to see if it was empty, by displaying to the page. Gave me the same syntax error on the page.
I added the space as you mentioned, and i still get the same syntax error.

It's really odd, because it works just fine on the other version of mysql, but the actual server i have to run the site from, it fails.

HELP!!!!

Shamrox

anyone? anyone?

nemonoman

The IN syntax changed in different versions of MySQL. I forget precisely when, but it made my code break after the switchover.

It used to be that
WHERE xxx IN ()

WHERE xxx IN (NULL)

would return No Error (although the results might not be what was desired).

At a certain version upgrade, a NULL IN clause threw an error.

The workaround is to create a dummy value for the in clause:

WHERE id IN ('dummyvalue', 1, 2,3)
WHERE id IN ('dummyvalue')

Shamrox

I'm not sure I follow you. The value shouldn't ever be null. If the query is run, there would be a value in the field for $value. If I created a dummy record, wouldn't the query be run with that dummy value?

Sxooter

Normally distinct and group by are exclusive, and one negates the need for the other.

Note that selecting fields that are not grouped by, without an aggregate, may give you a different result each time, and if there's no chance of that, then you probalby need to normalize your data.