Simple working sample how to configure Webix to work with MySQL

Hi everyone,

Yesterday downloaded Webix, looks great.
Now trying to make it work with MySQL DB, spent already 2 days while trying to configure it - no success.
Checked all samples, it refers to sqlite and seems that config has different format than the one needed to configure for MySQL.

Here is my data.php:

romanr@MonitoringSystem:/var/www/tttool_mod/public_html/data$ more data.php 
<?php
require_once("data_connector.php"); //!connector
$dbtype = "MySQL";
$conn = mysql_connect(10.240.105.60, "roman","Pass");

$data = new JSONDataConnector($conn, $dbtype);

$data->render_table("TRACKERS_MOD_TIME","TRACKER_NAME","TRACKER_NAME, TRACKER_MOD_TIME");

?>
romanr@MonitoringSystem:/var/www/tttool_mod/public_html/data$ 

data_connector.php has been just copied from samples.

here my index.html:

romanr@MonitoringSystem:/var/www/tttool_mod/public_html/data$ more ../index.html 
<!DOCTYPE HTML>
<html>
    <head>
	<link rel="stylesheet" href="codebase/webix.css" type="text/css"> 
	<script src="codebase/webix.js" type="text/javascript"></script> 
    </head>
    <body>
        <script type="text/javascript" charset="utf-8">


webix.ready(function(){
/*	webix.ui({
	rows: [
		{ view:"toolbar", id:"mybar", elements:[
			{ view:"button", value:"Add", width:70 }, 
			{ view:"button", value:"Delete", width:70 },
			{ view:"button", value:"Update", width:70 },
			{ view:"button", value:"Clear Form", width:85}]
		},
		{ cols:[
			{view:"form", id:"myform", width: 200, elements:[
				{ view:"text", name:"ticketID", placeholder:"Work Order ID", width:180, align:"center"},  
					 { view:"button", value:"Get status", width:120, click:"getWOStatus" },
				{ view:"text", name:"woNewStatus", placeholder:"WO new status", width: 180, align:"center"},
					 { view:"button", value:"Set new status", width:120 },
				{}
			]},
			{ template:"Column 2"  }]
		}
	]


	});
*/

});

$$("grid").load("data/data.php");
	</script>

    </body>
</html>
romanr@MonitoringSystem:/var/www/tttool_mod/public_html/data$ 

I am just trying to get some data from DB but it just shows empty page :frowning:
Can anyone point to the error or provide some sample config and page file so I can use it as template?

Please note that I can connect to DB from this host.

I also took sample 03_db_data.html and tried to use but getting table with no values, so it seems that data.php is not set up correctly.

romanr@MonitoringSystem:/var/www/tttool_mod/public_html/data$ more ../03_db_data.html 
<!DOCTYPE html>
<html>
	<head>
		<title>Loading from DB</title>
	<link rel="stylesheet" href="codebase/webix.css" type="text/css"> 
	<script src="codebase/webix.js" type="text/javascript"></script> 

		<link rel="stylesheet" type="text/css" href="../common/samples.css">
		<script src="../common/testdata.js" type="text/javascript" charset="utf-8"></script>
	</head>
	<body>
		<div class='header_comment'>Loading from DB (sqllite + php)</div>
		<div id="testA" style='height:600px'></div>
		<hr>
		
		<script type="text/javascript" charset="utf-8">

		webix.ready(function(){
			grida = webix.ui({
				container:"testA",
				view:"datatable",
				columns:[
					{ id:"package",	header:"Name", 			width:200 },
					{ id:"section",	header:"Section",		width:120 },
					{ id:"size",	header:"Size" , 		width:80  },
					{ id:"architecture",	header:"PC", 	width:60  }
				],
				
				autowidth:true,

				url:"data/data.php"
			});	
		});
		</script>
	</body>
</html>
romanr@MonitoringSystem:/var/www/tttool_mod/public_html/data$ 

Note that Webix datatable will receive any valid incoming JSON ( or any available format with the correspondingdatatype property).

First of all, please check the response from the server?

Okay, I modified data.php, now it can connect to DB and returns “Connected to MySQL” message.

Here is data.php:


<?php

$dbtype = "MySQL";

$username = "roman";
$password = "Pass";
$hostname = "10.240.105.60";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

$selected = mysql_select_db("MASTER_TRACKER_DB",$dbhandle)
  or die("Could not select MASTER_TRACKER_DB");
/*
//execute the SQL query and return records
$result = mysql_query("SELECT SITE_ID, 3G_SITE_ID FROM MASTER_TRACKER WHERE SITE_ID LIKE '%ABK000%'");
//fetch tha data from the database
while ($row = mysql_fetch_array($result)) {
   echo "SITE_ID:".$row{'SITE_ID'}." 3G_SITE_ID:".$row{'3G_SITE_ID'}.
   "<br>";
}
*/
$data = new JSONDataConnector($dbhandle, $dbtype);

$data->render_sql("SELECT SITE_ID, 3G_SITE_ID FROM MASTER_TRACKER WHERE SITE_ID LIKE '%ABK000%'", "SITE_ID", "SITE_ID, 3G_SITE_ID");

//close the connection
mysql_close($dbhandle);
?>

And here is my test page where i want to see extracted values:


<!DOCTYPE html>
<html>
        <head>
                <title>Loading from DB</title>
        <link rel="stylesheet" href="codebase/webix.css" type="text/css"> 
        <script src="codebase/webix.js" type="text/javascript"></script> 

        </head>
        <body>
                <div class='header_comment'>Loading from DB (sqllite + php)</div>
                <div id="testA" style='height:600px'></div>
                <hr>

                <script type="text/javascript" charset="utf-8">

                webix.ready(function(){
                        grida = webix.ui({
                                container:"testA",
                                view:"datatable",
                                columns:[
                                        { id:"SITE_ID", header:"SIZE_ID",               width:200 },
                                        { id:"3G_SITE_ID",header:"3G_SITE_ID",          width:120 }
                                ],
                                autowidth:true,
                                url: "data/data.php"
                        });     
                });


                </script>
        </body>
</html>

But in output I see empty table.
Can you please point on the error?

When I use below code in data.php to check if I can extract data from table, I can see output:

$result = mysql_query("SELECT SITE_ID, 3G_SITE_ID FROM MASTER_TRACKER WHERE SITE_ID LIKE '%ABK000%'");
//fetch tha data from the database
while ($row = mysql_fetch_array($result)) {
   echo "SITE_ID:".$row{'SITE_ID'}." 3G_SITE_ID:".$row{'3G_SITE_ID'}.
   "<br>";
}

No any ideas? :frowning:

for datatable returned data must be json encoded array.

e.g. [{SITE_ID: 1, 3G_SITE_ID: 11},{SITE_ID: 2, 3G_SITE_ID: 22}]

you can achieve this by json_encode(mysql_fetch_assoc($result));

Hi, you are close with the first example. But you have to select the database.


<?php 
include ("data_connector.php");

$conn = mysql_connect("host","user","pass");
mysql_select_db("your database", $conn);

$data = new JSONDataConnector($conn);

$data->render_table("your table", "an id field","all, your, other, fields");
	
	?>

Thanks mirko,

I wish I could do it work…
I copied data_connector.php to the folder where data.php, then modified data.php as you suggested.

But when i am checking result, I am getting message like “Page doesn’t work”. If I comment out data_connector.php then i can see same empty page with 1 string “Connected to MySQL”. :frowning:

Do I need to modify data_connector.php as well?

I did it!!! :slight_smile:

Thank you Mirko for helping me to figure out what was the problem.
I copied data_connector.php to local dir, but since this script also invoke other scripts like base_connector.php etc. which still places in other dir it didn’t work. After I specified full path to original location (i.e. like require_once("…/samples/common/connector/data_connector.php"); ) it started to work.

Thanks everyone for help and especially to Mirko bro.

Have a nice day.