Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
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. – ceejayoz Apr 24 '09 at 21:48

6 Answers

up vote 9 down vote accepted

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
}
share|improve this answer
up vote 1 down vote

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"];
}
share|improve this answer
up vote 0 down vote

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

share|improve this answer
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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 ?>

share|improve this answer
up vote 0 down vote

$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>";

 }

}

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.