Skip to main content
S
smiffy47
Known Participant
May 12, 2009
Answered

Getting data from msql tables and emailing the result

  • May 12, 2009
  • 1 reply
  • 891 views

Below is a bit of code that I'm wanting to gather some data from a couple of msql tables and email them to a user. (It's to remind user of their username and password in a low security situation - They have submitted their email address)

I've pasted some elements together from other pages, in the hope that it will do what I want.

At present it fails with the message

"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 's Test'' at line 1", having displayed $org as requested (as a test)

I'd be grateful if anyone can debug this problem, or otherwise advise.

<?php require_once('../../Connections/tormented3.php'); ?>
<?php
//now the recordset to link email-address to organisaton
$colname_rstSix = "1";
if (isset($_GET['email'])) {
  $colname_rstSix = (get_magic_quotes_gpc()) ? $_GET['email'] : addslashes($_GET['email']);
}
mysql_select_db($database_tormented3, $tormented3);
$query_rstSix = sprintf("SELECT ck_organisation FROM chk_sixmonth WHERE ck_email = '%s'", $colname_rstSix);
$rstSix = mysql_query($query_rstSix, $tormented3) or die(mysql_error());
$row_rstSix = mysql_fetch_assoc($rstSix);
$totalRows_rstSix = mysql_num_rows($rstSix);
//end recordset
$org = $row_rstSix['ck_organisation'];
echo $org; //temporary test. Yes, it does send correct text to monitor

//now recordset to link organisation to access details

mysql_select_db($database_tormented3, $tormented3);
$query_rstPword = sprintf("SELECT organisation, username, password FROM passwords WHERE organisation = '%s'", $org);
$rstPword = mysql_query($query_rstPword, $tormented3) or die(mysql_error());
$row_rstPword = mysql_fetch_assoc($rstPword);
$totalRows_rstPword = mysql_num_rows($rstPword);

//start emailing routine       
$start = "Your Access Information for updating the Torbay Mental Health website.
        Your Username is\n";// Set up the email message header
        $username = $row_rstSix['username'];
        $pword = $row_rstSix['password'];
      $message .= "$start $username . Your Password is $pword\n Thank you for updating your entries.";
          if ( mail( $email, 'Access information reminder',
                    $message, "From: feedback@sample.org.uk \r\n")){
              header('location:http://www.sample.org.uk');// redirect to page if email has been successfully sent
            exit;
        }else{
            $error == '<p style="color:red;">An error occured, your email could not be sent. Please try again</p>';
        }

?>

Many thanks.

This topic has been closed for replies.
Correct answer Mark_A__Boyd

I assumed your first SELECT ck_organisation... query succeeded because your echo $org printed what you expected.

Isn't it the second SELECT organisation... query that is failing? Did you try the echo or die method in that query? Or is that the one that outputs "Query was empty"?

Try editing the following code snippet and let's see what happens. I have added a couple more conditions where you might want to send them back to the form. I also added backticks (`) around the field names in the second query just in case any of those are reserved MySQL words (I didn't look them up, but they are available in the MySQL docs online).

mysql_select_db($database_tormented3, $tormented3);
$query_rstSix = sprintf("SELECT ck_organisation FROM chk_sixmonth WHERE ck_email = '%s'", $colname_rstSix);
$rstSix = mysql_query($query_rstSix, $tormented3) or die(mysql_error());
$totalRows_rstSix = mysql_num_rows($rstSix);
if (!$totalRows_rstSix) {
  // no record exists with email $colname_rstSix
  // Alert user and let them try again.
  exit;
}
$row_rstSix = mysql_fetch_assoc($rstSix);
//end recordset
$org = $row_rstSix['ck_organisation'];
echo $org; //temporary test. Yes, it does send correct text to monitor

//now recordset to link organisation to access details
// mysql_select_db($database_tormented3, $tormented3); // not needed. Still selected from above
$query_rstPword = sprintf("SELECT `organisation`, `username`, `password` FROM `passwords` WHERE `organisation` = '%s'", $org);
echo "$query_rstPword<br>";
$rstPword = mysql_query($query_rstPword, $tormented3) or die(mysql_error());
$totalRows_rstPword = mysql_num_rows($rstPword);
if (!$totalRows_rstPword) {
  // no record in passwords with $org
  // Decide what you want to do in this situation.
  // exit; ???
}
$row_rstPword = mysql_fetch_assoc($rstPword);

--
Mark A. Boyd
Keep-On-Learnin' :-)

1 reply

Mark_A__Boyd
Mark_A__Boyd
Inspiring
May 13, 2009

Echo the query before sending it to check if it looks OK.

OR

$rstPword = mysql_query($query_rstPword, $tormented3) or die("Invalid query: $query_rstPword<br>" . mysql_error());

You might also want to deal with cases where the $_GET['email'] is not set. For instance, at the top of your script. I recommend mysql_real_escape_string() over addslashes any day.

if (isset($_GET['email'])) {
  $colname_rstSix = mysql_real_escape_string($_GET['email']);
} else {
  echo 'NO EMAIL SUPPLIED. GO BACK AND TRY AGAIN!';
  exit;
  // or something friendlier with a link back to the form page
}

--
Mark A. Boyd
Keep-On-Learnin' :-)

S
smiffy47Author
Known Participant
May 13, 2009

Mark. Thanks for the improvement suggestion, which I have made.

echoing the query produces:

Invalid query: SELECT ck_organisation FROM chk_sixmonth WHERE ck_email =  'stevesmiths@sample.co.uk'
Query was empty

But what I don't understand is how the query can be empty and yet, if not interrupted, the correct value for $org is echoed a few lines later.

S
smiffy47Author
Known Participant
May 13, 2009

I assumed your first SELECT ck_organisation... query succeeded because your echo $org printed what you expected.

Isn't it the second SELECT organisation... query that is failing? Did you try the echo or die method in that query? Or is that the one that outputs "Query was empty"?

Try editing the following code snippet and let's see what happens. I have added a couple more conditions where you might want to send them back to the form. I also added backticks (`) around the field names in the second query just in case any of those are reserved MySQL words (I didn't look them up, but they are available in the MySQL docs online).

mysql_select_db($database_tormented3, $tormented3);
$query_rstSix = sprintf("SELECT ck_organisation FROM chk_sixmonth WHERE ck_email = '%s'", $colname_rstSix);
$rstSix = mysql_query($query_rstSix, $tormented3) or die(mysql_error());
$totalRows_rstSix = mysql_num_rows($rstSix);
if (!$totalRows_rstSix) {
  // no record exists with email $colname_rstSix
  // Alert user and let them try again.
  exit;
}
$row_rstSix = mysql_fetch_assoc($rstSix);
//end recordset
$org = $row_rstSix['ck_organisation'];
echo $org; //temporary test. Yes, it does send correct text to monitor

//now recordset to link organisation to access details
// mysql_select_db($database_tormented3, $tormented3); // not needed. Still selected from above
$query_rstPword = sprintf("SELECT `organisation`, `username`, `password` FROM `passwords` WHERE `organisation` = '%s'", $org);
echo "$query_rstPword<br>";
$rstPword = mysql_query($query_rstPword, $tormented3) or die(mysql_error());
$totalRows_rstPword = mysql_num_rows($rstPword);
if (!$totalRows_rstPword) {
  // no record in passwords with $org
  // Decide what you want to do in this situation.
  // exit; ???
}
$row_rstPword = mysql_fetch_assoc($rstPword);

--
Mark A. Boyd
Keep-On-Learnin' :-)


It's up and running now. It seems the backticks made the difference, but I've not checked to see which term(s) were implicated.

Thanks for all your help and advice. I've learn't a bit more php/mysql, struggling with this, but will I remember it...?

Steve

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices