[f8] php/sql - fetch entries by titleID

[tomera]

Hi there, I've got a a guestbook for my flash based video site and I'd like to fetch and print to flash only the entries related to the current playing video.
Each video has a unique titleID number. I print the ID number to a text field in flash and send it via php to mySQL database where it is stored under my titleID column.
this all works great now i'm trying to fetch back to my read area the related comments using the unique titleID.

this is my query line but I keep getting undefined messages:

$sql = 'SELECT * FROM ' . $table . 'where titleID='".$titleID."';

any ideas what is wrong with the code?

[alimadzi]

tomera wrote:

this is my query line but I keep getting undefined messages:

$sql = 'SELECT * FROM ' . $table . 'where titleID='".$titleID."';

any ideas what is wrong with the code?

Here's my recommendation...

echo $sql; exit;

Then if the query looks okay (no variables missing), try running it directly against your database (via command line or phpMyAdmin). Post again if you're still having problems.

[tomera]

thx man but i'm not sure i'm following you as i'm fairly new to database...is this a way to check whether the query syntex code is right?

[alimadzi]

tomera wrote:

thx man but i'm not sure i'm following you as i'm fairly new to database...is this a way to check whether the query syntex code is right?

Yes, exactly. Since we can't see any of the rest of your code, we don't know where the $table or $titleID variables come from or whether they are set correctly. Echoing out the query to the screen and then running it directly in your database is a good way to debug these sorts of issues.

Give it a try and then post more of your PHP code if you're still stuck. More code will give us a better chance of helping you.

[tomera]

thx for your help, like i said i'm able to pass the titleID number from flash to the database but now i'd like to fetch from the database only comments that have the unique titleID for the video currently playing

ok here is my read and write code:

$action = $_GET['action'];

switch($action) {
case 'read' :
// Fetch all comments from database table
$sql = 'SELECT * FROM ' . $table . 'where titleID=$titleID;
$allComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
$numallComments = mysql_num_rows($allComments);
// Fetch page-wise comments from database table
$sql .= ' ORDER BY time DESC LIMIT ' . $_GET['NumLow'] . ', ' . $numComments;
$fewComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
$numfewComments = mysql_num_rows($fewComments);
// Generate Output for Flash to Read
print '&totalEntries=' . $numallComments . '&';
print "<br>&entries=";

	 if($numallComments == 0) {
	    print "No entries in the guestbook, as yet..";
	 } else { 
	    while ($array = mysql_fetch_array($fewComments)) {
		   $name = mysql_result($fewComments, $i, 'name');
		   $email = mysql_result($fewComments, $i, 'email');
		   $comments = mysql_result($fewComments, $i, 'comments');
		   $time = mysql_result($fewComments, $i, 'time');

		   print '<b>Name: </b>' . $name .  '<br><b>Comments: </b>' . $comments . '<br><i>Date: ' . $time . '</i><br><br>';
		   $i++;
	    }
	}
	// Print this only when there aren't any more entries..
	if($_GET['NumLow'] > $numallComments) {
	   print 'No More Entries!&';
	}
	break;

  case 'write' :
     // Recieve Variables From Flash
             $titleID = ereg_replace("&", "%26", $_POST['videoID']);
	 $name = ereg_replace("&", "%26", $_POST['yourname']);
	 $email = ereg_replace("&", "%26", $_POST['youremail']);
	 $comments = ereg_replace("&", "%26", $_POST['yourcomments']);
	 $submit = $_POST['submit'];

	 // Current system date in yyyy-mm-dd format
	 $submitted_on = date ("Y-m-d H:i:s",time());

	 // Check if its submitted from Flash
	 if($submit == 'Yes'){
	 // Insert the data into the mysql table
	 $sql = 'INSERT INTO ' . $table . 
            ' (`ID`, 
                               `titleID`,
			   `name`, 
			   `email`, 
			   `comments`, 
			   `time`
			  ) 
			  VALUES 
			  (\'\','
                               . '\'' . $titleID . '\',' 
			   . '\'' . $name . '\',' 
			   . '\'' . $email . '\',' 
			   . '\'' . $comments . '\',' 
			   . '\'' . $submitted_on . '\'
			   )';
	 $insert = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());

[alimadzi]

A couple things:

1. Still can't see where you're setting $table or $titleID. Did you echo out the query and run it in your database?

2. Please wrap PHP code in the special PHP tags so we can read it more easily. Thanks.

[tomera]

thx, I'm begining to suspect that the problems is my AS - PHP to AS communication once the entries are fetched, but just in case here is my code:

<?

/*

-----
*/

$DBhost = "";

$DBuser = "";

$DBpass = "";

$DBName = "";

$table = "";

$numComments = 10;

$DBConn = mysql_connect($DBhost,$DBuser,$DBpass) or die("Error in GuestBook Application: " . mysql_error());

mysql_select_db($DBName, $DBConn) or die("Error in GuestBook Application: " . mysql_error());

$action = $_GET['action'];

switch($action) {
case 'read' :
$sql = 'SELECT * FROM ' . $table . 'where titleID=$titleID;
$allComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
$numallComments = mysql_num_rows($allComments);

	 $sql .= ' ORDER BY `time` DESC LIMIT ' . $_GET['NumLow'] . ', ' . $numComments;
	 $fewComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
	 $numfewComments = mysql_num_rows($fewComments);
	 print '&totalEntries=' . $numallComments . '&';
	 print "<br>&entries=";	

	 if($numallComments == 0) {
	    print "No entries in the guestbook, as yet..";
	 } else { 
	    while ($array = mysql_fetch_array($fewComments)) {
		   $name = mysql_result($fewComments, $i, 'name');
		   $email = mysql_result($fewComments, $i, 'email');
		   $comments = mysql_result($fewComments, $i, 'comments');
		   $time = mysql_result($fewComments, $i, 'time');

		   print '<b>Name: </b>' . $name .  '<br><b>Comments: </b>' . $comments . '<br><i>Date: ' . $time . '</i><br><br>';
		   $i++;
	    }
	}
	if($_GET['NumLow'] > $numallComments) {
	   print 'No More Entries!&';
	}
	break;

  case 'write' :
           $titleID = ereg_replace("&", "%26", $_POST['videoID']);
	 $name = ereg_replace("&", "%26", $_POST['yourname']);
	 $email = ereg_replace("&", "%26", $_POST['youremail']);
	 $comments = ereg_replace("&", "%26", $_POST['yourcomments']);
	 $submit = $_POST['submit'];


	 $submitted_on = date ("Y-m-d H:i:s",time());


	 if($submit == 'Yes'){

	 $sql = 'INSERT INTO ' . $table . 
            ' (`ID`, 
                           `titleID`,
			   `name`, 
			   `email`, 
			   `comments`, 
			   `time`
			  ) 
			  VALUES 
			  (\'\','
                               . '\'' . $titleID . '\',' 
			   . '\'' . $name . '\',' 
			   . '\'' . $email . '\',' 
			   . '\'' . $comments . '\',' 
			   . '\'' . $submitted_on . '\'
			   )';
	 $insert = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());

	 print "&gb_status=Thank you for posting a comment.&done=yes&";
	 return;
	 }
	 print "&_root.write.gb_status=Error!&";
	 break;

}
?>

[alimadzi]

Looking at your code, this is not going to work...

$sql = 'SELECT * FROM `' . $table . '`where titleID=$titleID;

You're not using quotes properly and are probably getting a parsing error. Change to this...

$sql = "SELECT * FROM '$table' where titleID = '$titleID'";

Also, I still don't see where $table and $titleID are being set to any value. What do you see when you echo the $sql variable?

[tomera]

after altering the code i still get undefined.

this is what i used to create my table:

CREATE TABLE guestbook (
ID int(5) NOT NULL auto_increment,
name text NOT NULL,
email text NOT NULL,
comments text NOT NULL,
time datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (ID)
)

then i added the titleID column do i need to make it the primary key?
I'm not sure how to echo...sorry for the lack of knowledge here.
any ideas?

[alimadzi]

tomera wrote:

I'm not sure how to echo...sorry for the lack of knowledge here.
any ideas?

Just add the echo line after you define the query. Like this...

$sql = "SELECT * FROM '$table' where titleID = '$titleID'";
echo $sql; exit;

Until we know what that query looks like, there's not much we can do to help you. It appears from the code you've shown that your variables are either empty or not set.

[maxxd]

OK - try this...

$sql = "SELECT * FROM $table WHERE titleID = $titleID ORDER BY 'time' DESC LIMIT ".$_GET['NumLow'].", $numComments;";

print("&query=&$sql");

Now, in Flash, try just tracing the contents of the loadVars object with

myLoadVars.onLoad = function():Void{
      trace(myLoadVars.toString());
}

It should show you the completed query, and tell you what values are being passed from Flash and what values are being actually assigned in the query string. It probably wouldn't hurt to get a look at the ActionScript code you're using as well, but the above should get you moving.

Basically, it looks like alimadzi is pointing you on the right track - your variables aren't getting set before you try to use them. There's no assignment for $table, $titleID, or $numComments. Also, you need to take a closer look at your quotation mark usage. You never close the quotes off the first $sql assignment string, which is throwing off everything else.

[tomera]

hey guys thx, i understand what you're saying but i'm still getting undefined message.
this is my AS code:

function loadEntries(act, increment) {
num = new Number(parent.NumLow);
if(ct == "Next") {
parent.NumLow = num + increment;
} else if(act == "Previous") {
parent.NumLow = num - increment;
} else {
parent.NumLow = 0;
}
parent.read.low.text = parent.NumLow;
parent.read.high.text = Number(parent.NumLow) + 10;
_parent.read.entries.text = "Loading entries... Please wait...";

myEntries = new LoadVars()
myEntries.ref = this
myEntries.load("GuestBook.php?action=read&r="+random(999)+"&NumLow="+_parent.NumLow) 
myEntries.onLoad = function(success){ 
	if(success){ 
		entries.text = this.entries;
		totalEntries.text = this.totalEntries;
				num = Number(_parent.NumLow)
		var totalEntries = Number(this.totalEntries); 
		var total = num+increment
		if(num==0){prev_button.enabled=false;
		back.gotoAndStop(2);}
		else
		{prev_button.enabled=true;
		back.gotoAndStop(1);}
	}
} 

}
_parent.read.loadEntries("Default", 10);
stop();

myLoadVars.onLoad = function():Void{
trace(myLoadVars.toString());
}

[maxxd]

OK - sorry. Should've been more specific with my post. Delete the portion of the code that I posted, then comment out everything within the onLoad function for the myEntries LoadVars. Now, use "trace(myEntries.toString());" inside the myEntries.onLoad function and that - as long as you're still in Flash and testing the movie, as opposed to viewing it in a browser - will trace out the entire object. This will tell you what the MySQL query is actually saying. Copy the output from the Output window and post it here, and it'll give us a better idea of what is and is not being passed.

In the meantime, there are some syntax errors within both the ActionScript and the php you've posted so far. As alimadzi pointed out, you've got some quotation mark problems in the php - that's gotta be fixed, because you never actually close the quotes after you assign the first MySQL string to the $sql variable. Beyond that, I still can't find where you're assigning a value to the $table, $titleID, and $numComments variables, but it's late and I'm tired, so maybe I'm missing it.

In the ActionScript code you've posted you're missing a lot of end-line semicolons, which (I'm pretty sure) need to be there. Unless I'm confused, AS doesn't accept whitespace as a line delimiter, so your lines are running together as they're typed now.

One more thing - it'd be easier on people reading the posts and trying to help if you make use of the php and code tags in the text editor - it preserves the tab spaces and color coding you find in DreamWeaver's code view and Flash's ActionScript editor. Just makes it easier to follow nesting and all.

[tomera]

ok man thanks for your help so far.
this is what i got in the output window when i pasted the code outside of the function:

onLoad=%5Btype%20Function%5D&ref=%5Flevel0%2Elineup%5Fdisplay%2Etitle%5Finfo%2Ecomments%5Fmc%2Eread

when i paste it inside the function i get nothing.

now just to clerify i got AS for the view area where i retrieve my entries from the database but i also have code for the write form that sends the data to SQL:

i got this on the first frame of the mc:
videoID.tabIndex = 1
yourname.tabIndex = 2
youremail.tabIndex = 3
yourcomments.tabIndex = 4
clear.tabIndex = 5
submit.tabIndex = 6

and this AS for the submit_bt:

on(click){
var myTitle😮bject = new Object();
var myTitle = root.bcPlayer.getCurrentTitle();
root.lineup_display.title_info.comments_mc.write.videoID.text = +myTitle.id;
trace(myTitle.linkURL);

	videoID = _parent._parent.write.videoID.text;
yourname = _parent._parent.write.yourname.text;
youremail = _parent._parent.write.youremail.text;
yourcomments = _parent._parent.write.yourcomments.text;
if (yourname eq "") {
	_parent._parent.write.gb_status.text = "Required: Name";
} else if (youremail eq "") {
	_parent._parent.write.gb_status.text = "Required: Email Address";
} else if (!youremail.length || youremail.indexOf("@") == -1 || youremail.indexOf(".") == -1) {
	_parent._parent.write.gb_status.text = "Required: Valid Email Address";
} else if (yourcomments eq "") {
	_parent._parent.write.gb_status.text = "Required: Comments";
} else {
	_parent._parent.write.gb_status.text = "Please wait...";
	newEntry = new LoadVars()
	newEntry.ref = this
	newEntry.submit = "Yes" 
	newEntry.videoID = videoID
	newEntry.yourname = yourname
	newEntry.youremail = youremail 
	newEntry.yourcomments = yourcomments 
	newEntry.sendAndLoad("GuestBook.php?action=write&r="+random(999), newEntry, "POST") 
	newEntry.onLoad = function(success){ 
		if(success){ 
			_parent._parent.write.gb_status.text = this.gb_status;
			_parent._parent.read.loadEntries("Default", 10);
			_parent._parent.write.yourname.text = "";
			_parent._parent.write.youremail.text = "";
			_parent._parent.write.yourcomments.text = "";
		}
	} 
}

}

now like i said in my first post, this setup works great both writing to SQL and fetching entries from SQL using the current code so the syntax must be somewhat ok...?

but i'm trying to set it up so it will retrieve entries from SQL by titleID, SQL database stores the correct data everytime i use the guesbook to post a comment but does not present the comment in the view area....

[tomera]

hi there alimadzi and maxxd thank you for your help so far, i'm just a beginner in SQL and PHP, well today i made some progress

if i add this quary line:

$sql = 'SELECT * FROM guestbook WHERE ' . ' titleID = 123456789';

to my case 'read' i get only entries associated with this titleID number but i would like to have a variable instead of a number,
i've got a variable that stores the titleID number in SQL, but how do i use the same varaible to fetch unique titleID entries from SQL, any ideas how i should proceed?
cheers