Is my syntax wrong??

Shamrox · October 18, 2005

Based on my code below, can anyone tell me why this outputs data fine in one version of Mysql, but gives me a bad syntax error in another?

<?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> </td>
     <td> </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> </td>
     <td> </td>
   </tr>
   <tr>
     <td> </td>
     <td> </td>
     <td> </td>
     <td> </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> </td>
     <td> </td>
   </tr>
 </table>
 <br /> 
 <br /> 
</form>    <p> </p></td>
</table>
<p> </p>
<p><?php echo $row_rs_username['name']; ?></p>
<p>
</p>
<p>  </p>
<p> </p>
<p> </p>
</body>
</html>
<?php
mysql_free_result($rs_pubid);

mysql_free_result($rs_pubid2);
?>

Here is the specific code that creates the output query

$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";

AndyB · October 18, 2005

It might help if we knew the MySQL version number that works, the version number that fails, and the specific error you get when the script fails.

Shamrox · October 18, 2005

It might help if we knew the MySQL version number that works, the version number that fails, and the specific error you get when the script fails.

307934[/snapback]

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

Oh, and the above error shows up as the only data that is in the excel spreadsheet that I have it output to.

Sign In

Is my syntax wrong??

Recommended Posts

Shamrox

Link to comment

Share on other sites

AndyB

Link to comment

Share on other sites

Shamrox

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information