Jump to content


Photo

Using PHP to display a SQL Query


  • Please log in to reply
17 replies to this topic

#1 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 20 July 2006 - 04:50 PM

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):
<?php
$host = "localhost";
$user = "myusername";
$pwd = "mypassword";
$db = "mypassword";
?>

Then a connection.php file:

<?php
require_once("config.php");

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

mysql_select_db($db, $connection);
?>

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.

<?php>
require_once("connection.php");

$query = SELECT all fields FROM profiles WHERE id=1
?>

Any help, would be much appreciated.
~ Mutley.

#2 ChaosXero

ChaosXero
  • Members
  • PipPipPip
  • Advanced Member
  • 80 posts

Posted 20 July 2006 - 04:55 PM

$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"
   }
}
(I'm sure about the query syntax, however the foreach loop I haven't done much.  YOu might check the syntax)

#3 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 20 July 2006 - 05:04 PM

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.
~ Mutley.

#4 ChaosXero

ChaosXero
  • Members
  • PipPipPip
  • Advanced Member
  • 80 posts

Posted 20 July 2006 - 05:08 PM

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

Try that now.

#5 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 20 July 2006 - 05:15 PM

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
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#6 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 20 July 2006 - 05:16 PM

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

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

Try that now.


~ Mutley.

#7 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 20 July 2006 - 05:17 PM

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


#8 treilad

treilad
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 20 July 2006 - 05:17 PM

Missing a ; after the echo.

#9 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 20 July 2006 - 05:18 PM

try this
<?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";
   }
}
?>

Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#10 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 20 July 2006 - 05:26 PM

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.  :)
~ Mutley.

#11 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 20 July 2006 - 05:27 PM

remove the single quotes - ' - from your query.

Also make sure you are connected to the database too.

#12 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 20 July 2006 - 05:44 PM

remove the single quotes - ' - from your query.

Also make sure you are connected to the database too.


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?
~ Mutley.

#13 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 20 July 2006 - 05:48 PM

Somthing like this:
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>";


#14 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 20 July 2006 - 06:02 PM

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?
~ Mutley.

#15 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 20 July 2006 - 07:22 PM

That code replaces the following:
while($r= mysql_fetch_assoc($result)) {
   foreach($r as $field=>$var){
      echo "$field: $var\n";
   }
}
So the full code will now be this:
<?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>";

?>


#16 HeyRay2

HeyRay2
  • Members
  • PipPipPip
  • Advanced Member
  • 223 posts

Posted 20 July 2006 - 07:26 PM

Anything within the quotes of...

echo " ";

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

#17 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 20 July 2006 - 09:46 PM

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

#18 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 20 July 2006 - 10:18 PM

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

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

?>

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. :)
~ Mutley.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users