PHP - I Need Help To Optimize My Mysql Queries, It Takes Forever To Load

I was wondering if it is possible to perform the following query.  Say I have a list of names each with an id number ranging from 1 to 10.  Is it possible to perform a query where you list all of these records while repeating one records.  For example could I do a query that lists record number 6 then 10, 9, 8, 7, 6 etc.  Or another example would be listing record number 10, then 10, 9, 8, 7, 6 etc?

Thanks for any help. 

I'm trying to figure out how to combine these queries, to decrease server load and load time, but I'm not so good with MySQL queries. Does anyone have any ideas, any help is greatly appreciated, I've been tinkering with this for days now.

$Actv = 0;
$ActvCount = -1;
$ActvUpgrade = 0;
$ActvRenewal = 0;
$ActvVehicleAdds = 0;

$result2 = mysql_query("SELECT * FROM tblOperators WHERE OperatorLocale = 'USA' and OperatorStatus = 'ACTIVE' and Team = 'RENEWALS'");
while($row2 = mysql_fetch_array($result2)) {
 $operID = $row2['OperatorID'];
 
 $result = mysql_query("SELECT * FROM tblUserPayments WHERE OperatorID = '$operID' AND PaymentStatus='OK' AND PaymentDate LIKE '$currentDate%'");

 while($row = mysql_fetch_array($result))
 {
	if($row['PaymentReason'] == 'ACTIVATION'){
		++$ActvCount;
		//if($row['PaymentMethod'] == 'CREDITCARD'){
			$ActvUpgrade += $row['ChargeAmount'];
		//}
	}

	elseif($row['PaymentReason'] == 'UPGRADE'){
		$userid = $row['UserID'];
		$paymentdate = $row['PaymentDate'];
		$result1 = mysql_query("SELECT * FROM tblRenewalInvoices WHERE UserID='$userid' AND ('$paymentdate' >= DATE_SUB(DueDate, INTERVAL 90 DAY) AND '$paymentdate' < DATE_ADD(DueDate, INTERVAL 15 DAY)) AND ParentInvoiceID IS NULL ORDER BY InvoiceNum DESC LIMIT 1");
		if( $row1 = mysql_fetch_array($result1)) {
			$packageid = $row['PackageID'];
			$pack = mysql_query("SELECT * FROM tblUserPackages WHERE PackageID='$packageid';");
			if($pack1 = mysql_fetch_array($pack)){

				$expDate = $pack1['ExpirationDate'];
				$dueDate = $row1['DueDate'];
				$days = mysql_fetch_row(mysql_query("SELECT TO_DAYS('$expDate')-TO_DAYS('$dueDate');"));
				$months = (int)( ((int)$days + 14) / 30.4);
				$years = (int) ( ((int)$days + 182) / 365);
				$Intervals = 0;
				if($years > 0){
					$Intervals = $years;
				}
				if(($pack1['Package'] or 'GPS-SVL') or ($pack1['Package'] == 'GPS-1') or ($pack1['Package'] == 'GPS-1PLUS')){
					if($Intervals > 1){
						//if($row['PaymentMethod'] == 'CREDITCARD'){
							$Actv += $row['ChargeAmount'];
						//}
					}
					else{
						//if($row['PaymentMethod'] == 'CREDITCARD'){
							$ActvRenewal += $row['ChargeAmount'];
						//}
					}
				}
				else{
					$Actv += $row['ChargeAmount'];
				}
			}
			else{
			}
		}
		else{
			//if($row['PaymentMethod'] == 'CREDITCARD')
				$ActvUpgrade += $row['ChargeAmount'];
		}
	}

	elseif($row['PaymentReason'] == 'ADDVEHICLE'){
		//if($row['PaymentMethod'] == 'CREDITCARD')
			$ActvVehicleAdds += $row['ChargeAmount'];
	}
		
 }

$result = mysql_query("SELECT * FROM tblRenewalCalls WHERE OperatorID = '$operID' AND PayStatus='OK' AND DateSubmitted LIKE '$currentDate%'");

 while( $row = mysql_fetch_array($result) )
 {
	if($row['Charged']){
		if ((int)$row['RenewYears'] > 1) {
			$Actv += $row['RenewTotal'];
		}
		else{
			$ActvRenewal += $row['RenewTotal'];
		}
	 }
  }
 }
$total = $Actv+$ActvRenewal+$ActvUpgrade+$ActvVehicleAdds;

$ActvRenewal = $total - ($ActvVehicleAdds + $ActvUpgrade);

$upgradeEarned = $ActvUpgrade;
$renewalEarned = $ActvRenewal;

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.
Code: [Select]
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";
}

}

Hey all...

I'm running into a problem here. If you take a look at the code for this, you'll see it's to enter "horses" into a "class." It's all fine and dandy except I'm trying to get the way the horses are selected to have a different option. Right now they exist as a drop down/select option where you have to choose one at a time and enter submit. My goal is to have checkboxes that users can toggle and submit them all at once (with the rest of the eligibility checks etc still applicable). I've looked it up and the problem is, each user has a different amount of horses, so I can't account for all of them manually.

Could someone help me out?

On DaniWeb a user was trying to help me with an implode option, but I have zero idea how to work with that. I will literally need my hand to be held through this one.

Thank you in advance - I have been trying to do this myself for a good chunk of the day, but my PHP skills are quite limited.

Code: [Select]
<?php
session_start(); // Maintain session state
header("Cache-control: private"); // Fixes IE6's back button problem.
$page_title = "Class Information";

include('header.php');
$class_id = $_GET['id'];
$enter = $_POST['enter_horse'];
$enter_check = $_POST['check'];
$horse_id = $_POST['horse_id'];


//general show information
$result = @mysql_query("SELECT s.show_id, s.player_id, s.type, s.name, DATEDIFF(s.run_date, NOW()) AS datedif, c.class_id, s.entry_fee FROM classes c, shows s WHERE c.class_id='$class_id' AND c.show_id=s.show_id LIMIT 1")or die("Cannot find class! " . mysql_error());
$row = @mysql_fetch_array($result);
$show_id = $row['show_id'];
$show_name = $row['name'];
$runs_in = $row['datedif'];
$species = $row['species'];
$type = $row['type'];
$owner_id = $row['player_id'];
if(!$row['class_id']){myError("Invalid class!");include('footer.php');}
$entry_fee = $row['entry_fee'];
$num_entries = @mysql_num_rows(@mysql_query("SELECT horse_id FROM horses_entered WHERE class_id='$class_id'"));
$runs_in = "$runs_in day[s]";

if($enter){
//ensure horse is eligible to enter
$good = @mysql_num_rows(@mysql_query("SELECT horse_id FROM horses WHERE horse_id='$horse_id' AND player_id='$player_id' AND age>=2 AND age<=20"));
$exists = @mysql_num_rows(@mysql_query("SELECT horse_id FROM horses_entered WHERE horse_id='$horse_id' AND class_id='$class_id' LIMIT 1"));
if($my_money < $entry_fee){myError("You cannot afford the entry fee.", 1);}
if(!$good){myError("Are you sure you own the horse and it is between 3 and 20 years of age?");
}elseif($exists){myError("That horse is already entered in this class!");
}else{
@mysql_query("INSERT INTO horses_entered(horse_id, class_id) VALUES('$horse_id', '$class_id')")or die("Cannot create entry!");
if($type == 1 AND $entry_fee){@mysql_query("UPDATE players SET money=money+'$entry_fee' WHERE player_id='$owner_id' LIMIT 1")or die("Cannot update player money!"); $points=1;
}elseif($type == 2 AND $entry_fee){@mysql_query("UPDATE clubs SET money=money+'$entry_fee' WHERE president='$owner_id' LIMIT 1")or die("Cannot update player money2!"); $points=2;}
@mysql_query("UPDATE players SET money=money-'$entry_fee', points=points+'$points' WHERE player_id='$player_id' LIMIT 1")or die("Cannot update player money3! " . @mysql_error());
@mysql_query("UPDATE horses SET points=points+'$points' WHERE horse_id='$horse_id' LIMIT 1")or die("Cannot update horse points!");
myError("Class entered!");
}
}


//display the show information
echo "<table>
<tr><td><b>Class:</td><td>#$class_id</td></tr>
<tr><td><b>Show:</td><td><a href='shows.php?id=$show_id'>$show_name (#$show_id)</a></td></tr>
<tr><td><b>Runs:</td><td>$runs_in</td></tr>
<tr><td><b>Entry Fee:</td><td>$$entry_fee</td></tr>
<tr><td><b>Total Entrants:</td><td>$num_entries</td></tr>
<tr><td valign=top><b>Your Horses:</td><td>
<form action='classes.php?id=$class_id' method=POST>
<select name='horse_id'>
";
$result = @mysql_query("SELECT horse_name, breed, horse_id FROM horses WHERE player_id='$player_id' AND age>2 AND age<=20 ORDER BY horse_name ASC")or die("Cannot find horses! " . mysql_error());
while($row = @mysql_fetch_array($result)):
$horse_id = $row['horse_id'];
$horse_name = stripslashes($row['horse_name']);
$breed = $row['breed'];
echo "<option value='$horse_id'>$horse_name (#$horse_id), $breed</option>\n";
$prev_species = $species;
endwhile;
if(!$horse_id){echo "<option value=0>No eligible horses!";}


echo "
</select> &nbsp;&nbsp;
<input type=submit name='enter_horse' value='Enter Horse!'></td></tr></form>
<tr><td valign=top><b>Entrants:</td><td>
";

$query = "SELECT h.horse_name, h.horse_id, h.breed FROM horses_entered he LEFT JOIN horses h ON he.horse_id=h.horse_id WHERE he.class_id='$class_id' ORDER BY h.horse_name ASC";
$result = @mysql_query($query)or die(mysql_error());
while($row = @mysql_fetch_array($result)):
$name = $row['horse_name'];
$aid = $row['horse_id'];
$breed = $row['breed'];
$page = "horses.php";
echo "<a href='$page?id=$aid'>$name (#$aid)</a>, $breed<br>\n";
endwhile;
if(!$aid){echo "<i>No entrants.";}
echo "</td></tr>
</table>";

include('footer.php');
?>

can I do this

mysql_query("INSERT INTO directory (field, field, field,
field,field,field,
field,field,field,
fieldfield,field,
field,field,field,)
VALUES ('field','field','field',
'field','field','field',
'field','field','field',
'field','field','field'
,'field','field','field',)");

please ignore any syntax errors and stuff its just the concept of breaking it into different lines?

thanks

Hello Everyone,
I think this might be a bit of a challenge but its always worth getting some input for solutions.

I'm using jqplot to create graphs from data held in a database. And im having trouble thinking of a way to lay the data out in an acceptable way.

To plot a line the javascript takes a statement in this form:

line1 = [['x-axis1'],y-axis1],['x-axis2',y-axis2]

I'm running this query to get the data:


<?php
$query2 = "SELECT * FROM tracker WHERE username = '" . mysql_real_escape_string($usera) . "'";
$result = mysql_query($query2) or die('Error, query failed : ' . mysql_error()); 
while($row = mysql_fetch_assoc($result)) {   
$data[] = $row;
}
?>


This returns things like this:
$data[0]['date'], $data[0]['reps']
$data[1]['date'], $data[1]['reps']
etc...

Values are likely to be added or removed so I need to construct a loop of some kind to format it like this,

 line1 = [['{$data[0]['date']}',{$data[0]['reps']}],['{$data[1]['date']}',{$data[1]['reps']}],['{$data[2]['date']}'],{$data[2]['reps']}]];

Can someone point me in the right direction?

Thanks in Advanced

So I'm trying to basically trying to make an "advanced search" function in PHP/mysql that will allow users to search by a number of different options like search by zipcode, username, gender, etc.

Now my question is how do I vary my mysql query so that it searches for all these things based on whatever the user inputs?

For example, the user might need to search zipcode and username but not gender, but in another case, might need to search username and gender, but not zipcode. Obviously I could just do some if/else statements, but that would be increasingly more difficult as there are more fields.

What can I do?

I have an auctions website and I want to create a feature that is similar to ebay in which users will receive an email if one of the auctions they are watching will end in less than 3 hours. I will be using a cron job to call up this page every 15 minutes. When the cron job executes, I would like it to send 1 email per auction to every user that has that auction on their watchlist if the auction will be ending in 3 hours. I don't know whether it needs to be a separate email for each user or one mass email where their emails are hidden.

The 4 tables in my database we are concerned about are "auctions, "users, "watchlists" and "products."

I am trying to use the script phpMailer to execute the code because I heard it was the best one. Anways here is what I have so far. I am missing alot because I had no clue what to do.


<?php
require("class.phpmailer.php");


$holder = mysql_connect("localhost", "user", "password");
mysql_select_db("database", $holder);

// NEED TO FIX THIS // Need to get ALL of the auction id's where the end time is less than 3 hours and the notification hasn't already been sent //
$auctionid = mysql_query("SELECT id FROM auctions WHERE DATE_ADD(NOW(), INTERVAL 3 HOUR) <= end_time AND notification = 0", $holder);

// get the auction title of  EACH of the auctions selected above which is not stored in the auctions table but in the products table..will be used for body of email /// AGAIN, NEED THIS TO GET ME ALL OF THE NAMES OF AUCTIONS THAT ARE ENDING IN 3 HOURS//
$auctiontitle = mysql_query("SELECT name FROM products LEFT JOIN auctions ON auctions.product_id=products.id WHERE auctions.id = $auctionid", $holder);

// PROBABLY NEED TO FIX THIS // Need to get ALL of the email addresses  who have ANY of the above auction ids on their watchlist //
$email = mysql_query("SELECT email FROM users LEFT JOIN watchlists ON users.id=watchlists.user_id WHERE watchlists.auction_id = $auctionid", $holder);

// Update the auctions table. Turn notification to 1 so the notification for that auction can't be sent again // AGAIN NEED THIS FOR ALL OF THE AUCTIONS ENDING IN 3 HOURS //
$query1="UPDATE auctions SET notification = '1' WHERE id = '$auctionid'";
mysql_query($query1) or die(mysql_error());



$mail = new PHPMailer();

$mail->From = "no-reply@domain.com";
$mail->FromName = "Site Name";


// Getting and error message for the foreach but I saw a similar example and this is what I was told to do // NEED THIS TO ADD EACH OF THE EMAIL ADDRESSES INDIVIDUALLY //
foreach ( $email as $recipients ) {
$mail->AddAddress ($recipients);
}
$mail->WordWrap = 50;                                 // set word wrap to 50 characters
$mail->IsHTML(true);                                  // set email format to HTML

$mail->Subject = "Your Watched Auction is Ending Soon";

// Sample Body // WANT TO DISPLAY THE TITLE OF THE AUCTION (NAME OF PRODUCT) FOR THE AUCTION ID USING $auctiontile FROM ABOVE //
$mail->Body    = "Your auction titled $auctiontile is ending soon";

// Same as above //
$mail->AltBody = Your auction titled $auctiontile is ending soon";


if(!$mail->Send())
{
   echo "Message could not be sent.";
   echo "Mailer Error: " . $mail->ErrorInfo;
   exit;
}

echo "Message has been sent";


?>

In querying my database, I'm Selecting nameFirst and nameLast, and it produces a name like Joe Smith.  I'm trying to match a photo with the name.  Right now I'm uploading photos into a folder naming the file (e.g. SmithJoe.jpg). 

For reasons that involve writers being able to upload and access photos, I'm trying to use an image plugin.  When uploading photos, it strips capital letters, so SmithJoe,jpg goes in as smithjoe.jpg, and it's not matching my database query. 

Here is the code I'm working with that works quite well with this one exception:


$query = 'SELECT * FROM wp_playerRank';
$results = mysql_query($query);
while($line = mysql_fetch_assoc($results)) {

if ($line['wpID'] == $wp_tagID)  {

echo '<div class="player">';


// Here is the code that produces the image.  I need to get rid of capital letters for ease of use

echo '<div><img src="/wp-content/uploads/' . $line['nameLast'] . $line['nameFirst'] . '.jpg"></div>';



echo '<div class="playerData">' . $line['height'] . ' ';
if ($line['position'] == 'PG')  {echo 'Point Guard';}
elseif ($line['position'] == 'SG')  {echo 'Shooting Guard';}
elseif ($line['position'] == 'SF')  {echo 'Small Forward';}
elseif ($line['position'] == 'PF')  {echo 'Power Forward';}
elseif ($line['position'] == 'C')  {echo 'Center';}
echo '</div>';
echo '<div class="playerData">' . $line['hschool'] . '</div>';
echo '<div class="playerData">' . $line['summer'] . '</div>';
echo '<div class="playerData">Class of ' .$line['year'] . '</div>';

if ($line['committed'] == 'y') {
echo '<div>
<br>Committed to <strong>'. $line['college'] . '</strong></div>
';}

}
}

I have a PHP Page.  It is doing a lot of site crawling, throughout a lot of loops.
Basically it's getting a massive array together and it's huge.  However, it's so big that PHP times out beyond
belief. I mean there is no error or nothing. Google throws a major error about page not found, IE shows an internet connection error. It really
times out big time. Even though it's not really "A LOT". Any advice on what to do in that situation. I can't show the site/code because of NDA,
but was curious if someone had ever done that much work on a PHP page before to have it do that?

I am using this code in my "create_account.php" script...

Code: [Select]
mail($trimmed['email'], ' Please Activate Your Account', $body, 'From: admin@mywebsite.com <admin@mywebsite.com>');

It is taking anywhere from one to several hours to receive this e-mail in my inbox?!

It used to take seconds, and now takes forever.

I thought it was a Gmail or SPAM issue, but I just did a test on my AT&T paid account and the same issue so far...   

It's awfully hard to do development and testing if I have to wait a half-a-day to get these e-mails?!


Debbie

Hi, There is a feature in cpanel called optimize website and when you go in it there is a field to fill
Compress the specified MIME types. My question is this the correct way to optimize PHP CSS JS did i enter the mime types correctly?

text/html text/plain text/xml/css/application/x-httpd-php/application/x-javascript

Hey so I made a ToDo List as my first php script after starting to learn. I want you guys to just look at my code and tell me where I can improve. I want to drop any bad habits early on so they don't move forward with me. Also tell me where I can optimize and minimize the code so I don't write unnecessary code. Thanks! I'm going to omit things like some html that isn't crucial. You can see it live he  https://taziamoma.com/ToDoList/ .  Also try to do an SQL injection so I can see if I protected myself from it properly. Thank you!

index.php

<?php
include_once("includes/connection.php");

$errors = "";

if (isset($_POST['submit'])) {
	if (empty($_POST['task'])) {
		$errors = "You must fill in the task";
	} else {
		try {
			$task = $_POST['task'];
			$sql = "INSERT INTO todopost (title) VALUES ('$task')";
			$db->exec($sql);;
			header('Location: index.php');
		} catch(PDOException $e) {
			echo $sql. "<br>". $e->getMessage();
		}
		
	}
}

if (isset($_GET['del_task'])) {
	$stmt = $db->prepare("DELETE FROM todopost WHERE id = :id");
	$stmt->execute(array(':id' => $_GET['del_task']));
	header('Location: index.php');
}

<body>
<div class="outside">
	<div class="container">
		<div id="myDIV" class="header">
		  <h2 style="margin:5px">My To Do List</h2>
		  <form method="post" action="index.php" class="input_form">
		  	<?php if (isset($errors)) { ?>
			<p><?php echo $errors; ?></p>
			<?php } ?>
			<input type="text" name="task" class="input">
			<button type="submit" name="submit" class="addBtn">Add</button>
		</form>
		</div>


		<ul id="myUL">
			<?php 
			try {
				$stmt = $db->query('SELECT id, title FROM  todopost ORDER BY id DESC');
				while($row = $stmt->fetch()) { ?>
					<div class="li_cont">
						<li><?php echo $row['title']; ?></li>
						<a class="right" href="index.php?del_task=<?php echo $row['id'] ?>">x</a>
					</div>
				<?php }
			} catch(PDOException $e) {
				echo $e->getMessage();
			}
		  
		  	?>
		</ul>

	</div>
</div>

</body>
</html>

connection.php

<?php 
ob_start();
session_start();

$host = "localhost";
$username = "root";
$password = "";

try {
	$db = new PDO("mysql:host=$host;dbname=tazejesa_todo", $username, $password);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
	echo "Connection failed: ". $e->getMessage();
}

?>

 

I am creating a stream page that updates automatically without the page reloading by reloading a div on the page using a timer. However, I only want it to run the update and fade in/fade out when there is a new status in the table. I tried doing it through PHP getting the number of rows in the table on the page itself and then checking the number of rows on an action page, however, the number of rows didnt update when the div was reloaded. I then tried doing it using jquery/javscript but had the same problem.

How can I acheive this?   Thanks in advance

I am very new to PHP and have tried various techniques but I am getting a 500 error when clicking on the export button to download a csv report. I'm not sure why the previous developer did it this way.

Is there a better why in PHP to make this code better? Willing to understand and learn from an PHP expert.

The database is MYSQL.

            $coursefilterid = $_GET['course'];

            $conn = new mysqli($host, $username, $password, $database);
            if ($conn->connect_error) {
                die("Connection failed: " . $conn->connect_error);
            }

            $sqluserenrolled = "select mdl_user.username, mdl_user_enrolments.userid as enrolleduserid, mdl_enrol.courseid from mdl_user_enrolments
    Inner Join
    mdl_enrol
    on 
    mdl_enrol.id = mdl_user_enrolments.enrolid
    Inner Join
    mdl_user
    on
    mdl_user.id = mdl_user_enrolments.userid
    where  
    mdl_enrol.courseid = '" . $coursefilterid . "'
    order by mdl_user.username
    
    ";

            $queryenrolleduser = mysqli_query($conn, $sqluserenrolled);

            ?>

<html>
<head>
</head>
<body>

    <form method="post"
        action="<?php echo "userlistssiexport.php?id=$coursefilterid"?>">
        <input type="hidden" name="exportcourseid"
            value="<?php echo $coursefilterid;?>"> <input type="hidden"
            name="sessid" value="<?php echo $USER->sesskey;?>"> <input
            class="btn btn-primary" type="submit" name="submit"
            value="<?php echo "Export";?>">
    </form>


<?php
            $noteid = "";
            $cmId = "";
            ?>
          
    <table class="data-table">
        <caption class="title">User info</caption>
        <thead>
            <tr>
                <th>Username</th>
                <th>Firstname</th>
                <th>Lastname</th>
                <th>Email</th>
                <th>Last login</th>
                <th>Createddate</th>
                <th>Position</th>
                <th>Organization</th>
                <th>Certificate Request Date</th>
                <th>Role1</th>
                <th>Role2</th>
                <th>Role3</th>
            </tr>
        </thead>
        <tbody>

    <?php

            while ($row = mysqli_fetch_array($queryenrolleduser)) {

                $enrolleduserid = $row['enrolleduserid'];

                $sql = "select mdl_user.username as username, 
        mdl_user.firstname as firstname,
        mdl_user.lastname as lastname,
        mdl_user.email as email,
       mdl_user.lastlogin as lastaccess,
        mdl_user.timecreated as createddate,
        mdl_user_info_data.data as position 
        
        
        from mdl_user
        Inner Join 
        mdl_user_info_data
        on
        mdl_user_info_data.userid = mdl_user.id
        Inner Join
        mdl_user_info_field
        on 
        mdl_user_info_field.id = mdl_user_info_data.fieldid
        Inner Join
        mdl_user_lastaccess
        on
        mdl_user_lastaccess.userid = mdl_user.id
        
        
        where  mdl_user_info_field.id = 1
        and
        mdl_user.deleted = 0
        and
        mdl_user.id = '" . $enrolleduserid . "'
        group by mdl_user.username
        order by mdl_user.username
    
        ";
                $query = mysqli_query($conn, $sql);

                if (! $query) {
                    die('SQL Error: ' . mysqli_error($conn));
                } else {}

                ?>

        <?php
                $no = 1;
                $total = 0;
                $username = '';
                $coursename = '';
                $content = '';
                $modulename = '';
                $organization = '';
                $userid = '';
                $certificatedate = '';
                $userrole = '';
                $enrolleduserid = '';

                while ($row = mysqli_fetch_array($query)) {
                    // Do something here
                    $username = $row['username'];
                    $coursename = $row['coursename'];
                    $content = $row['content'];
                    $noteid = $row['noteid'];
                    // $notedatetime = date("d/m/y g:i (A)", $row['notedate']);
                    $notedatetime = date("D M j Y G:i A", $row['notedate']);
                    $lastaccess = date("D M j Y G:i A", $row['lastaccess']);
                    $createddate = date("D M j Y G:i A", $row['createddate']);
                    $datafile = $username . $coursename . $content;

                    echo '<tr>      
                    <td>' . $row['username'] . '</td>
                    <td>' . $row['firstname'] . '</td>
                    <td>' . $row['lastname'] . '</td>
                    <td>' . $row['email'] . '</td>                          
                    <td>' . $lastaccess . '</td>                                    
                    <td>' . $createddate . '</td>   
                    <td>' . $row['position'] . '</td>    
                                                    
                ';
                    $modid = $row['contextid'];
                    // Get module name

                    $sqlmodule = "select mdl_user.username as username, 
        mdl_user.firstname as firstname,
        mdl_user.lastname as lastname,
        mdl_user.email as email,
        FROM_UNIXTIME(mdl_user_lastaccess.timeaccess) as lastaccess,
        FROM_UNIXTIME(mdl_user.timecreated) as createddate,
        mdl_user_info_data.data as organization 
        
        
        from mdl_user
        Inner Join 
        mdl_user_info_data
        on
        mdl_user_info_data.userid = mdl_user.id
        Inner Join
        mdl_user_info_field
        on 
        mdl_user_info_field.id = mdl_user_info_data.fieldid
        Inner Join
        mdl_user_lastaccess
        on
        mdl_user_lastaccess.userid = mdl_user.id
        
        
        where  mdl_user_info_field.id = 3
        and
        mdl_user.deleted = 0
        and
        mdl_user.username ='" . $username . "'";

                    $querymodule = mysqli_query($conn, $sqlmodule);
                    ?>              
            <?php
                    $modulenamelink = "";
                    while ($row = mysqli_fetch_array($querymodule)) {

                        $organization = $row['organization'];
                    }

                    echo '<td>' . $organization . '</td>';

                    $sqlCertificateDateuid = "select id from mdl_user where username = '" . $username . "'";

                    $queryCertificateDateuid = mysqli_query($conn, $sqlCertificateDateuid);
                    while ($row = mysqli_fetch_array($queryCertificateDateuid)) {

                        $userid = $row['id'];
                    }

                    $sqlcertificatedate = "select * from mdl_certificateemail where userid = '" . $userid . "'
        and courseid = '" . $coursefilterid . "'";

                    $querycertificaterequestdate = mysqli_query($conn, $sqlcertificatedate);
                    while ($row = mysqli_fetch_array($querycertificaterequestdate)) {

                         $certificatedate = date("D M j Y g:i:s A", $row['unixdatetimecertificate']);
                    }
                    echo '<td>' . $certificatedate . '</td>';

                    $sqluserrole = "select mdl_role_assignments.userid, mdl_role_assignments.roleid,mdl_course_modules.course, mdl_role.shortname as rolename,FROM_UNIXTIME(mdl_role_assignments.timemodified)  from mdl_role_assignments 
        Inner Join
        mdl_context
        on
        mdl_context.id = mdl_role_assignments.contextid
        Inner Join
        mdl_course_modules
        on 
        mdl_course_modules.instance = mdl_context.instanceid
        Inner Join
        mdl_role
        on 
        mdl_role.id = mdl_role_assignments.roleid
        
        where mdl_course_modules.course = '" . $coursefilterid . "'
        and
        mdl_role_assignments.userid = '" . $userid . "'
        group by
        mdl_role_assignments.userid,
        mdl_role_assignments.roleid,
        mdl_course_modules.course,
        mdl_role.shortname,
        mdl_role_assignments.timemodified
        
        order by mdl_role_assignments.timemodified
        ";

                    $userlistrole = '';
                    $queryuserrole = mysqli_query($conn, $sqluserrole);
                    while ($row = mysqli_fetch_array($queryuserrole)) {

                        $userrole = $row['rolename'];
                        $userlistrole = array(
                            array(
                                $userrole
                            )
                        );
                        // echo '<td>'.$userrole.'</td>';
                    }

                    foreach ($userlistrole as $listrole) {

                        // echo $listrole;
                    }

                    $teacherrole = array(
                        'student'
                    );
                    foreach ($teacherrole as $rolename) {
                        $role = $DB->get_record('role', array(
                            'shortname' => $rolename
                        ));
                        $context = get_context_instance(CONTEXT_COURSE, $coursefilterid);
                        // $context = context_course::instance($cid1);
                        $teachers = get_role_users($role->id, $context);

                        foreach ($teachers as $teacher) {

                            $teacherid = $teacher->id;
                            if ($teacherid == $userid) {
                                echo '<td>student</td>';
                            }
                        }
                    }

                    $teacherrole = array(
                        'editingteacher'
                    );
                    foreach ($teacherrole as $rolename) {
                        $role = $DB->get_record('role', array(
                            'shortname' => $rolename
                        ));
                        $context = get_context_instance(CONTEXT_COURSE, $coursefilterid);
                        // $context = context_course::instance($cid1);
                        $teachers = get_role_users($role->id, $context);

                        foreach ($teachers as $teacher) {

                            $teacherid = $teacher->id;
                            if ($teacherid == $userid) {
                                echo '<td></td>';
                                echo '<td>editingteacher</td>';
                            }
                        }
                    }

                    $teacherrole = array(
                        'manager'
                    );
                    foreach ($teacherrole as $rolename) {
                        $role = $DB->get_record('role', array(
                            'shortname' => $rolename
                        ));
                        $context = get_context_instance(CONTEXT_COURSE, $coursefilterid);
                        // $context = context_course::instance($cid1);
                        $teachers = get_role_users($role->id, $context);

                        foreach ($teachers as $teacher) {

                            $teacherid = $teacher->id;
                            if ($teacherid == $userid) {
                                echo '<td>manager</td>';
                            }
                        }
                    }
                    echo '</tr>';
                }
            }

            ?>

    
        </tbody>
        <tfoot>

        </tfoot>
    </table>
</body>
</html>

<?php
    }
}

else {
    header("Location:/index.php");
    // echo "something";
    die();
}
}

else {

header("Location:/index.php");
die();

}

?>