Php mysql entry alerts broken

PNewCode · December 12, 2022

Hello. I have six files (I'll show the scripts below except for the delete.php because that doesn't matter in this). This was a project that I paid someone to do a year ago, so I can't tell you why "this or that" was put in any of the coding because I didn't create it. While I've learned a lot since then, I can't figure out why all of the sudden last week it stopped working.
How it used to work...

1: A website user would fill out a form and click send
2: The information would go to the database and in the alert database
3: The view page to see the messages would give an alert sound and make the page flash a color when a new entry was added to the database

Please don't ask why I want this. Thats not important. Also please don't ask why I'm doing this way instead of "that" way. Because I paid someone to make this, and this is what I got. I worked for a long time and suddenly stopped. I didn't make any changes.

Any help on why this no longer works is greatly appreciated ❤️

What does work:
The entry gets added to the database from the form, and is visible as needed. And delete function works as well.

What doesn't work:
The alert sound and making the page flash red.

Form (msgform.php)

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
</head>
<style type="text/css">
body {background:none transparent;
}
</style>



<body bgcolor="#FFFFFF" marginwidth="0" marginheight="0" text="#000000" leftmargin="0" topmargin="0" link="#FF0000" vlink="#FF0000" alink="#FF0000">
<form action="insertmsg.php" method="post" autocomplete="off">
  <p align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="4"> 
    <b><font color="#FFFF00">Name</font></b> 
    <input type="text" name="name" size="17" required />
    <b><font face="Verdana, Arial, Helvetica, sans-serif" size="4"><font color="#FFFF00"><font face="Verdana, Arial, Helvetica, sans-serif" size="4"><font color="#FFFF00"><font face="Verdana, Arial, Helvetica, sans-serif" size="4"><font color="#FFFF00"><font face="Verdana, Arial, Helvetica, sans-serif" size="4"><font color="#FFFF00"><font face="Verdana, Arial, Helvetica, sans-serif" size="4"> 
    <font color="#FFFF00"><font face="Verdana, Arial, Helvetica, sans-serif" size="4"><font color="#FFFF00"><font face="Verdana, Arial, Helvetica, sans-serif" size="4"><font color="#FFFF00"><font face="Verdana, Arial, Helvetica, sans-serif" size="4"> 
    <input type="hidden" name="reply" size="20" required value="https://www.peredy1.com/mods/msg/noall.php"/>
    </font></font></font></font></font></font> </font></font></font></font></font></font></font></font></font></b> 
    <b><font color="#FFFF00"><br>
    Link</font></b> <font color="#FFFF00"> 
    <input type="text" name="band" size="20" />
    <br>
    <font face="Verdana, Arial, Helvetica, sans-serif" size="4"><b>Message</b></font></font><font size="3"><i><br>
    </i></font> 
    <textarea name="extra" cols="34" rows="4" required></textarea>
    </font><font face="Verdana, Arial, Helvetica, sans-serif" size="4"><br>
    <input type="image" class="alertclick" src="redsend.jpg" name="image" />
    <img src="cancel.jpg" border="0" onClick="window.history.go(-1)"></font> 
  </p>
</form>
<div id="test"></div>


<div id="test"></div>

<script type="text/javascript">

  $(".alertclick").click(function(){

      $('#test').load('alert.php');


  })
</script>

Insert to DB (insertmsg.php)

<?php
$servername = "localhost";
$database = "removed for public view";
$username = "removed for public view";
$password = "removed for public view";


// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
}

 
 
echo ".";


    $name1 = $_POST['name'];
    $band1 = $_POST['band'];
    $song1 = $_POST['song'];
    $extra1 = $_POST['extra'];
    $reply1 = $_POST['reply'];

 $name = mysqli_real_escape_string($conn , $name1);
    $band = mysqli_real_escape_string($conn , $band1);
    $song = mysqli_real_escape_string($conn , $song1);
    $extra = mysqli_real_escape_string($conn , $extra1);
    $reply = mysqli_real_escape_string($conn , $reply1);

 
$sql = "INSERT INTO nametable (name, band, song, extra, reply) VALUES ('$name', '$band', '$song', 'Message: $extra', '$reply')";
if (mysqli_query($conn, $sql)) {
      echo " ";
} else {
      echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}



mysqli_close($conn);
?>
<style type="text/css">
body {background:none transparent;
}
</style>

<meta http-equiv = "refresh" content = "2; url = msgform.php" />

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<body bgcolor="#FF6600" text="#000000" link="#FF0000" vlink="#FF0000" alink="#FF0000">
<div align="center">
  <table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr> 
      <td valign="top" height="84" colspan="7"> 
        <div align="right"> 
          <p><br>
          </p>
        </div>
        <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" color="#FFFF00"><b>Completed</b></font><font color="#FFFFFF"><b></b></font><font face="Verdana, Arial, Helvetica, sans-serif"><br>
          <br>
          </font></div>
      </td>
    </tr>
  </table></div>

Alert Generator (alert_generate.php)

<?php
$servername = "localhost";
	$username = "removed for public view";
	$password = "removed for public view";
	$db="removed for public view";
	$conn = mysqli_connect($servername, $username, $password,$db);

	// $sql = "INSERT INTO `number`( `number`) 
	// VALUES ('$qty')";
$sql = "SELECT alert_status FROM alert";

$result = $conn->query($sql);
$alertvar = 0;
if ($result->num_rows) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
          if($row["alert_status"] == 1){
echo '<div class="redalert3"></div>';

	$alertvar = 1;


}else{

}
    }
}
if($alertvar == 1){
echo '<script>var x = document.getElementById("myAudio3");  x.play(); </script>';
$qty=3;
	// $sql = "INSERT INTO `number`( `number`) 
	// VALUES ('$qty')";
$sql = "UPDATE alert SET alert_status=$qty WHERE id=1";


	if (mysqli_query($conn, $sql)) {
		echo json_encode(array("statusCode"=>200));
	} 
	else {
		echo json_encode(array("statusCode"=>201));
}
	mysqli_close($conn);
}
$conn->close();
?>

The Alert (alert.php)

<?php
	$servername = "localhost";
	$username = "removed for public view";
	$password = "removed for public view";
	$db="removed for public view";
	$conn = mysqli_connect($servername, $username, $password,$db);


$qty=1;
	// $sql = "INSERT INTO `number`( `number`) 
	// VALUES ('$qty')";
$sql = "UPDATE alert
SET alert_status=$qty
WHERE id=1";


	if (mysqli_query($conn, $sql)) {
		echo json_encode(array("statusCode"=>200));
	} 
	else {
		echo json_encode(array("statusCode"=>201));
	}
	mysqli_close($conn);


?>

View messages (view.php)

<html>
<head>



<style>
.redalert3:after {
    background-color: red;
    content: "";
    height: 100%;
    width: 100%;
    position: absolute;
    top: 0;
left:0;
}
</style>




<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>


<title>Peredy Host Tool</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="Author" content="Peredy Izhere" />
<meta name="copyright" content="&copy; 2009-2021, All Rights Reserved" />
<meta name="revisit-after" content="7 days" />
<meta http-equiv="refresh" content="7" >

<link rel="apple-touch-icon" sizes="57x57" href="/fi/apple-icon-57x57.png">
<link rel="apple-touch-icon" sizes="60x60" href="/fi/apple-icon-60x60.png">
<link rel="apple-touch-icon" sizes="72x72" href="/fi/apple-icon-72x72.png">
<link rel="apple-touch-icon" sizes="76x76" href="/fi/apple-icon-76x76.png">
<link rel="apple-touch-icon" sizes="114x114" href="/fi/apple-icon-114x114.png">
<link rel="apple-touch-icon" sizes="120x120" href="/fi/apple-icon-120x120.png">
<link rel="apple-touch-icon" sizes="144x144" href="/fi/apple-icon-144x144.png">
<link rel="apple-touch-icon" sizes="152x152" href="/fi/apple-icon-152x152.png">
<link rel="apple-touch-icon" sizes="180x180" href="/fi/apple-icon-180x180.png">
<link rel="icon" type="image/png" sizes="192x192"  href="/fi/android-icon-192x192.png">
<link rel="icon" type="image/png" sizes="32x32" href="/fi/favicon-32x32.png">
<link rel="icon" type="image/png" sizes="96x96" href="/fi/favicon-96x96.png">
<link rel="icon" type="image/png" sizes="16x16" href="/fi/favicon-16x16.png">
<link rel="manifest" href="/fi/manifest.json">
<meta name="msapplication-TileColor" content="#ffffff">
<meta name="msapplication-TileImage" content="/fi/ms-icon-144x144.png">
<meta name="theme-color" content="#ffffff">



</head>

<body text="#FFFFFF" link="#FF6600" vlink="#FF6600" alink="#FF6600">
<font face="Verdana, Arial, Helvetica, sans-serif" size="3"> 


<div class="alertadd"></div>
<audio id="myAudio3"> <source src="alertsound3.mp3" type="audio/mpeg"> 
</audio> </font> 



<script>
document.addEventListener('contextmenu', event => event.preventDefault());
</script>


<style type="text/css">
body {background:none transparent; }
</style>




<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr valign="top"> 
    <td height="180"> 
      <p> <font face="Verdana, Arial, Helvetica, sans-serif"> 


<?php
$servername = "localhost";
$username = "removed for public view";
$password = "removed for public view";
$dbname = "removed for public view";

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

$sql = "SELECT id, name, band, song, extra, reply FROM nametable";
$result = $conn->query($sql);


$resultt = mysqli_query($conn, "select COUNT(id) AS count FROM `nametable`");

if(!$result) {
    die('Error: ' . mysqli_error($link));
} else {
    $num_rows = mysqli_fetch_assoc($resultt);
    // echo it
    echo "<font color='yellow'>Total Messages</font>: <font color='red'><b>" . $num_rows['count']."</font></b></br>";
}

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) { ;  ?><br>
        <font color='orange'>Message From: </font></font><font color="aqua" face="Verdana, Arial, Helvetica, sans-serif"><?php 
echo htmlspecialchars($row["name"]);  ?><br>
        <font color='lightblue'>Link From Viewer: </font><a target="_blank"  href='<?php echo $row["band"] ; ?> ' ><?php echo htmlspecialchars($row["band"]);  ?></a></font> 
        <font color="aqua" face="Verdana, Arial, Helvetica, sans-serif">
        <br>
        <font color='white'> <?php echo nl2br(htmlspecialchars($row["extra"]). "<br><br><a href='delete.php?id=".$row['id']."'>Delete</a>&nbsp;|&nbsp;<a href='".$row['reply']."'>Reply</a><br>___________<br>",60);
  }
} else {
  echo "0 Messages";
}
$conn->close();
?>



</font></font></p>


<pre>&nbsp;</pre>


      <audio id="myAudio3"> 
      <source src="alertsound3.mp3" type="audio/mp3"> 
      </audio> 



<script type="text/javascript">
  setInterval(function(){
      $('.alertadd').load('alert_generate.php');
 },300);
</script>



    </td>
  </tr>
</table>

</body>
</html>

requinix · December 12, 2022

Well, first question would be, what's changed recently? Because something has changed - software doesn't just randomly decide it's not going to work anymore.

Second question would be if you can see any errors: stuff related to PHP, stuff inside your own browser (like while you sit on the alerts page), anywhere. It not working means that whatever changed is likely creating some number of error messages.

PNewCode · December 12, 2022

@requinixNo errors are showing up. I almost suspect it could be something to do with the new PHP upgrade that my server forced on my hosting plan. I can't see a single thing different with what is on my site, and what I have on the computer that I haven't changed at all.

Barand · December 12, 2022

Given the obsolete nature of the code it's surprising it lasted this long.

If it's a hosted site it probable that errors are being written to the error log instead of being displayed. Check the log.

PNewCode · December 12, 2022

@BarandI just checked the log and there are no errors showing for those pages at all. There is one from a different page that's not related to this post but it's an easy fix. But for this, nothing

mac_gyver · December 12, 2022

how many rows are in the alert database table? this nonsense code assumes that there is one row of data, with an id of 1, is using a loop to retrieve the data from the table, and isn't doing anything to insure that the single expected row exists.

PNewCode · December 12, 2022

@mac_gyverthe database for the alert has 2 tables, and 1 row each. There is a different database for the alerts than the message itself. I think thats why it loops so that it just circles back to it's own update for a new entry. (I'm guessing)

PNewCode · December 12, 2022

image.png.1b70a1e1600974a2dba04b7a45b61e4b.png

Alert table
image.png.ba81e477131ea11852898c38fde34e3f.png
Number table
image.png.e7c5e3a29d13eb19ef5e11d2afd7138e.png

gizmola · December 12, 2022

Requinix pretty much nailed it. Something has changed, and according to you it was a php upgrade. That upgrade might not only be version related, but could be server configuration related. Often with package installs, configuration files get overwritten, and some setting might have been introduced that prevents something from happening that used to happen. So strictly speaking it could be a lot of things, that nobody here has any visibility into.

Looking at your code, this depends on a small javascript timer that repeatedly makes calls to the alert_generate.php script. So there are a few things I'd do to debug this on a surface level:

Access alert_generate.php directly. See what the result is. Is there an error being generated or do you get a result?
Use the chrome developer tools from the view.php page, and open the network tab. See what is happening there. Are there any javascript errors being generated? If network calls are happening as expected, each request can be inspected to see if it is succeeding or an error is occurring.

Since you shared the code, it has to be said:

I've seen better code from a high school student working on their 1st project
- This person didn't apparently know how to use require()? They literally copy and pasted the db credentials into each script rather than putting them in a single include file so they could be shared
The database design is humorous.
- The database code could be really old, and was perhaps retrofitted to mysqli_ but still they should have used named parameters
  - I'm not 100% sure that a clever individual couldn't introduce some sort of sql injection, since the alerting code reads values from a database to then formulate a potentially harmful query
Using the php end tag ( ?> ) is bad.
- Small changes to files that intermix presentation and logic like this can break them
  - My advice would be to go through all these php scripts and remove the end tags

The PHP Framework interoperability group has published code standards that are widely adopted, so I include a link here just for reference. You can see that here if you are interested: https://www.php-fig.org/psr/psr-12/

See section 2.2. It's qualified, but in your case, all your php scripts should not have php end tags at the end (assuming that is the last thing in any of the scripts.

PNewCode · December 12, 2022

@gizmolaThank you very much for taking all that time to give some assistance. I really appreciate that. Looks like at this point I can either go without the page working untill I can learn enough to understand everything you just said, or dig up some pennies to hire someone to make me a new one (which will probably have to be my solution so I have something untill I learn more)

I will definitely use your reply to do research and get better educated. Thank you

mac_gyver · December 13, 2022

i was able to get the view page to display red, but could not get the audio file to play (probably a browser permission or timing issue.) i recommend adding logging code in both the alert and alert_generate files, with datetime, file, and action being performed so that you can see what the code is actually doing.

for what this code is doing, there's about three times too much code and most of the markup is out of date, and even when it wasn't there's a huge amount of broken html present. if you rewrite it, the major things that will simplify it are -

put the form processing code and the form on the same page.
there's no need for the alert.php file, the ajax request to it, and requesting it when the submit button is pressed (which doesn't even mean that the form got submitted.) at the point of successfully completing the post method form processing code, you would set the alert status flag in the row in the alert database table.
there's also no need for the alert_generate.php file and the ajax requests to it at 300 millisecond intervals. you are already causing the view page to be periodically reloaded. if the alert status flag is set when the page is requested, just output the markup needed to cause the page to display red, play the audio file, and reset the alert status flag.
as already stated, put the database connection code into a separate .php file and require it when needed. switching to the much simpler and more modern PDO extension will simplify all the database code.
the only time handling database statement errors in your application code will do any good are for user recoverable errors, such as when inserting/updating duplicate or out of range user submitted values. since you are not dealing with these (you should use a run-once token to prevent multiple form submissions), there's no need for any database error handling logic in your code. just use exceptions for database statement errors and let php catch and handle any exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.)

the code for any web page should be laid out in this general order (this would apply to the form processing/form page and the view page) -

initialization
post method form processing
get method business logic - get/produce data needed to display the page
html document

the form processing/form page should -

detect if a post method form was submitted.
keep the form data as a set in a php array variable, then operate on elements in this array throughout the rest of the code, i.e. don't write out lines of code copying each form field to other variables for nothing.
trim all the input data before validating it. after you do item #2 on this list, you can accomplish this with one line of code.
validate all the inputs at once, storing validation errors in an array using the field name as the array index. note: there is no 'song' field in the form and those lines of code you have for it in the form processing code are/should be producing php errors.
after the end of all the validation logic, if there are no errors (the array holding the errors will be empty), use the form data.
use a prepared query when supplying external, unknown, dynamic values to the query when it gets executed.
after using the form data (since that code could produce additional errors, if there are no errors, perform a redirect to the exact same url of the current page to cause a get request for the page.
to display a one-time success message, store it in a session variable, then test, display, and clear the session variable at the appropriate location in the html document.
it is at this point where you would set the alert status flag in the alert database table.
if there are errors at step #5 or #7, the code will continue on to display the html document, display any errors, redisplay the form, populating the field values with the existing data.
any value you output in a html context should have htmlentities() applied to it to help prevent cross site scripting.
you should validate the resulting web pages at validator.w3.org

Sign In

Php mysql entry alerts broken

Recommended Posts

PNewCode

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

PNewCode

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

PNewCode

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

PNewCode

Link to comment

Share on other sites

PNewCode

Link to comment

Share on other sites

gizmola

Link to comment

Share on other sites

PNewCode

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information