mysql_result; mysql_fetch_row etc. which one?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonah
Forum Commoner
Posts: 31
Joined: Fri Jun 20, 2003 12:23 am

mysql_result; mysql_fetch_row etc. which one?

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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?
jonah
Forum Commoner
Posts: 31
Joined: Fri Jun 20, 2003 12:23 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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>';
}
jonah
Forum Commoner
Posts: 31
Joined: Fri Jun 20, 2003 12:23 am

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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 . '...';
jonah
Forum Commoner
Posts: 31
Joined: Fri Jun 20, 2003 12:23 am

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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?
jonah
Forum Commoner
Posts: 31
Joined: Fri Jun 20, 2003 12:23 am

Post 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.
Post Reply