php + Highcharts.js + convert from mysql to mysqli causes error

Question

Could somebody give me a helping hand howto convert those mysql things to mysqli?
I need this because the mysql-extention causes this alert:
"The mysql extension is deprecated and will be removed in ..."

I tried the ConvertingTool but it responses warnings/errors. I also tried to convert it with the help of google und w3school.

With the old deprecated mysql-version it looks like THAT

But if i try to use the mysqli-extention the graphs didn`t appear.

This is the connector.

<?php
define('DB_SERVER',"localhost");
define('DB_NAME',"Datenbank-Name");
define('DB_USER',"Datenbank-Username");
define('DB_PASSWORD',"Datenbank-Passwort");

$conn = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD);
if(is_resource($conn))
{
mysql_select_db(DB_NAME, $conn);
mysql_query("SET NAMES 'utf8'", $conn);
mysql_query("SET CHARACTER SET 'utf8'", $conn);
}

define("listViewTempPeriod", 24); // Anzeige der Stunden die ausgegeben werden sollen
define("NUMSENSORS", 2);          // Anzahl der Sensoren deren Werte in der Datenbank stehen
?>

The functions.

<?php

function delLastChar($string="")
{
$t = substr($string, 0, -1);
return($t);
}

function getChartValues($sensorID=0, $timePeriodInHours=24)
{
$q_data  = mysql_query("SELECT DATE_FORMAT(datumzeit,'%H') AS STUNDE, sensorwert 
                FROM arduino_sensorwerte 
                WHERE sensorid = ".$sensorID." AND datumzeit >= date_sub(now(), interval ".$timePeriodInHours." hour) and datumzeit <= now() 
                GROUP BY DATE_FORMAT(datumzeit, '%Y-%m-%d %H') 
                ORDER BY datumzeit DESC") or die(mysql_error()); 
$n_data = mysql_num_rows($q_data);
if($n_data > 0)
{
$chartValues   = '';
$stundenValues = '';

while($r_data = mysql_fetch_array($q_data))
{
$chartValues   .= $r_data['sensorwert'].',';  // Einzelne Werte durch Komma trennen
$stundenValues .= $r_data['STUNDE'].',';      // Einzelne Werte durch Komma trennen
}

$chartValues   = delLastChar($chartValues);     // Komma hinter dem letzten Temperaturwert entfernen
$stundenValues = delLastChar($stundenValues);   // Komma hinter letzter Stunde entfernen

return array($chartValues, $stundenValues);
}
}

function getSensorSettings($sensorID=0)
{
$q_data  = mysql_query("SELECT mpcharttype, mplinetype, mpname, mpdescription, mplinecolor 
                  FROM arduino_messpunkte 
                  WHERE messpunktid = ".mysql_real_escape_string($sensorID)) or die(mysql_error()); 
$n_data = mysql_num_rows($q_data);
if($n_data > 0)
{
$r_data = mysql_fetch_array($q_data);

switch ($r_data['mpcharttype']) 
{
case 1:
$mpChartType = 'spline';
break;
case 2:
$mpChartType = 'line';
break;
case 3:
$mpChartType = 'areaspline';
break;
case 4:
$mpChartType = 'area';
break;
case 5:
$mpChartType = 'column';
break;    
case 6:
$mpChartType = 'bar';
break;    
}

switch ($r_data['mplinetype']) 
{
case 1:
$mpLineType = 'solid';
break;
case 2:
$mpLineType = 'ShortDash';
break;
case 3:
$mpLineType = 'ShortDot';
break;
case 4:
$mpLineType = 'ShortDashDot';
break;
case 5:
$mpLineType = 'ShortDashDotDot';
break;    
case 6:
$mpLineType = 'Dot';
break;
case 7:
$mpLineType = 'Dash';
break;
case 8:
$mpLineType = 'LongDash';
break;
case 9:
$mpLineType = 'DashDot';
break;
case 10:
$mpLineType = 'LongDashDot';
break;
case 11:
$mpLineType = 'LongDashDotDot';
break;              
}

$mpName = $r_data['mpname'];
$mpDescription = $r_data['mpdescription'];
$mpLineColor = $r_data['mplinecolor'];

return array($mpChartType, $mpLineType, $mpName, $mpDescription, $mpLineColor);
}
}
?>

An "example" call on a mainpage:

 <?php

 include_once("inc/db.inc.php");
 include_once("inc/functions.inc.php");

 if(!isset($_GET['timePeriodInHours'])) $_GET['timePeriodInHours'] = 24; else $_GET['timePeriodInHours'] = $_GET['timePeriodInHours'];
 if(!isset($_GET['chartStyle'])) $_GET['chartStyle'] = 1; else $_GET['chartStyle'] = $_GET['chartStyle'];

 $colors = array('#89A54E','#80699B','#3D96AE','#DB843D','#92A8CD','#A47D7C','#B5CA92');

 for($i=0;$i<NUMSENSORS;$i++)
 {
   list($chartValues[], $stundenValues[]) = getChartValues($i+1, $_GET['timePeriodInHours'], 1);
 }

 $stundenValues = $stundenValues[0];
 ?>
 <!DOCTYPE html>
 <html>
   <head>
     <title>Sensoren &Uuml;bersicht</title>
     <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
     <meta http-equiv="refresh" content="300">
     <meta name="Robots" content="index,follow">
     <link rel="stylesheet" type="text/css" href="css/highcharts.css">
     <script src="http://code.jquery.com/jquery-1.10.1.min.js"></script>

 <script type="text/javascript">
 $(function () 
 {
   var chart;

   $(document).ready(function() 
   {        
     chart = new Highcharts.Chart(
     {
       chart: 
       {
         renderTo: 'container'
       },
       title: 
       {
         text: 'Temperaturwerte der letzten <?php echo $_GET['timePeriodInHours'];?> Stunden'
       },
       subtitle: 
       {
         text: 'Alle Messstellen'
       },
       xAxis: 
       {
         title:
         {
           text: ''
         },
         categories: [<?php echo $stundenValues;?>]
       },      
       yAxis:
       {   
         title: 
         {
           text: ''         
         },      
         labels: 
         {
           formatter: function() 
           {
             return this.value +'°C'
           }
         }
       },
       tooltip: 
       {
         crosshairs: true,
         shared: true
       },
       tooltip: 
       {
         formatter: function() 
         {
           return '<b>'+ this.series.name +'</b>'+this.x +' Uhr:  '+ this.y +'°C';
         }
       },  
       legend: 
       {
         enabled: true
       },
       credits:
       {
         enabled: false
       },
       series:
       [
 <?php 
       for($i=0;$i<=NUMSENSORS;$i++)
       {
         if(!empty($chartValues[$i]))
         {
           list($mpChartType, $mpLineType, $mpName, $mpDescription, $mpLineColor) = getSensorSettings($i+1);
 ?>        {
             type: '<?php echo $mpChartType;?>',
             dashStyle: '<?php echo $mpLineType;?>',
             name: '<?php echo $mpName;?>', 
             color: '#<?php echo $mpLineColor;?>', 
             data: [<?php echo $chartValues[$i];?>],
             marker: 
             {
               symbol: 'square',
               enabled: false,
               states: 
               {
                 hover: 
                 {
                   symbol: 'square',
                   enabled: true,
                   radius: 8
                 }
               }
             }    
           },
 <?php  
         }
       } //for 
 ?>
       ] //series      
     });
   });  
 });
 </script>

   </head>
 <body>

 <div id="wrapper">
   <script src="es_scripts/highcharts.js"></script>
   <div id="container"></div>
 </div>

 </body>
 </html>

@Phil honestly, the link from the accepted answer leads to a terrible "converter tool" which doesn't even work — Your Common Sense, Apr 15 '14 at 06:17
@YourCommonSense I suppose. OP could just search for *"php mysql mysqli migration guide"* instead — Phil, Apr 15 '14 at 06:48
I allready tried to solve it with the help of google and w3schools. But it didnt work how it should. The connection works but the graph didnt appeared anymore — ichdertobi, Apr 15 '14 at 07:02

Logan Wayne · Accepted Answer · 2015-11-09T07:29:08.390

-2

If you are going to convert your code to mysqli_*, then it should be in a prepared statement manner.

Lets re-establish your MySQL connection in db.inc.php:

<?php

  $conn = new mysqli("localhost", "Datenbank-Username", "Datenbank-Passwort", "Datenbank-Name");

  /* CHECK CONNECTION */
  if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }

  /* CHANGE CHARACTER SET TO utf8 */
  if (!$conn->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $mysqli->error);
    exit();
  } else {
    printf("Current character set: %s\n", $mysqli->character_set_name());
  }

  define("listViewTempPeriod", 24); // Anzeige der Stunden die ausgegeben werden sollen
  define("NUMSENSORS", 2);          // Anzahl der Sensoren deren Werte in der Datenbank stehen

?>

Your file where you store your functions (functions.inc.php):

<?php

  function delLastChar($string="")
  {
    global $conn;
    $t = substr($string, 0, -1);
    return($t);
  }

  function getChartValues($sensorID=0, $timePeriodInHours=24)
  {

    global $conn;

    if($stmt = $conn->prepare("SELECT DATE_FORMAT(datumzeit, '%H') AS STUNDE, sensorwert FROM arduino_sensorwerte WHERE sensorid = ? AND datumzeit >= date_sub(NOW(), interval ? hour) AND datumzeit <= NOW() GROUP BY DATE_FORMAT(datumzeit, '%Y-%m-%d %H') ORDER BY datumzeit DESC")){
      $stmt->bind_param("ii", $sensorID, $timePeriodInHours);
      $stmt->execute();
      $stmt->store_result();
      $n_data = $stmt->num_rows;
      $stmt->bind_result($stunde, $sensorwert);

      $chartValues = '';
      $stundenValues = '';

      while($stmt->fetch()){

        $charValues    = $sensorwert;
        $stundenValues = $stunde;

      } /* END OF WHILE LOOP */
      $stmt->close();
    } /* END OF PREPARED STATEMENT */

    $chartValues   = delLastChar($chartValues);     // Komma hinter dem letzten Temperaturwert entfernen
    $stundenValues = delLastChar($stundenValues);   // Komma hinter letzter Stunde entfernen

    return array($chartValues, $stundenValues);

  } /* END OF getChartValues FUNCTION */

  function getSensorSettings($sensorID=0)
  {

    global $conn;

    $stmt = $conn->prepare("SELECT mpcharttype, mplinetype, mpname, mpdescription, mplinecolor FROM arduino_messpunkte WHERE messpunktid = ?");
    $stmt->bind_param("i", $sensorID);
    $stmt->execute();
    $stmt->store_result();
    $n_data = $stmt->num_rows;
    $stmt->bind_result($mpcharttype, $mplinetype, $mpName, $mpDescription, $mpLineColor);

    if($n_data > 0)
    {
      while($stmt->fetch()){

        switch ($mpcharttype) 
        {
          case 1:
            $mpChartType = 'spline';
            break;
          case 2:
            $mpChartType = 'line';
            break;
          case 3:
            $mpChartType = 'areaspline';
            break;
          case 4:
            $mpChartType = 'area';
            break;
          case 5:
            $mpChartType = 'column';
            break;    
          case 6:
            $mpChartType = 'bar';
            break;    
          } /* END OF FIRST SWITCH */

        switch ($mplinetype) 
        {
          case 1:
            $mpLineType = 'solid';
            break;
          case 2:
            $mpLineType = 'ShortDash';
            break;
          case 3:
            $mpLineType = 'ShortDot';
            break;
          case 4:
            $mpLineType = 'ShortDashDot';
            break;
          case 5:
            $mpLineType = 'ShortDashDotDot';
            break;    
          case 6:
            $mpLineType = 'Dot';
            break;
          case 7:
            $mpLineType = 'Dash';
            break;
          case 8:
            $mpLineType = 'LongDash';
            break;
          case 9:
            $mpLineType = 'DashDot';
            break;
          case 10:
            $mpLineType = 'LongDashDot';
            break;
          case 11:
            $mpLineType = 'LongDashDotDot';
            break;              
        } /* END OF SECOND SWITCH */

      } /* END OF WHILE LOOP */

      return array($mpChartType, $mpLineType, $mpName, $mpDescription, $mpLineColor);

    } /* END OF CHECKING NUMBER OF RESULTS */

  } /* END OF getSensorSettings FUNCTION */

?>

If you expect a query to have one row of result only, you don't need to loop it anymore.

edited Nov 09 '15 at 07:29

answered Apr 15 '14 at 06:16

Logan Wayne

6,001
16
31
49

That didnt solve anything for me – ichdertobi Apr 15 '14 at 06:26
What's the error when you tried my answer? – Logan Wayne Apr 15 '14 at 06:27
With the deprecated mysql-extention it looks like that:[Picture](https://fbcdn-sphotos-f-a.akamaihd.net/hphotos-ak-frc1/t31.0-8/1960777_10202104218313118_1363264057278264012_o.jpg) But it gives me a php-output on my page. If I try your version, the graphs will not drawn – ichdertobi Apr 15 '14 at 06:33
There's no error when you try my answer? But the graph is not showing? Double check your variables that connects to the graph. – Logan Wayne Apr 15 '14 at 06:37
You're using `$con` all through your functions but it's not in scope. – Phil Apr 15 '14 at 06:41
@Phil Why isn't in the scope? I include the connection file to the main page. – Logan Wayne Apr 15 '14 at 06:43
1

http://php.net/manual/language.variables.scope.php – Phil Apr 15 '14 at 06:45
@user3534543 kindly check my updated answer. Thanks to Phil, I forgot global call function. – Logan Wayne Apr 15 '14 at 07:18
btw: at the mainpage I includet both: include_once("inc/db.inc.php"); include_once("inc/functions.inc.php"); – ichdertobi Apr 15 '14 at 07:21
@user3534543 which of those two files is your connection? – Logan Wayne Apr 15 '14 at 07:25
The file for the connection is the inc/db.inc.php – ichdertobi Apr 15 '14 at 07:29
And your functions.inc.php is the second bracket of codes in your post? – Logan Wayne Apr 15 '14 at 07:31
correct --> the db.inc.php loads before the functions.inc.php – ichdertobi Apr 15 '14 at 07:33
Have you tried my updated answer? What's the output? – Logan Wayne Apr 15 '14 at 07:34
the same like before --> no graphs – ichdertobi Apr 15 '14 at 07:35
Troubleshoot or double check the variables you are using for the graph, connecting from functions.inc.php – Logan Wayne Apr 15 '14 at 07:42
the variables are correct and the connection works, too. – ichdertobi Apr 15 '14 at 07:47
Can you also post your code for your graph? – Logan Wayne Apr 15 '14 at 07:48
No. In order for readers to troubleshoot your problem FURTHER, we need also to look at the whole code. – Logan Wayne Apr 15 '14 at 07:54
I edit my question and added there an example call on a mainpage – ichdertobi Apr 15 '14 at 08:05
@ichdertobi Try it again. I've changed the fetch_array query of yours in functions.inc.php – Logan Wayne Apr 15 '14 at 08:22
@Logan Wayne It didn´t work for me!? No change in the output – ichdertobi Apr 15 '14 at 08:28
Try to echo that variable first. If you get the expected output right, then try escaping the variable first (mysqli_real_escape_string) before inserting into query. – Logan Wayne Apr 15 '14 at 12:35

Your Common Sense · Answer 2 · 2014-04-15T06:40:46.223

-2

I need this because the mysql-extention causes this alert:
"The mysql extension is deprecated and will be removed in ..."

Simply add/change this code in your config/bootstrap file

error_reporting(E_ALL & ~E_DEPRECATED);

and this error will be gone.

"It doesn't solve the problem"? - OH, YES.

But the problem is not where you think it is.

To solve the real problem you have to spend GREAT LOT OF TIME, as it will take you couple years to learn programming first, and then some time to COMPLETE REWRITE your code. Because the only problem with this code is its outdated and insecure DESIGN.

It's design what you really need to change, not simple API calls.

In case you're ready for this - all right, take your time. But while learning and rewriting you may use this option as a temporary solution.
In case you aren't - just keep with this option. because just mechanical conversion from one API to another will do no good anyway, keeping this code as insecure as it is.

edited Apr 15 '14 at 06:40

answered Apr 15 '14 at 06:19

Your Common Sense

156,878
40
214
345

1

This didn`t solve the problem =/ That only disable the error-output – ichdertobi Apr 15 '14 at 06:20
That the mysql-extention is deprecated and maybe not longer supported in newer php-versions. Because of that i would like to use the mysqli-extention but I couldn`t solve it. For example: I allread tried the MySQLConverter .. – ichdertobi Apr 15 '14 at 06:28
1

This code DOES solve the problem of deprecation, while with "no longer support" there is no problem yet. – Your Common Sense Apr 15 '14 at 06:29
1

Your answer tells the OP how to make the error message go away. It does not answer his question: how to migrate from `mysql()` to `mysqli()` – staticsan Apr 15 '14 at 06:55
@staticsan not every question have to be taken lliterally, you know. – Your Common Sense Apr 15 '14 at 06:57
2

@YourCommonSense Perhaps. But telling someone to just disable the error message is IMO irresponsible. I have dealt with more than enough PHP code written by people who have turned errors off instead of writing it properly. – staticsan Apr 15 '14 at 06:59
1

@staticsan again, not all errors are the same. Why you are talking of turning off errors in general while it's particular error class in question? And nowhere did I say he shouldn't write properly. Quite contrary, I told him exactly otherwise. – Your Common Sense Apr 15 '14 at 07:01
1

@YourCommonSense If you take such a selective approach to responding to errors (of any class) than I do not want to encounter any of your PHP code. Because I am sure I will have to re-write it. – staticsan Apr 15 '14 at 07:07
@staticsan That's nice reproach from someone who can't keep with particular topic and cannot either tell my own code from the OP. But it's all right, you can keep with your irrelevant sermon. It's pure SO-way exactly. – Your Common Sense Apr 15 '14 at 07:12
1

In turn, I wouldn't give my code to someone who cannot read whole answer but stick with first paragraph only and **cannot get into context of the question**, nitpicking of the otherwise irrelevant bits. – Your Common Sense Apr 15 '14 at 07:15

php + Highcharts.js + convert from mysql to mysqli causes error

2 Answers2