php mysql join to get image

GreenUser · June 26, 2008

Hi all,

I want an avatar to display next to a post. The post comes from my stories table, the image from my users table. Well, this part looks fine, until it displays the image, it always displays my empty image, dummy.jpg. So meaning that $imgrows comes back empty. At least, I'm getting to the table, but how to display the picture of the individual user who posted the post.

It is set to view five posts, next to the first image is Resource id #20, next is " " 23, " " 26 and on.

<?php
$imgsql = "SELECT stories.username, users.image "." FROM stories, users ". " WHERE stories.username = users.username";
$imgres = mysql_query($imgsql);
$imgrows = mysql_num_rows($imgres);

$res = mysql_query($sql) OR die(mysql_error());
	echo "$res";

if($imgrows == 0)
{
	echo "No Image<br>";
}

	if(empty($imgrows['image'])) {
		echo "<img src='./userimg/dummy.jpg' width=100 height=100 alt='" . $imgrow['name'] . "'>";
	}
	else {
		echo "<img src='./userimg/" . $imgrows['image'] . "' width=100 height=100  alt='" . $imgrow['name'] . "'>";
	}
		echo"<a href='viewstory.php?id=".$row['id']."'> Somewhere in ".$row['location']."<strong>
			".$row['username']."</strong> ate at all the <strong>".$row['nagel']."</a></strong>
			<font color=red><b>".$avgrow['score']." % </b></font><p>";
?>

lemmin · June 26, 2008

Does "No Image" get printed? If not, you have an error in your sql. Put or die(mysql_error()); at the end of youe mysql_query().

If it isn't getting printed then I would guess that the data in your table isn't what you expect.

GreenUser · June 26, 2008

Sorry to have not been clear on that, the OR die is there and displays Resource Id #20, #23, #26, #29, #32 next to each image.

Now if the code looks like this it will echo No Image, with the dummy.jpg, and the sql error.

$imgsql = "SELECT stories.".$row['username'].", users.image "." FROM stories, users ". " WHERE stories.".$row['username']." = users.".$row['username']."";

lemmin · June 26, 2008

You only have the "or die" on one of the two querys, but since it DOES echo "No Image," there is not an error in the first one; however, there are a lot of ambiguos variables in your code. I am assuming that you only cute out a portion to post, but it makes it a little confusing because I don't know what some of those variables are supposed to be referencing. The code on this line makes absolutely no sense to me:

echo "<img src='./userimg/" . $imgrows['image'] . "' width=100 height=100  alt='" . $imgrow['name'] . "'>";

because $imgrows is supposed to be a $variable holding a single number and 'name' isn't even a column in the query, but since it looks like some code is missing I will assume you are fetching the array somewhere. In any case, to troubleshoot I would suggest the following code:

<?php
$imgsql = "SELECT stories.username, users.image "." FROM stories, users ". " WHERE stories.username = users.username";
$imgres = mysql_query($imgsql) or die(mysql_error());
while ($row = mysql_fetch_assoc($imgres))
	print_r($row);
?>

Try that and see what comes out. If it is still blank, I am pretty sure the data is either not there or doesn't exist in the manner that you expect.

Stooney · June 26, 2008

You need to fetch the rows from the resource. As it stands $imgrows is just a number of how many rows were pulled with the query. You need to user mysql_fetch_array($imgsql); somewhere to get the actual data into a usable format.

Try something like that: (untested)

<?php
$imgsql = "SELECT stories.username, users.image "." FROM stories, users ". " WHERE stories.username = users.username";
$imgres = mysql_query($imgsql);
$imgrows = mysql_num_rows($imgres);
$imgdata=mysql_fetch_array($imgres);

$res = mysql_query($sql) OR die(mysql_error());
echo "$res";

if($imgrows == 0)
{
	echo "No Image<br>";
}

if(empty($imgdata['image'])) {
	echo "<img src='./userimg/dummy.jpg' width=100 height=100 alt='" . $imgdata['name'] . "'>";
}
else {
	echo "<img src='./userimg/" . $imgdata['image'] . "' width=100 height=100  alt='" . $imgdata['name'] . "'>";
}
echo"<a href='viewstory.php?id=".$row['id']."'> Somewhere in ".$row['location']."<strong>
	".$row['username']."</strong> ate at all the <strong>".$row['nagel']."</a></strong>
	<font color=red><b>".$avgrow['score']." % </b></font><p>";
?>

GreenUser · June 27, 2008

Okay, leaving the print_r and adding a while loop of my own. It returns all the images next to the post, and an array of all the user post like this

Array ( [id] => 34 [cat_id] => 1 [username] => Brad [dateposted] => 2008-06-26 12:33:55 [location] => California [wen] => 2008-06-04 [nagel] => Sun Girl [body] => Sun. Sun.Sun.Sun.Sun.Sun. Sun.Sun.Sun.Sun.Sun. Sun.Sun.Sun. [fri] => 6 [fun] => 6 [soh] => 6 [con] => 6 [sma] => 6 [sex] => 6 [hyg] => 6 [mes] => 6 [aut] => 6 [est] => 6 )

Here is the full file, only posted the relevant, that I thought at the time.

<?php

require("db.php");

session_register("SESS_PARENT");
session_register("SESS_CHILD");

if(isset($_GET['parentcat']) && isset($_GET['childcat'])) {
if(is_numeric($_GET['parentcat'])) {
	$_SESSION['SESS_PARENT'] = $_GET['parentcat'];
}

if(is_numeric($_GET['childcat'])) {
	$currentcat = $_GET['childcat'];

	$_SESSION['SESS_CHILD'] = $_GET['childcat'];
}
}
else if(isset($_GET['parentcat'])) {
if(is_numeric($_GET['parentcat'])) {
	$currentcat = $_GET['parentcat'];

	$_SESSION['SESS_PARENT'] = $_GET['parentcat'];
	$_SESSION['SESS_CHILD'] = 0;
	}
}
else {
	$currentcat = 0;
}

require("header.php");

echo "<div id=main1>";

$hotsql = "SELECT * FROM stories ORDER BY RAND() LIMIT 1;";
$hotresult = mysql_query($hotsql);
$hotrow = mysql_fetch_assoc($hotresult);

$avgsql = "SELECT COUNT(id) AS number, AVG(rating) AS avg FROM ratings WHERE story_id = " . $hotrow['id'] . ";";
$avgresult = mysql_query($avgsql);
$avgrow = mysql_fetch_assoc($avgresult);

		echo"<strong><a href='viewstory.php?id=".$hotrow['id']."'>
			<p>".$hotrow['username']." xxx  ".$hotrow['nagel']."</p></p></a></strong>";

echo "<p>";
echo "<strong>Users Rating</strong>: ";

if($avgrow['number'] == 0) {
echo "No Ratings!";
}

else {
$a = (round($avgrow['avg'] * 2) / 2) . "<br>";
$a *= 10;

if($a%5 == 0 && $a%10 != null) {                   /// line 38
	$range = ($a / 10) - 0.5;
}
else {
	$range = $a / 10;
}

for($i=1;$i<=$range;$i++) {
echo "<img src='" . $config_basedir . "/img/hfull.png'>";
}

if($a%5 == 0 && $a%10 != null) {
echo "<img src='" . $config_basedir . "/img/hhalf.png'>";
}

$a = $a / 10;
$remain = 10 - $a;

for($r=1;$r<=$remain;$r++) {
	echo "<img src='" . $config_basedir . "/img/hnone.png'>";
	}
}


		echo "<p>" . $hotrow['body'] . "</p>";

echo "</div>";

///

echo "<div id=main2>";

if($currentcat == 0) {
	$sql = "SELECT * FROM stories ORDER BY dateposted DESC LIMIT 5;";
}

else {
	$parentsql = "SELECT parent FROM categories WHERE id = " . $currentcat . ";";
	$parentres = mysql_query($parentsql);
	$parentrow = mysql_fetch_assoc($parentres);

	if($parentrow['parent'] == 1) {
		$sql = sprintf("SELECT stories.* FROM stories INNER JOIN cat_relate
		ON stories.cat_id = cat_relate.child_id WHERE cat_relate.parent_id = %d UNION SELECT
		stories.* FROM stories WHERE stories.cat_id = %d;" , $currentcat, $currentcat);
		}
		else {
			$sql = "SELECT * FROM stories WHERE cat_id = ".$currentcat.";";
		}
	}

	$result = mysql_query($sql);
	$numrows = mysql_num_rows($result);

	if($numrows == 0) {
		echo "<h1>No Stories</h1>";
		echo "<p>There are currently no stories in this category.   </p>";
	}
	else {
		while($row = mysql_fetch_assoc($result)) {
			if($_SESSION['SESS_USERLEVEL'] == 10) {
				echo "<a href='deletestory.php?id=" . $row['id'] . "'>[X]</a> ";
		}


$avgsql = "SELECT SUM(fri + fun + soh + con + sma + sex + hyg + mes + aut + est) AS score FROM stories WHERE id = " . $row['id'] . ";";
$avgresult = mysql_query($avgsql);
$avgrow = mysql_fetch_assoc($avgresult);

///	$imgsql = "SELECT image FROM users WHERE id = ".$row['usernameid'].";";
/// $query1 = "SELECT family.Position, food.Meal "."FROM family, food "."WHERE family.Position = food.Position";

///	$imgsql = "SELECT stories.username, users.image "." FROM stories, users ". " WHERE stories.username = users.username";
///	$imgres = mysql_query($imgsql) or die(mysql_error());
///	while ($row = mysql_fetch_assoc($imgres))
///		print_r($row);

$imgsql = "SELECT stories.username, users.image "." FROM stories, users ". " WHERE stories.username = users.username";
$imgres = mysql_query($imgsql) or die(mysql_error());;
$imgrows = mysql_num_rows($imgres);
	print_r($row);
if($imgrows == 0)
{
	echo "No Image<br>";
}

while($imgrows = mysql_fetch_assoc($imgres))
	{
	if(empty($imgrows['image'])) {
		echo "<img src='./userimg/dummy.jpg' width=100 height=100 alt='" . $imgrow['name'] . "'>";
	}
	else {
		echo "<img src='./userimg/" . $imgrows['image'] . "' width=100 height=100  alt='" . $imgrow['name'] . "'>";
	}
}

		echo"<a href='viewstory.php?id=".$row['id']."'> Somewhere in ".$row['location']."<strong>
			".$row['username']."</strong> xxx <strong>".$row['nagel']."</a></strong>
			<font color=red><b>".$avgrow['score']." % </b></font><p>";
	}
}


require("footer.php");

?>

GreenUser · July 1, 2008

Bump

Here is what the code looks like now. When the page is displayed, every image from anyone who has posted a story, will display with each story. The only image I want to display next to each story is from the one who posted it.

Most of the code is at the bottom, marked off where the join statement starts. I have another join which I have commented out right above to try it differently.

Two tables, stories and users are pulled. I only need the 'image' column from the users table. This is a mess. lol

<?php

require("db.php");

session_register("SESS_PARENT");
session_register("SESS_CHILD");

if(isset($_GET['parentcat']) && isset($_GET['childcat'])) {
if(is_numeric($_GET['parentcat'])) {
	$_SESSION['SESS_PARENT'] = $_GET['parentcat'];
}

if(is_numeric($_GET['childcat'])) {
	$currentcat = $_GET['childcat'];

	$_SESSION['SESS_CHILD'] = $_GET['childcat'];
}
}
else if(isset($_GET['parentcat'])) {
if(is_numeric($_GET['parentcat'])) {
	$currentcat = $_GET['parentcat'];

	$_SESSION['SESS_PARENT'] = $_GET['parentcat'];
	$_SESSION['SESS_CHILD'] = 0;
	}
}
else {
	$currentcat = 0;
}

require("header.php");


$hotsql = "SELECT * FROM stories ORDER BY RAND() LIMIT 1;";
$hotresult = mysql_query($hotsql);
$hotrow = mysql_fetch_assoc($hotresult);

$avgsql = "SELECT COUNT(id) AS number, AVG(rating) AS avg FROM ratings WHERE story_id = " . $hotrow['id'] . ";";
$avgresult = mysql_query($avgsql);
$avgrow = mysql_fetch_assoc($avgresult);

		echo "<strong><a href='viewstory.php?id=".$hotrow['id']."'>
			<p>".$hotrow['username']." xxx  ".$hotrow['nagel']."</p></p></a></strong>";

echo "<p>";
echo "<strong>Users Rating</strong>: ";

if($avgrow['number'] == 0) {
echo "No Ratings!";
}

else {
$a = (round($avgrow['avg'] * 2) / 2) . "<br>";
$a *= 10;

if($a%5 == 0 && $a%10 != null) {                   /// line 38
	$range = ($a / 10) - 0.5;
}
else {
	$range = $a / 10;
}

for($i=1;$i<=$range;$i++) {
echo "<img src='" . $config_basedir . "/img/hfull.png'>";
}

if($a%5 == 0 && $a%10 != null) {
echo "<img src='" . $config_basedir . "/img/hhalf.png'>";
}

$a = $a / 10;
$remain = 10 - $a;

for($r=1;$r<=$remain;$r++) {
	echo "<img src='" . $config_basedir . "/img/hnone.png'>";
	}
}


		echo "<p>" . $hotrow['body'] . "</p>";
		echo "<strong><a href='viewstory.php?id=".$hotrow['id']."'>Rate this date</a></strong>";

echo "</div>";

///

echo "<div id=main2>";

if($currentcat == 0) {
	$sql = "SELECT * FROM stories ORDER BY dateposted DESC LIMIT 10;";
}

else {
	$parentsql = "SELECT parent FROM categories WHERE id = " . $currentcat . ";";
	$parentres = mysql_query($parentsql);
	$parentrow = mysql_fetch_assoc($parentres);

	if($parentrow['parent'] == 1) {
		$sql = sprintf("SELECT stories.* FROM stories INNER JOIN cat_relate
		ON stories.cat_id = cat_relate.child_id WHERE cat_relate.parent_id = %d UNION SELECT
		stories.* FROM stories WHERE stories.cat_id = %d;" , $currentcat, $currentcat);
		}
		else {
			$sql = "SELECT * FROM stories WHERE cat_id = ".$currentcat.";";
		}
	}

	$result = mysql_query($sql);
	$numrows = mysql_num_rows($result);

	if($numrows == 0) {
		echo "<h1>No Stories</h1>";
		echo "<p>There are currently no stories in this category.   </p>";
	}
	else {
		while($row = mysql_fetch_assoc($result)) {
			if($_SESSION['SESS_USERLEVEL'] == 9) {
				echo "<a href='deletestory.php?id=" . $row['id'] . "'>[X]</a> ";
		}
$avgsql = "SELECT SUM(fri + fun + soh + con + sma + sex + hyg + mes + aut + est) AS score FROM stories WHERE id = " . $row['id'] . ";";
$avgresult = mysql_query($avgsql);
$avgrow = mysql_fetch_assoc($avgresult);

//////////////////////////////////////////////////////////////////////////////
///	$imgsql = "SELECT username FROM stories LEFT JOIN users ON (stories.username = users.image)";

$imgsql = "SELECT stories.username, users.image "." FROM stories, users "." WHERE (stories.username = users.username)";
$imgres = mysql_query($imgsql) or die(mysql_error());;
	$imgrows = mysql_num_rows($imgres);

        if($imgrows == 0)
{
	echo "No Image<br>";
}

while($imgrows = mysql_fetch_assoc($imgres))
	{
	if(empty($imgrows['image'])) {
		echo "<img src='./userimg/dummy.jpg' width=100 height=100 alt='" . $imgrows['name'] . "'>";
	}
	else {
		echo "<img src='./userimg/" . $imgrows['image'] . "' width=100 height=100  alt='" . $imgrows['name'] . "'>";
	}
	}
		echo"<a href='viewstory.php?id=".$row['id']."'> Somewhere in ".$row['location']."<strong>
			".$row['username']."</strong> xxx <strong>".$row['nagel']."</a></strong>
			<font color=red><b>".$avgrow['score']." % </b></font><p>";
	}
	}


require("footer.php");

?>

Sign In

php mysql join to get image

Recommended Posts

GreenUser

Link to comment

Share on other sites

lemmin

Link to comment

Share on other sites

GreenUser

Link to comment

Share on other sites

lemmin

Link to comment

Share on other sites

Stooney

Link to comment

Share on other sites

GreenUser

Link to comment

Share on other sites

GreenUser

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information