display result only if payload=1

Question

I need help with the script. I want to show the result on page only when payload=1,if payload=0 do not display..

I've tried messing with mysql statements and I realized I need to change that in my script

this is my php:

    <?php
    $dbhost = "localhost";
    $dbuser = "user";
    $dbpass = "pass";
    $dbname = "rooms";

    $conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

    if(! $conn ) {
    die('Could not connect: ' . mysqli_error());
    }
    echo 'Connected successfully<br>';

    $sql = "SELECT ID, topic, payload, DateTime_created FROM room1  ORDER 
    BY id DESC LIMIT 1 ";

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

    if(! $retval ) {
    die('Could not get data: ' . mysqli_error());
    }

    while($row = mysqli_fetch_assoc($retval)) {
    echo "ID: " . $row["id"]. "<br>";
    echo "Topic: " . $row["topic"]. "<br>";
    echo "Payload: " . $row["payload"]. "<br>";
    echo "Timestamp: " . $row["DateTime_created"]. "<br>";
    }


   mysqli_free_result($retval);
   echo "Fetched data successfully\n";

   mysqli_close($conn);

my database is: rooms table:room1 ID topic payload DateTime_created

and my index.html

  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <script 
  src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"> 
  type="text/javascript" charset="utf-8"></script>
  <script>
  $(document).ready(function(){
  sendRequest();
  function sendRequest(){
      $.ajax({
        url: "vineri.php",
        success: 
          function(data){
           $('#listposts').html(data); 

        },
        complete: function() {
       // Schedule the next request when the current one's complete
       setInterval(sendRequest, 5000); // The interval set to 5 seconds
     }
     });
   };
    });

    </script>
    </head>
    <body>
    <div id="listposts"> </div>
    </body>

How do I get the result to display only when the payload is 1 ?

`div id="listposts">` < that is missing something here, being the `<` character. Bad paste? — Funk Forty Niner, Oct 02 '19 at 15:29
`mysqli_error()` < requires a db connection as the parameter for it. — Funk Forty Niner, Oct 02 '19 at 15:30
`SELECT ID ...ORDER BY id` and `$row["id"]` are two different animals. — Funk Forty Niner, Oct 02 '19 at 15:31
guys the code works i just need help with the javascript ,now it posts the last row in my table even if the payload is 1 or 0 ,i need to set up some kind of rule to display only if payload is 1 or if someone knows how to do that with mysql i will take any kind of help i can get — moment, Oct 02 '19 at 16:11
yes Joseph but then i get my last row with payload 1 witch might not be my actual last row — moment, Oct 02 '19 at 16:13

Nancy Moore · Accepted Answer · 2019-10-03T10:56:12.753

Try this code. I have re-written it for you. I run and tested it and it works for me.

First create table and insert as follows..

create table room1(ID int primary key auto_increment, topic varchar(30), payload int(11), DateTime_created timestamp);
   insert into room1(topic, payload) values ('nancy topic 1', 1);
   insert into room1(topic, payload) values ('nancy topic 2', 0);

here is vineri.php

<?php

$dbhost = "localhost"; 
$dbuser = "root"; 
$dbpass = "pass";
$dbname = "rooms";

$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);
// Check connection
if (!$conn) {
 die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT ID, topic, payload, DateTime_created FROM room1 where payload='1' ORDER BY ID DESC LIMIT 1";

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

while($row = mysqli_fetch_array($retval)){
  echo "ID: " . $row["ID"]. "<br>";
    echo "Topic: " . $row["topic"]. "<br>";
    echo "Payload: " . $row["payload"]. "<br>";
    echo "Timestamp: " . $row["DateTime_created"]. "<br>";
}


?>

index.html

<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
  <script 
  src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"> 
  type="text/javascript" charset="utf-8"></script>


<script>

$(document).ready(function(){

// set for formality. though you can pass it to backend as a variable   
var payload= 1;
var datasend = "payload="+ payload;

        $.ajax({

            type:'POST',
            url:'vineri.php',
            data:datasend,
                        crossDomain: true,
            cache:false,
            success:function(msg){

                $('#listposts').fadeIn('slow').prepend(msg);

            }

        });


});


</script>
  </head>
    <body>
    <div id="listposts"> </div>
    </body>

WARNING: ID and id may not be the same as its cap intensive. You can see that In my table creations and sql queries I used ID instead of id. so becareful of capital and small letter that you use

Updated content

Sorry for being late. I don not understand properly what you want. Here is just what I guessed you want

if you want to get last rows with last ID of 200 then

$sql = "SELECT ID, topic, payload, DateTime_created FROM room1 where ID='200' ";

by using limit 1, only one rows will be retrieved.

if you want to get last rows with last id of 200 where payload is 0

$sql = "SELECT ID, topic, payload, DateTime_created FROM room1 where ID='200' and payload='0' ";

Here is how I edited the code. you can test various sql queries that i commented

<?php


$dbhost = "localhost"; 
$dbuser = "root"; 
$dbpass = "pass";
$dbname = "rooms";

$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);
// Check connection
if (!$conn) {
 die("Connection failed: " . mysqli_connect_error());
}

//$sql = "SELECT ID, topic, payload, DateTime_created FROM room1 where payload='0'";

//if you want to get last rows with last id of 200 where payload is 0
//$sql = "SELECT ID, topic, payload, DateTime_created FROM room1 where ID='200' and payload='0' ";


//if you want to get last rows with last ID of 200 then
$sql = "SELECT ID, topic, payload, DateTime_created FROM room1 where ID='200' ";

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

while($row = mysqli_fetch_array($retval)){
 $ID = $row["ID"];
 $topic =  $row["topic"];
  $payload =  $row["payload"];
  $DateTime_created =  $row["DateTime_created"];
}




if($payload ==0){

// insert record here


// return payload to the screen
echo "success<br><br>";

echo "ID: " .  $ID. "<br>";
    echo "Topic: " . $topic. "<br>";
    echo "Payload: " . $payload. "<br>";
    echo "Timestamp: " . $DateTime_created. "<br>";



}else{

//cannot insert record
echo "failed";

}

?>

Nancy ,using WHERE payload=1 doesn't retrieve the last row every time that's why i don't use it,as i want to show in real time on the webpage if the msg payload is 1 and don't show anything if the msg payload is 0,my database data gets inserted from sensors so u can think of it as an alarm,so while payload=1 stay on the screen and when it goes to 0 clear the screen,do you have any ideeas on how to go about doing that? thank you so much for your time — moment, Oct 02 '19 at 21:48
see updated content section. I have added more insight and more codes updates — Nancy Moore, Oct 03 '19 at 10:57
Nancy my mysql query is fine,as i retrieve the last row all the time,i need to modify my — moment, Oct 03 '19 at 13:14
I kept order by and used some of ur code and it works now ! thank you so much Nancy — moment, Oct 03 '19 at 15:43

display result only if payload=1

1 Answers1