Help with dynamic select query

  • (6 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

79 Replies - 3331 Views - Last Post: 06 December 2016 - 01:11 PM Rate Topic: -----

#1 pfar54  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 432
  • Joined: 30-April 15

Help with dynamic select query

Posted 20 October 2016 - 11:06 AM

Hi everyone,

My question is a mix or php and javascript, so I decided to put it here. I know AJAX is needed for this (which I have created), I just do not know how to modify what I have to execute my desired result.

I am creating a commenting system, which works (for the most part) and so far I have the form, select query on the comment page to display comments on page load, ajax to send insert statement and php file for the insert statement. What I do not know how to do is what to add onto my php/ajax file to allow my comments to show up after posting them without reloading the page.

Could anyone please tell me what I will have to do from this point to add onto my code to make this work.

Thank you.

Form

<form action="" method="POST" id="comment-form">
            <label for="comment">Comment</label>
            <textarea cols="15" id="home_comment" name="comment" placeholder="Message" rows="5" maxlength="1000" required></textarea><br>
            <input type="hidden" name="token" value="<?php echo Token::generate(); ?>">
            <input id="comment-button" name="submit" type="submit" value="Post">
        </form>


Comment page SELECT query:

$select_comments_sql = "
	SELECT c. *,
	p.user_id, p.img
	FROM home_comments AS c
	INNER JOIN profile_img AS p
	WHERE c.user_id = p.user_id
	ORDER BY id DESC
";
/*$select_comments_sql = "
	SELECT c. *, p.user_id, p.img
	FROM home_comments AS c
	INNER JOIN (SELECT max(id) as id, user_id 
				FROM profile_img 
				GROUP BY user_id) PI
	  on PI.user_id = C.user_id
	INNER JOIN profile_img p
	  on PI.user_id = p.user_id
	 and PI.id = P.id
	ORDER BY C.id DESC
";*/

  if ($select_comments_stmt = $con->prepare($select_comments_sql)) {
		//$select_comments_stmt->bind_param("s", $user_id);
		$select_comments_stmt->execute();
		if (!$select_comments_stmt->errno) {
			//echo "error";
		}
		$select_comments_stmt->bind_result($comment_id, $comment_user_id, $comment_username, $home_comments, $comment_date, $commenter_user_id, $commenter_img);
	//var_dump($select_comments_stmt);	
		$comment_array = array();
		while ($select_comments_stmt->fetch()) {
			$comment_array[] = $comment_user_id;
			$comment_array[] = $comment_username;
			$comment_array[] = $home_comments;
			$comment_array[] = $comment_date;
			$comment_array[] = $commenter_user_id;
			$comment_array[] = $commenter_img;
			$commenter_img = '<img class="home-comment-profile-pic" src=" '.$commenter_img.'">';
			if ($home_comments === NULL) {
				echo 'No comments found.';
			} else {
				echo '<div class="comment-post-box">';
				echo $commenter_img;
				echo '<div class="comment-post-username">'.$comment_username. '</div>';
				echo $home_comments. "<br><br><br>";
				echo '</div>';
			}
		}
  }


AJAX file

$("#comment-form").on("submit", function (event) {
		event.preventDefault();
		
		var home_comment = $("#home_comment").val();
		
		$.ajax({ 
			url: "ajax-php/comment-send.php", 
			type: "POST",
			data: {
				"home_comment": home_comment
			},
			success: function (data) {
			//  console.log(data); // data object will return the response when status code is 200
				if (data == "Error!") {
					alert("Unable to post comment!");
					alert(data);
				} else {
					$("#comment-form")[0].reset();
					//$('.newsletter-popup').fadeIn(350).delay(2000).fadeOut();
				}
			},
			error: function (xhr, textStatus, errorThrown) {
				alert(textStatus + " | " + errorThrown);
				console.log("error"); //otherwise error if status code is other than 200.
			}
		});
	});


ini_set('display_errors', 1);
error_reporting(E_ALL);
require_once '../../core/init_subaccount.php';

//For user comment	
	$user = new User();
	
	$home_comment = $_POST['home_comment'];
	$username = $user->data()->username;
	$okay = true;
	
	if ( $okay ) { 
	
		$comment_insert = "
			INSERT INTO home_comments 
			(id, user_id, username, comment, date)
			VALUES(?, ?, ?, ?, NOW())
			";
		$comment_stmt = $con->prepare($comment_insert);
		$comment_stmt->bind_param('ssss', $id, $user_id, $username, $home_comment);
		$comment_stmt->execute();
		}


Is This A Good Question/Topic? 0
  • +

Replies To: Help with dynamic select query

#2 astonecipher  Icon User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,609
  • Joined: 03-December 12

Re: Help with dynamic select query

Posted 20 October 2016 - 11:11 AM

You need to look into this function,

setInterval(function(){ alert("Hello"); }, 3000);

That way, at a set interval you can update the page based on what is in the database on a timer. When used with AJAX, there is no refresh, you just have to include all the container so that you wont duplicate what is already shown.
Was This Post Helpful? 0
  • +
  • -

#3 pfar54  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 432
  • Joined: 30-April 15

Re: Help with dynamic select query

Posted 20 October 2016 - 11:16 AM

Include all of the container...

What do you mean by this.

This post has been edited by Dormilich: 21 October 2016 - 12:52 AM

Was This Post Helpful? 0
  • +
  • -

#4 pfar54  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 432
  • Joined: 30-April 15

Re: Help with dynamic select query

Posted 20 October 2016 - 11:22 AM

So, do I do something like this? How do I send the information I am inserting back into the ajax?
	setIntervall(commentCall, 300);
	
	function commentCall(){

		$("#comment-form").on("submit", function (event) {
			event.preventDefault();
			
			var home_comment = $("#home_comment").val();
			
			$.ajax({ 
				url: "ajax-php/comment-send.php", 
				type: "POST",
				data: {
					"home_comment": home_comment
				},
				success: function (data) {
				//  console.log(data); // data object will return the response when status code is 200
					if (data == "Error!") {
						alert("Unable to post comment!");
						alert(data);
					} else {
						$("#comment-form")[0].reset();
						//$('.newsletter-popup').fadeIn(350).delay(2000).fadeOut();
					}
				},
				error: function (xhr, textStatus, errorThrown) {
					alert(textStatus + " | " + errorThrown);
					console.log("error"); //otherwise error if status code is other than 200.
				}
			});
		});
	}

Was This Post Helpful? 0
  • +
  • -

#5 astonecipher  Icon User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,609
  • Joined: 03-December 12

Re: Help with dynamic select query

Posted 20 October 2016 - 11:47 AM

SetInterval should be calling something different than the insert method. You have one method that gathers what has changed, and another that inserts data: for two reasons, 1 separation of responsibility. 2, single purpose principle.
Was This Post Helpful? 0
  • +
  • -

#6 pfar54  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 432
  • Joined: 30-April 15

Re: Help with dynamic select query

Posted 20 October 2016 - 11:49 AM

Can they be within the same files? So, are you saying I have to make a new ajax call to a new set of php code which SELECT's the information I inserted?
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher  Icon User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,609
  • Joined: 03-December 12

Re: Help with dynamic select query

Posted 20 October 2016 - 11:54 AM

They can be within the same file if you setup basic routing. It all depends on you.



Here is an example of basic routes:

if ($_SERVER['REQUEST_METHOD'] == "POST") {
    
    $action = isset($_GET['p']) ? $_GET['p'] : '';
    switch ($action) {     
        case 'post':
            insertMessage($_POST);
            break;
        default:
            displayMessages();
    }
}


Was This Post Helpful? 0
  • +
  • -

#8 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1934
  • View blog
  • Posts: 5,931
  • Joined: 15-January 14

Re: Help with dynamic select query

Posted 20 October 2016 - 11:54 AM

Another way to do it is, after the comment gets inserted into the database, you use Javascript to create the new div elements for the comment, add the content to the elements, and append the elements to the page. No ajax required for that, but if a lot of people are commenting all at once then things might appear slightly out of order for some people. You'll still need a way using setInterval or setTimeout to check for new comments from other people, though. Ideally you wouldn't load the entire list of comments every time, only the new comments. You can include a timestamp with each comment and have Javascript send the most recent timestamp to the server so that the server only responds with the comments after that timestamp, and then you append those elements to the list of comments.
Was This Post Helpful? 0
  • +
  • -

#9 pfar54  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 432
  • Joined: 30-April 15

Re: Help with dynamic select query

Posted 20 October 2016 - 12:44 PM

I am a bit confused with what you guys mean by all of this. Does anyone have an example of something similar to this that I can see to better understand?
Was This Post Helpful? 0
  • +
  • -

#10 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1934
  • View blog
  • Posts: 5,931
  • Joined: 15-January 14

Re: Help with dynamic select query

Posted 20 October 2016 - 02:21 PM

I don't have an example, but which part are you confused by? It's a pretty simple concept, these are your options:

1. Periodically get the entire list of comments from the server and replace what's on the page.
2. Get only the new comments from the server and add them to the list of comments on the page.

The only additional step I added was to manually add the comment you just submitted to the page immediately instead of waiting to load it from the server. It shouldn't be that difficult to understand though, so where are you stuck?
Was This Post Helpful? 0
  • +
  • -

#11 pfar54  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 432
  • Joined: 30-April 15

Re: Help with dynamic select query

Posted 21 October 2016 - 08:02 AM

The concept makes sense to me. I am confused on the execution. I do not know how to fetch things via ajax from the server, or in my case my php file. I have only ever sent information to a php file via ajax.

Here is my attempt. I have changed the code a little bit since originally posting it (other than the attempted retrieval code). This attempt is not working, the insert query still works. I am getting one fatal error:

Fatal error: Call to undefined method mysqli_stmt::lastInsertId() in

for this line:

if($last_id = $comment_stmt->lastInsertId()){


Then this error shows up in the console:

Uncaught ReferenceError: datastring is not defined

for:

data: datastring,


Code for the comments page:

<form action="" method="POST" id="comment-form">
			<textarea id="home_comment" name="comment" placeholder="Write a comment..." maxlength="1000" required></textarea><br>
			<input type="hidden" name="token" value="<?php echo Token::generate(); ?>">
			<input id="comment-button" name="submit" type="submit" value="Post">
		</form>
		<div id="comment-container">
<?php
$select_comments_sql = "
	SELECT c. *, p.user_id, p.img
	FROM home_comments AS c
	INNER JOIN (SELECT max(id) as id, user_id 
				FROM profile_img 
				GROUP BY user_id) PI
	  on PI.user_id = c.user_id
	INNER JOIN profile_img p
	  on PI.user_id = p.user_id
	 and PI.id = p.id
	ORDER BY c.id DESC
";

  if ($select_comments_stmt = $con->prepare($select_comments_sql)) {
		$select_comments_stmt->execute();
		if (!$select_comments_stmt->errno) {
			//echo "error";
		}
		$select_comments_stmt->bind_result($comment_id, $comment_user_id, $comment_username, $home_comments, $comment_date, $commenter_user_id, $commenter_img);
	//var_dump($select_comments_stmt);	
		$comment_array = array();
		while ($select_comments_stmt->fetch()) {
			$comment_array[] = $comment_user_id;
			$comment_array[] = $comment_username;
			$comment_array[] = $home_comments;
			$comment_array[] = $comment_date;
			$comment_array[] = $commenter_user_id;
			$comment_array[] = $commenter_img;
			$commenter_img = '<img class="home-comment-profile-pic" src=" '.$commenter_img.'">';
			if ($home_comments === NULL) {
				echo 'No comments found.';
			} else {
				echo '<div class="comment-post-box">';
				echo $commenter_img;
				echo '<div class="comment-post-username">'.$comment_username. '</div>';
				echo '<div>'.$comment_date. '</div>';
				echo '<div class="comment-post-text">'.$home_comments. '</div>';
				echo '</div>';
			}
		}
  }



AJAX

function commentCall(){

		$("#comment-form").on("submit", function (event) {
			event.preventDefault();
			
			var home_comment = $("#home_comment").val();
			
			$.ajax({ 
				url: "ajax-php/comment-send.php", 
				type: "POST",
				data: {
					"home_comment": home_comment
				},
				success: function (data) {
				//  console.log(data); // data object will return the response when status code is 200
					if (data == "Error!") {
						alert("Unable to post comment!");
						alert(data);
					} else {
						$("#comment-form")[0].reset();
						//$('.newsletter-popup').fadeIn(350).delay(2000).fadeOut();
					}
				},
				error: function (xhr, textStatus, errorThrown) {
					alert(textStatus + " | " + errorThrown);
					console.log("error"); //otherwise error if status code is other than 200.
				}
			});
		});
	}
	
	setInterval(commentRetrieve, 300);
	
	function commentRetrieve(){
	
		$.ajax({ 
				url: "ajax-php/comment-send.php", 
				type: "get",
				data: datastring,
				success: function (data) {
				//  console.log(data);
					if (data == "Error!") {
						alert("Unable to retrieve comment!");
						alert(data);
					} else {
						$('#comment-container').prepend(data);
					}
				},
				error: function (xhr, textStatus, errorThrown) {
					alert(textStatus + " | " + errorThrown);
					console.log("error"); //otherwise error if status code is other than 200.
				}
			});
	
	
	}


PHP

//For user comment	
	$user = new User();
	
	$home_comment = $_POST['home_comment'];
	$username = $user->data()->username;
	$okay = true;
	
	if ( $okay ) { 
	
		$comment_insert = "
			INSERT INTO home_comments 
			(id, user_id, username, comment, date)
			VALUES(?, ?, ?, ?, NOW())
			";
		$comment_stmt = $con->prepare($comment_insert);
		$comment_stmt->bind_param('ssss', $id, $user_id, $username, $home_comment);
		$comment_stmt->execute();
		}
		
		
		// ********* added
		
		//Get the last insert id
    if($last_id = $comment_stmt->lastInsertId()){
		$select_comments_sql = "
		SELECT c. *, p.user_id, p.img
		FROM home_comments AS c
		WHERE c.id=$last_id
		INNER JOIN (SELECT max(id) as id, user_id 
					FROM profile_img 
					GROUP BY user_id) PI
		  on PI.user_id = c.user_id
		INNER JOIN profile_img p
		  on PI.user_id = p.user_id
		 and PI.id = p.id
		ORDER BY c.id DESC
	";

	  if ($select_comments_stmt = $con->prepare($select_comments_sql)) {
			$select_comments_stmt->execute();
			if (!$select_comments_stmt->errno) {
				//echo "error";
			}
			$select_comments_stmt->bind_result($comment_id, $comment_user_id, $comment_username, $home_comments, $comment_date, $commenter_user_id, $commenter_img);	
			$comment_array = array();
			while ($select_comments_stmt->fetch()) {
				$comment_array[] = $comment_user_id;
				$comment_array[] = $comment_username;
				$comment_array[] = $home_comments;
				$comment_array[] = $comment_date;
				$comment_array[] = $commenter_user_id;
				$comment_array[] = $commenter_img;
				$commenter_img = '<img class="home-comment-profile-pic" src=" '.$commenter_img.'">';
				if ($home_comments === NULL) {
					echo 'No comments found.';
				} else {
					echo '<div class="comment-post-box">';
					echo $commenter_img;
					echo '<div class="comment-post-username">'.$comment_username. '</div>';
					echo '<div>'.$comment_date. '</div>';
					echo '<div class="comment-post-text">'.$home_comments. '</div>';
					echo '</div>';
				}
			}
		}
    }


Anyone see anything that sticks out and I on the right path?
Was This Post Helpful? 0
  • +
  • -

#12 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1934
  • View blog
  • Posts: 5,931
  • Joined: 15-January 14

Re: Help with dynamic select query

Posted 21 October 2016 - 11:23 AM

You're using ajax to fetch data from the server here:

			$.ajax({ 
				url: "ajax-php/comment-send.php", 
				type: "POST",
				data: {
					"home_comment": home_comment
				},
				success: function (data) {
				//  console.log(data); // data object will return the response when status code is 200
					if (data == "Error!") {
						alert("Unable to post comment!");
						alert(data);
					} else {
						$("#comment-form")[0].reset();
						//$('.newsletter-popup').fadeIn(350).delay(2000).fadeOut();
					}
				},
				error: function (xhr, textStatus, errorThrown) {
					alert(textStatus + " | " + errorThrown);
					console.log("error"); //otherwise error if status code is other than 200.
				}
			});

There's a success function there, that gets passed something that you called data. What do you think that is? It's the data from the server. It doesn't matter that it's a post request or that you're also sending data or whatever else, any ajax request to the server can both send and receive data. There aren't different kinds of ajax requests for whether or not you get data back from the server, the server always sends a response (even if its empty) so there's always something from the server in the ajax response.

Quote

if($last_id = $comment_stmt->lastInsertId()){

Why is that line outside of the if statement right above it? You only execute the insert query inside that if statement, but you're trying to get the ID outside of the if statement. Why is the if statement there at all? You have a hard-coded boolean variable so that it will always execute. As far as the error message itself, here is the documentation for mysqli_stmt, like the error message says it does not have a method called lastInsertId:

http://php.net/manua...mysqli-stmt.php

As far as loading the comments goes, that's going to be a separate process independent of submitting a new comment, so create a new Javascript function to load the comments and send the ajax request to the server to get all of them and display them on the page.
Was This Post Helpful? 0
  • +
  • -

#13 pfar54  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 432
  • Joined: 30-April 15

Re: Help with dynamic select query

Posted 21 October 2016 - 11:39 AM

I have created a new function to load the comments. It is posted within my new AJAX code.

The comment inserts just fine and they load just fine on page load. Just the selection after input (to appear without loading) doesn't work.

This post has been edited by pfar54: 21 October 2016 - 11:49 AM

Was This Post Helpful? 0
  • +
  • -

#14 astonecipher  Icon User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2389
  • View blog
  • Posts: 9,609
  • Joined: 03-December 12

Re: Help with dynamic select query

Posted 21 October 2016 - 11:48 AM

errors in the console?
Was This Post Helpful? 0
  • +
  • -

#15 pfar54  Icon User is offline

  • D.I.C Regular

Reputation: 0
  • View blog
  • Posts: 432
  • Joined: 30-April 15

Re: Help with dynamic select query

Posted 21 October 2016 - 11:51 AM

Yes, this one is duplicated.

comment-home.js:41 Uncaught ReferenceError: datastring is not defined
comment-home.js:41 Uncaught ReferenceError: datastring is not defined

Actually now the insert isn't working since I made these changes.
Was This Post Helpful? 0
  • +
  • -

  • (6 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »