|
PHP - Mysql Query Vs Php Function Not Returning Same Values
Moodle 2.5
*nix server Theme: Essential ---------------------- Hi Folks I have a small mind bender in how php is returning results from a mysql query. There are two issues: 1) The mysql query from phpmyadmin is correct, while the php function that handles the query from the website is not. 2) It takes a very long time to run this query with php, 30 seconds to over a minute. Phpmyadmin is rather quick (Query took 0.0239 seconds). The query is:
SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.shortname AS 'Course', (
CASE
WHEN gi.itemname LIKE '%summative%' THEN 'SUMMATIVE'
WHEN gi.itemname LIKE '%formative 2%' THEN 'FORMATIVE 2'
ELSE 'MC'
END) AS 'Assessment', from_unixtime(gi.timemodified, '%d/%m/%y') AS 'Date',
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 70,'Yes' , 'No') AS Pass, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS 'Mark', ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS 'Mark'
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_grade_grades AS gg ON gg.userid = u.id
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id
AND gi.itemname != 'Attendance'
AND u.firstname LIKE '%03%'
AND gi.itemname LIKE '%mative%'
ORDER BY `Name` , `Surname` , `Course`, `Assessment` ASC
When I run the query in phpmyadmin , it gives back;Name Surname Category Module Course Assessment Date Competent Mark G03 Itumeleng Velmah Mokwa Fundamentals Communications CO1 119472 FORMATIVE 2 07/04/14 Yes 100.00 G03 Itumeleng Velmah Mokwa Fundamentals Communications CO1 119472 SUMMATIVE 07/04/14 Yes 100.00 G03 Itumeleng Velmah Mokwa Fundamentals Communications CO2 119457 FORMATIVE 2 05/04/14 Yes 100.00 G03 Itumeleng Velmah Mokwa Fundamentals Communications CO2 119457 SUMMATIVE 05/04/14 Yes 88.00 G03 Lally Sheila Mokane Fundamentals Communications CO1 119472 FORMATIVE 2 07/04/14 NYC 59.00 G03 Lally Sheila Mokane Fundamentals Communications CO1 119472 SUMMATIVE 07/04/14 Yes 90.00 G03 Lally Sheila Mokane Fundamentals Communications CO2 119457 FORMATIVE 2 05/04/14 Yes 100.00 G03 Lally Sheila Mokane Fundamentals Communications CO2 119457 SUMMATIVE 05/04/14 Yes 98.00And it is perfect so I have no issues with that. Now in php I call;
function print_overview_table_groups($COURSE, $choosegroup, $fromdate, $todate, $numarray)
{
global $DB;
//check data
if(!$choosegroup){
die('No Records To Display.');
}
$thisgroup = $numarray[$choosegroup];
$sql = "SELECT DISTINCT u.firstname AS 'Name' , u.lastname AS 'Surname',
(CASE
WHEN cc.parent = '2' THEN 'Fundamentals'
WHEN cc.parent = '3' THEN 'Core'
WHEN cc.parent = '4' THEN 'Elective'
END) AS 'Category',
cc.name AS 'Module',
c.shortname AS 'Course',
(CASE
WHEN gi.itemname LIKE '%summative%' THEN 'SUMMATIVE'
WHEN gi.itemname LIKE '%formative 2%' THEN 'FORMATIVE 2'
ELSE 'MC'
END) AS 'Assessment', from_unixtime(gi.timemodified, '%d/%m/%y') AS 'Date',
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 70,'Yes' , 'NYC') AS Competent, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS 'Mark'
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_grade_grades AS gg ON gg.userid = u.id
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id
AND gi.itemname != 'Attendance'
AND u.firstname LIKE '%03%'
AND gi.itemname LIKE '%mative%'
ORDER BY `Name` , `Surname` , `Course`, `Assessment` ASC";
return $DB->get_records_sql($sql);
}
This is returned to the index.php page from the function call;$lists = print_overview_table_groups($COURSE, $choosegroup, $fromdate, $todate, $numarray); print "<pre>"; print_r($lists); print "</pre>";The result is baffling...
Array
(
[G03 Itumeleng] => stdClass Object
(
[name] => G03 Itumeleng
[surname] => Mokwa
[category] => Fundamentals
[module] => Communications
[course] => CO2 119457
[assessment] => SUMMATIVE
[date] => 05/04/14
[pass] => Yes
[mark] => 88.00
)
[G03 Lally] => stdClass Object
(
[name] => G03 Lally
[surname] => Mokane
[category] => Fundamentals
[module] => Communications
[course] => CO2 119457
[assessment] => SUMMATIVE
[date] => 05/04/14
[pass] => Yes
[mark] => 98.00
)
)
I only get one record for each student.Can anyone help me solve this? Regards Leon Similar TutorialsI'm having trouble with a simple SELECT query. I just cannot figure out what the problem is... <?php //Include database connection details include 'login/config.php'; //Connect to mysql server $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } //Select database $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } $qry="SELECT * FROM members"; $result = mysqli_query($link, $qry); echo "<table>"; while($row = mysqli_fetch_array($result, MYSQL_ASSOC)) { $getid = ($row['member_ID']); $firstname = ($row['firstname']); $lastname = ($row['lastname']); $email = ($row['email']); echo "<tr><td>$firstname</td><td>$email</td></tr>"; } echo "</table>"; ?> I know I have a connection to the DB, and I know that the query will return values as I have tested in in phpmyadmin. Can anyone see anything obvious I am missing? Thanks Im trying to understand the code below. (taken from an O'REILLY book) Im trying to get my head around this, so can anyone tell me if im thinking about this in the right way; The function (fix_names) borrows the values from $a1,$a2 and $a3 and puts them into $n1,$n2, and $n3. It then processes the strings contained within these newly created variables and then returns the processed values back into $a1,$a2,and $a3? The variables $n1,$n2, and $n3 cannot be echoed as they were created within the function.? Please correct me if im wrong. Code: [Select] <html> <head> </head> <body> <?php $a1 = "EDWARD"; $a2 = "thomas"; $a3 = "wriGHT"; fix_names($a1,$a2,$a3); echo $a1." ".$a2." ".$a3; function fix_names(&$n1,&$n2,&$n3) { $n1 = ucfirst(strtolower($n1)); $n2 = ucfirst(strtolower($n2)); $n3 = ucfirst(strtolower($n3)); } ?> </body> </html> Hi Folks, I'm thinking this is simple, but just can't seem to figure it out. I use similar code for different tables that works fine, but for this table, it doesn't work so I'm thinking this is a MySQL issue. Here's the code: Code: [Select] $sqlmeds = mysql_query("SELECT ALL value FROM test WHERE type like 'media:'"); $meds = mysql_fetch_array($sqlmeds); foreach ($meds as $med){ echo "$med"; } and here is the relevant area of the table with row names "type" and "value" type value . . . media: painting media: works on paper media: collage/assemblage . . . When I run the query to find all 3 values from within phpMyAdmin, no problem, all three are returned, but when I run the exact same query from php, I only get "painting" returned, and oddly, I get an array with two values in it, both of which are "painting". Any ideas??? Thanks!! p.s. I also tried this just in case instead of the foreach, but same result: Code: [Select] for ($i=0; isset($meds[$i]); ++$i) { echo "$meds[$i]"; } Ok, this may be just because I have been programming all day and my mind has gone blank (happens alot), but this is my PHP script: Code: [Select] <?php $query_distinct_item_types = mysql_query("SELECT DISTINCT name FROM item_types"); while($item_types = mysql_fetch_array($query_distinct_item_types)){ $distinct_item_types[] = $item_types['name']; } foreach($distinct_item_types as $item){ $query_item_total = mysql_query("SELECT item_type, SUM(price) WHERE item_type='$item' FROM costs GROUP BY item_type"); while($item_total = mysql_fetch_array($query_total_price)){ $item_totals[] = $item_total['SUM(price)']; } } $item_summery = $item_totals; ?> $item_summery which is = to $item_totals is returning null, any idea's? Hello Guys, I have a question I have the following query $result3 = mysql_query("SELECT * FROM table1 WHERE ad_id='$id2'") or die(mysql_error()); $row3 = mysql_fetch_array( $result3 ); // Grab all the var $features = $row3['features']; I am turning it into an array (comma separated) $feature2 = explode(",", $features); print_r($feature2); ) The result is like so Array ( [0] => 5 [1] => 9 [2] => 13 I want to query the features for just the ids (F_name) are for the features . This query will show all.. I would like to just display the f_name values from the array query. // build and execute the query $sql = "SELECT * FROM features"; $result = mysql_query($sql); // iterate through the results while ($row = mysql_fetch_array($result)) { echo $row['f_name']; echo "<br />"; } Please Advise.. Thanks, Dan I have posted one set of values into my database and it worked fine but when i input another set they wont go inside unless i changes the value of the primary index colum. I want to be able to insert a new values regardless of the primary index value. Any idears...? Hello there, i'm not sure if this goes to mysql, or this section, but since it's PHP based, i would say here. So, here's the thing, i have full set up table that reads from mysql database and display it as table. Everything is working just fine, even "order" buttons that i made, now i would like to have below the table some counts, for example: My table name "canonkickoff" has column named "Prevoz" which contain only Yes and No answers, i would like to see below the table how much "Yes" answers are inside the column. Also i have other column called "VelicinaMajice", which contain 6 different answers ( XS, S, M, L, XL, XXL ), i would like to see (again, below the table) how much of every has been answered. Like: There a XS = 6 S = 1 M = 2 L = 3 And 32 answered with Yes. Could anyone help me out? Here's the full php table: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>CanonKickOff 2012 Tabela.</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <link rel="stylesheet" href="table.css" type="text/css"> </head> <body style="margin: 0 0 0 0;"> <? include("passwd.php"); @$start = $_GET["start"]; if($start =='') $start =0; include("lib.php"); $link = mysql_connect($host,$username,$password); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db($db, $link); if (!$db_selected) { die ("Can't use $db : " . mysql_error()); } //total number of records in the table $orderBy = array('Kompanija', 'id', 'ImePrezime', 'Email', 'Prevoz', 'VelicinaMajice', 'SlazemSe'); $order = 'id'; if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) { $order = $_GET['orderBy']; } $res = mysql_query("SELECT * from `$table` ORDER BY '.$order"); $res2 = mysql_query("SELECT * from `$table` "); @$rows = mysql_num_rows ($res2); $result = mysql_query("SELECT * from `$table` ORDER BY $order limit $start,40"); if (!$result) { die('Invalid query: ' . mysql_error()); } echo 'Sortiraj Po: <br>'; echo '<a href="?orderBy=id">ID:</a> '; echo '<a href="?orderBy=ImePrezime">Ime i Prezime:</a> '; echo '<a href="?orderBy=Kompanija">Kompanija:</a> '; echo '<a href="?orderBy=email">Email adresi:</a> '; echo '<a href="?orderBy=Prevoz">Prevoz:</a> '; echo '<a href="?orderBy=VelicinaMajice">Velicina majice:</a> '; echo '<a href="?orderBy=SlazemSe">Slazu se:</a> '; echo "<p align=center class = 'menu'> Ocitana tabela: $table </p>"; $cols = mysql_num_fields($result); $records = mysql_num_rows ($result); echo "<table align='center' width='1200' >"; echo "<tr bgcolor='BBCCDD' class='menu'>"; for ($i = 0; $i < $cols;$i++) { echo "<td align='center'>".mysql_field_name($result,$i)."</td>"; } echo "</tr>"; while ($row = mysql_fetch_array($result, MYSQL_NUM)) { echo "<tr bgcolor='F6F6F6' class='normal'>"; foreach ($row as $value) { echo "<td align='center'>".$value ."</td>"; } echo "</tr>"; } $end = $start + $records; echo "<tr align = 'center' bgcolor = 'BBCCDD' class='menu'><td colspan=$cols> $start do $end od ukupno: $rows </td></tr>"; echo "<tr align = 'center' class='mylink'><td colspan=$cols> "; if($start != 0) { $prev = $start - 40; echo "<a href='tabela.php?start=$prev'> Prethodna </a> "; } if($start<$rows-10) { $next = $start + 40; echo "<a href = 'tabela.php?start=$next'>Sledeca</a> "; } echo "</td></tr>"; echo "</table>"; ?> </body> </html> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=342695.0 Hi all, I have the following MySQL insert query: Code: [Select] $insert= mysql_query ("INSERT INTO tablename (column1,`".$EXPfields."`) VALUES ('$something','".$EXPvalues."')"); where $EXPfields is an array of table-field-names and $EXPvalues is an array of table-field-values. Now I want to write an equivalent query, but using UPDATE instead of INSERT INTO, but I don't want to write out all the field names/values separately, but again want to use $EXPfields and $EXPvalues. So something like this: Code: [Select] $update = mysql_query ("UPDATE tablename SET (column1,`".$EXPfields."`) = ('$something','".$EXPvalues."') WHERE .... "); Is this possible? If so, what is the proper syntax? Thanks! Hi, Doing a Query SELECT * FROM table WHERE field BETWEEN low_number AND high_number If i have three rows with three numbers: IE: 27, 50, 80. and i run the query above, it returns a result of: 27 and 50. How can i get it to return a result of: 27,50, and 80.?? WHY: age range search: i want to return an age range of people = and between the age of say: 20 and 70 but i want to include the 20 and 70. How can i do this? Hi All, I am working on system where i am storing column names in one table of database. To retrieve value of columns with separation of comma (exp. clol1, col2,col3..). for this i am using one function which i am calling middle of mysql query. but i am not getting fetch result over there. Below is the function i have to fetch column names. Code: [Select] function retrieve_columns() { $brand_id = $_SESSION['SESS_PRODUCT_CODE']; global $columnlist; $columns_query= "Select column_name from columns_list_to_display where brand_id=$brand_id"; $result=mysql_query($columns_query); $row_num=mysql_num_rows($result); $i=0; while ($row = mysql_fetch_array($result)) { $columnlist = ""; $i++; if($i != $row_num) { echo $row['column_name'].", "; } else { echo $row['column_name'].""; } } } I am writting query like as below: Code: [Select] $query = "SELECT " . $columnlist . "FROM $userlead_table LEFT JOIN notes ON $userlead_table.id = notes.leads_id LEFT JOIN lead_status ON $userlead_table.status = lead_status.status_code WHERE ((Date(a.submitted)>='$datefrom' AND Date(a.submitted)<='$dateto')) ORDER BY a.submitted DESC"; Please let me know where i am doing wrong. Hope one of this forum member would have proper solution for it. Thanks in advance. Regards, Jitendra What would be the correct way to close a mysql query? At current the second query below returns results from the 1st query AND the 2nd query The 3rd query returns results from the 1st, 2nd and 3rd query. etc etc. At the moment I get somthing returned along the lines of... QUERY 1 RESULTS Accommodation 1 Accommodation 2 Accommodation 3 QUERY 2 RESULTS Restaurant 1 Restaurant 2 Restaurant 3 Accommodation 1 Accommodation 2 Accommodation 3 QUERY 3 RESULTS Takeaways 1 Takeaways 2 Takeaways 3 Restaurant 1 Restaurant 2 Restaurant 3 Accommodation 1 Accommodation 2 Accommodation 3 Code: [Select] <!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 include($_SERVER['DOCUMENT_ROOT'].'/include/db.php'); ?> <title>Untitled Document</title> <style type="text/css"> <!-- --> </style> <link href="a.css" rel="stylesheet" type="text/css" /> </head><body> <div id="listhold"> <!------------------------------------------------------------------------------------------------------------------------------------------------------> <div class="list"><a href="Placestostay.html">Places To Stay</a><br /> <?php $title ="TITLE GOES HERE"; $query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Accommodation' AND confirmed ='Yes' ORDER BY name"); echo mysql_error(); while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0]; $i = -1; foreach($nt as $value) {$i++; $FileName = str_replace(' ','_',$nt[$i]) . ".php"; $FileUsed = str_replace('_',' ',$nt[$i]); echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>" . $nt[$i] . "</a>" . "<br/>"; $FileHandle = fopen($FileName, 'w') or die("cant open file"); $pageContents = file_get_contents("header.php"); fwrite($FileHandle,"$pageContents");} fclose($FileHandle); ?> </div> <!------------------------------------------------------------------------------------------------------------------------------------------------------> <div class="list"><a href="Eatingout.html">Eating Out</a><br /> <?php $title ="TITLE GOES HERE"; $query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Restaurant' AND confirmed ='Yes' ORDER BY name"); echo mysql_error(); while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0]; $i = -1; foreach($nt as $value) {$i++; $FileName = str_replace(' ','_',$nt[$i]) . ".php"; $FileUsed = str_replace('_',' ',$nt[$i]); echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>" . $nt[$i] . "</a>" . "<br/>"; $FileHandle = fopen($FileName, 'w') or die("cant open file"); $pageContents = file_get_contents("header.php"); fwrite($FileHandle,"$pageContents");} fclose($FileHandle); ?> </div> <!------------------------------------------------------------------------------------------------------------------------------------------------------> <div class="list"><a href="Eatingin.html">Eating In</a><br /> <?php $title ="TITLE GOES HERE"; $query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Takeaways' AND confirmed ='Yes' ORDER BY name"); echo mysql_error(); while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0]; $i = -1; foreach($nt as $value) {$i++; $FileName = str_replace(' ','_',$nt[$i]) . ".php"; $FileUsed = str_replace('_',' ',$nt[$i]); echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>" . $nt[$i] . "</a>" . "<br/>"; $FileHandle = fopen($FileName, 'w') or die("cant open file"); $pageContents = file_get_contents("header.php"); fwrite($FileHandle,"$pageContents");} fclose($FileHandle); ?> </div> <!------------------------------------------------------------------------------SKILLED TRADES BELOW---------------------------------------------------> <div class="list"><a href="Skilledtrades.html">Skilled Trades</a><br/> <?php $title ="TITLE GOES HERE"; $query = mysql_query("SELECT DISTINCT subtype FROM business WHERE type ='Skilled Trades' AND confirmed ='Yes' ORDER BY name"); echo mysql_error(); while($ntx=mysql_fetch_row($query)) $nt[] = $ntx[0]; $i = -1; foreach($nt as $value) {$i++; $FileName = str_replace(' ','_',$nt[$i]) . ".php"; $FileUsed = str_replace('_',' ',$nt[$i]); echo "<a href='" . str_replace(' ','_',$nt[$i]) . ".php?title=$title&subtype=$FileUsed'>" . $nt[$i] . "</a>" . "<br/>"; $FileHandle = fopen($FileName, 'w') or die("cant open file"); $pageContents = file_get_contents("header.php"); fwrite($FileHandle,"$pageContents");} fclose($FileHandle); ?> </div> Nothing is being returned. Why?
<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);
require("../PHPMailer/class.phpmailer.php");
include 'includes.php';
$mail = new PHPMailer;
$subject = $_POST['subject'];
$text = $_POST['newsletterBody'];
$mail->IsSMTP(); // Set mailer to use SMTP
$mail->Host = 'localhost'; // Specify main and backup server
$mail->Port = '465';
$mail->SMTPAuth = true; // Enable SMTP authentication
$mail->Username = '**EMAIL_USERNAME**'; // SMTP username
$mail->Password = '**EMAIL_PASSWORD**';
$mail->SMTPAuth = true;
$mail->SMTPSecure = 'ssl'; // Enable encryption, 'ssl' also accepted
$mail->From = '**EMAIL_ADDRESS**';
$mail->FromName = '**EMAIL_NAME';
$email = getEmail("62", $DBH);
foreach ($email as $newEmail)
{
$addEmail = $newEmail->email;
$mail->AddAddress($addEmail);
$DBH = null;
$addEmail = "";
}
$mail->AddReplyTo('**EMAIL_REPLY_TO**', '**EMAIL_REPLY_TO_NAME**');
$mail->WordWrap = 50; // Set word wrap to 50 characters
$mail->IsHTML(true); // Set email format to HTML
$mail->Subject = $subject;
$mail->Body = $text;
$mail->AltBody = $text;
if(!$mail->Send()) {
echo 'Message could not be sent.';
echo 'Mailer Error: ' . $mail->ErrorInfo;
exit;
}
$insertSql = "INSERT INTO newsletter_log (date, title, body) VALUES (?,?,?)";
$insertParams = array(time(), $subject, $text);
$newsletterAdd = $DBH->prepare($insertSql);
$newsletterAdd->execute($insertParams)
echo 'Message has been sent <a href='newsletter.php'>Return back</a>";
?>
This is the include section that you need
function getEmail($inId, $DBH)
{
if (!empty($inId))
{
$blogstmt = $DBH->prepare("SELECT email_addr FROM newsletter_emails WHERE id = :userId");
$blogstmt->bindParam(":userId", $inId);
$blogstmt->execute();
}
else
{
$blogstmt = $DBH->prepare("SELECT * FROM newsletter_emails");
$blogstmt->execute();
}
$postArray = array();
$results = $blogstmt->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row){
$myPost = new nlEmails($row["id"], $row['email'], $DBH);
array_push($postArray, $myPost);
}
return $postArray;
$DBH = null;
}
class nlEmails {
public $id;
public $email;
function __construct($inId=null, $inEmail=null, $DBH)
{
if(!empty($inId))
{
$this->id = $inId;
}
if(!empty($inEmail))
{
$this->email = $inEmail;
}
}
}
The database is created with the newsletter_emails "id", "first_name", "last_name", "email_addr". Thanks!
Hi all, I am having some trouble with a script.. all works fine except for the calculation part. It isn't adding up, but the reason for this is because when I echoed all the variable it seems that they are blank, the problem is I can't figure out why? <?php # you don't display errors on in-use scripts, do you? ini_set('display_errors',0); error_reporting(E_ALL|E_STRICT); class webPoll { # makes some things more readable later const POLL = true; const VOTES = false; # number of pixels for 1% on display bars public $scale = 2; public $question = ''; public $answers = array(); private $header = '<form class="webPoll" method="post" action="%src%"> <input type="hidden" name="QID" value="%qid%" /> <h4>%question%</h4> <br /> <ul>'; private $center = ''; private $footer = "\n</ul>%button%\n</form>\n"; private $button = '<p class="buttons"><button type="submit">Vote!</button></p>'; private $md5 = ''; /** * --- * Takes an array containing the question and list of answers as an * argument. Creates the HTML for either the poll or the results depending * on if the user has already voted */ public function __construct($params) { $this->question = array_shift($params); $this->answers = $params; $this->md5 = md5($this->question); $this->header = str_replace('%src%', $_SERVER['SCRIPT_NAME'], $this->header); $this->header = str_replace('%qid%', $this->md5, $this->header); $this->header = str_replace('%question%', $this->question, $this->header); # seperate cookie for each individual poll isset($_COOKIE[$this->md5]) ? $this->poll(self::VOTES) : $this->poll(self::POLL); } private function poll($show_poll) { $replace = $show_poll ? $this->button : ''; $this->footer = str_replace('%button%', $replace, $this->footer); # static function doesn't have access to instance variable if(!$show_poll) { $results = webPoll::getData($this->md5); $votes = array_sum($results); } for( $x=0; $x<count($this->answers); $x++ ) { $this->center .= $show_poll ? $this->pollLine($x) : $this->voteLine($this->answers[$x],$results[$x],$votes); } echo $this->header, $this->center, $this->footer; } private function pollLine($x) { isset($this->answers[$x+1]) ? $class = 'bordered' : $class = ''; return " <li class='$class'> <label class='poll_active'> <input type='radio' name='AID' value='$x' /> {$this->answers[$x]} </label> </li> "; } private function voteLine($answer,$result,$votes) { echo "Answer: $answer"; echo "<br />"; echo "Result: $result"; echo "<br />"; echo "Votes: $votes"; echo "<br />"; echo "<br />"; $result = isset($result) ? $result : 0; $percent = round(($result/$votes)*100); $width = $percent * $this->scale; return " <li> <div class='result' style='width:{$width}px;'> </div>{$percent}% <label class='poll_results'> $answer </label> </li> "; } // remainder of script here ?> The ones that are returning blank a $result and $votes $results should be the number of votes a specific option has received while $votes is the total number of votes in the whole poll. My database contains the following data... QID AID votes 685b9628ca340529fa54208c65721dd7 2 205 685b9628ca340529fa54208c65721dd7 0 5 685b9628ca340529fa54208c65721dd7 1 2 It's from the following tutorial - http://net.tutsplus.com/tutorials/ph...poll-with-php/ Can anyone advise me here? Many thanks, Greens85 SELECT i.item_id, i.title, i.price, i.p_and_p, SUM(i.price + i.p_and_p) AS `total_price`, i.listing, i.condition, i.start_date_time, i.listing_duration, CONVERT_TZ(DATE_ADD(i.start_date_time, INTERVAL concat(i.listing_duration) DAY), '+00:00', u.time_zone) AS `end_date_time` FROM items i LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id LEFT JOIN categories c ON c.name = 'test' JOIN users u WHERE u.username = 'Destramic' AND i.start_date_time < NOW() AND DATE_ADD(i.start_date_time, INTERVAL concat(i.listing_duration) DAY) >= NOW()I'm having a problem with my query returning more than 1 rows...I've even copied the row which is returning to see if that'll return 2 rows but it doesn't can anyone explain why this is happening please? Greetings all! I've been working on a project for about a week now and everything had been going fine until this evening. I'm querying a single row from a user information table based on the userID and doing various things based off of the information that is returned. For whatever reason, the query is not returning all of the information anymore. Code follows: Code: [Select] $userToEdit = mysqli_real_escape_string($GLOBALS['link'], $_POST['userToEdit']); $userSQL = "SELECT fName, lName, email, volunteer, staff, admin, active, volunteerID FROM userinfo WHERE userID=" . $userToEdit; $result = mysqli_query($GLOBALS['link'], $userSQL); if (!$result) { $message = 'There was an error retrieving the user information from the database.'; include '../html/error.html.php'; exit(); } $editInfo = mysqli_fetch_assoc($result); The strange part is that the database i'm querying is located on my remote host(GoDaddy). When I run the app from my local Apache server and query the remote DB, everything works fine, however, when I upload the files to my host, not all of the information is being returned. For example, using the print_r() function while on my local host, i get: Code: [Select] Array ( [fName] => Taylor [lName] => Hughes [email] => taylor@gmail.com [volunteer] => 1 [staff] => 0 [admin] => 0 [active] => 1 [volunteerID] => 13 ) But when I execute the app on my remote host, the print_r() function outputs: Code: [Select] Array ( [fName] => Taylor [lName] => Hughes [email] => taylor@gmail.com [volunteer] => [staff] => [admin] => [active] => [volunteerID] => 13 ) I'm not sure why this is happening but it is affecting multiple queries and subsequently multiple forms and functionality in different parts of the application. Any thoughts or suggestions would be greatly appreciated. I've browsed around for about an hour with no luck. I'm writing in PHP 5.3 and the remote MySQL DB is version 5.0 Oh! And if it helps, I just came to the realization that all the items not being returned are of the BIT data type in the tables. Hello all, Trying to figure out how to display database results so that they are numbered (for editing and deletion purposes). I want to be able to edit the message text and update the database information with the UPDATE command, but have not gotten that far yet. Current problem is that I am returning no results. Here is my script: Code: [Select] <!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=iso-8859-1" /> <title>My Profile</title> <link href="loginmodule.css" rel="stylesheet" type="text/css" /> </head> <body> <h1>My Profile </h1> <a href="member-index.php">Home</a> | <a href="member-profile.php">My Profile</a> | Update Posts | <a href="logout.php">Logout</a> <br /><br /> <?php $subject = $_POST['subject']; $message_text = $_POST['message_text']; //Connect to mysql server $link = mysql_connect('XXXXXX', 'XXXXXX', 'XXXXXX'); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } //Select database $db = mysql_select_db('ryan_iframe'); if(!$db) { die("Unable to select database"); } // validate incoming values $subject = (isset($_GET['subject'])) ? (int)$_GET['subject'] : 0; $message_text = (isset($_GET['message_text'])) ? (int)$_GET['message_text'] : 0; ob_start(); $id = $_GET['SUBJECT']; $query = sprintf( " SELECT SUBJECT, MSG_TEXT, UNIX_TIMESTAMP(MSG_DATE) AS MSG_DATE FROM FORUM_MESSAGE WHERE SUBJECT = '$id' ORDER BY MSG_DATE DESC", DB_DATABASE, DB_DATABASE, $subject, $subject); $result = mysql_query($query) or die(mysql_error()); $num = mysql_numrows($result); mysql_close(); $i = 0; while ($i < $num) { $subject = mysql_result($result, $i, "SUBJECT"); $message_text = mysql_result($result, $i, "MSG_TEXT"); echo '<div style="width: 400px;padding:20px;">'; echo '<table border=0 width="400px">'; echo '<tr>'; echo '<td style="vertical-align:top;width:auto;">'; echo 'Date: '; echo '</td>'; echo '<td style="vertical-align:top;width:320px;">'; echo date('F d, Y', $row['MSG_DATE']) . '</td>'; echo '</tr>'; echo '<tr>'; echo '<td style="vertical-align:top;width:auto;">'; echo 'Subject: '; echo '</td>'; echo '<td style="vertical-align:top;width:320px;">'; echo '<div>' . htmlspecialchars($row['SUBJECT']) . '</div>'; echo '</td>'; echo '</tr>'; echo '<tr>'; echo '<td style="vertical-align:top;width:auto;">'; echo 'Message: '; echo '</td>'; echo '<td style="vertical-align:top;width:320px;">'; echo '<div>' . htmlspecialchars($row['MSG_TEXT']) . '</div>'; echo '</td>'; echo '</tr>'; echo '<tr>'; echo '<td style="vertical-align:top;width:auto;">'; echo '</td>'; echo '<td style="vertical-align:top;width:320px;text-align:center;">'; echo '<form method="post">'; echo '<input type="hidden" name="update" value="true" />'; echo '<input type="submit" value="Update" />'; echo ' '; echo '<input type="hidden" name="delete" value="true" />'; echo '<input type="submit" value="Delete" />'; echo '</form>'; echo '</td>'; echo '</tr>'; echo '</table>'; echo '<br />'; echo '<hr />'; echo '</div>'; ++$i; } ?> </body> </html> Thanks in advance. Ryan Originally, I would get both, and unfortunately would inconsistently use both. Then, I wanted more consistently, so configured php.ini to only return objects as I felt accessing them was more concise. And then later, I found myself needing arrays more often, and initially would just typecast them to arrays but eventually my standard was to set the PDO's fetch style to an array. And now I find myself almost always explicitly requesting arrays and occasionally requesting columns or named values. Do you configure ATTR_DEFAULT_FETCH_MODE, and if so to what? PS. Anyone use FETCH_CLASS, FETCH_INTO or FETCH_NUM? If so, what would be a good use case? Dear All Members here is my table data.. (4 Columns/1row in mysql table)
id order_no order_date miles How to split(miles) single column into (state, miles) two columns and output like following 5 columns /4rows in mysql using php code.
(5 Columns in mysql table) id order_no order_date state miles 310 001 02-15-2020 MI 108.53 310 001 02-15-2020 Oh 194.57 310 001 02-15-2020 PA 182.22
310 001 02-15-2020 WA 238.57 ------------------my php code -----------
<?php
if(isset($_POST["add"]))
$miles = explode("\r\n", $_POST["miles"]);
$query = $dbh->prepare($sql);
$lastInsertId = $dbh->lastInsertId(); if($query->execute()) {
$sql = "update tis_invoice set flag='1' where order_no=:order_no"; $query->execute();
} ----------------- my form code ------------------
<?php -- Can any one help how to correct my code..present nothing inserted on table
Thank You Edited February 8, 2020 by karthicbabuCode: [Select] //policies subreport function opportunity_sub_report($business_contact_id){ global $edit, $styles; $sql="Select * from opportunities WHERE op_business_contact_id='$business_contact_id'"; $rs=myload($sql); echo '<table>'; if (count($rs)>0){ // print a header row echo '<tr> <td>Opportunity ID</td> <td>Category</td> <td>Agent</td> <td>Opportunity Name</td> <td>Priority</td> <td>Assigned To</td> <td>Opportunity Type</td> <td>Opporunity Status</td> <td>Opportunity Submission</td> <td>Due Date</td> <td>Contracting Recieved(Y or N)?</td> <td>New Business</td> <td>Notes</td> <td>Business Contact ID</td> </tr>'; // print all data rows foreach ($rs as $r){ echo '<tr> <td>'.display_field('', $p,$r['op_id']).'</td> <td>'.display_field('',$p,$r['op_category']).'</td> <td>'.display_field('', $p,$r['op_agent_id']).'</td> <td>'.display_field('', $p, $r['op_opportunity_name']).'</td> <td>'.display_field('', $p, $r['op_priority']).'</td> <td>'.display_field('', $p, $r['op_assigned_to']).'</td> <td>'.display_field('', $p, $r['op_opportunity_type']).'</td> <td>'.display_field('', $p, $r['op_status']).'</td> <td>'.display_field('', $p, $r['op_submission_date']).'</td> <td>'.display_field('', $p, $r['op_due_date']).'</td> <td>'.display_field('', $p, $r['op_contracting_received_y_or_n']).'</td> <td>'.display_field('', $p, $r['op_new_business_app_received_y_or_n']).'</td> <td>'.display_field('', $p, $r['op_wholesaler_notes']).'</td> <td>'.display_field('', $p, $r['op_business_contact_id']).'</td> </tr>'; } } echo '</table>'; } I'm having some trouble with some code I'm tasked with maintaining. This code works, but it doesn't return the information in a tab. I'm using tabber.js It's being called here Code: [Select] echo ' <tr> <td class="timegrid" colspan=5> <div class="tabber"> <div class="tabbertab" title="Contracting"></div> <div class="tabbertab" title="Licensing"></div> <div class="tabbertab" title="Cases"></div> <div class="tabbertab" title="Commissions"></div> <div class="tabbertab" title="Opportunities">'.opportunity_sub_report($rs[0][bc_business_contact_id]).'</div> <div class="tabbertab" title="Tasks"></div> <div class="tabbertab" title="Calendar"></div> <div class="tabbertab" title="Attachments">'.agent_detail_attachments($rec_id).'</div> </div> </td> </tr>'; echo '</table>'; I've tried changing it from echo to return, but I'm not sure how to keep the foreach loop intact in such a way. I'm trying to create subreports in a web app, that would act like access. Any help would be much appreciated. |
|||||||