CODEWITHSUNDEEP
phpmysqlsqlwhile-loop
user4316754
user4316754

Reputation: 21

How to echo all rows with all columns?

Can I echo all rows? here's what my script is:

$options=mysql_query("SELECT col1, col2, col3, col4 FROM table1");
while($allrowsandcolumns=mysql_fetch_array($options)) { 
    $options1=mysql_query("SELECT * FROM table2 where id=$allrowsandcolumns");
} 
while($rows=mysql_fetch_array($options1)) { 
    echo $rows['name'];
}

My Error says Notice : Array to string conversion in line 3 of code above

IM guessing its because this is not how you display all columns & rows ...

Upvotes: 0

Views: 1030

Answers (1)

EdgeCaseBerg
EdgeCaseBerg

Reputation: 2841

I agree with Jay Blanchard that you should use something other than the mysql_* functions.

First off the error message is because you're using an array in place of a string: 

"SELECT * FROM table2 where id=$allrowsandcolumns"

$allrowsandcolumns is an array of results from your database that contains col1, col2, col3, col4 from your first query. Visually it looks like this:

array ( 
    "col1" => value1
    "col2" => value2
    "col3" => value3
    "col4" => value4
)

I think we can agree that trying to put an array in a string won't work. Instead you likely want something like this:

"SELECT * FROM table2 where id=" . $allrowsandcolumns["col1"]

Or whichever column is the id column matching with table2.

As far as echoing out all the rows... This looks to me like you're new to programming. But nesting is easily explained:

Right now you have this:

$options=mysql_query("SELECT col1, col2, col3, col4 FROM table1");
while($allrowsandcolumns=mysql_fetch_array($options)) { 
    $options1=mysql_query("SELECT * FROM table2 where id=$allrowsandcolumns"); 
} 
while($rows=mysql_fetch_array($options1)) { 
    echo $rows['name'];
}

This is not doing what you think it's doing. It would be looping through every row in table1, then using the last one in $options when it fetches and echos a Name. What you need to do is nest the loop like so:

$options=mysql_query("SELECT col1, col2, col3, col4 FROM table1");
while($allrowsandcolumns=mysql_fetch_array($options)) { 
    $options1=mysql_query("SELECT * FROM table2 where id= " . $allrowsandcolumns["col1"]);
    while($rows=mysql_fetch_array($options1)) { 
        echo $rows['name'];
    }
}

That said. This is a bad idea. Looping SQL queries is terrible for performance. In your quest for knowledge, look up join queries so that you can retrieve all these results at once. Something like this:

 SELECT col1,col2,col3,col4 
 FROM table1 JOIN table2 on table2.id = table1.col1

Then you can have a single loop through those values.

Upvotes: 1

Related Questions