Modifying php/javascript to pull from database

[?foru]

1) Script Title: Ultimate Fade-in slideshow (v1.51)

2) Script URL (on DD): http://www.dynamicdrive.com/dynamicindex14/fadeinslideshow.htm

I located here: http://www.dynamicdrive.com/forums/showthread.php?t=10016 a php script written to grab all available photos in a directory. Thank you
Twey! The following works perfectly.


<?php
// getpics.php
$directory = "slideshow"; // Directory in which the images are stored, relative to the location of this script. Must have the slash.

header("Content-Type: text/javascript");
$dir = opendir($directory);
for($i=0;$file = readdir($dir);$i++)
if(strpos($file, ".") === 0) {
$i--;
continue;
} else echo("fadeimages[$i]=['$directory/$file', 'http://example.com/products.php', ''];\n");
?>

My question is that I have been try to get this to pull from a mySQL table, and I can't seem to get it to work.


<?php
$db_username = "I connect to db ok"; // Your database login username
$db_password = "I connect to db ok"; // Your database login password
$db_name = "I connect to db ok"; // The name of the database you wish to use
$db_host = "I connect to db ok"; // The address of the database. Often this is localhost, but may be for example db.yoursite.com

$tbl_name = "slideshow"; // The name of your table in the database
$fadebgcolor = "black"; // The color you would like to display in slideshow background - #000000 will also work


$dbh=mysql_connect($db_host, $db_username, $db_password) or die ('You need to set your database connection in this file.</td></tr></table></body></html>');
mysql_select_db($db_name) or die ('You need to set your database connection in this file.</td></tr></table></body></html>');

//display all the items from database

$query = "SELECT * FROM $tbl_name ORDER BY id";
$result = mysql_query($query);

//while(list($id,$image,$link)= mysql_fetch_row($result))
//{
$id=$r["id"];
$image=$r["image"];
$link=$r["link"];

for( $i = 0; $i < $row = $r=mysql_fetch_array($query); $i++)
{
header("Content-Type: text/javascript");

echo "fadeimages[".$row["id"]."]=['".$row["image"]."', '".$row["link"]."', ''];\n";
}
?>
The reason for the database is so that the enduser can use an update page to change the links and images if they sell a product that is featured in the fading slideshow. I've tried the "for" and "while", but I must be overlooking something. I would greatly appreciate if someone had a suggestion/solution.

Thank you

[thetestingsite]

<?php
$db_username = 'I connect to db ok'; // Your database login username
$db_password = 'I connect to db ok'; // Your database login password
$db_name = 'I connect to db ok'; // The name of the database you wish to use
$db_host = 'I connect to db ok'; // The address of the database. Often this is localhost, but may be for example db.yoursite.com

$tbl_name = 'slideshow'; // The name of your table in the database

$dbh=mysql_connect($db_host, $db_username, $db_password) or die ('You need to set your database connection in this file.</td></tr></table></body></html>');
mysql_select_db($db_name) or die ('You need to set your database connection in this file.</td></tr></table></body></html>');

//display all the items from database

$query = 'SELECT * FROM $tbl_name ORDER BY id';
$result = mysql_query($query);

header('Content-Type: text/javascript');

$count = 0;

while($q = mysql_fetch_array($result)) {
echo "fadeimages[".$count."]=['".$q['image']."', '".$q['link']."', ''];\n";

$count++;
}
?>


Hope this helps.

[?foru]

Thank you for the fast reply thetestingsite

I used your code from $query on down, but the slideshow didn't appear. No php parse errors or anything so we are good there. I also double checked the database itself to make sure that update.php was working correctly to update the db and it was.

Just to verify that all paths were correct and to try to narrow it down that it might be an issue with getpics.php I manually added the photos/links into the javascript...

fadeimages[0]=["http://example.com/images/W013.jpg", "http://example.com/proddetail.php?prod=W013", ""]

and that worked like it should. I am using full paths in the db like above with $id $image $link as the vars. I also tried a few other things but was unsuccessful. Any ideas? Thank you

[thetestingsite]

Can you post a link to a test page with the code on it so that I can examine it further.

[?foru]

Here you go :)
http://winnersbred.com/slideshow/

Thank you

[thetestingsite]

Apparently getpics.php is not returning any results. I'll look at the code again and see if I can see if I can find anything missing, but it may take a little while as I am still at work at the moment.

[?foru]

No problem, I appreciate the help so far. I'll continue to look at things as well.

[?foru]

The great thing about PHP is that most of the time there are multiple ways of doing things. I found a solution that may not be the best, but it seems to work with no lag time. Below is the code so if anyone else needs anything like this they can use it.

getpics.php (viewing it alone outputs all the items just like you would see if you added them in manually to your page.

<?php
$host="your info"; // Host name
$username="your info"; // Mysql username
$password="your info"; // Mysql password
$db_name="your info"; // Database name
$tbl_name="your info"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Retrieve data from database
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);

header("Content-Type: text/javascript");
// Start looping rows in mysql database.
while($rows=mysql_fetch_array($result)){
?>
fadeimages[<? echo $rows['id']; ?>]=["<? echo $rows['image']; ?>", "<? echo $rows['link']; ?>", ""]
<?
// close while loop
}

// close connection
mysql_close();
?>

In your page where the javascript is located add...
<?php include("getpics.php"); ?> where your links are to appear. When you view the source it will look like you manually added everything in. If I get a chance I might try to go back to see if your original code does the same.

I really appreciate the fast replies, and this seems like a great community!

[thetestingsite]

Glad to see you found a solution, and thanks for posting it so that others may benefit from it.

[student101]

In your page where the javascript is located add...
<?php include("getpics.php"); ?>

Just an update here;

// here you add your Connect to host and select database.

mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Retrieve data from database
$sql="SELECT image FROM $tbl_name"; //changed to include one field this part is so COOL!
// You can allow for restriction like SELECT image FROM $tbl_name WHERE side = 'left'
$result=mysql_query($sql);

//header("Content-Type: text/javascript"); //closed, it's not needed see below
// Start looping rows in mysql database.

$i = 0; //added this

while($rows=mysql_fetch_array($result)){
echo("fadeimages[$i]=['".$rows[image]."', '', ''];\n"); //changed this line

$i++; //added this

// close while loop
}

// close connection
mysql_close();

Now to include this you add this to a page, called random.php


<script type="text/javascript">
<!--
var fadeimages=new Array()
-->
</script>
<script src="getpics.php" type="text/javascript"></script>
<script type="text/javascript">
<!--
var fadebgcolor="#9FCBF0"

/***********************************************
* Ultimate Fade-In Slideshow (v1.51): © Dynamic Drive (http://www.dynamicdrive.com)
* This notice MUST stay intact for legal use
* Visit http://www.dynamicdrive.com/ for this script and 100s more.
***********************************************/

////NO need to edit beyond here/////////////

rest of code...


Cheers