Pulling image name from DB with PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
nkzle
Forum Newbie
Posts: 11
Joined: Sat Sep 05, 2009 11:57 am
Location: Raleigh, NC

Pulling image name from DB with PHP

Post by nkzle »

Hey guys,
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!!
User avatar
Robert07
Forum Contributor
Posts: 113
Joined: Tue Jun 17, 2008 1:41 pm

Re: Pulling image name from DB with PHP

Post by Robert07 »

Hello,
The first thing I do when faced with a sql query which doesn't do what I expect is to echo the query, then paste it into phpmyadmin and tweak the query until it works. Then once I get it to work I'll make the appropriate changes to the code which is supposed to generate the query. I would suggest this approach in this case, since the reason for the error isn't obvious from just reading through it. If there are no errors printed out in phpmyadmin (or even a mysql console interface if you want to use that for testing) from your query, try removing the joins and where clause, one at a time, until you get some results back from the query. Once you narrow down what causes the query to not return what you want it to, then try changing that piece and adding it back in, to make it act the way you want. If you get it narrowed down like that and then get stuck, it would be easier for someone else to help you figure it out.
Regards,
Robert
User avatar
nkzle
Forum Newbie
Posts: 11
Joined: Sat Sep 05, 2009 11:57 am
Location: Raleigh, NC

Re: Pulling image name from DB with PHP

Post by nkzle »

Thank you, I'm about to try that right now. One issue i have though is I tried to use the echo call and it actually disrupted the whole design of the page. I took it out, page went right back to normal. Is there any specific way I should use the echo call? Also I tried on another forum and got a few bits of useful feedback to help revise the code to this (the first part with the array assignments is the same):

case 'music':

$con = mysql_connect("localhost","username","pw");
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`
WHERE `media`.`med_file` = `{$this->aTableFields['medProfId']}`
AND `med_id` = `P`.`PrimPhoto`
";

$result = mysql_fetch_object($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>';

now I get the the path to the profile image folder/thumb_Resource%20id%20#91 when it should be thumb_123729084.jpg for example. I think it may be the way im calling the to the query and not the query itself, or maybe the other way around, i'm very new to this and only understand the basic concept and i know i'm dealing with some hard coding as well, so forgive me i misunderstand some things =)

Thanks again!!
User avatar
Robert07
Forum Contributor
Posts: 113
Joined: Tue Jun 17, 2008 1:41 pm

Re: Pulling image name from DB with PHP

Post by Robert07 »

Yes the echo will mess up the page but it's only for debug purposes, to understand what is happening. It sounds like you are getting closer, but I think you'll need to examine the structure of the tables and make sure you are pulling the information you want to pull. I think the fastest way to do that is to pull out the query and run it directly on the db, tweaking it until it pulls the right info, then make the code changes to match whatever tweaks you made so it works.
User avatar
nkzle
Forum Newbie
Posts: 11
Joined: Sat Sep 05, 2009 11:57 am
Location: Raleigh, NC

Re: Pulling image name from DB with PHP

Post by nkzle »

Thank you very much and yes that makes complete sense. just how do i go about testing it within phpmyadmin?

::EDIT:: NEVERMIND I found how to do it =)
User avatar
nkzle
Forum Newbie
Posts: 11
Joined: Sat Sep 05, 2009 11:57 am
Location: Raleigh, NC

Re: Pulling image name from DB with PHP

Post by nkzle »

i ran the simple query from phpmyadmin:

"SELECT `med_file`
FROM `media`
WHERE `med_id` = 1
"

and it worked fine, put back in the php code:

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

mysql_select_db("dbname", $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>';

and it doesent work anymore, it just shows resource%id blah blah. it has to be the way im calling the query from the img src right? im still trying to reasearch and find the answer and i know i gotta be close, but could it be the way im calling from $imgid to the query?
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Pulling image name from DB with PHP

Post by jackpf »

You need to fetch the result set from the database using one of the mysql_fetch_* functions.
User avatar
nkzle
Forum Newbie
Posts: 11
Joined: Sat Sep 05, 2009 11:57 am
Location: Raleigh, NC

Re: Pulling image name from DB with PHP

Post by nkzle »

they give me " supplied argument is not a valid MySQL result resource " warnings, =/. i'm still messin with it though
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Pulling image name from DB with PHP

Post by jackpf »

Sounds like your query might be failing. Put "or die(mysql_error());" after your query.
User avatar
nkzle
Forum Newbie
Posts: 11
Joined: Sat Sep 05, 2009 11:57 am
Location: Raleigh, NC

Re: Pulling image name from DB with PHP

Post by nkzle »

which fetch function should i use? i tried row and array and it resulted in "array" in the call, using the object call gave me this:
"Catchable fatal error: Object of class stdClass could not be converted to string "

So now im just at a lost because i tested it in phpmyadmin with the simple query return and it worked, but it doesent work in my php code.. heres where im at now after changing from the object call

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

mysql_select_db("dbname", $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`.`Owner` = 1
";

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

if (!$imgid)
{
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>';
break;

this:
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`.`Owner` = 1

works perfect in myphpadmin
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Pulling image name from DB with PHP

Post by jackpf »

Read how these functions work. fetch_array() returns an array of the columns. fetch_object() returns an object.

You need to access the elements of the array/object. use var_dump() or print_r() to see the contents of the array/object if you're unsure what it consists of.
User avatar
nkzle
Forum Newbie
Posts: 11
Joined: Sat Sep 05, 2009 11:57 am
Location: Raleigh, NC

Re: Pulling image name from DB with PHP

Post by nkzle »

so how will i code that? like this?:

$result = mysql_query($sql);
$imgid = print_r($result);
User avatar
nkzle
Forum Newbie
Posts: 11
Joined: Sat Sep 05, 2009 11:57 am
Location: Raleigh, NC

Re: Pulling image name from DB with PHP

Post by nkzle »

i changed it out to:

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

and it messed up the page and also printed this: Resource id #91Resource id #96Resource id #100

same thing when i do "echo" as well.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Pulling image name from DB with PHP

Post by jackpf »

Yes, you need to use one of the mysql_fetch_* functions!!!!!

Look...here's an example:

Code: Select all

 
//connect to your database...etc...
 
$sql = mysql_query("SELECT `Some_Column` FROM `Some_Table` WHERE `Condition`='whatever';") or trigger_error(mysql_error());
 
$fetch = mysql_fetch_array($sql);
 
echo $fetch['Some_Column']; // will print out the data from the column "Some_Column" from the database.
 
If you still don't understand this, I think you need to go and sit down for a couple of hours and go through a good tutorial.
User avatar
nkzle
Forum Newbie
Posts: 11
Joined: Sat Sep 05, 2009 11:57 am
Location: Raleigh, NC

Re: Pulling image name from DB with PHP

Post by nkzle »

Just wondering, do the same work if your calling it from a img src as so:

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

$imgid is the var for the sql $result

I have a whole browser full of tabs with tutorials, i guess im just a bit slow :mrgreen:
Post Reply