mysql_fetch_row() doesn't work

[agnese camellini]

Hello,

the problem i'm facing deals with mysql table reading,

 

I'm substantially trying to print the entries of a table in a web page (classics table), and it works. But it doesn't contains the data about the authors of the books, which is in an other table named "authors". The connection between the two tables are the ISBN of the books, so i'm attempting to get the data of both the tables ($query, and $queryw), turning it into arrays.

 

The first array is made from each row of the table of the books,i fetch every time the loop starts and i traverse it to print the data into the web page.

In order to connect the right author in the output i'm trying to traverse with a second loop an array made from each row the table of the author. I'm using the same for loop as for the first table, fetching the data from each row of the authors table, then comparing the ISBN number associated to the author in it with the ISBN of the book.

 

I added a print_r function to understand why the two mysql_fetch_row gives two different array. The output of those is pasted below the code.

$queryw="SELECT * FROM authors";
$resultw= mysql_query($queryw);
if (!$resultw)die("Database access failed: " . mysql_error());

 

$query= "SELECT * FROM classics";
$result= mysql_query($query);

if (!$result)die("Database access failed: " . mysql_error());

$rows = mysql_num_rows($result);

for($j = 0; $j < $rows ; ++$j)
{
    $row = mysql_fetch_row($result);
    $rowsw = mysql_num_rows($resultw);
    print_r($row);
    echo "<br />";
    for($i = 0; $i < $rowsw; ++$i)
    {
        $roww = mysql_fetch_row($resultw);
        print_r($roww);
        if ($roww[1]=$row[3])
        $row[]="$roww[0]";
    }

 

    echo <<<_END
<pre>
    Author $row[4]
    Title $row[0]
    Category $row[1]
    Year $row[2]
    ISBN $row[3]
</pre>
<form action="sqltezt.php" method="post">
<input type="hidden" name="delete" value="yes"/>
<input type="hidden" name="isbn" value="$row[3]" />
<input type="submit" value="DELETE RECORD" /></form>
_END;

 

}

This is the first array ($row)

Array ( [0] => The Adventures of Tom Sawyer [1] => Classic Fiction [2] => 1876 [3] => 9781598184891 )

 

This is the second array ($roww)
Array ( [0] => Mark Twain [1] => 9781598184891 [2] => 1 ) Array ( [0] => Jane Austen [1] => 9780582506206 [2] => 2 ) Array ( [0] => Charles Darwin [1] => 9780517123201 [2] => 3 ) Array ( [0] => William Shakespeare [1] => 9780192814968 [2] => 4 ) Array ( [0] => Charles Dickens [1] => 9780141439969 [2] => 5 ) Array ( [0] => Charles Dickens [1] => 9780099533474 [2] => 6 ) Array ( [0] => David Sklar [1] => 0596101015 [2] => 7 ) Array ( [0] => Adam Trachtenberg [1] => 0596101015 [2] => 8 ) Array ( [0] => Danny Goodman [1] => 0596527403 [2] => 9 ) Array ( [0] => Hugh E. WIlliams [1] => 0596005436 [2] => 10 ) Array ( [0] => David Lane [1] => 0596005436 [2] => 11 ) Array ( [0] => Rasmus Lerdorf [1] => 0596006815 [2] => 12 ) Array ( [0] => Kevin Tatroe [1] => 0596006815 [2] => 13 ) Array ( [0] => Peter MacIntyre [1] => 0596006815 [2] => 14 )

 

So the second array is not containing only one row of the authors table, but all of them.

 

The consequence is that the output in the webpage is right only for the first book:

 

    Author Mark Twain
    Title The Adventures of Tom Sawyer
    Category Classic Fiction
    Year 1876
    ISBN 9781598184891

But for the others it gives me a notice:

 

 Notice: Undefined offset: 0 in [path]sqltezt.php on line x

line x is

 

$row[]="$roww[0]";

 

So it seems like it takes the array the first time, in the right way, but after it doesn't recognize it any more (or something like this).

However, there is an error in this setting and i cannot see where.

 

probably i don't have clear in my mind what this loop really does so if you could help.

If you need me to paste more line of the script (like some other part of the mysql query, just ask.

Tnks

agnese 

[Robert Gonzalez]

Why not just return your data the way you want it returned from the query? Or are the tables not related?

--
This group is managed and maintained by the development staff at 360 PSG. An enterprise application development company utilizing open-source technologies for todays small-to-medium size businesses.
 
For information or project assistance please visit :
http://www.360psg.com
 
You received this message because you are subscribed to the Google Groups "Professional PHP Developers" group.
To post to this group, send email to Professi...@googlegroups.com
To unsubscribe from this group, send email to Professional-P...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Professional-PHP

[Javier Montani]

Try mysql_fetch_assoc instead of mysql_fetch_row.

2010/9/7 Robert Gonzalez <robert.anth...@gmail.com>

[Ovidiu Alexa]

Hi, may I suggest this way to 'grab' info from the DB?

function askDB($mySQL)
{
// I use die in case there's an error for debugging. Don't use this on a public site. instead use this.
// remove comment for public sites $sql=mysql_query($mySQL);
  $sql=mysql_query($mySQL)or die('<div style="font-size:11px; color:#333333; font-family:Arial;">'.mysql_error().$mySQL.'</div>');
  $i=0;
  while($af_sql=mysql_fetch_assoc($sql))
    {
    $final[$i]=$af_sql; 
    $i++;
    }          
  return $final;
  mysql_close();
  }


$sql="SELECT * FROM authors";
$result_authors=askDB($sql);

$sql="SELECT * FROM classics";
$result_classics=askDB($sql);


print_r($result_authors);
print_r($result_classics);

Cheers!

[Robert Gonzalez]

Ok, I have to take a moment to bitch at that code...

Why are you indexing your array when PHP does that for you anyway? Why are you closing your MySQL connection at the end of a query function? Why not do something like this, for select queries...

<?php

function select($sql) {

  // You don't have to, but making your connection available is good practice

  global $conn; // Your mysql connection resource

  

  // Assumes all query variables have been escaped

  if ( ($rs = mysql_query($sql, $conn)) === false ) {

    return false; // There was an error, up to you to find it

  }

  // Get the result of the query

  while ($rows[] = mysql_fetch_assoc($rs));

  // Return it

  return $rows;

[Ovidiu Alexa]

thanks for the heads up, that is a very old function that has been working on my scripts, it's not mine, I admit... but it does the job. I think that yours is much better. I shall rethink that function .

Cheers!

[dilip prajapati]

Hi

Try to write your code in this way

$rows = mysql_num_rows($result);

while($row = mysql_fetch_row($result))
{

   
    $rowsw = mysql_num_rows($resultw);
    print_r($row);
    echo "<br />";

    while($roww = mysql_fetch_row($resultw))
    {

       
        print_r($roww);
        if ($roww[1]=$row[3])
        $row[]="$roww[0]";
    }

 

    echo <<<_END
<pre>
    Author $row[4]
    Title $row[0]
    Category $row[1]
    Year $row[2]
    ISBN $row[3]
</pre>

--