ChartDirector Support - View Thread 1301326314

Variables in mysql query

Posted by David Paterson on Mar-28-2011 23:31

Hello Peter,

I have been using ChartDirector for several years, and what a fantastic product it is.

I have been linking to the graphs to display on an html/php page, using a direct link from the page to a unique graph file.

I am now attempting to create a dynamic link that would link to the graphs using a variable from the html page. I have attempted so many different permutations, with no success. The best I can get is a blank graph with no data being displayed.

I would be greatful if you could give the scripting a once over to see if there are any fundamental mistakes, or how I should be going about this with ChartDirector.

Using ChartDirector PHP and Mysql5

The link on the webpage to dispaly the graph  (seaGRAS2.php) would be like this

--------------------------------------------

/charts/data/seaGRAS2.php?locate=550N020E

I would then be attempting to insert the variable locate into the mysql where query as below.

<?php
if (isset($_GET["locate"]))
     {
    $locn=$_GET["locate"];
     }
    else
     {
     $locate="560N020E";
     }

require_once("/usr/local/lib/php/extensions/no-debug-non-zts-20060613/phpchartdir.php");

if (!$link = mysql_connect('localhost', 'site2', 'xxxx')) {
  echo 'Could not connect to mysql';
  exit;
  }

if (!mysql_select_db('site2_north2', $link)) {
  echo 'Could not select database';
  exit;
  }

$query = 'SELECT DISTINCT `SWpd`,`SWpd1`,`Swdir`,`Label`,`Label2`,`Issue`,`AvPd`,`location` FROM `xx` WHERE `LATLON` = "$locate" LIMIT 0, 62';
$result = mysql_query($query) or die (mysql_error());

if (!$result) {
  echo "DB Error, could not query the database\\n";
  echo 'MySQL Error: ' . mysql_error();
  exit;
  }

while ($row = mysql_fetch_row($result)) {
    $SWpd[] = $row[0];
    $SWpd1[] = $row[1];
    $Swdir[] = $row[2];
    $Label[] = $row[3];
    $Label2[] = $row[4];
    $Issue = $row[5];
    $AvPd[] = $row[6];
    $location = $row[7];
}

--------------------
If I replace the $locate with 550N020E all is fine.

Any help much appreciated.

Regards
Dave

Re: Variables in mysql query

Posted by Peter Kwan on Mar-29-2011 00:09

Hi David,

The graph is empty probably because there is no data.

To debug the problem, you can print out your SQL query to see if it is correct.

Just by reading the code, I think:

$locn=$_GET["locate"];

should be:

$locate=$_GET["locate"];

Hope this can help.

Regards
Peter Kwan

Re: Variables in mysql query

Posted by David Paterson on Mar-29-2011 00:24

Hello Peter,

Sorry that locn was just a typo in the pasted copy I sent to you, should have been as below.

If I replace the $location in the query string with a location as below, all works fine.

$query = 'SELECT DISTINCT `SWpd`,`SWpd1`,`Swdir`,`Label`,`Label2`,`Issue`,`AvPd`,`location` FROM `xx` WHERE `LATLON` = "550N020E" LIMIT 0, 62';
$result = mysql_query($query) or die (mysql_error());

--------------------------------------------

/charts/data/seaGRAS2.php?locate=550N020E

I would then be attempting to insert the variable locate into the mysql where query as below.

<?php
if (isset($_GET["locate"]))
     {
    $locate=$_GET["locate"];
     }
    else
     {
     $locate="560N020E";
     }

require_once("/usr/local/lib/php/extensions/no-debug-non-zts-20060613/phpchartdir.php");

if (!$link = mysql_connect('localhost', 'site2', 'xxxx')) {
  echo 'Could not connect to mysql';
  exit;
  }

if (!mysql_select_db('site2_north2', $link)) {
  echo 'Could not select database';
  exit;
  }

$query = 'SELECT DISTINCT `SWpd`,`SWpd1`,`Swdir`,`Label`,`Label2`,`Issue`,`AvPd`,`location` FROM `xx` WHERE `LATLON` = "$locate" LIMIT 0, 62';
$result = mysql_query($query) or die (mysql_error());

if (!$result) {
  echo "DB Error, could not query the database\\n";
  echo 'MySQL Error: ' . mysql_error();
  exit;
  }

while ($row = mysql_fetch_row($result)) {
    $SWpd[] = $row[0];
    $SWpd1[] = $row[1];
    $Swdir[] = $row[2];
    $Label[] = $row[3];
    $Label2[] = $row[4];
    $Issue = $row[5];
    $AvPd[] = $row[6];
    $location = $row[7];
}

Peter Kwan wrote:

Hi David,

The graph is empty probably because there is no data.

To debug the problem, you can print out your SQL query to see if it is correct.

Just by reading the code, I think:

$locn=$_GET["locate"];

should be:

$locate=$_GET["locate"];

Hope this can help.

Regards
Peter Kwan

Re: Variables in mysql query

Posted by Peter Kwan on Mar-30-2011 00:36

Hi David,

Have you print your query out to see if it is equivalent to your working hard coded query? I remember in PHP, single quoted strings do not have variable interpolation. This should be immediately visible if you print out the $query. If you want variable interpolation, you may need to use a double quoted string.

Hope this can help.

Regards
Peter Kwan

Re: Variables in mysql query

Posted by David Paterson on Mar-30-2011 07:23

Hi Peter,

Thanks for your thoughts on that. When printing the table out, there was no data when using the variable. After spending days trying every possible permutation I have changed to a double quote string and HEY PRESTO ! It's now working.

Thanks again for your excellent advice.

Regards
Dave