[Resolved] PHP SQL Query for simple page...

Beavis2084

I'm so noob, i could only think of this site as a place to find a guru for this small task.

i have a table in MYSQL ( looks like this one: http://coasters.coleclan.net/trackrecord.html )

i want to make that page dynamic. next year when i visit more parks and ride new coasters, i want to add a record to the database, and have the table displayed on a PHP page ... get it?

ok... i've seen so much crap on "connecting" and then "querys" that i am swimming.

can anyone please tell me how to make a simple PHP page that will query the database and display the contents of the table similar to how it appears at the URL above?

Thank you!

nelsons

It can be frustrating starting out. Try this. Modify the UPPERCASE areas to suit your database. This may be wordy, but it should work for you enough to get you started.

<?php
$host = "localhost";
$user = "USERNAME";
$pass = "PASSWORD";
$dbase = "DATABASENAME";

$dbcnx = @mysql_connect($host,$user,$pass);
if (!$dbcnx) {
die('unable to connect to database server at this time.');
}

if (! @mysql_select_db("$dbase") ) {
die('unable to connect to db at this time.');
}

$result = @('SELECT * FROM TABLENAME');
if (!$result) {
die ('Error performing query: ' . mysql_error() . '');
}

if (mysql_num_rows($result) < 1) {
echo ("No records available");
} else {

while ($row = mysql_fetch_array($result) ) {
echo (' ' . $row['FIRSTDATAFIELD'] . ' ');
echo ($row['SECONDDATAFIELD'] . ' ');
echo ($row['THIRDDATAFIELD'] . ', ');
echo ($row['FOURTHDATAFIELD'] . ', ');
echo ($row['FIFTHDATAFIELD'] . ' ');
echo ('');
}

}

Roger_Ramjet

Try reading this, and download the 4 chapters as well. Build Your Own Database Driven Website Using PHP & MySQL http://www.sitepoint.com/books/phpmysql1/ Simple starter that will cover all you need right now.

Beavis2084

Awesome!

thank you all!

i took that code, and played with it a bit, i think i actually have it all done...

http://coasters.coleclan.net/trackrecord.php

now, i want to make a final tweak....

the page shows all the coasters ridden including the length in feet. the title of the page includes the total track length in miles.. ( =sum of all coasters' track lengths/5279.8556430 )

what i want to do now is this......

how do i extract the values of the 'length' field from the table, add them up and divide by 5279.8556430 and insert that into the <title> tag using PHP ???

Beavis2084

nelsons

Mechanically speaking, this may be clunky, but Ibelieve it will work for you.

Create a varialbe called $Clength.
Initialize it with 0.
Each time you output the lengh of track, add the length to the $Clength variable.

$CLength = $Clength + $row['xxx']
When your loops done you will then have the $Clength with total footage.
Take the total footage and manipulate accordingly.

BTW: The sitepoint books are excellent. A worthwhile investment.

If your still stuck - repost part of your code

Beavis2084

okay... i understand that okay... but am unclear where to stick that in my code....

here is my existing code...........

<?php 
$host = "localhost"; 
$user = "username"; 
$pass = "password"; 
$dbase = "coasters"; 

$dbcnx = @mysql_connect($host,$user,$pass); 
if (!$dbcnx) { 
die('unable to connect to database server at this time.<p>'); 
} 

if (! @mysql_select_db("$dbase") ) { 
die('unable to connect to db at this time.<p>'); 
} 

$result = @mysql_query('SELECT * FROM trackrecord ORDER BY name'); 
if (!$result) { 
die ('Error performing query: ' . mysql_error() . '<p>'); 
} 

if (mysql_num_rows($result) < 1) { 
echo ("No records available"); 
} else { 


// create table and add first row with headers.
echo ('<table align=center width=60% border=0');
echo ('<tr align=center>
        <td><font face="Verdana" size="4"><strong><u>Name </u></strong></font></td>
        <td><font face="Verdana" size="4"><strong><u>Type </u></strong></font></td>
        <td><font face="Verdana" size="4"><strong><u>Park </u></strong></font></td>
        <td align=right><font face="Verdana" size="4"><strong><u>Length (ft) </u></strong></font></td>
        </tr>');

// this is the data output from the table.
while ($row = mysql_fetch_array($result) ) { 
echo ('<tr>');
echo ('<td><font face="Verdana" size="3">' . $row['name'] . '</td><td><font face="Verdana" size="3">' . $row['type'] . '</td><td><font face="Verdana" size="3">' . $row['park'] . '</td><td align=right><font face="Verdana" size="3">' . $row['length'] . '</td></tr></b>');
} 
// END of data extraction, close table.
echo ('</tr></table>'); 

} 

?>

any assistance is great!

Beavis2084

nelsons

To place the mileage in the title tag place this in the middle of it:
<?php echo $miles; ?>

You may have to work without output buffering so that everything executes/parses before writting out. Will have to see.

<?php
$host = "localhost";
$user = "username";
$pass = "password";
$dbase = "coasters";

$dbcnx = @mysql_connect($host,$user,$pass);
if (!$dbcnx) {
die('unable to connect to database server at this time.');
}

if (! @mysql_select_db("$dbase") ) {
die('unable to connect to db at this time.');
}

$result = @('SELECT * FROM trackrecord ORDER BY name');
if (!$result) {
die ('Error performing query: ' . mysql_error() . '');
}

if (mysql_num_rows($result) < 1) {
echo ("No records available");
} else {

// create table and add first row with headers.
echo ('<table align=center width=60% border=0');
echo ('<tr align=center>
<td>Name </td>
<td>Type </td>
<td>Park </td>
<td align=right>Length (ft) </td>
</tr>');

// we create a var with a zero value
$Clen = 0;
// this is the data output from the table.
while ($row = mysql_fetch_array($result) ) {
echo ('<tr>');
echo ('<td>' . $row['name'] . '</td><td>' . $row['type'] . '</td><td>' . $row['park'] . '</td><td align=right>' . $row['length'] . '</td></tr>');
// we take the existing $Clen and add the lenghth of the latest pulled row[length] to it
$Clen = ($Clen + $row['length']);
}
// END of data extraction, close table.
echo ('</tr></table>');
echo ("");
// Now we echo back the total of all the lengths added together
echo $Clen;

// Now we echo back the $Clen divded by footage
$miles = ($Clen / 5279.8556430);
echo $miles;

}

Beavis2084

last bit... how do we round the final number to 2 decimal places ?? i.e.: 34.25 miles

Beavis2084

wait a sec...

that other stuff was broken. it did not generate a number.

i think the mention about "buffering" you said above must be why.

what do we do?

Beavis2084

nelsons

number_format($Clen, 2, '.', '') might do it for you on the number formatting.

I just looked, didnt see an error. Is it at insertion of the miles in the title? If so, skip that until all else is working.

Beavis2084

yeah.. my problem was the title insert part.

you should not see anything cause i just remarked-out the added in code.

i'll try out that format bit.

it seems that i can;t insert the variable anywhere BEFORE the loop. so how do i work around that ?

Beavis2084

nelsons

All things considered, it may take you more time to figure that one out - than it would to just manually update it after adding a new coaster ride.

This gives you plenty of time to delve into the output buffering issue. I'm not very experienced in that specific area yet, so am of limited assistance.

Did the number rounding work?