MySQL. PHP. Table in WebViewer with JavaScript

[Juan_Antonio]

This example is based on this topic:
https://community.appinventor.mit.edu/t/a-strange-behaviour-of-response/82885

BaseDatos_MySql.aia (5.5 KB)

This MySQL

cng_insert2.php

<?php
date_default_timezone_set("Europe/Madrid");
$servername = "localhost";
$username = "id20681340_fake";
$password = "Contraseña_fake";
$database = "id20681340_gas";

$dbc = mysqli_connect($servername, $username, $password, $database);

if (!$dbc) {
    die("DATABASE CONNECTION FAILED:" . mysqli_error($dbc));
    exit();
}

$boton = mysqli_real_escape_string($dbc, $_GET["boton"]);

 if ($boton == "btnSend"){
			$datex = mysqli_real_escape_string($dbc, $_GET["date"]);
			$p_ltr = mysqli_real_escape_string($dbc, $_GET["p_ltr"]);
			$p_rat = mysqli_real_escape_string($dbc, $_GET["p_rat"]);
			$p_amt = mysqli_real_escape_string($dbc, $_GET["p_amt"]);
			 
			$query2 = "select * from sales where ddate='$datex'";
			$result=mysqli_query($dbc, $query2);		 
			$rows_count = mysqli_num_rows($result);
				if ($rows_count > 0) {
				 $query = "update sales set p_ltr='$p_ltr',p_rat='$p_rat',p_amt='$p_amt' where ddate='$datex'";
					if (mysqli_query($dbc, $query)) {
						echo "Records updated successfully.";
						 exit();
					} else {
						echo "ERROR: Could not able to execute" .$query." ".mysqli_error($dbc);
					}
					
			 } else { 
					$query = "INSERT INTO sales(ddate,p_ltr,p_rat,p_amt)
			 VALUES ('$datex','$p_ltr','$p_rat','$p_amt')";
					if (mysqli_query($dbc, $query)) {
						echo "Records added successfully.";
					} else {
						echo "ERROR: Could not able to execute" .$query." ".mysqli_error($dbc);
					}
				}
			mysqli_close($dbc);
 };
 
 if ($boton == "btnGet"){
	$hacer = mysqli_query ($dbc, "SELECT * FROM sales");
	$resultado = mysqli_query($GLOBALS['dbc'], "SHOW COLUMNS FROM sales");
	$numerodefilas = mysqli_num_rows($resultado);
		if ($numerodefilas > 0) {
		$en_csv='';
			while ($rowr = mysqli_fetch_row($hacer)) {
				for ($j=0;$j<$numerodefilas;$j++) {
				$en_csv .= $rowr[$j].",";
				}
		 $en_csv = substr($en_csv, 0, -1);
		 $en_csv .= "\n";
			}
		}
    $en_csv = substr($en_csv, 0, -1);
	print $en_csv;
	mysqli_close($dbc);
 }
?>

TableView from
https://community.appinventor.mit.edu/t/tableview-with-webviewer-javascript-and-css-helped-by-chat-openai-gpt-perplexity/81658

MySql from
http://kio4.com/appinventor/340C_appinventor_mysqli.htm

[Frank-HRO]

Hello Juan Antonio,
I tested your AIA and the php script with my host.
If I use a MySQL database with version 5.7 and port 3306, everything works.
If I use MySQL version 8.0 with port 3307, it doesn't work. No data is displayed or sent.

Is there a tip on how it also works under MySQL 8.0?

Greetings Frank

[TIMAI2]

You are not using the default port 3306. You will need to add your non-default port number to the credentials in order to connect to your mysql.

Only change one thing at a time

[Frank-HRO]

Hello TimAI2,

thanks for your answer.
I changed it according to my host's instructions:

<?php
date_default_timezone_set("Europe/Madrid");
$servername = "127.0.0.1";
$username = "testuser-ai2";
$password = "testuser-ai2";
$database = "test-ai2";
$dbport = "3307"

$dbc = mysqli_connect($servername, $username, $password, $database, $dbport);

if (!$dbc) {

According to the hoster, "localhost" must be replaced by "127.0.0.1" and the port must be 3307.

It doesn't work. No data is displayed or sent.

Frank

Image extract from the hoster's databases:

[TIMAI2]

Does it work in a browser ?

[Frank-HRO]

No, it doesn't work there either.
I have now sent a request to the hoster.

Frank

[TIMAI2]

Aha !

[Frank-HRO]

Answer from the host if anyone else has this problem:

<?php
date_default_timezone_set("Europe/Madrid");
$servername = "127.0.0.1:3307";
$username = "testuser-ai2";
$password = "testuser-ai2";
$database = "test-ai2";

$dbc = mysqli_connect($servername, $username, $password, $database);

if (!$dbc) {

Frank

[TIMAI2]

Yes, I had forgotten that one, it popped up somewhere else too.

Well done.