Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
speedy33417
Forum Contributor
Posts: 128 Joined: Sun Jul 23, 2006 1:14 pm
Post
by speedy33417 » Sat Sep 30, 2006 8:07 am
I'm working on a photo album, where I need to get a path out of my database. $album indicates which album we're seeing and determines a path_id. The value of $album can be "main" if we're still on the main page or "some album name" if we already made an album selection.
If $album="main", then I don't need to need to do anything and $path=""
If $album is other than "main" then I need to get the correct path_id, then get the correct path. For some reason I'm not getting the else part of my if statement working.
Here's my code:
Code: Select all
if ($album="main") {
$path="";
} else {
echo "I'm here";
$sql2 = "SELECT album_pathid FROM albums WHERE album_thumbpicfilename = '$album' ";
$pathresult = mysql_query($sql2);
while ($row = mysql_fetch_assoc($pathresult))
{
$pathid['album_pathid'][] = $row['album_pathid'];
}
$pathid = $pathid['album_pathid'][0];
$sql3 = "SELECT path_text FROM paths WHERE path_id = '$pathid' ";
$pathresult = mysql_query($sql3);
while ($row = mysql_fetch_assoc($pathresult))
{
$path['path_id'][] = $row['path_id'];
}
$path = $path['path_id'][0];
}
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Sat Sep 30, 2006 8:28 am
speedy33417
Forum Contributor
Posts: 128 Joined: Sun Jul 23, 2006 1:14 pm
Post
by speedy33417 » Sat Sep 30, 2006 8:45 am
Thanks feyd!
I thought I was done with that fix, but for some reason I'm not getting any result for $path, however $pathid comes back fine.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Sat Sep 30, 2006 8:51 am
Combining the queries into a single query could improve it:
Code: Select all
SELECT paths.path_text
FROM albums
INNER JOIN paths
ON ( albums.album_pathid = paths.path_id )
WHERE
albums.album_thumbpicfilename = '$album'
LIMIT 1
speedy33417
Forum Contributor
Posts: 128 Joined: Sun Jul 23, 2006 1:14 pm
Post
by speedy33417 » Sat Sep 30, 2006 9:09 am
That's very nice. I'm a little uneasy about doing that, but I really should get my head around it soon.
I inserted your select statement into my code and I'm trying to get the result on one line. I have a different variable queried this way:
Code: Select all
$total = mysql_result(mysql_query("SELECT COUNT(*) FROM albums WHERE album_root = '$album' "),0);
How would I do this with this code?
Code: Select all
$path = mysql_result(mysql_query("SELECT paths.path_text FROM albums INNER JOIN paths ON ( albums.album_pathid = paths.path_id ) WHERE albums.album_thumbpicfilename = '$album' LIMIT 1"),0);
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Sat Sep 30, 2006 9:23 am
The basic idea is similar to your previous snippet
Code: Select all
$result = mysql_query("the query") or die(mysql_error());
$foo = mysql_fetch_assoc($result);
if (is_array($foo))
{
echo $foo['paths_text'];
}
else
{
echo 'no results';
}
speedy33417
Forum Contributor
Posts: 128 Joined: Sun Jul 23, 2006 1:14 pm
Post
by speedy33417 » Sat Sep 30, 2006 9:38 am
Hmmm, still something wrong.
If $album is "main" it works fine, but otherwise I'm still not getting my string out of the db to use as a path
Code: Select all
if ($album=="main") {
$path="";
} else {
$pathresult = mysql_query("SELECT paths.path_text FROM albums INNER JOIN paths ON ( albums.album_pathid = paths.path_id ) WHERE albums.album_thumbpicfilename = '$album' LIMIT 1") or die(mysql_error());
$foo = mysql_fetch_assoc($pathresult);
if (is_array($foo))
{
echo $foo['paths_text'];
}
else
{
echo 'no results';
}
}
I'm not getting anything echoed. Not even no results.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Sat Sep 30, 2006 9:47 am
oops, paths_text should be path_text.
I would guess you don't have your error_reporting set to E_ALL as that should have fired a E_NOTICE.
speedy33417
Forum Contributor
Posts: 128 Joined: Sun Jul 23, 2006 1:14 pm
Post
by speedy33417 » Sat Sep 30, 2006 10:44 am
It works!!!!!
I owe you BIG TIME! Much appreciated!