PHP - I Want All Values, But Mysql Is Only Returning One

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.00

And 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

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! 

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 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;'>&nbsp;</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

I'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