Page 1 of 1

mysql_result; mysql_fetch_row etc. which one?

Posted: Sat Apr 15, 2006 2:57 pm
by jonah
I've been banging my head against a wall it seems for some time now and
I just don't seem to get my code to work properly. I am trying to read a
record set into some echo statements which produce html output. I create
a SELECT query which can extract about 20 rows of data which contain
a number of fields based on an input statement. So, for an example:

Create the SQL query:

Code: Select all

for ( $i = $xint; $i >= $yint; $i-- ) {
$sqlstring = $sqlstring . "`" . $i . "`";
If ($i > $yint) { $sqlstring = $sqlstring . ","; }
}
Create the recordset from the query

Code: Select all

$r2=mysql_query("SELECT " . $sqlstring . " FROM Land",$db) or die(mysql_error());

Code: Select all

Create the output string for the  recordset
for ( $i = $xint; $i >= $yint; $i-- ) {
$land = $land . number_format(mysql_result($r2,$i,$i),0) . "</td><td align='right'>";
If ($i = $yint) { $land = $land . "</tr>"; }
}
The variable $land is supposed to hold the recordset output to be appended to an
echo statement later on formatting the output into a table.

My problem is that I am getting no output in the $land variable and have come to
the conclusion that I really don't quite know how to do this. The mysql_result comments
in the PHP manual state that "If you came to this like I did, looking for a function that grabs
info from the result but does not increment it to the next result row, you are out of luck."

With the above code I am simply trying to create output from a single row of the recordset
but based on the fields selected in the query. I use code further down to increment through
the remaining rows of the recordset. But this is not working correctly and I must admit I am
at a loss as to why. I clearly don't understand how the mysql_result function is supposed
to work and I really need some expert advice on how I should structure this code or if
I should be using a different function.

Any help appreciated.

Posted: Sat Apr 15, 2006 3:08 pm
by feyd
your calls to mysql_result() are running a diagonal lookup across both rows and columns. Is this how you want it to be done?

Posted: Sat Apr 15, 2006 3:30 pm
by jonah
No! As I tried to explain above, I really don't know what I'm doing,
but I only want to creat a string from a single row in the recordset.

e.g.

1. extrat 20 records with field names 2005 2004 2003

2. create a string which contains field 2005 2004 2003 data for the first row
and append to an echo statement to produce html output.

3. then increment the recordset to the next record and do the same thing again.

I can do this very easily with vbScript and an Access database, but have
migrated the Access DB to MySQL and need to redo the reporting in PHP.

Posted: Sat Apr 15, 2006 3:39 pm
by feyd
replace

Code: Select all

for ( $i = $xint; $i >= $yint; $i-- ) {
$land = $land . number_format(mysql_result($r2,$i,$i),0) . "</td><td align='right'>";
If ($i = $yint) { $land = $land . "</tr>"; }
}
with

Code: Select all

while($row = mysql_fetch_row($rs2))
{
  $land .= '<tr><td align="right">' . implode('</td><td align="right">', $row) . '</td></tr>';
}

Posted: Sat Apr 15, 2006 4:44 pm
by jonah
Well we are getting closer. While the code above seems to work
I am getting output for each row in the recordset assigned to
one line of html output. It seems that using 'while' iterates through
the rows of the recordset each time a $land variable is created.

Is there another way to do this without using 'while' so that I will
select only one row at a time in creating the variable?

I could, I suppose, use 'while' in a single fetch procedure to produce
a table, but I have to have the capability of adding a different recordset
row just beneath the one for 'land' and additionally add underscoring's
and totals. I can only do this by interating one recordset row at a time.

Thanks again!

Posted: Sat Apr 15, 2006 4:56 pm
by feyd
The code I posted will add a table row for each record in the result set. Notice the .= operator I have in there, that's the same as

Code: Select all

$land = $land . '...';

Posted: Sat Apr 15, 2006 5:07 pm
by jonah
Agreed, but it does so in a single while statement. Are you saying
that I am going to have to assgin each output row to a variable
and then use the variable's in creating the html output?

I must say that having to use a lot of variables is what I am
trying to stay away from. To give you an idea of what the output
should look like:

Code: Select all

ID  Name   2005 2004 2003
1    1st 
      Land        5        4       3
      Bldg         5        4       3
                  -----  ------ ------
      Total      10        8      6
                  ===  ===  ===
 
2     2nd
Same repeated.

Posted: Sat Apr 15, 2006 5:13 pm
by John Cartwright
Are you saying
that I am going to have to assgin each output row to a variable
and then use the variable's in creating the html output?

I must say that having to use a lot of variables is what I am
trying to stay away from.
You are only using a single variable, if you are doing as Feyd suggested. I'm a bit confused on what your getting at.. could you please clarify a bit?

Posted: Sat Apr 15, 2006 10:02 pm
by jonah
Well, it all boils down to the fact that I have to somehow isolate and
output a single row of the 'one variable' recordset, and interleave
it with a single row output from another recordset and add underscoring
and totals. I haven't really belabored that since I was simply trying
to get an idea of how to extract data from the one recordset and
include it in a string. In other words, I wasn't sure just what method
to use.

Now I can see that I can create the output I need, I just have to do it
one recordset row at a time. I thought the previous post indicating what
my output would eventually look like would give you some idea as to
what I was attempting to do.

I suppose I have a good enough start and I'll be able to figure it out.