Using PHP to display a SQL Query

[Mutley]

Hi there,

Having a problem. I want it so if you goto this certain page it displays a row from the table and all the fields.

I have done a config.php which is this (but filled in):
[code]
<?php
$host = "localhost";
$user = "myusername";
$pwd = "mypassword";
$db = "mypassword";
?>
[/code]

Then a connection.php file:

[code]<?php
require_once("config.php");

$connection = mysql_connect($host, $user, $pwd) or die("&error1=".mysql_error());

mysql_select_db($db, $connection);
?>[/code]

Now I want to display a query in a table from the database but no idea how.

I guess it is something like this but alot more complex. The table is called "profiles" and I want it to display all the fields in a table, under the user id 1.

[code]
<?php>
require_once("connection.php");

$query = SELECT all fields FROM profiles WHERE id=1
?>
[/code]

Any help, would be much appreciated.

[ChaosXero]

[code]
$query = "SELECT * FROM 'profiles' WHERE 'id'=1";
$result = mysql_query($query);
$r= mysql_fetch_array($result);

while($result) {
   foreach($r as $var){
      echo $var."\n"
   }
}
[/code]
(I'm sure about the query syntax, however the foreach loop I haven't done much.  YOu might check the syntax)

[Mutley]

Nope, get parse error on line 12. Where } is.

Is that all I need? Do I not need any HTML table formatting to display the result?

Thanks for the fast reply though.

[ChaosXero]

[code]
<?PHP
$query = "SELECT * FROM 'profiles' WHERE 'id'=1";
$result = mysql_query($query);

while($r= mysql_fetch_array($result)) {
  foreach($r as $var){
      echo $var."\n"
  }
}
?>
[/code]

Try that now.

[ryanlwh]

don't use mysql_fetch_array in this case, because it will duplicate the records (returning the numerical indices and the associative indices). use either mysql_fetch_row or mysql_fetch_assoc

[Mutley]

[quote author=ChaosXero link=topic=101277.msg400592#msg400592 date=1153415316]
[code]
<?PHP
$query = "SELECT * FROM 'profiles' WHERE 'id'=1";
$result = mysql_query($query);

while($r= mysql_fetch_array($result)) {
  foreach($r as $var){
      echo $var."\n"
  }
}
?>
[/code]

Still get the error on line 8. Parse error: parse error, unexpected '}', expecting ',' or ';' in .

Try that now.
[/quote]

[wildteen88]

[code=php:0]<?PHP
$query = "SELECT * FROM 'profiles' WHERE 'id'=1";
$result = mysql_query($query);

while($r= mysql_fetch_assoc($result)) {
  foreach($r as $var){
      echo $var."\n"; //missing ;
  }
}
?>[/code]

[treilad]

Missing a ; after the echo.

[ryanlwh]

try this
[code]<?PHP
$query = "SELECT * FROM 'profiles' WHERE 'id'=1";
$result = mysql_query($query);

while($r= mysql_fetch_assoc($result)) {
  foreach($r as $field=>$var){
      echo "$field: $var\n";
  }
}
?>[/code]

[Mutley]

Error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 5.

This line:
while($r= mysql_fetch_assoc($result)) {

Any ideas?

Great replies so far guys, thanks.  :)

[wildteen88]

remove the single quotes - ' - from your query.

Also make sure you are connected to the database too.

[Mutley]

[quote author=wildteen88 link=topic=101277.msg400608#msg400608 date=1153416453]
remove the single quotes - ' - from your query.

Also make sure you are connected to the database too.
[/quote]

Awesome it worked, thank-you so much!

How do I make it so I can format the result fields? For example each field result is displayed in a html table? Do I use "echo" somehow?

[wildteen88]

Somthing like this:
[code]echo "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\n  ";

while($r = mysql_fetch_assoc($result))
{
    echo "<tr>\n    ";

    foreach($r as $field => $var)
    {
        echo "<td>{$var}</td>\n  ";
    }

    echo "<tr>\n";
}
echo "</table>";[/code]

[Mutley]

Could you give a more detailed explination on how to tie this into the first piece? I presume something needs to go after each echo?

[wildteen88]

That code replaces the following:
[code]while($r= mysql_fetch_assoc($result)) {
  foreach($r as $field=>$var){
      echo "$field: $var\n";
  }
}[/code]
So the full code will now be this:
[code]<?PHP
$query = "SELECT * FROM 'profiles' WHERE 'id'=1";
$result = mysql_query($query);

echo "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\n  ";

while($r = mysql_fetch_assoc($result))
{
    echo "<tr>\n    ";

    foreach($r as $field => $var)
    {
        echo "<td>{$var}</td>\n  ";
    }

    echo "<tr>\n";
}

echo "</table>";

?>[/code]

[HeyRay2]

Anything within the quotes of...

[code]echo " ";[/code]

Will be printed to your web browser, so simply layout your table to how wildteen88 mentioned and you should be good to go... :)

[Mutley]

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 8?

[Mutley]

Fixed it, similar problem to before, this is correct:

[code]<?PHP
$query = "SELECT * FROM profiles WHERE id=1";
$result = mysql_query($query);

echo "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\n  ";

while($r = mysql_fetch_assoc($result))
{
    echo "<tr>\n    ";

    foreach($r as $field => $var)
    {
        echo "<td>{$var}</td>\n  ";
    }

    echo "<tr>\n";
}

echo "</table>";

?>[/code]

Now if I wish to have each part in a row, rather than columns, how would I do that? I would like it to look something like this, with 2 columns, one with the title and the other column with the data:

Name: $name
DOB: $dob
Favourite food: $food
...

Thanks alot. :)