MySQL/PHP problem

Factual Questions
1

Okay, I’m a complete novice and I shouldn’t be messing with these things, but I am, and here goes.

I have a database, and a form which allows people to put their information into that database. That’s all fine and good. What I want to do is have a page which displays all of the usernames of the people who have put their information into the database. What I’ve done is this:


<?php
$db = mysql_connect("localhost", "root", "mypasswordwhichyoudon'tneedtoknow");
mysql_select_db("thenameofmydatabase",$db);
$result = mysql_query("SELECT * FROM thetablewithmydata",$db);
printf("%s<br>
", mysql_result($result,0,"username"));
?>

This, of course, only displays the first result.

I need to display all of the results in the “username” column of the table.

Can anyone clue me in as to the way to make that happen?

2

OK, I’ve only used Postgres databases with PHP but the logic should be the same, basically you’re going to need a loop to go through the results and print each row, so instead of …


printf("%s<br>
", mysql_result($result,0,"username"));

you need


$rows = mysql_num_rows($result);
for($i = 0; $i < $rows; $i++) {
  printf("%s<br>
", mysql_result($result,$i,"username")));
}

I may have stuffed up the syntax slighty and there may be an easier way but the idea is sound, this will also not choke if the query returns no results (although you may want to check for that a print something different).

Check the online manual at the PHP website, it has information on all the PHP functions including the mySQL stuff. It’s generally very helpful when trying to get things to work.

SD

3

That works perfectly! Thank you so much, SpaceDog. I’d send you a cookie if the board had a baked good upload function. :slight_smile:

4

I know your OP question has already been anwered, but in the future, if you need to get multiple columns at the same time, try this out:

<?php
// Connect
$db = mysql_connect("host", "user", "pass");
mysql_select_db("myDB", $db);

$query = "SELECT col1, col2, col3 FROM myTable";
$result = mysql_query($query);

// Here's the good bit...
if (!mysql_error()) {
   // mysql_fetch_assoc() retrieves one row's worth of results at a time
   // and returns it in the form of an associative array
   while ($nextRow = mysql_fetch_assoc($result)) {
      // You can then work with the data in a couple of ways...
      /// Directly from the array...
      echo "Col 1: " . $nextRow['col1'] . "<br/>";

      /// Through a temporary variable (saves on typing if you use the var a lot)...
      $col2 = $nextRow['col2'];
      echo "Col 2: $col2<br/>";
   }
} else {
   echo "Error: " . mysql_error();
}

// Disconnect and clean up
mysql_close($db);
unset($query);
unset($db);
?>

You can do lots of really brilliant stuff with PHP and MySQL; for more info, check out the PHP docs.

5

In PHP, do you really have to store multiple rows of a query result in a single scalar variable, and then use a separate function to retrieve it? That seems, well, ugly.

Doesn’t PHP have arrays or any types of data structures? If so, are there no mysql libraries that use them?

I was thinking of trying PHP out… that’s why I’m sticking my nose into a thread about a language that I obviously don’t know. Trying not to be judgemental but it just seems bizarre to me.

6

Of course it has.
Notice the line a bit below:


$col2 = $nextRow['col2'];

Which is clearly an index into an array or map of some type. I think the mysql_fetch_assoc() function is just a nice convenient way to get the next thing out of the result array. It makes the loops pretty and easy.