[SOLVED] I've done much more complicated things than this, what am I missing?

matthewst · July 19, 2007

I'm working on a super simple database and php script. The problem is I can't get it to work!

I keep getting this error "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Library/Tenon/WebServer/WebSites/www.ABCAdvertising.net/abcadmin/dcloset.php on line 28", line 28 is "while ($row_shorts = mysql_fetch_assoc($result_shorts))".

Here is the php page(just trying to get the shorts column to work right now).

<?PHP
include('include/user_check.php');
include('include/db_con.php');	
?>

<html>
<head>
<title>D Closet</title>
</head>
<body>
<table>
<tr>
<th colspan="2">SHORTS</th><th colspan="2">JEANS</th><th colspan="2">POLOS</th><th colspan="2">T-Shirts</th>
</tr>
<tr>
<th>Amount</th><th>Color</th><th>Amount</th><th>Color</th><th>Amount</th><th>Color</th><th>Amount</th><th>Color</th>
</tr>

<?PHP
$query = "SELECT type FROM dc";
	$result_query = mysql_query($query);
  	while ($row_query = mysql_fetch_assoc($result_query))
	{
	$type = $row_query['type'];
}
$shorts = "SELECT color, number FROM dc WHERE '$type' = shorts";
	$result_shorts = mysql_query($shorts);
	while ($row_shorts = mysql_fetch_assoc($result_shorts))
	{
	$number_shorts = $row_shorts['number'];
	$color_shorts = $row_shorts['color'];	
?>
<tr>
<td><?=$number_shorts; ?></td><td><?=$color_shorts; ?></td><td><?=$number_jeans; ?></td><td><?=$color_jeans; ?></td><td><?=$number_polos; ?></td><td><?=$color_polos; ?></td><td><?=$number_tshirts; ?></td><td><?=$color_tshirts; ?></td>
</tr>
<?PHP
}
?>
</table>
</body>
</html>

Here is what the entire database table looks like

type____color____number

shorts__blue_____2

jeans___black___3

polos___white____4

tshirts__green____1

SammyP · July 19, 2007

You have a sql error. If you check mysql_error after your sql calls it will tell you.

I suspect you don't want $type to be in single-quotes in the inner query.

I am not sure what output you want, but I think you can probably get to it more easily than the way you are doing it.

GingerRobot · July 19, 2007

Try doing:

$result_query = mysql_query($query) or die(mysql_error());

It appears that there is a problem with your query. Given the simplicity of the query itselft, i would guess that perhaps the table dc doesn't exist or there is a problem with the connection.

matthewst · July 19, 2007

changed it to this

<?PHP
$query = "SELECT * FROM dc";
	$result_query = mysql_query($query) or die(mysql_error());
  	while ($row_query = mysql_fetch_array($result_query))
	{
	$type = $row_query['type'];

$shorts = "SELECT * FROM dc WHERE '$type' = type";
	$result_shorts = mysql_query($shorts) or die(mysql_error());
	while ($row_shorts = mysql_fetch_assoc($result_shorts))
	{
	$number_shorts = $row_shorts['number'];
	$color_shorts = $row_shorts['color'];
	}	
?>
<tr>
<td><?=$number_shorts; ?></td><td><?=$color_shorts; ?></td><td><?=$number_jeans; ?></td><td><?=$color_jeans; ?></td><td><?=$number_polos; ?></td><td><?=$color_polos; ?></td><td><?=$number_tshirts; ?></td><td><?=$color_tshirts; ?></td>
</tr>
<?PHP
}
?>

now my page displays without any errors but it displays everything not just the shorts data

[attachment deleted by admin]

SammyP · July 19, 2007

I can only see Shorts data!

dbo · July 19, 2007

I'd do what you're doing in multiple queries in a single query. It'll cut down in some overhead and extra calls to the DB. use GROUP BY. Then use a variable to store the current type. As soon as the current type changes then start a new row.

matthewst · July 19, 2007

i should have explained my pic

2 blue shorts----1 black jeans----3 white polos----4 green tshirts

dbo · July 19, 2007

if( $type == "shorts" )

{

$shorts = "SELECT * FROM dc WHERE '$type' = type";

$result_shorts = mysql_query($shorts) or die(mysql_error());

while ($row_shorts = mysql_fetch_assoc($result_shorts))

{

$number_shorts = $row_shorts['number'];

$color_shorts = $row_shorts['color'];

}

SammyP · July 19, 2007

Am I right in assuming there can be shorts of different colours in your table, and you want to list them separately, and then probably do the same for other items of clothing?

Answer that and I can talk you through it fairly easily.

How do you want your output to look, is what I guess I'm asking.

matthewst · July 19, 2007

this

<?PHP
$query = "SELECT type FROM dc";
	$result_query = mysql_query($query) or die(mysql_error());
  	while ($row_query = mysql_fetch_array($result_query))
	{
	$type = $row_query['type'];
/////////////////////////////	
echo $type;
////////////////////////////	
if( $type == "shorts" )
{
	$shorts = "SELECT * FROM dc WHERE '$type' = type GROUP BY type";
		$result_shorts = mysql_query($shorts) or die(mysql_error());
		while ($row_shorts = mysql_fetch_assoc($result_shorts))
		{
		$number_shorts = $row_shorts['number'];
		$color_shorts = $row_shorts['color'];
		}
}
?>
<tr>
<td><?=$number_shorts; ?></td><td><?=$color_shorts; ?></td>

gets me pic 2

sammy, I'm trying to display this

____SHORTS__________JEANS__________POLOS_________T-SHIRTS___

numbS__colorS____numbJ__colorJ____numbP__colorP____numbT__colorT

"Am I right in assuming there can be shorts of different colours in your table, and you want to list them separately, and then probably do the same for other items of clothing?" yes

[attachment deleted by admin]

dbo · July 19, 2007

I guess I have no idea what you're trying to do anymore. I thought you said you wanted to only display shorts.

pocobueno1388 · July 19, 2007

Look at this part of your query:

WHERE '$type' = type

It looks like you have it backwards. Try changing it to:

WHERE type='$type'

So your full query would look like this after the change:

$shorts = "SELECT * FROM dc WHERE type='$type' GROUP BY type";

EDIT:

Never mind, I just saw that $type holds a field name or something from a previous query. I don't understand the need for your first query though...

matthewst · July 19, 2007

poco, that gives me the same results as before (post #9)

dbo, i am just working on shorts right now, but I plan on displaying all the data

dbo · July 19, 2007

I wouldn't worry about the display at all until you have your query working correctly. Try banging it out in php my admin to get the query to return what you want... also echo your query variables to make sure that what you think is getting sent to the db is actually what is being sent. Once you have the query grabbing the data you need... we could format it anyway you wanted. While I can buy the display for the if statement thing I suggested you use.... I've got no idea why it would return those results. It should only give you shorts data. Test this query.

$query = "SELECT `type`, `color`, `number` FROM `dc` GROUP BY `type` ORDER BY `type`";

then do a simple output just putting each row on its own line. Seee if it's the data you need to get. If it is we can help you lay it out desired. One problem at a time... and start at the source... the query in this case.

SammyP · July 19, 2007

Anyway, this is what I can help you to do, which is get a table for each type of clothing. It will be quite hard to put them all into one big table, but we can look at that later.

1.Your outer query should be this:

$query = "SELECT type FROM dc GROUP BY type";

The grouping will ensure we create one table for each type of clothing.

2.Now inside your loop set $type = $row_query['type'] as you do already, and output some table tag stuff and a heading.

3. Run the inner loop, using this query: "SELECT * FROM dc WHERE type= '$type'";

Ideally in this loop don't call anything shorts, as they won't always be shorts.

4. As you go through the inner loop, output something like this <tr><td>$row['colour']</td><td>$row['number']</td></tr>

5. Outside that loop, close the table.

6. Outside the main loop, you're finished.

This will get us started, I numbered things in case it isn't clear.

If there is a chance that your table could hold two rows like this:

Shorts, Red, 4

Shorts, Red, 5

meaning you want to display Red 9 in the Shorts table, you will need to change the inner query to this:

"SELECT Colour, Sum(Number) As Total FROM dc WHERE type= '$type' GROUP BY Colour";

Also, remove the 'u's from Colour everywhere. Silly Americans. And if you use this query here, the field name will be 'Total; now.

Let me know how we're doing.

matthewst · July 19, 2007

ok sammy, this

<?PHP
$query = "SELECT type FROM dc GROUP BY type";
	$result_query = mysql_query($query) or die(mysql_error());
  	while ($row_query = mysql_fetch_array($result_query))
	{
	$type = $row_query['type'];
/////////////////////////////just for show	
echo $type;
////////////////////////////end just for show
	$items = "SELECT * FROM dc WHERE type='$type' GROUP BY type";

		$result_items = mysql_query($items) or die(mysql_error());
		while ($row_items = mysql_fetch_assoc($result_items))
		{
		$number_shorts = $row_shorts['number'];
		$color_shorts = $row_shorts['color'];
		?>
		<tr><td><?=$row['color']; ?></td><td><?=$row['number']; ?></td></tr>
		<?PHP
		}
?>

gets me pic 2 from post #9

working on yours db

matthewst · July 19, 2007

myguess (codewalkers) solved it

thanks everyone for your help

<?PHP
include('include/user_check.php');
include('include/db_con.php');	
?>

<html>
<head>
<title>D Closet</title>
</head>
<body>
<table>
<tr>
<th colspan="2">SHORTS</th><th colspan="2">JEANS</th><th colspan="2">POLOS</th><th colspan="2">T-Shirts</th>
</tr>
<tr>
<th>Amount</th><th>Color</th><th>Amount</th><th>Color</th><th>Amount</th><th>Color</th><th>Amount</th><th>Color</th>
</tr>
<tr>
<?PHP
$query = "SELECT type FROM dc";
	$result_query = mysql_query($query);



while ($row_query = mysql_fetch_assoc($result_query))
	{
	         $type = $row_query['type']; 	

        $shorts = "SELECT color, number FROM dc WHERE type = '$type'";
	$result_shorts = mysql_query($shorts);
	while ($row_shorts = mysql_fetch_assoc($result_shorts))
	{

	$number = $row_shorts['number'];
	$color = $row_shorts['color'];			

	print "<td>$number </td><td>$color</td>";
	}
}
?>
</tr>
</table>
</body>
</html>

Sign In

[SOLVED] I've done much more complicated things than this, what am I missing?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information