Jump to content

Problem wth PHP and mysql queries


zerocool

Recommended Posts

Hi,

I would appreciate any help with this at all that you guys can give me.  I am trying to get a list of unique users from a mysql table, then get the records for each of those user.  I am then converting to a .csv and sending it through an email.  I have the following code and I always only get the same record.  The file name changes to the correct user but the data in the .csv is always only the first user.  Thanks in advance.

echo "getusers";
$server = "localhost";
$link = mysql_connect("$server", "$user", "$password");

mysql_select_db("$database");

$getusers = "select distinct(`User`) from realtordata";
$result1 = mysql_query($getusers);
$numusers = mysql_num_rows($result1);
$userarray[$numusers];
//	$s = 0;
$userarray[$numusers];
$uservar = $userarray[$s];
echo "<table width = 5% border = '2'";
while ($row = mysql_fetch_array($result1))
{
	$userarray[$s] = $row['User'];

//		echo $userarray[$s];
	$csv_file_name = "$uservar.csv";
	echo "<tr><td>$userarray[$s] </td></tr>";
//		$s++;
	$mySQL_query = "SELECT  *  FROM `realtordata` where `User` = '$uservar'   order by `Status`";
	$result = mysql_query($mySQL_query);
	$numrecs = mysql_num_rows($getdata);
	echo "<table  border = '1'";
	echo "<th> User </th><th>Date Added</th><th>First Name</th><th>Last Name </th><th> Status</th><th> Loan Timeframe</th><th>Need Purchase Realtor</th>
		<th> Real Estate Agent Name</th><th>Real Estate Agent Phone</th><th>Need Selling Realtor</th><th>Agent Company or Broker</th>
		<th> Agent Email</th><th>Agent City</th><th>Agent State</th>";
	while($row = mysql_fetch_array($result))
	{
		$s++;
		echo "$uservar";
		$userarray[$s] = $row['User'];
		$user = $userarray[$s];
		$date = $row['Date Added'];
		$fname = $row['First Name'];
		$lname = $row['Last Name'];
		$timeframe = $row['Loan Timeframe'];
		$needpurchaserealtor = $row['Need Purchase Realtor'];
		$agentname = $row['Real Estate Agent Name']; 
		$agentphone = $row['Real Estate Agent Phone'];
		$needsellingrealtor = $row['Need Selling Realtor'];
		$agentcompanyorbroker = $row['Real Estate Agent Company or Broker'];
		$agentemail = $row['Real Estate Agent Email'];
		$agentcity = $row['Real Estate Agent City'];
		$agentstate = $row['Real Estate Agent State'];

		echo "<tr><td> $user </td>";
		echo "<td> $date </td>";
		echo "<td> $fname </td>";
		echo "<td> $lname </td>";
		echo "<td> $timeframe </td>";
		echo "<td> $needpurchaserealtor </td>";
		echo "<td> $agentname </td>"; 
		echo "<td> $agentphone </td>";
		echo "<td> $needsellingrealtor </td>";
		echo "<td> $agentcompanyorbroker </td>";
		echo "<td> $agentemail </td>";
		echo "<td> $agentcity </td>";
		echo "<td> $agentstate </td></tr>";


// Reuseable CSV file variables
$csv_contain = '"';
$csv_separate = ",";
$csv_end_row = "\n";
/*
// run the MySQL query and check to see if results were returned*/
$result = mysql_query($mySQL_query);
if (!$result) {
  die("ERROR: Invalid query \n MySQL error: " . mysql_error() . "\n Your query: " . $query);
}
echo "Step 3: MySQL query ran successfully. \n\n";

// store the number of columns from the results
$columns = mysql_num_fields($result);

// Build a header row using the mysql field names
$header_row = '';
for ($i = 0; $i < $columns; $i++) {
  $column_title = $csv_contain . stripslashes(mysql_field_name($result, $i)) . $csv_contain . $csv_separate;
  $header_row .= $column_title;
}
$csv_file .= $header_row . $csv_end_row; // add header row to CSV file

// Build the data rows by walking through the results array one row at a time
$data_rows = '';
while ($row = mysql_fetch_array($result)) {
  for ($i = 0; $i < $columns; $i++) {
    // clean up the data; strip slashes; replace double quotes with two single quotes
    $data_rows .= $csv_contain . preg_replace('/"/', '\'\'', stripslashes($row[$i])) . $csv_contain . $csv_separate;
  }
  $data_rows .= $csv_end_row; // add data row to CSV file
}
$csv_file .= $data_rows; // add the data rows to CSV file

echo "Step 4: CSV file built. \n\n";

/*
-------------------------------------- 
    SEND EMAIL WITH ATTACHMENT
    This requires the use of complex variable parsing {curly braces surrounding variable}
      For more information see: "PHP: Complex Variables in Strings" at Tinsology.net
      http://tinsology.net/2009/06/php-complex-variables-in-strings/

*/
// start setting up the email header
$headers = "From: ".$email_from;

// create boundary string
// boundary string must be unique using MD5 to generate a pseudo random hash
$random_hash = md5(date('r', time())); 
$mime_boundary = "==Multipart_Boundary_x{$random_hash}x";

// set email header as a multipart/mixed message
// this allows the sending of an attachment combined with the HTML message
$headers .= "\nMIME-Version: 1.0\n" .
"Content-Type: multipart/mixed;\n" .
" boundary=\"{$mime_boundary}\"";

// multipart boundary for the HTML message
$email_message = "This is a multi-part message in MIME format.\n\n" .
"--{$mime_boundary}\n" .
"Content-Type:text/html; charset=\"iso-8859-1\"\n" .
"Content-Transfer-Encoding: 7bit\n\n" .
$email_html_msg . "\n\n";

// encode CSV file with MIME base64
// required for sending it as an email attachment
$data = chunk_split(base64_encode($csv_file)); 

// multipart boundary for the email attachment
$email_message .= "--{$mime_boundary}\n" .
"Content-Type: application/octet-stream;\n" .
" name=\"{$csv_file_name}\"\n" .
"Content-Disposition: attachment;\n" .
" filename=\"{$csv_file_name}\"\n" .
"Content-Transfer-Encoding: base64\n\n" .
$data . "\n\n";

// end the multipart message
$email_message .= "--{$mime_boundary}--\n";

// try to send the email and verify the results
$sendit = @mail($email_to, $email_subject, $email_message, $headers);
if(!$sendit) {
  die("ERROR: The Email could not be sent.");
}
echo "Step 5: Email sent with attachment. \n\n";	
	}

	}

Link to comment
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.

×
×
  • 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.