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
matthewdacruzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

matthewdacruzAuthor Commented:
test.php = inc_widget_orv_performace.php in errors above
Julian HansenCommented:
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"];

Open in new window

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
Julian HansenCommented:
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,
    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 ?
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();
}

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Julian HansenCommented:
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);

Open in new window

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.

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.

Start your 7-day free trial
matthewdacruzAuthor 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();
				}
?>

Open in new window

Julian HansenCommented:
You are welcome.
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.