|
PHP - How Can I Optimize My Php Code For Better Performance
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();
}
?>
Similar TutorialsHey 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();
}
?>
Hi guys,
Having some trouble entering some php into my wordpress site that runs on Optimize Press.
I'm trying to insert this URL/ tracking code into my page as a hyperlink - 'Click Here Now'.
http://www.mysite.co...1_50onRed&sub1=<?php echo $sub1; ?>&sub2=<?php echo $sub2; ?>&sub3=<?php echo $sub3; ?>&sub4=<?php echo $sub4; ?>&sub5=<?php echo $sub5; ?>&lpid=<?php echo $lpid; ?>
This is what I have done so far:
<a href="http://www.mysite.co...;sub1=<?php echo $sub1; ?>&sub2=<?php echo $sub2; ?>&sub3=<?php echo $sub3; ?>&sub4=<?php echo $sub4; ?>&sub5=<?php echo $sub5; ?>&lpid=<?php echo $lpid; ?>">Click Here Now</a>
This does generate a hyperlink, however when I click it, I get: 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 '=' at line 2
I understand for this code to work the page has to be saved in php. I thought wordpress did this automatically?
Can anyone help please? Also, please bear in mind I am using the Optimize Press plugin.
Many thanks in advance!
Tom
Is there a performance or security issue when embedding HTML code to be written to a page using a series of ECHO statements within PHP? Here's two examples: <?php // Some PHP code echo "<p>Hello, World</p>\n"; // More php code ?> versus: <?php> // Some PHP script commands ?> <p>Hello World\n"; <?php // More PHP code ?> Is there a performance issue or potential security flaw that would make the first example any worse/better than the second? (Is one method more "secure" than the other method, I guess is what I'm asking?) I like the ease of just throwing in HTML within a PHP script without having toe escape quote marks, etc. But, I'm a bit concerned about security since whatever technique I use will be incorporated into a "commercial" production website. 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 This topic has been moved to Miscellaneous. http://www.phpfreaks.com/forums/index.php?topic=321105.0 This topic has been moved to Application Design. http://www.phpfreaks.com/forums/index.php?topic=320813.0 Hello everyone... I made point system that ranks users in my app. There are two parts to the system, a primary score and a dynamic score. The primary score is based on how you well you perform and the dynamic score is based on how well your friends perform. Both of these get added together to equal your total score. The problem is that I have a lot of looping occurring which results in about 400 queries being run when I access the page that executes this class. Can this be optimized? What's the best way to do this? I don't want to store the data as I want this presented in real-time. Code: [Select] <?php class edge { public function get_all_users_edge() { $sql = "SELECT id FROM users WHERE status = 3"; $result = mysql_query($sql) or die(mysql_error()); $ids = array(); while (($row = mysql_fetch_assoc($result)) !== false) { $ids[] = $row; } $all_edges = array(); foreach ($ids as $k => $id) { $edge = $this->get_users_edge($id['id']); $all_edges[$k] = $edge; } return $all_edges; } public function get_users_edge($uid) { $users_primary_edge = $this->get_users_primary_edge($uid); $users_dynamic_edge = $this->get_users_dynamic_edge($uid); return $users_primary_edge + $users_dynamic_edge; } public function get_users_primary_edge($uid) { if (active_id($uid) === false) { return false; } $uid = (int)$uid; $sql = "SELECT CHAR_LENGTH(`users`.`credentials`) AS `cred_chars`, CHAR_LENGTH(`users`.`specialties`) AS `spec_chars`, `companies`.`companyvideo` AS `video`, `investor_info`.`accredited` AS `accredited`, ( SELECT COUNT(`user_id`) FROM `blog_posts` WHERE `user_id` = '${uid}' ) AS `post_count`, ( SELECT COUNT(`uid`) FROM `votes` WHERE `uid` = '${uid}' ) AS `vote_sent_count`, `votes_received`.`inv_vote_received_count`, `votes_received`.`pub_vote_received_count`, ( SELECT COUNT(`userid`) FROM `employees` WHERE `userid` = '${uid}' ) AS `joined_count`, `votes2`.`ent_count`, `votes2`.`inv_count`, ( SELECT COUNT(`company_id`) FROM `company_fans` LEFT JOIN `companies` ON `company_fans`.`company_id` = `companies`.`companyid` LEFT JOIN `employees` ON `employees`.`companyid` = `companies`.`companyid` WHERE `userid` = '${uid}' ) AS `company_fans_count`, ( SELECT COUNT(`user_id`) FROM `recommendations` WHERE `user_id` = '${uid}' ) AS `recommendation_count`, ( SELECT COUNT(`partner_id`) FROM `partners` WHERE (`user_id` = '${uid}' OR `friend_id` = '${uid}') AND `approved` = '1' ) AS `associate_count` FROM `users` LEFT JOIN `investor_info` ON `investor_info`.`uid` = `users`.`id` LEFT JOIN `employees` ON `employees`.`userid` = `users`.`id` LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid` LEFT JOIN ( SELECT `employees`.`userid`, SUM( IF( `votes`.`vote_type` = 1, 1, 0 ) ) AS `inv_vote_received_count`, SUM( IF( `votes`.`vote_type` = 0, 1, 0 ) ) AS `pub_vote_received_count` FROM `votes` INNER JOIN `employees` ON `employees`.`companyid` = `votes`.`company_id` WHERE `employees`.`userid` = '${uid}' GROUP BY `employees`.`userid` ) AS `votes_received` ON `votes_received`.`userid` = `users`.`id` LEFT JOIN ( SELECT `beta_keys`.`ref_id`, SUM( IF( `users`.`accounttype` = 1, 1, 0 ) ) AS `ent_count`, SUM( IF( `users`.`accounttype` = 0, 1, 0 ) ) AS `inv_count` FROM `beta_keys` INNER JOIN `users` ON `users`.`id` = `beta_keys`.`userid` WHERE `beta_keys`.`ref_id` = '${uid}' AND `users`.`status` = 3 GROUP BY `users`.`id` ) AS `votes2` ON `votes2`.`ref_id` = `users`.`id` WHERE `users`.`id` = '${uid}'"; $result = mysql_query($sql) or die(mysql_error()); $row = mysql_fetch_assoc($result); $score = 0; if((!empty($row['cred_chars'])) && (!empty($row['spec_chars']))) { $score += 200; } if(!empty($row['video'])) { $score += 50; } if($row['accredited'] == 1) { $score += 100; } $score += (50 * $row['post_count']); $score += (5 * $row['vote_sent_count']); $score += (3 * $row['inv_vote_received_count']); $score += (1 * $row['pub_vote_received_count']); $score += (200 * $row['joined_count']); $score += (40 * $row['ent_count']); $score += (200 * $row['inv_count']); $score += (10 * $row['company_fans_count']); $score += (30 * $row['recommendation_count']); $score += (20 * $row['associate_count']); return $score; } public function get_users_dynamic_edge($uid) { $uid = (int)$uid; $sql = "( SELECT `users`.`id` FROM partners INNER JOIN `users` ON `partners`.`user_id` = `users`.`id` WHERE partners.friend_id = '${uid}' AND `approved` = 1 ) UNION ALL ( SELECT `users`.`id` FROM `partners` INNER JOIN `users` ON `partners`.`friend_id` = `users`.`id` WHERE `partners`.`user_id` = '${uid}' AND `approved` = 1 )"; $result = mysql_query($sql) or die(mysql_error()); $i = 0; $score = 0; while (($row = mysql_fetch_assoc($result)) !== false) { $dynamic_scores[$i] = array( 'uid' => $row['id'], 'score' => $this->get_users_primary_edge($row['id']), ); $i++; } if(!empty($dynamic_scores)) { foreach($dynamic_scores as $k => $dynamic_score) { $score += $dynamic_score['score']; } } return ($score * 0.1); } } ?> We are toying with the idea of using PHP to do sorting instead of our database to reduce load and scale. Basically what we would have is multidimensional arrays that are resturned from the SQL engine and we will need to sort them on a specific key in PHP. We have written this functionality, but are looking to make it faster ANYWAY possible. Do you guys see anyway to speed up this code, without throwing more hardware at the problem yet? Currently on our test server this code takes approx 2.4 - 2.6 seconds to execute. We would love others to try this code and report what it takes them to execute. We are shooting for sub 1 second time. Code: [Select] $array = array(); $temp = array(); for($i = 0; $i < 100000; $i++) { $temp['id'] = rand(10000, 99999); $temp['history_id'] = rand(100000000, 999999999); $array[] = $temp; } //// // Start timer //// $mtime = microtime(); $mtime = explode(" ",$mtime); $mtime = $mtime[1] + $mtime[0]; $starttime = $mtime; //// // Actual sort //// usort($array, function($a, $b) { //Compare numbers if ($a['history_id'] == $b['history_id']) { return 0; } return ($a['history_id'] < $b['history_id']) ? -1 : 1; }); //// // End timer //// $mtime = microtime(); $mtime = explode(" ",$mtime); $mtime = $mtime[1] + $mtime[0]; $endtime = $mtime; $totaltime = ($endtime - $starttime); //// // Output execution time //// echo "Executed in: <strong>". $totaltime ."</strong> seconds."; Hi sorry for my bad English I have this queries but when I using num_queries show over 40 for this code!! how can I optimize it for best performance category table is : ".$prefix."_modules_categories item table is : ".$prefix."_modules $display = $openclose; if ($display == 0) { $show = "none"; $catimg = "<img src=\"$closedimg\" align=\"absmiddle\" border=\"0\">"; } else { $show = "show"; $catimg = "<img src=\"$openimg\" align=\"absmiddle\" border=\"0\">"; } //Categories $result = $db->sql_query("select mcid, mcname FROM ".$prefix."_modules_categories WHERE visible='1' ORDER BY mcposition"); //$coin=0; global $multilingual, $currentlang; while ($row = $db->sql_fetchrow($result)) { $mcid = $row['mcid']; $mcname = $row['mcname']; //$coin++; $mcname1 = $mcname; if(strlen($mcname1) == 0){ if ($multilingual == 1) { include("modules/languages/lang-".$currentlang.".php"); $mcname = "$mcnamed[$mcid]"; } else { include("modules/languages/lang-english.php"); $mcname = "$mcnamed[$mcid]"; } }else{ $mcname = "$mcname1"; } $content .= "</div><table border=0 cellpadding='1' cellspacing=1><tr><td width='16'><a id=\"x".$mcname."\" href=\"javascript:Toggle('".$mcname."','$align');\">$catimg</a></td><td><b>".$mcname."</b></table>\n"; $content .= "<div id=\"".$mcname."\" style=\"display: $show; margin-left: 1em;\">\n"; $result2 = $db->sql_query("SELECT title, custom_title, view, url FROM " . $prefix . "_modules WHERE active='1' AND inmenu='1' AND title!='$def_module' AND mcid='$mcid' ORDER BY custom_title ASC"); while ($row = $db->sql_fetchrow($result2)) { $title = $row['title']; $custom_title = $row['custom_title']; $view = $row['view']; $url = $row['url']; $arraymodules[] = "$title:$custom_title:$view:$url"; $m_title = $title; $m_title2 = ereg_replace("_", " ", $m_title); include("modules/".$title."/language/lang-".$currentlang.".php"); if ($custom_title != ""){$m_title2 = $custom_title;}else{ if(strlen($mudulename) != 0){ $custom_title = "$mudulename"; } $m_title2 = $custom_title; } if ($m_title != $main_module) { $showit = 0; switch($view){ case 0: // All users case $showit = 10; break; case 1: // Registered user access if (is_user($user) || is_admin($admin)) $showit = 1; break; case 2: // Admin only access if (is_admin($admin)) $showit = 2; break; default: // Subscriber only access if (is_user($user) || paid()) $showit = 3; break; } global $db , $prefix; $rowz = $db->sql_fetchrow($db->sql_query("SELECT gtset FROM ".$prefix."_config")); $gtset = intval($rowz['gtset']); if ($showit != 0) { $adds = $_SERVER['SERVER_NAME']; if ($url != "" AND $gtset != 0) { $content .= "<table border=0 cellpadding='1' cellspacing=1><tr><td width='16'><img src=\"$arrowimg\" align=\"absmiddle\" border=\"0\"></td><td><a href=\"$url\" title=\"$m_title2\" onMouseOver=\"self.status=' $m_title2 ';return true\" onmouseout=\"self.status='';return true\">$m_title2</a></td></tr></table>\n"; } else { $content .= "<table border=0 cellpadding='1' cellspacing=1><tr><td width='16'><img src=\"$arrowimg\" align=\"absmiddle\" border=\"0\"></td><td><a href=\"modules.php?name=$m_title\" title=\"$m_title2\" onMouseOver=\"self.status=' $m_title2 ';return true\" onmouseout=\"self.status='';return true\">$m_title2</a></td></tr></table>\n"; } } } $mudulename = ""; } } $content .= "</div>"; $db->sql_freeresult($result); $db->sql_freeresult($result1); I have a script to write out the number of hits each of my websites got each day of the week since I don't trust Google Analytics spying on my websites. The problem is that I have to query each day individually and count how many instances there are with that domain. Since I setup my database give me the time, location, demographics, etc. on each line I'm not sure how to optimize this further. Help would be greatly appreciated. Code: [Select] echo '<table><tr><td>Domain</td>'; foreach($timearray as $t){ echo '<td>'.$t.'</td>'; } echo '</tr>'; $i = 0; foreach($urlarray as $u){ $today = date("Y-m-d"); $yesterday = date("Y-m-d", mktime(0,0,0,date("m") , date("d")-1, date("Y"))); $lastmonth = date("Y-m-d", mktime(0,0,0,date("m")-1 , date("d")-1, date("Y"))); $twoweeks = date("Y-m-d", mktime(0,0,0,date("m") , date("d")-15, date("Y"))); $result3 = mysql_query("SELECT refer,Count(*) as count FROM `approved` WHERE time>'$lastmonth 23:59:59' AND time<'$today' AND refer LIKE '%$u%'"); $row3 = mysql_fetch_array($result3); $result4 = mysql_query("SELECT refer,Count(*) as count FROM `approved` WHERE time>'$twoweeks 23:59:59' AND time<'$today' AND refer LIKE '%$u%'"); $row4 = mysql_fetch_array($result4); if($row3['count']==0){echo "<tr style='background:red'>";}elseif($row4['count']>0){echo "<tr style='background:green'>";}elseif($i % 2){echo "<tr style='background:#CCC'>";}else{echo "<tr style='background:#FFF'>";} echo '<td>'.$u.' '.$row3['count'].' '.$row4['count'].'</td>'; foreach($timearray as $t){ $result = ''; $result = mysql_query("SELECT COUNT(*) AS count FROM `approved` WHERE time LIKE '$t%' AND refer LIKE '%$u%'"); while($row = mysql_fetch_array($result)){ echo '<td>'.$row['count'].'</td>'; } } echo '</tr>'; $i++; } echo '</tr></table>'; Im going to use a large array of arrays, each of one having a lot of values and some sub arrays. My question is... is faster to use arrays or is better to have a object to acces using methods and all? i suppose objects are slower... Also i was planing in use arrays with string keys in nearly all places, normally these are slower, but in php hashes and arrays are the same tipe so i dont know... A few questions that if I was more knowledgeable about MySQL (such as the query log that I've heard of). - Does mysqli::multi_query() make multiple requests to the server or just one? - If it is just splitting the queries on the semicolon and then making multiple requests, is it still faster than looping and querying in PHP? - Does it take less memory than, lets say, mysql_query() foreach query? - Is it faster than, lets say again, mysql_query() looped? The last 2 I can probably just benchmark myself but I'm hoping someone will know off had. Background (blah blah blah) I'm working on a lean rapid development framework for myself (and maybe others eventually) to use. Much like Cake and other frameworks it does a lot of queries, often times more then you need. The sacrifice of course is performance VS ease of development, but I'm trying to not make a martyr out of my framework :-) I've observed on many occasions that simple queries can take more time than a complex query on seemingly random occasions. My understanding is that this is because of the connection, not the real amount of time it took for the server to query. I figure if I avoid multiple requests to the server I will cut down on the time everything takes. My theory is based on the same concept of combining all your CSS and JS files into single files to avoid dozens of HTTP requests which slow down page load time. Alright guys, I see people recommending prepared statements (PDO/MySQLi) and saying that they are way to go these days. However upon doing a bit more research, I've found that prepared statements, PDO in particular, is lacking in terms of performance, especially using SELECT statements. Now I'm starting a new project, which is basically a text based game with lots of queries and DB interaction, so I'm really interested in knowing what's the best approach for me. I was leaning towards PDO but I don't want it crawling my server under heavy load. I appreciate any advices or first hand experiences on this. I am wondering since in php when you write string in " " quotes php will look if there is any variable and if it is it will read that variable and replace variable name with that value inside the string. However when i use ' ' quotes php will not look for any variables inside that string. So my question is when you write a really big application is it good to always use ' ' quotes when you can instead of " " ones. Does that have an impact on performance. Thanks I have the following simple code to test against collision on a primary key I am creating: Code: [Select] $machine_ids = array(); for($i = 0; $i < 100000; $i++) { //Generate machine id returns a 15 character alphanumeric string $mid = Functions::generate_machine_id(); if(in_array($mid, $machine_ids)) { die("Collision!"); } else { $machine_ids[] = $mid; } } die("Success!"); Any idea why this is taking minutes to run? Anyway to speed it up? I want to do a test while refreshing the page 100 times with 500 rows in a table, and then without 500 rows in a table and with different kind of php code, i need to do some type of testing to get results back in to show which way is faster for mysql/php. Any idea how to do this any scripts out there or a built in php/mysql function? Thanks Do I really have to care about the amount of spaces/tabs I have in my program? Is my code good? Example: Code: [Select] class SportListout{ public $start; public $db; private $junk = 'This is junk data. Unable to find it \'s parents. It is recommended that you delete this data.'; function __construct($db){ $this -> db = $db; $this -> setStartingParent(); } function searchListout($sp){ $notif = 'There are no search results that match that criteria.'; if(strlen($sp) > 0){ if(strlen($sp) < 3){ This is my common text format. Is this ok or should I not make spaces in between functions? Hello,
Any ideas why server resources not exhausted on the code below, when my.cnf and php.ini are both set to use half the resources each? PHP & MySQL will both utilize all allotted resources on other scripts, so it's not a tuning problem. There is no script-side tuning.
The bottleneck: pdo select as shown below
foreach($bigList as $listObject) {
$sql = $dbl->prepare("SELECT * FROM fewMillionRows WHERE indexedCol1=:indexedCol1 AND indexedCol2=:indexedCol2 AND indexedCol3=:indexedCol3 AND indexedCol4=:indexedCol4 LIMIT 1");
$sql->execute($preparedValues);
$return = $sql->fetchAll(PDO::FETCH_ASSOC);
}
On a dedicated server with 8GB RAM, the server uses >5% cpu/ram but takes a long time to finish the script.
Second Question
What are some alternative designs?
Because the column values all happen to be alphanumeric, I could select the entire table and store it in an array. Accessing the keys like so: indexedCol1indexedCol2indexedCol3indexedCol4.
Results:
MyISAM - Select Whole Table: 30 seconds
MyISAM – Select Individual Rows (10k times) – 68 seconds
InnoDB – Select Whole Table: 30 seconds
InnoDB – Select Individual Rows (10k times) – 131 seconds
I am surprised it takes so long to select a whole table. The server resources use 1% for about 20 seconds, then cpu/ram jump to 30%+ for about 10 seconds. This is still drastically faster than individual selects.
In this instance, $bigList is over 500k items. At 68 seconds per 10k rows it’s absurdly long. Building an array with key/values is the only realistic way I currently know of, but I suspect there is a much better way of doing this.
As far as I know, I cannot do a select like so: SELECT * FROM t1 WHERE (column1,column2,column3) IN ((val1, val2,val3), (val4, val5,val6))
There is no way to determine whether a row was found for each entry as far as I know.
Hi All, I have written the following to validate my dynamic includes, one question is i will be using sessions to control user access to certain pages. Obviously the session_start() has to go into my index.php file. Can anyone see any problems with this or my dynamic include validation code. My page varilable is populated using the mod_rewirte function in appache. <?PHP include('inc/settings.inc.php'); if(isset($_GET['page'])) { //remove slashes $page = stripslashes($_GET['page']); //rebuild the extension and file name $filename = 'lib/'.$page.'.php'; //Check to see if the file exists in lib if (file_exists($filename)) { //Dynamic Switch $allowed = array( array("test", "New Customers"), array("home", "Home Page"), ); $iffed = false; $get_section = $_GET['page']; //Create a dynamic switch to check for files being in my allowed array foreach($allowed as $rd) { if($get_section == $rd[0]) { $iffed = $rd; $content = $filename; foreach($rd as $value) { $page_title = $value; } } } if($iffed === false) { //File is not in my include list. die( "Page does not pass the validated inclusion list." ); } } else { //Page does not exist in my lib folder. die('Page does not exist, please contact the administrator.'); } } else { // If no page is requested then default home. $filename = 'lib/home.php'; $content = '1'; $page_title = 'Home'; } ?> Thanks in advance. Sam |
|||||||