Using mysql_result() Deletes Row From Result

[Steve]

Trying to do a pretty simple page where I display car parts on a page based on the category selected from a drop down list. Here's my tables setup:

categories
code CHAR(3)
description (25)

parts
category CHAR(3)
part_number CHAR(15)
years CHAR(9)
description CHAR(35)
price DECIMAL(6,2)
graphic CHAR(15)

What I try to do is pretty simple:

1)Query db to get items in category based on what is chosen in the drop down list
2) Write header of table for displaying data, including category name as title
3) Cycle through returned result using mysql_fetch_array().

Pretty simple. The problem I'm having is with getting the category name. I use one query to get everything:

SELECT parts.category, parts.part_number,parts.years, parts.description, parts.price, parts.graphic, categories.description AS Category";
FROM parts INNER JOIN categories on parts.category = categories.code
WHERE parts.category = '$parts_choice'

I then use mysql_result() to get the category name from the first row (since it will be the same in all rows). The problem I'm having is that
when I use mysql_result(), the data from the last row in the result set is not displayed, even though the HTML for that row is written out, and
the "No parts found for this category" message is written to the page (see below).
for ($i = 0;$i < $num_results2; $i++)
{
$row2 = mysql_fetch_array($result2);

if (!$row2)
{
echo "No parts found for this category.";
}
?>
<tr>
<td><?php echo $row2["part_number"]?></td>
<td><?php echo $row2["years"]?></td>
<td><?php echo $row2["description"]?></td>
<td>$<?php echo $row2["price"]?></td>
<?php
if (strlen($row2["graphic"]) 0)
{
?>
<td align="center"><img src="graphics/<?php echo $row2["graphic"].".jpg"?>" border="0"></td>
<?php
}
else
{
?>
<td>No picture available</td>
<?php
}
?>
</tr>
<?
}

So it's like for some reason, reading the Category name from the first row causes the last row from the result to be deleted. Has anyone seen
this before, or can someone explain why this might be happening?

Thanks.

Steve

[Jerry Stuckle]

Steve wrote:

Trying to do a pretty simple page where I display car parts on a page
based on the category selected from a drop down list. Here's my tables
setup:

categories
code CHAR(3)
description (25)

parts
category CHAR(3)
part_number CHAR(15)
years CHAR(9)
description CHAR(35)
price DECIMAL(6,2)
graphic CHAR(15)

What I try to do is pretty simple:

1)Query db to get items in category based on what is chosen in the drop
down list
2) Write header of table for displaying data, including category name as
title
3) Cycle through returned result using mysql_fetch_array().

Pretty simple. The problem I'm having is with getting the category
name. I use one query to get everything:

SELECT parts.category, parts.part_number,parts.years, parts.description,
parts.price, parts.graphic, categories.description AS Category";
FROM parts INNER JOIN categories on parts.category = categories.code
WHERE parts.category = '$parts_choice'

I then use mysql_result() to get the category name from the first row
(since it will be the same in all rows). The problem I'm having is that
when I use mysql_result(), the data from the last row in the result set
is not displayed, even though the HTML for that row is written out, and
the "No parts found for this category" message is written to the page
(see below).
for ($i = 0;$i < $num_results2; $i++)
{
$row2 = mysql_fetch_array($result2);

if (!$row2)
{
echo "No parts found for this category.";
}
?>
<tr>
<td><?php echo $row2["part_number"]?></td>
<td><?php echo $row2["years"]?></td>
<td><?php echo $row2["description"]?></td>
<td>$<?php echo $row2["price"]?></td>
<?php
if (strlen($row2["graphic"]) 0)
{
?>
<td align="center"><img src="graphics/<?php echo
$row2["graphic"].".jpg"?>" border="0"></td>
<?php
}
else
{
?>
<td>No picture available</td>
<?php
}
?>
</tr>
<?
}

So it's like for some reason, reading the Category name from the first
row causes the last row from the result to be deleted. Has anyone seen
this before, or can someone explain why this might be happening?

Thanks.

Steve

Steve,

The code you've posted shouldn't do that. Mind posting ALL of you
rcode? For instance - where do you fetch the data and retrieve the
category?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Petr Vileta]

"Steve" <ra***********@hotmail.comwrote in
news:Xr******************************@comcast.com. ..
[...]

I then use mysql_result() to get the category name from the first row
(since it will be the same in all rows). The problem I'm having is that
when I use mysql_result(), the data from the last row in the result set is
not displayed, even though the HTML for that row is written out, and the
"No parts found for this category" message is written to the page (see
below).
for ($i = 0;$i < $num_results2; $i++)
{
$row2 = mysql_fetch_array($result2);

try this

$query = "SELECT parts.category, parts.part_number,parts.years,
parts.description, parts.price, parts.graphic, categories.description AS
Category";
FROM parts INNER JOIN categories on parts.category = categories.code
WHERE parts.category = '$parts_choice'";
$result2 = mysql_query($query) or die("mysql_error());
$rows2 = mysql_num_rows($query);
if ($rows2==0)
{
echo "No parts found for this category.";
}
else
{
while ($row2 = mysql_fetch_array($result2, MYSQL_ASSOC))
{
echo "<tr>\n<td>", $row2["part_number"], "</td>\n",
"<td>", $row2["years"], "</td>\n",
"<td>", $row2["description"], "</td>\n",
"<td>\$", $row2["price"], "</td>\n"
}
}
?>

--

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)

[Steve]

Steve,

The code you've posted shouldn't do that. Mind posting ALL of you
rcode? For instance - where do you fetch the data and retrieve the
category?

Here you go.

<?
if (isset($_POST['parts_choice']))
{
$parts_choice = $_POST['parts_choice'];
if (substr($parts_choice,0,6) != "Choose")
{
if (db_connect())
{

$query2 = "select parts.category, parts.part_number,parts.years, parts.description, parts.price, parts.graphic, categories.description
AS Category";
$query2 = $query2." FROM parts INNER JOIN categories on parts.category = categories.code";
$query2 = $query2." where parts.category = '$parts_choice'";

//echo("<p class=\"text\" align=\"center\">\$query2= $query2</p>");

$result2 = mysql_query($query2) or die(mysql_error());
$num_results2 = mysql_num_rows($result2) or die(mysql_error());

if ($_POST['get_parts'] == "Display Parts")
{
$category_name = mysql_result($result2,0,6);
?>
<center>
<h2><?php echo $category_name ?></h2>
<table border="1" cellpadding="5">
<tr>
<th>Part Number</th>
<th>Years</th>
<th>Description</th>
<th>Price</th>
<th>Picture</th>
</tr>
<?
}
else
{
echo("<p class=\"text\" align=\"center\">There was an error.</p>");
}

for ($i = 0;$i < $num_results2; $i++)
{
$row2 = mysql_fetch_array($result2);

if (!$row2)
{
echo "No parts found for this category.";
}
?>
<tr>
<td><?php echo $row2["part_number"]?></td>
<td><?php echo $row2["years"]?></td>
<td><?php echo $row2["description"]?></td>
<td>$<?php echo $row2["price"]?></td>
<?php
if (strlen($row2["graphic"]) 0)
{
?>
<td align="center"><img src="graphics/<?php echo $row2["graphic"].".jpg"?>" border="0"></td>
<?php
}
else
{
?>
<td>No picture available</td>
<?php
}
?>
</tr>
<?
}
?>
</table>
<br>
<br>

<?
}
}
}
do_html_footer();
?>

[Steve]

I then use mysql_result() to get the category name from the first row
(since it will be the same in all rows). The problem I'm having is that
when I use mysql_result(), the data from the last row in the result set
is not displayed, even though the HTML for that row is written out, and
the "No parts found for this category" message is written to the page
(see below).

I went back and looked again, and discovered that it is actually the first row in the returned data that is not displayed, not the
last row. So it looks like what's happening is that mysql_result() is removing the data from the first row (the row I'm reading
the get the category name), but it looks like the row is actually there when mysql_fetch_array() is extracting the data, hence the
empty row displayed in the table.

Steve

[Jerry Stuckle]

Steve wrote:

>

>Steve,

The code you've posted shouldn't do that. Mind posting ALL of you
rcode? For instance - where do you fetch the data and retrieve the
category?

Here you go.

<?
if (isset($_POST['parts_choice']))
{
$parts_choice = $_POST['parts_choice'];
if (substr($parts_choice,0,6) != "Choose")
{
if (db_connect())
{

$query2 = "select parts.category,
parts.part_number,parts.years, parts.description, parts.price,
parts.graphic, categories.description AS Category";
$query2 = $query2." FROM parts INNER JOIN categories on
parts.category = categories.code";
$query2 = $query2." where parts.category = '$parts_choice'";

//echo("<p class=\"text\" align=\"center\">\$query2=
$query2</p>");

$result2 = mysql_query($query2) or die(mysql_error());
$num_results2 = mysql_num_rows($result2) or die(mysql_error());

if ($_POST['get_parts'] == "Display Parts")
{
$category_name = mysql_result($result2,0,6);
?>
<center>
<h2><?php echo $category_name ?></h2>
<table border="1" cellpadding="5">
<tr>
<th>Part Number</th>
<th>Years</th>
<th>Description</th>
<th>Price</th>
<th>Picture</th>
</tr>
<?
}
else
{
echo("<p class=\"text\" align=\"center\">There was an
error.</p>");
}

for ($i = 0;$i < $num_results2; $i++)
{
$row2 = mysql_fetch_array($result2);

if (!$row2)
{
echo "No parts found for this category.";
}
?>
<tr>
<td><?php echo $row2["part_number"]?></td>
<td><?php echo $row2["years"]?></td>
<td><?php echo $row2["description"]?></td>
<td>$<?php echo $row2["price"]?></td>
<?php
if (strlen($row2["graphic"]) 0)
{
?>
<td align="center"><img src="graphics/<?php echo
$row2["graphic"].".jpg"?>" border="0"></td>
<?php
}
else
{
?>
<td>No picture available</td>
<?php
}
?>
</tr>
<?
}
?>
</table>
<br>
<br>

<?
}
}
}
do_html_footer();
?>

Are you sure it's the last row you're not getting, and not the first?

I think your problem is you're trying to fetch the category with
mysql_result, then using mysql_fetch_array() to get the data.

From the PHP manual on mysql_result():

"Calls to mysql_result() should not be mixed with calls to other
functions that deal with the result set."

Perhaps you should get your category description after you fetch the
contents for the first row.

Another option would be to run a query to fetch just the category
description, rather than return it in every single row (just to save a
query). At some point the extra data returned will take longer than a
second select statement.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Jerry Stuckle]

Steve wrote:

>

>I then use mysql_result() to get the category name from the first row
(since it will be the same in all rows). The problem I'm having is
that when I use mysql_result(), the data from the last row in the
result set is not displayed, even though the HTML for that row is
written out, and the "No parts found for this category" message is
written to the page (see below).

I went back and looked again, and discovered that it is actually the
first row in the returned data that is not displayed, not the last row.
So it looks like what's happening is that mysql_result() is removing the
data from the first row (the row I'm reading the get the category name),
but it looks like the row is actually there when mysql_fetch_array() is
extracting the data, hence the empty row displayed in the table.

Steve

Ah, now I see this update :-)

See my update above. No, you can't mix mysql_result() and
mysql_fetch_array() successfully.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Steve]

Another option would be to run a query to fetch just the category

description, rather than return it in every single row (just to save a
query). At some point the extra data returned will take longer than a
second select statement.

I thought about doing that, but it seemed more efficient to try to get everything with one query.

Thanks for your help.

Steve

[Jerry Stuckle]

Steve wrote:

>Another option would be to run a query to fetch just the category
description, rather than return it in every single row (just to save a
query). At some point the extra data returned will take longer than a
second select statement.

I thought about doing that, but it seemed more efficient to try to get
everything with one query.

Thanks for your help.

Steve

Yep, you can do it in one query. Something like:

$category = null;

....

while ($row2 = mysql_fetch_array($result2)) {
if ($category == null)
$category = $row2['Category'];

... Rest of processing

}

BTW - this construct is better, IMHO, then getting the number of rows
and using a for loop.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================