Skip to Content

Hello Readers,

Here I am going to show how to connect an SAPUI5 application to a MySQL database using PHP.

 

Prerequisites –

  • Install WAMP server / XAMPP server.
  • Install PHP.Usually comes in built with the server.

Step 1 – Create a database and a table,Insert content.

In my case I am using PHPMyAdmin to create a database (Employee).In it I am creating a table (user) and within it I am inserting some records.

Step 2- Create a SAPUI5 application.

Here,I am creating an SAPUI5 application,wherein I am creating an empty JSON model and calling an ajax function with source to my php file.On the success function,to the JSON model,I set the data recieved from my PHP file.

Afterwards I am showing the data in a table(ui.table.Table), by setting the JSON model.

As this is an basic program,all the code is implemented in the index.html file itself.

My index.html file goes like this :-

<!DOCTYPE HTML>
<html>
	<head>
		<meta http-equiv="X-UA-Compatible" content="IE=edge">
		<meta http-equiv='Content-Type' content='text/html;charset=UTF-8'/>

		<script src="https://sapui5.hana.ondemand.com/resources/sap-ui-core.js"
				id="sap-ui-bootstrap"
				data-sap-ui-libs="sap.m,sap.ui.table,sap.ui.commons"
				data-sap-ui-theme="sap_bluecrystal">
		</script>
		<!-- only load the mobile lib "sap.m" and the "sap_bluecrystal" theme -->

		<script>
		
		var json = new sap.ui.model.json.JSONModel();
		$.ajax({                                      
		      url: 'tableData.php',                  
		      async:false,        
		      success: function(data)          //on recieve of reply
		      {
				json.setData(JSON.parse(data));
		      },
		      error: function(err)
		      {
		    	  console.log(err);
		      }
		    });
		

		var tableo = new sap.ui.table.Table({
			title		:	"Arjun's First Table",
			visibleRowCount	: 5
		});
		tableo.addColumn(new sap.ui.table.Column({
			label : new sap.ui.commons.Label({text:"Userid"}),
			template : new sap.ui.commons.TextField().bindProperty("value","Userid"),
			width : "140px"
		}));
		tableo.addColumn(new sap.ui.table.Column({
			label : new sap.ui.commons.Label({text:"Name"}),
			template : new sap.ui.commons.TextField().bindProperty("value","Name"),
			width : "140px"
		}));
		tableo.addColumn(new sap.ui.table.Column({
			label : new sap.ui.commons.Label({text:"Age"}),
			template : new sap.ui.commons.TextField().bindProperty("value","Age"),
			width : "140px"
		}));
		tableo.addColumn(new sap.ui.table.Column({
			label : new sap.ui.commons.Label({text:"Email"}),
			template : new sap.ui.commons.TextField().bindProperty("value","Email"),
			width : "140px"
		}));
		
		tableo.setModel(json)
		tableo.bindRows("/result");
		tableo.placeAt("content");
		
		
		</script>

	</head>
	<body class="sapUiBody" role="application">
		<div id="content"></div>
	</body>
</html>

Step 3-  Write the PHP file

 

In my PHP file tableData.php, I am writing a query to the database and sending the data to front end in JSON format.

 

<?php 

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "employee";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
	die("Connection failed: " . $conn->connect_error);
}

 $sql = "select * from user";
 
 $res = mysqli_query($conn,$sql);
 
 $result = array();
 
 while($row = mysqli_fetch_array($res)){
 array_push($result, 
 array('Userid'=>$row[0],'Name'=>$row[1],'Age'=>$row[2],'Email'=>$row[3]));
 }
 
 echo json_encode(array('result'=>$result));
?>

Step 4 – Now execute the index.html –

 

Thus, we have successfully displayed MySQL table data into an SAPUI5 table.

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

  1. Ewen MEHEUST

    Hi Arjun,

     

    Thanks for your example. Using your code for example, the data of my database don’t appear in the browser.

     

    Where do you put your “tableData.php” file? At the root of your application?

     

    It would seem I don’t succeed to access to this file.

     

    The error message FireFox sent to me, below :

    responseText: “<html>\n<head>\n<meta http-equiv=\”Content-Type\” content=\”text/html;charset=utf-8\”/>\n<title>Error 404 Not Found</title>\n</head>\n<body><h2>HTTP ERROR 404</h2>\n<p>Problem accessing /Hello/tableData.php. Reason:\n<pre> Not Found</pre></p><hr><a href=\”http://eclipse.org/jetty\”>Powered by Jetty:// 9.3.9.v20160517</a><hr/>\n\n</body>\n</html>\n”

    Could you help me, please?

     

    Thanks and Regards.

     

    Ewen.

    (1) 
    1. Arjun Biswas Post author

      Hi Ewen,

      Thank you for trying out the example.

      Yes you have to place the tableData.php file at your root folder i.e. the webContent folder of your application with the index.html file.

      The folder structure should look something like this :

      Within your tableData.php, you can write php code to fetch your data from the database and send it to your java script in JSON format.

       

      Hope this helps,

      Regards.

      (0) 
      1. Ewen MEHEUST

        Hi Arjun,

         

        Thank you for your response.

        For me the php file appears like in the image attached.

        Now, I have this error message :

        ________________________________________________________________

        Erreur d’analyse XML : aucun élément trouvé
        Emplacement : http://localhost:60003/Hello/tableData.php
        Numéro de ligne 27, Colonne 3 :
        tableData.php:27:3
        SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

        _________________________________________________________________

        Can you help me again?

         

        Thanks and Regards.

         

        Ewen.

         

        (1) 
        1. Arjun Biswas Post author

          Hi  Ewen,

          Sure I can try to help you with that.

          I too faced a similar issue a week ago. As per my experience, the problem is in your PHP file i.e. the data being sent from your PHP file to your AJAX is not in JSON format. Could you log the data in your success call-back function and check once. In my case the SELECT query in my PHP was creating some problems, due to which the “echo” from PHP was not in JSON format and when I was parsing that in my AJAX, it was throwing the same error.

          Hope this helps,

          Regards.

           

          (0) 
          1. Ewen MEHEUST

            Hi Arjun,

             

            Really, I think the problem is in my php file in my application.

            If I launch directly my php file in browser without use Eclipse, the code sends me “true”. Then, the code is good.

             

            But in Eclipse, I don’t see how to parse the “echo” of my php file. I do different tests but without success.

             

            Thanks and Regards.

             

            Ewen.

             

            (0) 
              1. Ewen MEHEUST

                Hi Arjun,

                 

                The code of my index.html file, below :

                 

                <!DOCTYPE HTML>

                <html>

                <head>

                <meta http-equiv=”X-UA-Compatible” content=”IE=edge”/>

                <meta charset=”UTF-8″/>

                <title>Hello</title>

                <script id=”sap-ui-bootstrap” src=”https://sapui5.hana.ondemand.com/resources/sap-ui-core.js” data-sap-ui-theme=”sap_bluecrystal” data-sap-ui-libs=”sap.m,sap.ui.table,sap.ui.commons”> </script>

                <script> var json = new sap.ui.model.json.JSONModel(); $.ajax({

                url: ‘tableData.php’,

                async:false,

                success: function(data)

                {

                json.setData(JSON.parse(data));

                },

                error: function(err)

                {

                console.log(err);

                } });

                var oTable = new sap.ui.table.Table({

                title : “hellotable”,

                visibleRowCount : 5 });

                oTable.addColumn(new sap.ui.table.Column({ l

                abel : new sap.ui.commons.Label({text:”Identifiant”}),

                template : new sap.ui.commons.TextField().bindProperty(“value”,”Identifiant”),   width : “150px”

                }));

                oTable.addColumn(new sap.ui.table.Column({

                label : new sap.ui.commons.Label({text:”Bonjour”}),

                template : new sap.ui.commons.TextField().bindProperty(“value”,”Bonjour”),     width : “150px”

                }));

                oTable.setModel(json);

                oTable.bindRows(“/result”);

                oTable.placeAt(“content”);

                </script>
                </head>

                <body class=”sapUiBody” role=”application”>

                <div id=”content”></div>

                </body>

                </html>

                 

                The code of my tableData.php file, below :

                 

                <?php

                $servername = “localhost”;
                $username = “root”;
                $password = “”;
                $dbname = “hello”;

                // Create connection
                $conn = new mysqli($servername, $username, $password, $dbname);
                // Check connection
                if ($conn->connect_error) {
                die(“Connection failed: ” . $conn->connect_error);
                }

                $sql = “select * from hellotable”;

                $res = mysqli_query($conn,$sql);

                $result = array();

                while($row = mysqli_fetch_array($res)){
                array_push($result,
                array(‘Identifiant’=>$row[0],’Bonjour’=>$row[1]));
                }

                echo json_encode($result);
                ?>

                 

                When I launch the application, I have 2 error messages :

                The first :

                Erreur d’analyse XML : aucun élément trouvé
                Emplacement : http://localhost:52375/Hello/tableData.php
                Numéro de ligne 27, Colonne 3 :
                tableData.php:27:3

                The second :

                SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

                 

                It would seem the sapui5 application doesn’t succeed to access to the tableData.php file

                I have tried to sent the application.

                 

                Thanks and Regards.

                (0) 
                1. Arjun Biswas Post author

                  Hi Ewen MEHEUST,

                  If your SAPUI5 application would not have succeeded to access the tableData.php file, you would have got the 404 file not found error, so I guess the application can access the file. By analysing your PHP code, I would suggest you to echo the $result and $row variables and check the output, the problem is that your select query is not returning an JSON parsable string. Do you have any BLOB data in your table.

                  Regards.

                  (0) 
                  1. Ewen MEHEUST

                    Hi Arjun,

                    Thanks for your response.

                    I don’t have BLOB data in my table, just an integer and a char. I have forgotten to update my tableDtata.php in wamp (C:\wamp64\www\demo\tableData.php). With the same tableData.php code present in my application, I have a 404 file not found error.

                     

                    abort: function abort()
                    always: function always()
                    complete: function add()
                    done: function add()
                    error: function add()
                    fail: function add()
                    getAllResponseHeaders: function getAllResponseHeaders()
                    getResponseHeader: function getResponseHeader()
                    overrideMimeType: function overrideMimeType()
                    pipe: function then()
                    progress: function add()
                    promise: function promise()
                    readyState: 4
                    responseText: “<html>\n<head>\n<meta http-equiv=\”Content-Type\” content=\”text/html;charset=utf-8\”/>\n<title>Error 404 Not Found</title>\n</head>\n<body><h2>HTTP ERROR 404</h2>\n<p>Problem accessing /Hello/table.php. Reason:\n<pre> Not Found</pre></p><hr><a href=\”http://eclipse.org/jetty\”>Powered by Jetty:// 9.3.9.v20160517</a><hr/>\n\n</body>\n</html>\n”
                    setRequestHeader: function setRequestHeader()
                    state: function state()
                    status: 404
                    statusCode: function statusCode()
                    statusText: “Not Found”
                    success: function add()
                    then: function then()
                    __proto__: Object { … }
                    index.html:36:10

                     

                    It would seem the application doesn’t succeed to access. Would you like I sent my application to you?

                     

                    Thanks and Regards.

                     

                    Ewen.

                    (0) 
                      1. Ewen MEHEUST

                        Hi Arjun,

                         

                        Thank you for your email, but my message with my SAPUI application code and SQL database code has been blocked.

                         

                        Regards.

                         

                        Ewen.

                        (0) 
                      2. Ewen MEHEUST

                        Hi Arjun,

                         

                        Like mx.google has reject the email I have sent to you with my code in attachement, I have sent to you a link via WeTransfer you can download my code.

                        Good receipt.

                         

                        Regards.

                         

                        Ewen.

                        (0) 

Leave a Reply