PHP - Retrieve Cirtain Values From Akey And Value Query

Am a newbie in php. Since I can't insert values to the database with respect to a user Id or with any other token using WHERE clause.

I.e "INSERT INTO receipts(date) VALUES(example) where id="**....."

If I need to fetch several values of column for a particular user, how do I go about it? Thank you!!!

Dear All Members

here is my table data..

(4 Columns/1row in mysql table)

id                 order_no               order_date                  miles
310                001                   02-15-2020                MI,108.53
                                                                                     Oh,194.57
                                                                                     PA,182.22
                                                                                     WA,238.57

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
//invoice.php  
include('../includes/config.php');

if(isset($_POST["add"]))
{
$order_no=$_GET['order_no'];
$order_date=$_GET['order_date'];
$miles=$_POST['miles'];

$miles = explode("\r\n", $_POST["miles"]);
$newdata = "'" . implode("','", $_POST["miles"]) . "'";
$sql ="INSERT INTO tble_states_miles(order_no,order_date, miles) VALUES(:order_no, :order_date, :newdata)";

$query = $dbh->prepare($sql);
$query-> bindParam(':order_no', $order_no, PDO::PARAM_STR);
$query-> bindParam(':order_date', $order_date, PDO::PARAM_STR);
$query-> bindParam(':miles', $newdata, PDO::PARAM_STR);

$lastInsertId = $dbh->lastInsertId();
if($lastInsertId)
{
echo "<script type='text/javascript'>alert('Added Sucessfull!');</script>";
echo "<script type='text/javascript'> document.location = 'ifta_miles_calculationlist_added.php'; </script>";
}
else 
{
$error="Something went wrong. Please try again";
}

if($query->execute()) {

$sql = "update tis_invoice set flag='1' where order_no=:order_no";
$query = $dbh->prepare($sql);
$query-> bindParam(':order_no', $order_no, PDO::PARAM_STR);

$query->execute();

}
}
?>

----------------- my form code ------------------

 

 <?php
                   $order_no=$_GET['order_no'];
        $sql = "SELECT * from tis_invoice where order_no = :order_no";
        $query = $dbh -> prepare($sql);
        $query->execute(
  array(
   ':order_no'       =>  $_GET["order_no"]
  )
 );
        $result = $query->fetchAll();
        foreach($result as $row)
?>
    
                    <form method="post">
                        <table align="center" style="width: 55%">
                        <tr><td>Invoice Number</td></tr>
                        <tr><td><input type="text" name="order_no" value="<?php echo $row["order_no"]; ?>" readonly=""></td></tr>
                        <tr><td>Invoice Date</td></tr>
                        <tr><td><input type="text" name="order_no" value="<?php echo $row["order_date"]; ?>" readonly=""></td></tr>
                        <tr><td>
                               <textarea name="miles" class="form-control" rows="10" placeholder="" required></textarea>
                           </td></tr>
                           <tr><td>
                         
                            <input type="submit" name="add" class="btn btn-primary" value="Add" />
                       </td></tr></table>
                    </form>

-- Can any one help how to correct my code..present nothing inserted on table

 

Thank You

Edited February 8, 2020 by karthicbabu

Hi, after following lots of advice and changing to MySqli I am running into a few probs. This is me just probably missing something stupid, I know what I want, but can't figure out what query I should use and where I should place it. All the queries I have tried have failed.   I just need a query that gets the $current_stored_password from the password field on the database, to confirm the last check

elseif 
	($current_password !== $current_stored_password) {
	include 'includes/overall/header.php';
	echo $current_password . ' AND ' . $_POST['current_password'] . ' Password and password again do not match';
	include 'includes/overall/header.php';

}

Here is the whole script.

<?php
session_start();
error_reporting(0);
//ini_set('display_errors', '1');

require( 'database.php' );

$username = $_SESSION['loggedinuser'];
$current_stored_password = $_SESSION['password'];
$current_password = $_POST['current_password'];
$password = mysqli_real_escape_string($con, md5( $_POST['password']));
$password_again = mysqli_real_escape_string($con, md5( $_POST['password_again']));


// Run checks

if	(isset($_POST['current_password'], $_POST['password'], $_POST['password_again'])) {
	
if( strlen( $_POST['current_password'] ) < 8 )
    {
        include('includes/overall/header.php');
        echo "Password Must Be 8 or More Characters.";
        include('includes/overall/footer.php');
}
elseif( strlen( $_POST['password'] ) < 8 )
    {
        include('includes/overall/header.php');
        echo "Password Must Be 8 or More Characters.";
        include('includes/overall/footer.php');
    }
elseif 
		( strlen( $_POST['password_again'] ) < 8 )
    {
        include('includes/overall/header.php');
        echo "Password Must Be 8 or More Characters.";
        include('includes/overall/footer.php');
    }
elseif 
	($password !== $password_again) {
	include 'includes/overall/header.php';
	echo ' Password and password again do not match';
	include 'includes/overall/header.php';
}
elseif 
	($current_password !== $current_stored_password) {
	include 'includes/overall/header.php';
	echo $current_password . ' AND ' . $_POST['current_password'] . ' Password and password again do not match';
	include 'includes/overall/header.php';

} else {

// Define a query to run 
$query = "UPDATE `user` SET `password` = '$password' WHERE `username` = '$username'"; 

// Query the database 
$result = mysqli_query($con,$query); 

// Check if the query failed 
if( !$result ) 
{ 
   die('There was a problem executing the query ('.$query.'):<br>('.mysqli_errno($con).') '.mysqli_error($con)); 
} 

else {

include 'includes/overall/header.php';
	echo 'Password has been changed';
include 'includes/overall/footer.php';
}    
}
}

// Close the connection 
mysqli_close($con); 
?> 

At the moment the message displayed when the form is submitted is

	echo $current_password . ' AND ' . $_POST['current_password'] . ' Password and password again do not match';
 

How do I retrieve the password from the database to compare against the current password entered by the user? Any help is much appreciated.   PS. Yes I know I have repeated code and that md5 is not secure, but I am just building onto a template I got and will be making changes to shorten the code and secure the password soon    

I have mysql select query shown below retrieving data from a database:

$sql = "
SELECT 
	student, sum( test_score ) as score
FROM 
	test
INNER JOIN 
	level 
ON 
	test.level_id = level.level_id
GROUP BY 
	student"
$result = $mysqli->query($sql, MYSQLI_STORE_RESULT);
if($result)
{
      while ($row = $result->fetch_array(MYSQLI_ASSOC))
       {
	    var_dump($row);
       }
}

On inspection of the retrieved array with var_dump(), i have these result below:

array (size=2)
  'John' => string 'English' (length=2)
  'Score' => string '20' (length=3)

array (size=2)
  'Mary' => string 'Math' (length=3)
  'Score' => string '35' (length=3)

array (size=2)
  'Samuel' => string 'Physics' (length=3)
  'Score' => string '5' (length=3)

 How do I get at and print out the highest score from this set of array values within this context.
 I tried this echo max($row['count']) but i understandably got this feedback:
Warning: max(): When only one parameter is given, it must be an array   Thanks.

Hi,

I've can use the following URL to filter the query below:

www.mysite.com?levels=PreK
...this will show me alll the PreK members.

www.mysite.com?levels=Elem
...this will show me all the Elem members.

How do I filter it to show both me BOTH the PreK and Elem members?

Do I need to change the query?

        WHERE ((user_info.Type LIKE '$Type%')
                and ((user_info.snr_First LIKE '%$name%')
                or (user_info.snr_Last LIKE '%$name%'))
      and (user_info.snr_Home_phone LIKE '$snr_Home_phone%')
    and (user_info.snre_eMail LIKE '$snre_eMail%')
    and ((user_info.PreK LIKE '$levels%')
or (user_info.Elem LIKE '$levels%')
or (user_info.MS LIKE '$levels%')
or (user_info.HS LIKE '$levels%')
or (user_info.College LIKE '$levels%')
        or (user_info.Admin LIKE '$levels%')
                or (user_info.Prvt LIKE '$levels%')))
         ORDER BY user_info.$sort LIMIT $eu, $limit ";
$result = mysql_query($query);

Thanks.

~Wayne

Hi all

I have a form which takes details from drop down menus. But what I need is a wild card if someone leaves the selected option value as "all" it adjusts the SQL query to not search that cell so it brings all entries without filtering.

My form looks like this:

Code: [Select]
<select name="area" id="area">
       <option value="all">--All areas--</option>
      <?php 
   $getareas = mysql_query (" SELECT * FROM `areas` ORDER by name ASC");
   while ($showareas = mysql_fetch_array($getareas)) { ?>
      <option value="<?php echo $showareas['id']; ?>"><?php echo $showareas['name']; ?></option>
      <?php } ?>
    </select>

My SQL query looks lie this:

Code: [Select]
$getads = mysql_query(" SELECT * FROM adverts WHERE categoryid = 1 AND areaid = '".$searchedarea."' AND makeid = '".$searchedmake."' AND modelid = '".$searchedmodel."' AND berth = '".$searchedberth."'

AND live = 1 AND approved = 1 AND paid = 1 AND dateexpired >= '".$todaysdate."' ORDER BY seller ASC , type ASC, id ASC") or die (mysql_error());

So if they select 'all" in the drop down field, the SQL query doesn't have 'AND areaid = '".$searchedarea."'' in it?

Is this possible, or can I add a wildcard into the SQL query?

Many thanks

Pete

I have this form

<form action="" method="get" name="size"> Størrelse <hr><label><input type="radio" value="9" name="size" onchange="this.form.submit()">92 (2år)</label>


<label><input type="checkbox" value="147" name="size" onchange="this.form.submit()">68 (6-9 mdr.)
</label><label><input type="checkbox" value="150" name="size" onchange="this.form.submit()">86 (18-24 mdr.)</label>
<input type="checkbox" value="149" name="size" onchange="this.form.submit()">80 (12-18 mdr.)</label>
<label><input type="checkbox" value="148" name="size" onchange="this.form.submit()">74 (9-12 mdr.)</label>


</form>

I want to make it so that when the user clicks one checkbox for example size 68 and then checks size 80 I want both sizes to be in the URL query string. But every time the user clicks a new checkbox, the query string changes accordingly. Maybe with a separator like ?size=4:23 and then I will handle it with the DB stuff.   Please tell me how to add multiple elements in the query string with this form, thank you!
Edited by Stefany93, 21 May 2014 - 08:03 AM.

So my question is the following:

i have a form where the user can search for multiple terms (green, red, black, orange etc) the terms are seperated by a coma when the form is submitted and then those terms are placed in an array.

I then make for values matching those terms, using the following script:

Code: [Select]

//$tagArr = array(red, black);

$makeQuery = mysql_query("SELECT * FROM points_tags WHERE tag_id IN('".implode("','", $tagArr)."')");
$num_results = mysql_num_rows($makeQuery);


The table "point_tags" containts two columns one that has that holds a value id and the other holds the term, a value can have multiple terms so it looks kinda like this:

tag_id term_id car red car black boat orange plane black
You get the idea right?

Anyway the current query is grabbing all values matching those terms in. However what i want to get done is grab the values
that have those exact terms only, so in theory something similar to the following:

Code: [Select]

$makeQuery = mysql_query("SELECT * FROM points_tags WHERE tag_id="term1' AND tag_id='term2'");


Where if i search for red and black i only get car returned because car has both the terms red and black, while plane has black it does not contain
the term red. Does that make sense?

I've been trying to figuring this out myself for a while already and i just can't think of a way to do this. I hope someone here can help me out

EDIT:::

What i meant to accomplish with the second query example was that i need to accomplish that same type of task (or using OR ) but while keeping the values in an array, so again in theory that (tag_id='term1' AND tag_id='term2') would just keep incrementing based on the number of values i'm searching for, so if there is only one value been searched for then there would not be any "AND".

The AND or OR would get added if there are more than one value.

Make sense what i'm talking about?
 

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 use AJAX method to get the value for `$month` from the year-month selection in my page.  The year-month is pass correct correctly when it gets into my PHP code. But i am not sure how do i get the value and use it for my sql condition. 

I'm trying to plot a graph using Chart JS according to the year-month selection from the user. when the user select "2021-03" all the order that is made in the month of March will be plotted into the graph. There's no data shown in my page now and i do not know how can i query it. Can anyone enlighten me how can i do it? Because i do not know what mistake did i made here. Any kind of explanation will be appreciated. Thanks!

 

This is the code that i use for year-month selection 

<div class="col-2 my-auto">
                <input type="month" id="month" class="form-control" value="<?php echo date('Y-m'); ?>">
              </div>

               <div class="col-2 my-auto">
                <button type="button" class="btn btn-info" onclick="loadchart();">
                  <i class="nc-icon nc-zoom-split"></i>
                </button>
              </div>

This is the AJAX method

 <script type="text/javascript">
function loadchart()
{
 $('#spin1').show();
 var month= $('#month').val();
  var data = {    
    month: month,
  }
  $.ajax({
    method:"GET",
    data:data,
    url:"includes/loadchart.php",
    success:function(data)
    {
        $('#loadchart').html(data);
    }
  });

}

this is my loadchart.php

<?php
include '../session.php';
if(isset($_GET['month']))
{
    $months = $_GET['month'];
?>
<?php
  $days = array();
  $gross_sales = array();
  $average_sales = array();
  $type = CAL_GREGORIAN;
  $month = date('n'); 
  $year = date('Y'); 
  $day_count = cal_days_in_month($type, $month, $year); 
  for ($i = 1; $i <= $day_count; $i++) {
      $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH('".$months."') AND YEAR(order_datetime) = YEAR('".$months."') AND order_status = 5 ";
    $query = $conn->query($sql);
    $total = $query->num_rows;
    $row = $query->fetch_assoc();
    if($row['purchase_price'] != 0)
    {      
      $gross_sales[] = $row['purchase_price'];
    }
      
    else if ($row['purchase_price'] == 0 && $i <= date('d')) {
      $gross_sales[] = 0;
    }

    $average_sales[] =  $row['purchase_price'] / $day_count;

    $day =  $i.'/'.$month; 
    array_push($days, $day);
  }

  $days = json_encode($days);
  $daily_gross_sales = json_encode($gross_sales);
  $average_gross_sales =  json_encode($average_sales);
?>
  <script>
    const colors = {
      colorcode: {
        fill: '#51cbce',
        stroke: '#51cbce',
      },
    };
    var ctx2 = document.getElementById("attChart").getContext("2d");
    const attChart = new Chart(ctx2, {
      type: 'line',
      data: {
        labels: <?php echo $days; ?>,
        datasets: [{
          label: "Gross Sales: RM",
          fill: true,
          pointBackgroundColor: colors.colorcode.stroke,
          borderColor: colors.colorcode.stroke,
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $daily_gross_sales; ?>,
          showLine: true
        },
        {
          label: "Average Sales: RM",
          fill: true,
          pointBackgroundColor:   '#FF0000',
          borderColor: ' #FF0000 ',
          pointHighlightStroke: '#FF0000',
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $average_gross_sales; ?>,
          showLine: true
    }
        ]
      }
    <?php   
}
?>

I do not know why my query from the loadchart.php is incorrect. Please help me with this advice me on this issue thanks!

  $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH('".$months."') AND YEAR(order_datetime) = YEAR('".$months."') AND order_status = 5 ";

 

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> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    }
     echo "</td></tr>";
     echo "</table>";
 ?>
</body>

</html>

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

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