flash[8] + php + sql - fetch entries by titleID

[tome]

 

Hi there, I've got a a guestbook setup 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

 

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

 

but I keep getting undefined message in my entries text field in flash,

any ideas what am i doing wrong?

[bubblegum.anarchy]

Post the string stored in $sql.

[tome]

 

here goes, any ideas appreciated  ???

 

<?

/*

-----

 

-----

*/

 

$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;

}

?>

[bubblegum.anarchy]

no no - post the string stored in the variable $sql during the files processing, not the entire files code.

[tome]

 

i'm fairly new to all this so forgive my ignorance, is this it:

 

CREATE TABLE guestbook (

  titleID text NOT NULL,

  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)

)

 

 

 

[tome]

 

 

hi there bubblegum.anarchy 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

[bubblegum.anarchy]

SELECT * FROM guestbook WHERE titleID = {$titleID}

 

$titleID would first require approriate injection protection if the value is being passed from browser input.

 

something like settype($titleID, "integer"); to ensure that $titleID holds an integer rather than malicious mysql code.

[tome]

thx man this code works fine

 

$sql = "SELECT * FROM $table WHERE titleID = $videoID ";

 

when i set up this varaible:

 

$videoID = "titleID";

 

 

however i still get all the comments from my database instead of the ones related to the current playing video.

 

if I change the varaible name with a titleID number like this:

 

$sql = "SELECT * FROM $table WHERE titleID = 123456789";

 

i'm able to get only titles associated with this titleID number, however this way i will have to create a php form for each video....i've got tons

 

my logic tells me that I'm doing something wrong in defining the titleID varaible for the flash/php connection,

any ideas? I've been stuck on this for days...

[bubblegum.anarchy]

see this bit of code (the problem code):

 

$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);

 

add this line:

 

print '<PRE style="text-align:left;">'; print_r($sql); die("<P>Script Halted...</P>"); print '</PRE>';

 

so your code looks like this:

 

$action = $_GET['action'];

switch($action) {
case 'read' :
$sql = "SELECT * FROM '$table' where titleID = '$titleID'";
print '<PRE style="text-align:left;">'; print_r($sql); die("<P>Script Halted...</P>"); print '</PRE>';
$allComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
$numallComments = mysql_num_rows($allComments);

 

and post the string stored in $sql... this is my first reply request.

[tome]

 

ok i must be really ignorant but i've got no idea what you mean, i take your code add it to my php file put the file on my ftp and get undefined in my test site.

if i post a comment through the test site i get the following varaibles stored to my db:

 

 

titleID

ID

name

email

comments

time

 

 

if i use myPHPadmin choose SQL and run my php code as quary i get syntax errors... ???

[bubblegum.anarchy]

Look, my apologies for not being helpful - maybe another forumer will be more so.

[tome]

 

ok some progress guys, bubblegum.anarchy or anyone else who's willing to help, sorry for being so slow but like i said i'm a newbie  ???.

So i added the line u gave me bubblegum.anarchy  and here is the print:

 

SELECT * FROM guestbook WHERE titleID = titleID  ORDER BY `time` DESC LIMIT 20, 10Script Halted...

also it only retrieves the first comment from each group of ten.

 

I understand now that the titleID varaible in php is empty hence the undefined message, so it needs to be defined and passed from my flash MC to the php form via loadVars, however i'm not sure how to do this?

[fenway]

 

ok some progress guys, bubblegum.anarchy or anyone else who's willing to help, sorry for being so slow but like i said i'm a newbie  ???.

So i added the line u gave me bubblegum.anarchy  and here is the print:

 

SELECT * FROM guestbook WHERE titleID = titleID  ORDER BY `time` DESC LIMIT 20, 10Script Halted...

also it only retrieves the first comment from each group of ten.

 

I understand now that the titleID varaible in php is empty hence the undefined message, so it needs to be defined and passed from my flash MC to the php form via loadVars, however i'm not sure how to do this?

 

Where is the the actual ID value that you want?