Jump to content

Need help with a bit of php and mysql code.

ericjw316

By ericjw316
in PHP Coding Help

 Share

Recommended Posts

ericjw316 Newbie

ericjw316

Posted
Posted

At this time i am building an email generation report program.  I have everything working for the first and second report.  But when i try to update and get the 3 run of emails it doesnt update the previous one.  below you will find the code that i have been working on and i have everything working except for the update to run every single time.

 

$con = mysql_connect("test","test","test");

// $con = mysql_connect("localhost","root","");

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("test", $con);
// mysql_select_db("test", $con);

$id = $_POST['id'];
$demo = $_POST['demo'];

$limit = $row["unique_recp"];

}

$res = mysql_query("SELECT emails FROM email_list WHERE custid = '$id' and demo = 'N' LIMIT ".$limit." ");

while ($row = mysql_fetch_array($res))
{
echo "<div id=\"left\">".$row['emails']."</div><div id=\"clr\"></div>";
}
mysql_query($res);

$res2 = "update email_list set demo = 'Y' WHERE custid = '$id' LIMIT ".$limit."";

mysql_query($res2);

mysql_close($con);

 

I have put in test for the actual data.

 

Let me know if you can figure out what is going on?  If you need to see the enter code i can send it.

jcbones Newbie

jcbones

Posted
Posted 
<?php
$con = mysql_connect("test","test","test");

// $con = mysql_connect("localhost","root",""); 

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("test", $con);
// mysql_select_db("test", $con);
//need sanitation and validation on these varaibles.
$id = (!empty($_POST['id'])) ? (int)$_POST['id'] : NULL; 
$demo = (!empty($_POST['demo']) && $_POST['demo'] == 'N') ? 'N' : 'Y';

//limit is out of place for a pull from the database.
$limit = 1;  //set a default for limit, so that it will not EVER be empty.
$limit = $row["unique_recp"]; //limit is not even required in this script, unless email_list has more than 1 email per user id.

}
if(empty($id)) { //exit the script if ID isn't set.
exit('There is no ID to query');
}
$res = mysql_query("SELECT emails FROM email_list WHERE custid = '$id' and demo = 'N' LIMIT $limit");
while ($row = mysql_fetch_array($res))
{
echo "<div id=\"left\">".$row['emails']."</div><div id=\"clr\"></div>";
}
//mysql_query($res); //You already performed this query, you cannot query a resource.

$res2 = "update email_list set demo = 'Y' WHERE custid = '$id' LIMIT $limit"; //not sure you want to limit an update.

mysql_query($res2);

mysql_close($con);

ericjw316 Newbie

ericjw316

Posted
  • Author
Posted

The code looked promising but it didn't work.

 

Here is what i am trying to do.  I start off by marking leads with a client id.  I put in some specif info and than i select the client id.  I than print out the results on the screen with only so many selected.  After the number is selected they should be marked with a Y for used to where they will not be used any longer.  The only item i am trying to get to work is the emails i select to be marked as used so i can not use them again.  Also the ID is an auto increment field so it is never empty or null.

 

Here is the full code:

<?php

global $email_cnt ;
$email_cnt=$_POST["email_cnt"];
global $campaign ;
$campaign=$_POST["campaign"];
$file = 'export';

//`email_cnt` = '$email_cnt'

//$sql="UPDATE comm_report SET `date` = '$date', `product` = '$product', `client_name` = '$client_name', `comm_check` = '$comm_check', `resid_check` = '$resid_check', `check_num_comm` = '$check_num_comm', `check_num_resid` = '$check_num_resid', `client_id` = '$client_id', `website_name` = '$website_name'
//WHERE `crid` = '$crid'";
   
?> 

<?php

$con = mysql_connect("localhost","glennn_admin","olno5893");

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("glennn_eric", $con);

$id = (!empty($_POST['id'])) ? (int)$_POST['id'] : NULL; 
//$id = $_POST['id'];
//$demo = $_POST['demo'];
$demo = (!empty($_POST['demo']) && $_POST['demo'] == 'N') ? 'N' : 'Y';

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
$result = mysql_query("SELECT campaign FROM campaign WHERE campaign='$id'");

while ($row = mysql_fetch_array($result)) {

$title = $row['campaign'];
}
?>


<title><?php echo $title; ?></title>

<link rel="stylesheet" href="print.css" type="text/css" media="print" /> 

<style type="text/css">
<!--
#font {
font-family:Lucida Console;
font-size:12px;
}

#center {
float:left;
width:500px;
text-align:right;
font-family:Lucida Console;
font-size:12px;
}
#left {
float:left;
margin-left:0px;
text-align:left;
padding-right:0px;
font-family:Lucida Console;
font-size:12px;
}

#left2 {
clear:left;
width:250px;
margin-left:0px;
text-align:left;
padding-right:0px;
font-family:Lucida Console;
font-size:12px;
}

#left3 {
float:left;
margin-left:0px;
text-align:left;
padding-right:0px;
font-family:Lucida Console;
font-size:12px;
}

#right {
float:left;
width:150px;
text-align:left;
font-family:Lucida Console;
font-size:12px;
}
#clr {
clear:both;
height:15px;
}
-->
</style>
</head>

<body>
<div align="right"><form><input type="button" value=" Print this page "
onclick="window.print();return false;" /></form> </div>
<div id="main" class="print">
<!--<div align="right"><a href="javascript:windows.print()">Print</a></div>-->
<!--
<input name="id" type="hidden" value="<?php echo $id ?>" />
-->
</div>


</div>
<div id="clr" style="clear:both; height:15px;"  class="print"></div>
<div id="left" class="print">     General Statistics</div>
<?php



$result = mysql_query("SELECT * FROM campaign WHERE campaign='$id'");

while ($row = mysql_fetch_array($result)) {


?><br />
    <br />
<div id="left" class="print"> Campaign Name: </div>
<div id="left2" class="print"> <span class="font"><?php echo $row["campaign"]; ?></span></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">Campaign Link: </div>
<div id="left2" class="print"> Link to newsletter that was sent</div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">Email Subject: </div>
<div id="left2" class="print"> Slash Business Expenses!</div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">Campaign sent by admin user: </div>
<div id="left2" class="print"> admin </div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>


<div id="left" class="print">Email List sent to: </div>
<div id="right" class="print"><?php echo $row["email_list"]; ?></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">Start Time: <?php echo $row["tstart"]; ?></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">Finish Time: <?php echo $row["tfinish"]; ?></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">Campaign ID: <?php echo $row["campaign_id"]; ?></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">The Total Recipients: <?php echo $row["total"]; ?>  download information to file</div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">The total who should receive this email if 
sending is successful: <?php echo $row["total"]; ?></div>

<div id="clr" class="print"></div>

<div id="left" class="print">The total rejected recipients: 0Download information
to file/Download with server messages/Send</div>
<div id="clr" class="print"></div>

<div id="left" class="print">Percent who received mailing: 99%</div>
<div id="clr" class="print"></div>

<div id="left" class="print">Forward to a friend clicked: 0</div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="left" class="print">    Read Ratio Statistics (HTML emails only)</div>
<div id="clr" class="print"></div>

<div id="left" class="print">Unique recipients who read email: <br /><?php echo $row["unique_recp"]; ?></div>
<div id="clr" class="print"></div>
<br />

<div id="left" class="print">Read Ratio Percentage: 
<?php

$result = mysql_query("SELECT * FROM campaign where campaign='$id'");


while($ejw = mysql_fetch_array($result))
  {
  $a = $row['unique_recp'] / $row['total'];
  echo round($a, 3) ."%";
  }


?> 






</div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">Number of times email was opened: <?php echo $row["emails_opened"]; ?></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">Un-Subscribes: 
<?php echo $row["unsubscribes"]; ?></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="left" class="print">Bounces: <?php echo $row["bounces"]; ?></div>

<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left"  class="print">        Link Statistics (Click column to sort field)</div>   <br /> 
    <div id="left" class="print">    Domain name Clicks from this mailing
    Total clicks from this campaign
    Download/Send Subscribers
    <br />    Download/Send:Total clicks:2:2:</p>
    
</div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div id="left" class="print">	            Most read recipients Top 
<?php echo $row["unique_recp"]; ?> 
(HTML Emails Only)</div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>

<div class="print" id="font">
  <?php

$limit = 1;  //set a default for limit, so that it will not EVER be empty.
$limit = $row["unique_recp"]; //limit is not even required in this script, unless email_list has more than 1 email per user id.

}

if(empty($id)) { //exit the script if ID isn't set.
exit('There is no ID to query');

}

$res = mysql_query("SELECT emails FROM email_list WHERE custid = '$id' and demo = 'N' LIMIT $limit");
while ($row = mysql_fetch_array($res))
{
echo "<div id=\"left\">".$row['emails']."</div><div id=\"clr\"></div>";
}
//mysql_query($res); //You already performed this query, you cannot query a resource.

$res2 = "update email_list set demo = 'Y' WHERE custid = '$id' LIMIT $limit"; //not sure you want to limit an update.

mysql_query($res2);
/*$res2 = "update email_list set 'demo' = 'Y' WHERE custid = '$id' LIMIT ".$limit."";

mysql_query($res2);
//$upd2 = mysql_query("UPDATE email_list SET demo = 'Y' WHERE custid = '$id' LIMIT ".$limit."  ");

//mysql_query($upd2);
/*
// AND campaign.used != '1' LIMIT ".$limit."  Joe


$emails = array();
while ($row = mysql_fetch_assoc($res)) {
  $emails[] = $row['emails'];
}

foreach ($emails as $email) {
  // fwrite your $email
}

*/



//$upd = "UPDATE email_list SET used = '1' WHERE used = '0' LIMIT ".$limit;



/*$EJW ="UPDATE email_list SET demo = 'Y' WHERE custid = '$id' LIMIT ".$limit;
mysql_error();
mysql_query($EJW);

/*mysql_query("UPDATE Persons SET Age = '36'
WHERE FirstName = 'Peter' AND LastName = 'Griffin'");*/



/*


$fileName = $id.".txt";
$fileHandle = fopen($fileName, 'w') or die("can't open file");



$string = "Campaign Name:\n";
$string .= $campaign."\n\n\n";

$string .= "Campaign Link:\n";
$string .= "Link to newsletter that was sent\n\n\n";

$string .= "Email Subject:\n";
$string .= "Slash Business Expenses!\n\n\n";

$string .= "Campaign sent by admin user:\n";
$string .= "admin\n\n\n";

$string .= "Email List sent to:\n";
$string .= $email_list."\n\n\n";

$string .= "Start Time:\n";
$string .= $tstart."\n\n\n";

$string .= "Finish Time:\n";
$string .= $tfinish."\n\n\n";

$string .= "Campaign ID:\n";
$string .= $campaign_id."\n\n\n";

$string .= "The Total Recipients:\n";
$string .= $total."\n\n\n";

$string .= "The total who should receive this email if \n
sending is successful:\n";
$string .= $total."\n\n\n";

$string .= "The total rejected recipients: 0 Download information \n
to file/Download with server messages/Send\n\n\n";

$string .= "Percent who received mailing: 100%\n\n\n";

$string .= "Forward to a friend clicked: 0\n\n\n";

$string .= "Read Ratio Statistics (HTML emails only)\n\n\n";


$string .= "Unique recipients who read email:\n";
$string .= $unique_recp."\n\n\n";

$string .= "Read Ratio Percentage:\n";
$string .= $read_emails."\n\n\n";

$string .= "Number of times email was opened:\n";
$string .= $emails_opened."\n\n\n";

$string .= "un-subscribes:\n";
$string .= $unsubscribes."\n\n\n";


fwrite($fileHandle, $string);

fclose($fileHandle);

*/
mysql_close($con);
?>
</div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="left" class="print">    Download all subscribers who read this email</div>
<div id="clr" class="print"></div>
    <div id="left3" class="print">Send email to all subscribers who read this email</div>
<div id="clr" class="print"></div>
<div id="clr" class="print"></div>
<div id="left3" class="print">Download all subscribers who did NOT read this email</div>
<div id="clr" class="print"></div>
   <div id="left3" class="print">Send email to all subscribers who did NOT read this email</div>

</div>
<br />
<br />
<br />

</body>
</html>

ericjw316 Newbie

ericjw316

Posted
  • Author
Posted

After reading some stuff online.  I just wanted to clarify:

$res2 = "update email_list set demo = 'Y' WHERE custid = '$id' LIMIT $limit";

 

The line of code above is supposed to update the selected items that will show on the page to be printed.  Those items should be marked with a "Y".  The first time i run the code it works but when i try to run the same code a second time with the same client which does happen as we have to do these reports at least 2 times a month for each client.  The code doesn't do anything at all.  This is my dilemma.

jcbones Newbie

jcbones

Posted
Posted

The current script will update the database everytime it loads.

 

You should put all form processing at the top of your scripts.  If you process forms at the bottom of scripts, when the page reloads it looks as if nothing happened (and that is correct, because it happens after page load).

 

 

jcbones Newbie

jcbones

Posted
Posted

1. Make sure your variables are what you think they are... echo them.

 

2. Do not limit an update, you should specify EXACTLY which row you want to update, otherwise you might be getting a different row than you want.

 

3. Separate processing and display.  Processing should always be done first, then the display.

 

4. Echo something in your loops, that will let you know where the script is.

 

5. Make sure your error reporting is set to it's highest, and display errors is on.

 

6. If all of that fails, take the bare essentials that you desire, and run it in a test script.  This will weed out all of the display's etc.

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.