Jump to content


Photo

Is my syntax wrong??


  • Please log in to reply
2 replies to this topic

#1 Shamrox

Shamrox
  • Members
  • PipPipPip
  • Advanced Member
  • 92 posts

Posted 18 October 2005 - 05:26 PM

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>&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);
?>


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


#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 18 October 2005 - 05:47 PM

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.
Legend has it that reading the manual never killed anyone.
My site

#3 Shamrox

Shamrox
  • Members
  • PipPipPip
  • Advanced Member
  • 92 posts

Posted 18 October 2005 - 05:52 PM

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.

View Post



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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users