displaying data from a mysql select statement

[brea]

I am very new to php and I have information in my database that I want to display and I just cannot get it to display. Any help would be appreciated.

here is the sql statement I am running..

("SELECT `ID` , `First_Name` , `Last_Name` , `Mnth_pts` FROM `members` WHERE 1 ORDER BY `Mnth_pts` DESC LIMIT 0 , 30

I then want to take the results of that query and display them on the page. It should look like so..

ID First Name Last Name Pts
X John Doe 555
Y Jane Doe 544
Z Jim Doe 300
and so on for whatever number of people are returned.

Thanks for your help on this.

 

[sleipnir214]

The four stages of interaction between PHP and MySQL (and most data sources) is:[ol][li]Connect to the server[/li][li]Optionally select the database on the server[/li][li]Pass MySQL an SQL query[/li][li]Optionally process the results[/li][/ol]

These steps are accomplished by use of the mysql_connect(), mysql_select_db(), mysql_query() and mysql_fetch_*() functions.

You can see all four of these steps in sample code on the PHP online manual page for mysql_fetch_assoc()


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[chessbot]

The names of fields and tables in your SQL statement shouldn't be in quotes, I believe.

--Chessbot

"Violence is the last refuge of the incompetent." -- Asimov, Foundation

 

[sleipnir214]

Those aren't quotes -- their backticks. You can use those in a query when you've done the unwise thing of naming your tables or columns using MySQL reserved words. Otherwise they're not necessary.

A lot of MySQL GUI apps automatically place them around every table and column name as a precaution against problems cause by the use of a reserved word not known to the software.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[chessbot]

Oh, ok.

--Chessbot

"Violence is the last refuge of the incompetent." -- Asimov, Foundation

 

[sleipnir214]

Good Lord, look at my spelling.

The first sentence in that post should have read:

Those aren't quotes -- they're backticks.


And the sentence the comprises the second paragraph contains:

precaution against problems cause by

which should read:

precaution against problems caused by


That'll teach me to proofread.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[brea]

Thank you very much. I have just about everything I need here. It's displaying the informaiton but not in a readable format....

Here is my code taken from the explanation of mysql_fetch_assoc()

CODE START

<?PHP
$conn = mysql_connect("localhost" , "username" , "pwd");
if (!$conn) {
echo "unable to connect to DB:" . mysql_error();
exit;
}
if (!mysql_select_db("members")) {
echo "Unable to select members db: " . mysql_error();
exit;
}
$sql = "SELECT `ID` , `First_Name` , `Last_Name` , `Mnth_pts` FROM `members` WHERE 1 ORDER BY `Mnth_pts` DESC LIMIT 0 , 30";
$result = mysql_query($sql);
if (!$result) {
echo "Could not successfully run query ($sql) from DB: " . mysql_error();
exit;
}
if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}
while ($row = mysql_fetch_assoc($result)) {
echo $row["ID"];
echo $row["First_Name"];
echo $row["Last_Name"];
echo $row["Mnth_pts"];
}
mysql_free_result($result);
?>

CODE END


here is what it outputs with no page breaks....


3davidjones5999mikejones540 and so on.

What I would like to see is...

3 David Jones 599
9 Mike Jones 540

Once again thanks for the help and the rest was informative as well.

 

[chessbot]

echo <<<END
<table>
<tr>
<th>ID</th>
<th>First name</th>
<th>Last name</th>
<th>Points this month</th>
</tr>
END;
while ($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>{$row['ID']}</td>";
echo "<td>{$row['First_Name']}</td>";
echo "<td>{$row['Last_Name']}</td>";
echo "<td>{$row['Mnth_pts']"</td>";
echo "</tr>";
}
echo "</table>";

--Chessbot

"Violence is the last refuge of the incompetent." -- Asimov, Foundation

 

[brea]

I put that in but now nothing shows up at all? If I have it right I'm either missing something in the code or I have a wrong character in there or something. Here is what I have at this point.

<?PHP
$conn = mysql_connect("localhost" , "username" , "pwd");
if (!$conn) {
echo "unable to connect to DB:" . mysql_error();
exit;
}
if (!mysql_select_db("members")) {
echo "Unable to select members db: " . mysql_error();
exit;
}
$sql = "SELECT `ID` , `First_Name` , `Last_Name` , `Mnth_pts` FROM `members` WHERE 1 ORDER BY `Mnth_pts` DESC LIMIT 0 , 30";
$result = mysql_query($sql);
if (!$result) {
echo "Could not successfully run query ($sql) from DB: " . mysql_error();
exit;
}
if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}
<table>
<tr>
<th>ID</th>
<th>First name</th>
<th>Last name</th>
<th>Points this month</th>
</tr>
END;
while ($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>{$row["ID"]}</td>";
echo "<td>{$row["First_Name"]}</td>";
echo "<td>{$row["Last_Name"]}</td>";
echo "<td>{$row["Mnth_pts"]}</td>";
echo "</tr>";
}
echo "</table>";
mysql_free_result($result);
?>

 

[brea]

never mind I think I have it with the html table coding. Thanks for all of the help on this one.