Jump to content

Is my syntax wrong??

Shamrox

By Shamrox
in MySQL Help

 Share

Recommended Posts

Shamrox Member

Shamrox

Posted
Posted

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

Link to comment
https://forums.phpfreaks.com/topic/2690-is-my-syntax-wrong/
Share on other sites
Shamrox Member

Shamrox

Posted
  • Author
Posted
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.

Link to comment
https://forums.phpfreaks.com/topic/2690-is-my-syntax-wrong/#findComment-8943
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

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.