Jump to content

Archived

This topic is now archived and is closed to further replies.

Shamrox

Is my syntax wrong??

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.