[RESOLVED] Pulling image name from DB with PHP

[nkzle]

Hey guys,
--I'm asking this on a couple of forums, praying for some help!! --
I'm using Dolphin open script smart community builder and did a lot of customizing to it, but one thing I'm trying has had me stuck for over a week now (because I'm a PHP noob). I want to pull the member's primary photo from our database and use it as their music thumbnail. Currently it's just a simple default global thumbnail that's called upon in a simple URL request. I've already set up a string to each members profile image URL and did multiple inner joins in a sqlQuery which i believe is executed right because i asked for a error if there was a issue and now I don't receive any but when I look for the thumbnail nothing shows up. Here's the pieces of the code:

///this apiece in one function that the music FILES are defined in a array:

case 'music':
$this->sPrLinkPar = 'permalinks_gallery_music';
$sAddPath = 'ray/modules/music/files/';
$this->sFilesPath = BX_DIRECTORY_PATH_ROOT . $sAddPath;
$this->sFilesUrl = $this->aConfigSite['url'] . $sAddPath;
$this->sThumbUrl = $this->aConfigSite['profileImage']; //I added
$this->sMainTable = 'RayMusicFiles';
$this->sViewActionName = ACTION_ID_VIEW_GALLERY_MUSIC;

$this->aTableFields = array(
'medID' => 'ID',
'medProfId'=> 'Owner',
'medPhoto' => 'med_id',// I added
'medTitle' => 'Title',
'medUri' => 'Uri',
'medDesc' => 'Description',
'medTags' => 'Tags',
'medDate' => 'Date',
'medViews' => 'Listens',
'Approved' => 'Approved'
);

break;

////here's the piece in another function where i try to connect to the database and display the image (i took out my db info for security reasons):

case 'music':
$con = mysql_connect("localhost","host","pswrd");
if (!$con)
{
die('Could not connect, D: ' . mysql_error());
}

mysql_select_db("db_name", $con);

$result = mysql_query("SELECT media.{$this->aTableFields['medPhoto']}
FROM ((media INNER JOIN Profiles AS P ON P.ID = media.med_prof_id)
INNER JOIN {$this->sMainTable} AS Ray ON Ray.{$this->aTableFields['medProfId']} = media.med_prof_id)
INNER JOIN media AS thumb ON thumb.med_id = P.PrimPhoto
WHERE thumb.med_file = {$this->aTableFields['medProfId']}
");

if (!$result)
{
die('U F*d Up Somewhere, D: ' . mysql_error());
}

$sPhoto = $result;
if ($aFile['medCount'] - 1 > 0) {
$sImage = '<div class="lastFilesPic"><a href="'.$sHref.'"><img src="'.$this->sThumbUrl.$aData['medProfId'].'/thumb_'.$sPhoto['medProfId'].'"></a></div>';
}
break;

EDIT NOTE: Sorry I forgot to add :
'media' table is where the members images(med_file) are stored. Each image is named like 13769123687122.jpg or something and is identified by ID('med_id')
'Profiles' of course is the table of the members ID's('ID') and primary photo ID's('PrimPhoto') etc.
{$this->sMainTable} is the table 'RayMusicFiles' which is where the members music info is stored along with their member ID's({$this->aTableFields['medProfId']})

Any help will be GREATLY appreciated. This one little thing has been wreckin' my brain the past week :banghead: and I searched and searched online through Google and other resources like w3schools and though I've came a lot closer than before (the first little codes i tried to put together were HILARIOUS) I still can't afford the time to continue to dance in circles around this issue.

Please help!!

[tomhath]

Store the SQL in a php variable and echo it out before you run the query, you'll probably spot the problem. But from your description the WHERE clause doesn't look right. Is the name of the media file the medProfId?

WHERE `thumb`.`med_file` = `{$this->aTableFields['medProfId']}`

[nkzle]

Thank you for your response!! "medProfID" is the member ID number in that specific table which in that case is 'RayMusicFiles'.That's why i tried to inner join all 3 tables for that sake. when you say store it as a PHP variable, do you mean something like:

$sPhoto = $result;

echo $sPhoto

?

Just want to make sure I fully understand =D. Should the WHERE be the column of the file? Maybe I did it backwards, I wouldn't be surprised.

[tomhath]

Try something like this. Your query is hard to read because you mix variables and hardcoded table and column names, and have a couple of unnecessary sets of parenthesis:

$sql = "SELECT `media`.`{$this->aTableFields['medPhoto']}`
FROM ((`media` INNER JOIN `Profiles` AS `P` ON `P`.`ID` = `media`.`med_prof_id`)
INNER JOIN `{$this->sMainTable}` AS `Ray` ON `Ray`.`{$this->aTableFields['medProfId']}` = `media`.`med_prof_id`)
INNER JOIN `media` AS `thumb` ON `thumb`.`med_id` = `P`.`PrimPhoto`
WHERE `thumb`.`med_file` = `{$this->aTableFields['medProfId']}`
";

echo $sql;

mysql_select_db("db_name", $con);

$result = mysql_query($sql);

[tomhath]

THis is how I read your query, you are selecting two rows from the media table because you're doing a self join to it, once as 'media' and once as 'thumb'.

SELECT media.medPhoto -- return a row from the media table
FROM media 
	-- the media table
INNER JOIN Profiles AS P 
	ON P.ID = media.med_prof_id 
	-- select a media row for user by profile ID
INNER JOIN RayMusicFiles AS Ray 
	ON Ray.medProfId = media.med_prof_id 
	-- select user by profile id
INNER JOIN media AS thumb 
	ON thumb.med_id = P.PrimPhoto 
	-- selecting another row from the media table ??
	-- if this is the thumbnail, what is the other row you joined to P.ID ??
WHERE thumb.med_file = Ray.medProfId
	-- this confuses me you joined RaymedProfID to one media row on med_prof_id,
	-- then you joined it to a different media row on med_file
	-- but you never do anything with the second row (from thumb) ??

[nkzle]

OK we're definitely closer with few issues. When i Changed out the code with your suggestion, the "echo $sql;" line messed up the whole page. I took that out and the page showed up fine. and before when i try to look at the music thumbnail address it'll be blank after the profile image directory(which is $this->sThumbUrl = $this->aConfigSite['profileImage'];with "thumb" after it, now instead of being blank after 'thumb', i get Resource&#37;20id%20#91, Resource%20id%20#96 and Resource%20id%20#100 (there are currently three music files need thumbnails btw) So there has definitely been some progress. You can see what page I'm referring to here: http://uknoe.com/uknoe/music/all/10/1.

I'ma continue to play around with the code and I commented on your layout of how you read the query and you we're basically right, this is just to clear up a few things:

SELECT media.medPhoto -- return a row from the media table // Correct
FROM media
-- the media table // Correct
INNER JOIN Profiles AS P
ON P.ID = media.med_prof_id
-- select a media row for user by profile ID // Correct
INNER JOIN RayMusicFiles AS Ray
ON Ray.medProfId = media.med_prof_id
-- select user by profile id //Correct
INNER JOIN media AS thumb
ON thumb.med_id = P.PrimPhoto
-- selecting another row from the media table ?? // In the 'Profiles' table where the users main info is, it has a Primary Photo row(PrimPhoto) which just identifies which of the member's photo in the 'media' table to use as his/her main profile picture (example if your account had 4 photos in the 'media' table, and you picked photo id 3 (med_id) as your Primary photo(PrimPhoto))--I hope i didn't confuse more!! =D, so I was trying to join the two together.
-- if this is the thumbnail, what is the other row you joined to P.ID ?? //The member ID for that table('media') which was 'med_prof_id' so when they call on the member ID it'll select the same member in both tables, and the same when i try to add RayMusicFile table which is 'Owner' (defined as "medProfId" in the array above the sql code)
WHERE thumb.med_file = Ray.medProfId
-- this confuses me you joined RaymedProfID to one media row on med_prof_id,
-- then you joined it to a different media row on med_file
-- but you never do anything with the second row (from thumb) ?? //I simply thought WHERE should 'med_file' from 'media' table which would be selected by the member profile ID (from 'profiles', 'media' and 'RayMusicFiles')

I hope this helped!! I'm still playing around with it though and thank you so much for your swift responses!!!

[nkzle]

Oh BTW this is where the code stand now (just the 2nd part, first part with the arrays is still the same)

		case 'music':	

		$con = mysql_connect("localhost","username","password");
                    if (!$con)
                    {
                    die('Could not connect, D: ' . mysql_error());
                    }

                    mysql_select_db("db_name", $con);

                    $sql = "SELECT `media`.`{$this->aTableFields['medPhoto']}`
                    FROM ((`media` INNER JOIN `Profiles` AS `P` ON `P`.`ID` = `media`.`med_prof_id`)
                    INNER JOIN `{$this->sMainTable}` AS `Ray` ON `Ray`.`{$this->aTableFields['medProfId']}` = `media`.`med_prof_id`)
                    INNER JOIN `media` AS `thumb` ON `thumb`.`med_id` = `P`.`PrimPhoto`
                    WHERE `thumb`.`med_file` = `{$this->aTableFields['medProfId']}`
                    ";


                    $result = mysql_query($sql); 

                    if (!$result)
                    {
                    die('U F*d Up Somewhere, D: ' . mysql_error());
                    }

                $imgid = $result;

                            $sImage = '<div class="lastFilesPic"><a href="'.$sHref.'"><img src="'.$this->sThumbUrl.$aData['medProfId'].'/thumb_'.$imgid['medProfId'].'"></a></div>';

			break;

[tomhath]

I think the problem is that you are only returning one field from the media table, the column named medPhoto, but later you try to get the value of $imgid['medProfId']. Are those the same? If not, $imgid['medProfId'] will be NULL.

Also the last join to thumb doesn't do anything as far as I can tell; it either selects the same row as the one you already selected from media, or some other row that you don't return to php. Remember that the row returned by "FROM media" doesn't have to be the same row as the one returned by INNER JOIN media AS thumb, think of those as two different tables (even though they are physically the same table, they are two different sets of data in your query).

Maybe what you intended is:

$sql = "SELECT `media`.`{$this->aTableFields['medPhoto']}`
FROM  `media` 
INNER JOIN `Profiles` AS `P` 
    ON `P`.`ID` = `media`.`med_prof_id` 
INNER JOIN `{$this->sMainTable}` AS `Ray` 
    ON `Ray`.`{$this->aTableFields['medProfId']}` = `media`.`med_prof_id` 
WHERE `media`.`med_file` = `{$this->aTableFields['medProfId']}`
    and `media`.`med_id` = `P`.`PrimPhoto`");

[nkzle]

Hey, am i suppose to re-add the parenthesis? I get a Parse error, unexpected ')' at the end of it. Also I was wondering, is

" WHERE media.med_file = {$this->aTableFields['medProfId']}" does this mean it picks the med_file based on the member Profile ID number thats submitted? thats whats i was aiming for and i think the rest is what i was looking for.

Thanks again!!

[nkzle]

I modified your suggestion a tad bit to:

                    $sql = "SELECT `media`.`{$this->aTableFields['medPhoto']}`
                    FROM `media`
                    INNER JOIN `Profiles` AS `P`
                    ON `P`.`ID` = `media`.`med_prof_id`
                    INNER JOIN `{$this->sMainTable}` AS `Ray` ON `Ray`.`{$this->aTableFields['medProfId']}` = `media`.`med_prof_id`
                    WHERE `media`.`med_file` = `{$this->aTableFields['medProfId']}`
                    AND `med_id` = `P`.`PrimPhoto`
                    "; 

$imgid = $result;

$result = mysql_query($sql);

and now the page works again but im getting the same results. could it be the img src?:

                    $sImage = '<div class="lastFilesPic"><a href="'.$sHref.'"><img src="'.$this->sThumbUrl.$aData['medProfId'].'/thumb_'.$imgid.'"></a></div>';

[tomhath]

You need to call mysql_query before you'll have anything in $result, move the assignment to $imgid to after you call it.

Do you have some other way to test the query? Something like phpmyadmin or one of the other mysql management tools would be very helpful troubleshooting this. I always get the query working right first before trying to incorporate it into other code.

[nkzle]

i do have phpadmin but i dont know how to exactly test it. I did take everything out and tried a simple query first to get the query workin and i'm still getting "Resource ID blah blah" so obviously I'm just not doing doing the whole thing right :queasy:. Heres the simple query i'm trying:

$con = mysql_connect("localhost","uknoecom_uKnk","$}iz}9%2");
if (!$con)
{
die('Could not connect, D: ' . mysql_error());
}

                    mysql_select_db("uknoecom_thebase", $con);
                    $sql = "SELECT `med_file`
                    FROM `media`
                    WHERE `med_id` = 1  
                    "; 

                    $result = mysql_query($sql);
                    $imgid = $result;

                    if (!$result)
                    {
                    die('U F*d Up Somewhere, D: ' . mysql_error(). " Actual query: " . $sql);
                    }

                            $sImage = '<div class="lastFilesPic"><a href="'.$sHref.'"><img src="'.$this->sThumbUrl.$aData['medProfId'].'/thumb_'.$imgid.'"></a></div>';

what am i doing wrong? I selected the file from the med_file column which holds the image file name (for example 1243256745.jpg) FROM the table 'media' which it's in and put the WHERE as the med_id which is the id of the first image ("1"). what am i doing wrong?

[nkzle]

wow i totally forgot to take out my db info =(

[nkzle]

anyway i did the simple query in phpmyadmin(

"SELECT med_file FROM media WHERE med_id = 1"
and it worked. but when i put back in the code like before, it just pull some resource ID claim or somthing

[tomhath]

You have it right, the result from mysql_query is a resource id (just a number). Now you need to get the rows returned by the query that are identified by the resource. I should've noticed this earlier, it's been a while since I wrote much php code...

http://us.php.net/manual/en/function.mysql-fetch-array.php

[nkzle]

Yup i figured it out yesturday. I had to use the object fetch array AFTER the query (that was the tough part) as so:

$result = mysql_query($sql);
$imgid = mysql_fetch_object($result);

and also wasent calling from the img src right so i switched it up as so:

$sImage = '<div class="lastFilesPic"><a href="'.$sHref.'"><img src="'.$this->sThumbUrl.$aData['medProfId'].'/thumb_'.$imgid->med_file.'"></a></div>';

after that all i had to do was set the WHERE in the query to choose the column from the member ID number being call as so:

WHERE Ray.{$this->aTableFields['medProfId']}= {$aData['medProfId']}

so thank you for all your help!!! this forum and another forum along with countless tutorials and blogs helped me get this answer and i appreciate very very much. If this post could help someone else that would be awsome and just for that purpose heres the final code that works:

$con = mysql_connect("localhost","name","pw");
if (!$con)
{
die('Could not connect, D: ' . mysql_error());
}

mysql_select_db("db name", $con);

$sql = "SELECT media.med_file
FROM media
INNER JOIN Profiles AS P ON P.ID = media.med_prof_id
INNER JOIN RayMusicFiles AS Ray ON Ray.Owner = media.med_prof_id
INNER JOIN Profiles AS PP ON PP.PrimPhoto = media.med_id
WHERE Ray.{$this->aTableFields['medProfId']}= {$aData['medProfId']}
";

$result = mysql_query($sql);
$imgid = mysql_fetch_object($result);

if (!$result)
{
die('U F*d Up Somewhere, D: ' . mysql_error(). " Actual query: " . $sql);
}

$sImage = '<div class="lastFilesPic"><a href="'.$sHref.'"><img src="'.$this->sThumbUrl.$aData['medProfId'].'/thumb_'.$imgid->med_file.'"></a></div>';

There isn't any forum post that I've found on the EXACT issue i was having because everyone was using print or echo to display their results and i couldn't find any post where someone had issue calling from a var within the img src. Not say saying there isn't any, i just didn't find any so hopefully this will help others!!

Thanks again guys!!