9 Replies - 286 Views - Last Post: 31 October 2017 - 10:01 AM

#1 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

changing background color of text when value changes on mysql db

Posted 27 October 2017 - 11:48 PM

To keep it simple I want the text on the web page to change color depending on the value searched for in the database. So if the value is 0 make it green and if it is 1 make it red. I have tried different ways and tried to search online but this is the closest I could get but still it does not work. I think it may have something to do with my php select statement but I dont see where. Also I do not want the page to reload every time rather consistently look for it. This is what I have so far:

index.html
<html>
<head>
<link rel="stylesheet" href="styles.css">

<script type="text/javascript">
function fetchState() {
  $.get('selectdb.php', function(data) { 
    $('#generator-state').removeClass('on off').addClass(data.state === 'on' ? 'on' : 'off');
  });
}

setInterval(fetchState, 10);
</script>


</head>
<body>

<div id="generator-state">HELLO MY NAME IS</div>

</body>
</html>


selectdb.php

 <?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "log_table";

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

$sql = "Select value1 FROM log_table WHERE device_name=LR_temp1";
$result = mysql_query($sql);
$data = mysql_fetch_row($result);
?>  


styles.css
#generator-state { height: 100px; width: 100px;}
#generator-state.on { background: green; }
#generator-state.off { background: red; }



Is This A Good Question/Topic? 0
  • +

Replies To: changing background color of text when value changes on mysql db

#2 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6377
  • View blog
  • Posts: 25,768
  • Joined: 12-December 12

Re: changing background color of text when value changes on mysql db

Posted 28 October 2017 - 01:03 AM

You should not be using the mysql extension library, it has been deprecated for many years and removed from PHP 7. See my signature below for relevant links.

Nor should you be mixing the mysqli and mysql extensions.

Your first task then, is to switch to, preferably, PDO.



A basic debugging step is to display the value of data obtained via Ajax. Your PHP is not returning anything. You should echo the value to return; assigning $data in PHP does not automatically populate an argument named data in JS.

The device_name is also not specified correctly.
Was This Post Helpful? 0
  • +
  • -

#3 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

Re: changing background color of text when value changes on mysql db

Posted 28 October 2017 - 04:41 PM

So I did this:

 <?php
$link = mysql_connect('localhost','root','')
mysql_select_db('log_table', $link);
$sql = 'SELECT value1 FROM log_table LIMIT 1';
$result = mysql_query($sql, $link) or die(mysql_error());
$row = mysql_fetch_assoc($result);
echo($row);
?> 


However, I am still not getting anything is this the correct way of returning something?
Was This Post Helpful? 0
  • +
  • -

#4 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1829
  • View blog
  • Posts: 5,758
  • Joined: 15-January 14

Re: changing background color of text when value changes on mysql db

Posted 30 October 2017 - 09:52 AM

Echo is how you output data in PHP, but you're trying to output an entire array. mysql_fetch_assoc returns an array of data, not a single value. You can encode the array as JSON and send it back that way, or you can output a single value from that array.

But, more importantly, your code is not going to run at all in the current version of PHP. You're using the old mysql extension, which has been out of date since 2004 and removed entirely in the current version (at this point that code is 13 years out of date - why anyone would bother learning something that old is beyond me). When people suggest you need to change that, that's not a "when you get around to it" kind of suggestion. It means you're doing something wrong, and you need to fix it if you want your code to run at all.
Was This Post Helpful? 0
  • +
  • -

#5 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

Re: changing background color of text when value changes on mysql db

Posted 30 October 2017 - 01:30 PM

So I have changed my code around a bit now that I had more time to look at it. If I run the php file direct I get a response of 1 which is correct from the db but if I go through the html file I see nothing. Can you explain a little more as to what you mean be encode JSON? This is what it looks like now:

index.html
<html>
<head>
<meta content="text/html;charset=utf-8" http-equiv="Content-Type">
<meta content="utf-8" http-equiv="encoding">
<link rel="stylesheet" href="styles.css">
   </head>
    <body>

<div id="generator-state">HELLO MY NAME IS</div>
<script type="text/javascript">
function fetchState() {
  $.get('selectTest.php', function(data) { 
    $('#generator-state').removeClass('on off').addClass(data.state === 'on' ? 'on' : 'off');
  });
}

setInterval(fetchState(), 10*60*1);
</script>

</body>
</html>



selectTest.php

<!DOCTYPE html>
<html>
<body>

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "log_table";

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

$sql = "SELECT value1 FROM log_table";
$result = $conn->query($sql);
$getID = mysqli_fetch_assoc(mysqli_query($conn, "SELECT value1 FROM log_table"));
$data = $getID['value1'];
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<br>" . $row["value1"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

</body>
</html>


This post has been edited by Brandon222: 30 October 2017 - 01:39 PM

Was This Post Helpful? 0
  • +
  • -

#6 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1829
  • View blog
  • Posts: 5,758
  • Joined: 15-January 14

Re: changing background color of text when value changes on mysql db

Posted 30 October 2017 - 03:15 PM

In your callback function you're checking data.state, but data is just going to be the text returned by the server. It doesn't have a state property I don't think. In your case, data is going to be set to this:

<!DOCTYPE html>
<html>
<body>

<br>1<br>

</body>
</html>


You probably don't want all of that HTML in the response. Sure, that looks great in a browser, but you only need to return the actual data you're looking for. Even if you were returning an HTML response to an ajax request, you would probably only return specific elements to update the page with instead of a complete HTML document.
Was This Post Helpful? 0
  • +
  • -

#7 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

Re: changing background color of text when value changes on mysql db

Posted 30 October 2017 - 03:57 PM

Alright so I finally got it working. Now I am just wondering if there is a better way of doing this where it doesnt flash on me constently. Everytime it checks it looks like its refreshing the page in a way. Here is my new code please let me know if there is a more appropriate way of doing this:

index.php

<html>
<head>
<meta content="text/html;charset=utf-8" http-equiv="Content-Type">
<script src="http://code.jquery.com/jquery-latest.js"></script>
<meta content="utf-8" http-equiv="encoding">
<link rel="stylesheet" href="styles.css">
<script type="text/javascript">
function UpdateDb()
{
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("result").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","updatedb.php",true);
xmlhttp.send();
}
</script>

<script>
 $(document).ready(function() {
    setInterval(function(){
    $.post('selectTest.php', {}, function(data){
		$('#responsecontainer').html(data);
 });}, 5000); 
 }); 
</script>
   </head>
    <body>

<button onclick='UpdateDb()'>Update</button>
<div id="responsecontainer">
</div>

</body>
</html>


updatedb.php

 <?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "log_table";

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

$sql = "UPDATE log_table SET value1=1";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?> 
<html>
Hello there!
<a href='index.html'>HOME</a>
</html> 



selectTest.php

<!DOCTYPE html>
<html>
<body>

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "log_table";

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

$sql = "SELECT value1 FROM log_table";
$result = $conn->query($sql);
$getID = mysqli_fetch_assoc(mysqli_query($conn, "SELECT value1 FROM log_table"));
$data = $getID['value1'];
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		if($row["value1"] == "0")
			echo "<div id='generator-state' class='on'><br> ON <br></div>";
		else
			echo "<div id='generator-state' class='OFF'><br> OFF <br></div>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

</body>
</html>



styles.css

#generator-state { height: 100px; width: 100px;}
#generator-state.on { background: green; }
#generator-state.off { background: red; }


Was This Post Helpful? 0
  • +
  • -

#8 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1829
  • View blog
  • Posts: 5,758
  • Joined: 15-January 14

Re: changing background color of text when value changes on mysql db

Posted 30 October 2017 - 05:28 PM

Well, first, you're writing a complete HTML document into a div, and I'm not sure how the browser would handle that. You end up with a structure like this:

...
<div id="responsecontainer">
<!DOCTYPE html>
<html>
<body>

<div id='generator-state' class='on'><br> ON <br></div>

</body>
</html>
</div>
...

Obviously, it's not valid to have a doctype, html tag, or body tag inside another body tag.

I would return only the word "ON" or "OFF", put the rest of the markup in the parent page. There's no reason to return all of that markup unless it's going to be too complex to represent everything in the parent page. You don't need the br tags either, use CSS to set the margin on that inner div element, and only update the contents of that inner div with the response from the server. Since the class matches the word you're returning, you can even set the class on that div with the same thing that was returned from the server. And, if you're only returning that, then instead of always setting the HTML and forcing the browser to redraw that, you can check what the current HTML of that div is and only change it if it's different.
Was This Post Helpful? 0
  • +
  • -

#9 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 150
  • Joined: 16-November 08

Re: changing background color of text when value changes on mysql db

Posted 30 October 2017 - 07:02 PM

Any idea how I can tell the browser to only redraw when something changes?
Was This Post Helpful? 0
  • +
  • -

#10 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1829
  • View blog
  • Posts: 5,758
  • Joined: 15-January 14

Re: changing background color of text when value changes on mysql db

Posted 31 October 2017 - 10:01 AM

Get the current text inside the div and compare it with what came back from the server. Only change the div if it's different.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1