2

Ok I have a table with a few fields. One of the fields is username. There are many times where the username is the same, for example:

    • username: bob
    • password: bob
    • report: 1
    • username: bob
    • password: bob
    • report: 2

I did a SQL statement to select * where username='bob'; but when I do the following PHP function, it will only return the last result:

$thisrow = mysql_fetch_row($result);

I need to get every field from every row. How should I go about doing this?

$mainsection="auth"; //The name of the table
$query1="select * from auth where username='$user'"; 
$result = mysql_db_query($dbname, $query1) or die("Failed Query of " . $query1);  //do the query
$thisrow=mysql_fetch_row($result);
echo "Study: " . $thisrow[1] . " - " . $thisrow[5];

Sorry for such a dumb question. I can't seem to get the while loops of more than one field working for the life of me.

Improve this question
1
  • 1
    Beware of SQL injection with $user. Use mysql_real_escape_string($text) to ensure special characters are escaped. Also, mysql_fetch_assoc($result) will give you a named array - i.e. $thisrow['name_of_database_field'] - much easier to work with in the long run. Commented Apr 24, 2009 at 21:48

6 Answers 6

Reset to default
10

mysql_fetch_row fetches each row one at a time. In order to retrieve multiple rows, you would use a while loop like this:

while ($row = mysql_fetch_row($result))
{
    // code
}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Use a loop, and use mysql_fetch_array() instead of row:

while($row = mysql_fetch_array($result)) {
   echo "Study: " . $row[1] . " - " . $row[5];
   // but now with mysql_fetch_array() you can do this instead of the above
   // line (substitute userID and username with actual database column names)...
   echo "Study: " . $row["userID"] . " - " . $row["username"];
}
Improve this answer

Comments

0

I suggest you to read this: http://www.w3schools.com/php/php_mysql_select.asp It will give you an overview idea of how to properly connect to mysql, gather data etc

For your question, you should use a loop:

while ($row = mysql_fetch_row($result)){//code}

As said by htw

Improve this answer

Comments

0

You can also obtain a count of all rows in a table like this:


    $count = mysql_fetch_array(mysql_query("SELECT COUNT(*) AS count FROM table"));
    $count = $count["count"];

You can also append a normal WHERE clause to the select above and only count rows which match a certain condition (if needed). Then you can use your count for loops:

    $data = mysql_query("SELECT * WHERE username='bob'");
    for ($i = 0; $i

Also, mysql_fetch_array() is usually a lot easier to use as it stores data in an associative array, so you can access data with the name of the row, rather than it's numeric index.

Edit: There's some kind of bug or something going on where my second code block isn't showing everything once it's posted. It shows fine on the preview.

Improve this answer

Comments

0

I like to separate the DB logic from the display. I generally put my results into an array that I can call within the HTML code. Purely personal preference; but here's how'd I'd approach the problem: (I'd take the $sql out of the error message in production)

<?php
$sql="
  SELECT *
  FROM auth
  WHERE username='$user';
  ";
$result = mysql_query($sql)
  or die("Failed Query : ".mysql_error() . $sql);  //do the query

while ($ROW = mysql_fetch_array($result,MYSQL_ASSOC)) {
  $USERS[] = $ROW;
}
?>

HTML CODE

<? foreach ($USERS as $USER) { ?>

Study: <?=$USER['dbFieldName'];?> - <?=$USER['dbFieldName2'];?>

<? } //foreach $USER ?>

Improve this answer

Comments

0

$qry=mysql_query(select * where username='bob');

if(mysql_num_rows($qry))

{

 while($row=mysql_fetch_array($qry,MSQL_NUM))

 {

        echo $row[0]."&nbsp;&nbsp;".$row[1]."&nbsp;&nbsp;".$row[2]."<br>";

 }

}

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.