Need help with Mysqli query

Hi Experts
I have written a mysqli query for a chart (chartjs).
http://www.chartjs.org/docs/

I can't seem to figure out where I have made an error
Please can someone look over the code and see if I have built query correct.

Basically I am making a query with 2 parameteres.
I am looking to output the results into a json format to populate the chart.
If there is a better way of doing this I would appreciate the input.

I get the following errors
==================================
errors
==================================
Notice: Undefined variable: data1_layer in C:\Program Files (x86)\Zend\Apache2\htdocs\trident\inc_widget_orv_performace.php on line 77
Warning: join(): Invalid arguments passed in C:\Program Files (x86)\Zend\Apache2\htdocs\trident\inc_widget_orv_performace.php on line 77
Notice: Undefined variable: data2_layer in C:\Program Files (x86)\Zend\Apache2\htdocs\trident\inc_widget_orv_performace.php on line 78
Warning: join(): Invalid arguments passed in C:\Program Files (x86)\Zend\Apache2\htdocs\trident\inc_widget_orv_performace.php on line 78
Notice: Undefined variable: data3_layer in C:\Program Files (x86)\Zend\Apache2\htdocs\trident\inc_widget_orv_performace.php on line 79
Warning: join(): Invalid arguments passed in C:\Program Files (x86)\Zend\Apache2\htdocs\trident\inc_widget_orv_performace.php on line 79
=================================

I have attached the code plus data to support the query
any help would be great

thanks

Matt

data_ranked_competitors.xlsx
test.php
data_ranked_competitors_detail.xlsx
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
test.php = inc_widget_orv_performace.php in errors above
0
Commented:
You are doing a fetch_row() on the $row and accessing the$row as if it were assoc - change to fetch_assoc();

Then have a look at this code
//define variables
$labels = 'null';$data1_layer = 0;
$data2_layer = 0;$data3_layer = 0;

// bind variables
$labels[] =$row["ORV_keyword"];
$data1_layer[] =$row["MAX_yearly_orv"];
$data2_layer[] =$row["Missed_Opportunity_ORV"];
$data3_layer[] =$row["Business_yearly_orv"];

You define $data1_layer as an int by assigning it to 0 and then try to use it as an array... and you overwrite the definition on each iteration of the loop Thats good for starters - will post more as I find them 0 Commented: A more detailed description. HINT: spend some time formatting the layout of your code - makes it easier to debug both for you and those trying to assist. $stmt = $mysqli->stmt_init(); // CHANGED TO HEREDOC STRING TO IMPROVE READABILITY$query = <<< QUERY
SELECT
data_ranked_competitors_detail.keyword AS ORV_keyword,
data_ranked_competitors_detail.ryorv_max AS MAX_yearly_orv,
(data_ranked_competitors_detail.ryorv_max - data_ranked_competitors_detail.yorv_real) AS Missed_Opportunity_ORV,
FROM
data_ranked_competitors
LEFT JOIN data_ranked_competitors_detail ON data_ranked_competitors_detail.comp_id = data_ranked_competitors.comp_id
WHERE
data_ranked_competitors.dfy_id = ?
GROUP BY ORV_keyword
ORDER BY
data_ranked_competitors_detail.ryorv_max DESC
LIMIT ?
QUERY;
if ($stmt->prepare($query)){
$stmt->bind_param("is",$dfy_id, $range);$stmt->execute();

// get results
$result =$stmt->get_result();

// build chart string
if ($result) { // OK UP TO HERE BUT NOW THINGS GO PEAR SHAPED // YOU DEFINE$labels AND $data - BUT YOU NEVER USE THEM$labels = array();
$data = array(); // NOT REALLY REQUIRED BUT OK$result->data_seek(0);
while ($row =$result->fetch_assoc()) {
// YOU ARE DEFINING, IN THE LOOP, VARIABLES YOU INTEND TO MODIFY
// INSIDE THE LOOP.
// ON EACH ITERATION YOU WIPE OUT WHAT YOU DID ON THE PREVIOUS ITERATION
//define variables

// THESE VARAIBABLES ARE BEING ASSIGNED SCALARS - BUT LOWER DOWN
// YOU ARE USING THEM AS ARRAYS
$labels = 'null';$data1_layer = 0;
$data2_layer = 0;$data3_layer = 0;

// bind variables
// APART FROM $labels WHICH IS DEFINED AS A SCALAR AND IS NOW // BEING USED AS AN ARRAY - THE OTHER 3 ARRAYS HAVE // NEVER BEEN DEFINED ANYWHERE$labels[] = $row["ORV_keyword"];$data1_layer[]   = $row["MAX_yearly_orv"];$data2_layer[]   = $row["Missed_Opportunity_ORV"];$data3_layer[]   = $row["Business_yearly_orv"]; } // NOT REALLY NECESSARY AS THE STATEMENTS BELOW OVERWRITE - BUT OK //define variables$data_string = 0;
$data_string2 = 0;$data_string3 = 0;
$labels_string = 0; // Now you can aggregate all the data into one string$data_string = "[" . join(", ", $data1_layer) . "]";$data_string2 = "[" . join(", ", $data2_layer) . "]";$data_string3 = "[" . join(", ", $data3_layer) . "]";$labels_string = "['" . join("', '", $labels) . "']"; } else { print('MySQL query failed with error: ' .$mysqli->error);
}
$result->close(); }  0 Commented: Last point - binding the$range to the LIMIT appears to break the query so that it does not return rows. This is because you are binding it as a string.

If you set $range = 20 and then $stmt->bind_param("ii", $dfy_id,$range);

It resolves the problem.

Maybe one of the other experts can comment on how to put a max,min limit in with a bind - personally I would just include it directly in the query as a string variable.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
HI Julian
Thanks for the help.
Got it working. Coding late at night does not work!

Here is what I ended up with
<?php
// Create the PHP MySQLi object
$mysqli =$framework_builder_mysqli;

// Check for any connection errors
if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" .$mysqli->connect_errno . ") " . $mysqli->connect_error; } // set parameters$dfy_id = $_SESSION['dfy_id']; // set filter range$range = '0,20';

if(isset($_POST["orv_range"])==''){$_POST["orv_range"] = '0,20';
} else{
$range =$_POST["orv_range"];
}
//prepare statement
$stmt =$mysqli->stmt_init();
if ($stmt->prepare(" SELECT data_ranked_competitors_detail.keyword AS ORV_keyword, data_ranked_competitors_detail.ryorv_max AS MAX_yearly_orv, (data_ranked_competitors_detail.ryorv_max - data_ranked_competitors_detail.yorv_real) AS Missed_Opportunity_ORV, data_ranked_competitors_detail.yorv_real AS Business_yearly_orv FROM data_ranked_competitors LEFT JOIN data_ranked_competitors_detail ON data_ranked_competitors_detail.comp_id = data_ranked_competitors.comp_id WHERE data_ranked_competitors.dfy_id = ? GROUP BY ORV_keyword ORDER BY data_ranked_competitors_detail.ryorv_max DESC LIMIT$range
")){
$stmt->bind_param("i",$dfy_id);
$stmt->execute(); // get results$result = $stmt->get_result(); // build chart string if ($result)
{
// define arrays
$labels = array();$data1_layer	= array();
$data2_layer = array();$data3_layer	= array();

//loop through data
while ($row =$result->fetch_assoc())
{

$labels[] =$row["ORV_keyword"];
$data1_layer[] =$row["MAX_yearly_orv"];
$data2_layer[] =$row["Missed_Opportunity_ORV"];
$data3_layer[] =$row["Business_yearly_orv"];
}

// Now you can aggregate all the data into one string
$data_string = "[" . join(", ",$data1_layer) . "]";
$data_string2 = "[" . join(", ",$data2_layer) . "]";
$data_string3 = "[" . join(", ",$data3_layer) . "]";
$labels_string = "['" . join("', '",$labels) . "']";
} else {
print('MySQL query failed with error: ' . $mysqli->error); } //$result->close();
}
?>

0
Commented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.