PHP - Foreach-ing Through Mysql Query Returning Null :s

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

I have a table called "playlists", and a table called "musics". In the musics table, there is a column playlist_id which references the playlist that each music is contained.   I'm using api calls to display information on the site with JavaScript, so I need to return a JSON.   I need the json with the following structu   [   Playlists: [                   {                     Name: "etc",                     musics: [                                    {                                      name: "teste.mp3"
                                  },                                   {                                      name: "test2.mp3"                                    }                                  ]                      },                      ...                  ] ]   And this is my code:    $query = $con->prepare("SELECT * FROM playlists WHERE user_id = :id");

Hi All,

First time posting here. I've googled the problem, but can't seem to find a response that's the same.

All I want to do is have a list of id numbers and for each id number in the array, submit a MySQL query to retrieve information relating to the id number.

When I execute the code below however, I end up with only the last item in the array being printed in the echo statement.

Any clues?

Thanks,

Code: [Select]
// get array of ids
$ids = getIDs($ids);

// loop through input list
foreach ($ids as &$id) {
getVarDetails($id);
}

function getVarDetails($local)
{
$con = mysql_connect('localhost:3306', 'root', '********');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// set database as Ensembl
mysql_select_db("Ensembl", $con);

$result = mysql_query("SELECT * FROM variations WHERE name = '$local' LIMIT 1");
$row = mysql_fetch_array($result)

while($row = mysql_fetch_array($result))
{
echo $row['name'] . " " . $row['id'];
echo "<br />";
}

// close connection
mysql_close($con);
}

Okay so I created a theme for my website that will also utilize a custom CMS script I developed and its not cooperating with me nicely.

Here's my head of my header file for my template:

Code: [Select]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" <?php language_attributes(); ?>>
<head>
<title><?php bloginfo('name') ?>:&nbsp;<?php bloginfo('description') ?></title>
<link rel="stylesheet" type="text/css" media="all" href="<?php bloginfo( 'stylesheet_url' ); ?>" />
<meta http-equiv="content-type" content='<?php bloginfo("html_type"); ?> charset=<?php bloginfo('charset'); ?>' />
<?php if(is_singular()){ wp_enqueue_script('comment-reply');} ?>
<?php wp_head(); ?>
<?php require ('efedmanager/inc/dbconfig.php'); ?>
</head>
Now on my index page that calls the header file I DO NOT get a error saying that the file could not be located. So that's good news. Here's my dbconfig.php file.

Code: [Select]
<?php

/**
 * @author Jeff Davidson
 * @copyright 2010
 */

// This file contains the database access information and establishes a connection to MySQL and selects the database

// Set the database access information as contstants

DEFINE ('DB_USER', '?');
DEFINE ('DB_PASSWORD', '?');
DEFINE ('DB_HOST', '?');
DEFINE ('DB_NAME', '?');

// Make the database connection

$dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if (!$dbc) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

?>
Now when I go to one of my custom pages like this one it brings up the message:

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in /home/xtremer/public_html/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()'d code on line 38

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /home/xtremer/public_html/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()'d code on line 39

Code: [Select]
<div id="champions" class="content">

<h1 class="pageheading">KOW Champions and Contenders</h1>

<?php
$championsQuery = "
SELECT 
    titles.titleName, 
    titles.shortName,      
    champions.champID,      
    champions.con1ID,      
    champions.con2ID,      
    champions.con3ID,      
    biochamp.shortName AS championshortName,      
    biochamp.characterName AS champion,      
    biocon1.shortName AS con1shortName,      
    biocon1.characterName AS con1,      
    biocon2.shortName AS con2shortName,      
    biocon2.characterName AS con2,      
    biocon3.shortName AS con3shortName,      
    biocon3.characterName AS con3 
FROM      
    champions     
    LEFT JOIN titles AS titles         
        ON titles.ID = champions.titleID      
    LEFT JOIN characters AS biochamp          
        ON champions.champID = biochamp.ID      
    LEFT JOIN characters AS biocon1          
        ON champions.con1ID = biocon1.ID      
    LEFT JOIN characters AS biocon2          
        ON champions.con2ID = biocon2.ID      
    LEFT JOIN characters AS biocon3          
        ON champions.con3ID = biocon3.ID  
WHERE
    titles.statusID = '1'  
ORDER BY      
    titles.ID"; 
$championsResult = mysqli_query($dbc, $championsQuery); 
while ( $row = mysqli_fetch_array ( $championsResult, MYSQLI_ASSOC ) ) {
    $fieldarray=array('titleName','shortName','championID','championshortName','champion','con1ID','con1','con1shortName','con2ID','con2','con2shortName','con3ID','con3','con3shortName');     
    foreach ($fieldarray as $fieldlabel) {         
        ${$fieldlabel} = $row[$fieldlabel];     
    } 
?>

<div id="title"><span class="large">< ?php echo $titleName ?></span>&nbsp;

< ?php
if (file_exists('images/championshots/'.$titleshortName.'/'.$championshortame.'.png')) {
    echo "<img class=champion src=images/championshots/".$shortName."/".$champion.".png alt= />\n";
} else {
echo "<img class=champion src=images/championshots/".$shortName."/".$shortName.".png alt= />\n";
}

if (strlen ($champion) < 1) {
    echo "<span class=medium>Vacant";
} else {
    echo "<span class=medium><a href=/bio?shortName=".$championshortName.">".$champion."</a></span>\n";
    echo "<span class=medium>(Since TBD)</span>";
}
?>

<span class="contender">Contenders</span>

<ul>&nbsp;

< ?php
if ( strlen ($con1) < 1) {
    echo "<li><span class=medium>TBD</span>";
} else {
    echo "<li><a href=/bio?shortName=".$con1shortName.">".$con1."</a></li>\n";
}

if (strlen ($con2) < 1) {
    echo "<li><span class=medium>TBD</span>";
} else {
    echo "<li><a href=/bio?shortName=".$con2shortName.">".$con2."</a></li>\n";
}

if (strlen ($con3) < 1) {
    echo "<li><span class=medium>TBD</span>";
} else {
    echo "<li><a href=/bio?shortName=".$con3shortName.">".$con3."</a></li>\n";
}

?>

</ul>

</div>

< ?php 
}
?>

</div>

Hello   I am having an issue with encoding an array to JSON, my array seems to be formed correctly but when I try and echo the json_encode version I get "null". I echo the JSON error code and it is "0" indicating no issue..    I have read that non UTF-8 characters can be an issue, but this is simple data, no strange characters... but I added the utf8_encode anyway....   Here is my code:

$dataArray = array( 
 
'title' => 'Test Item',
'icon_url' => 'http://url/api/testimage.jpg',
'item_id' => '12345',
'expiration' => 600 
);
 
utf8_encode($dataArray);
 
$responsArray = array( 
 
'success' => '1',
'data' => $dataArray
);
 
 
utf8_encode($responsArray);
 
header('Content-type: application/json');
 
echo print_r($responsArray);
echo json_encode ($responseArray);
echo json_last_error();