2 Simple Questions Re: Replacing echo with database table unit

Evad

05-09-2004, 05:39 AM

My first question is extremely simple, and I suspect the second one isn't too tough, either. I'm a newbie; I've been working with webpages driven by PHP includes and echo functions and am just learning how to create MySQL tables, connect them with Dreamweaver and display them on web pages.

Below is the code from the head section of one of my pages:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<?php
$seg = '../../../';
$myname = 'France';
$mycode = 'fr';
$iso3 = 'fra';
$ison = '250';
$fips = '' . $mycode . '';
$internet = '' . $mycode . '';
$mynat = '' . $myname . '';
$nat = '' . $mycode . '';
$mycont = 'Eurasia (Europe)';
$cont = 'eurasia';
$contlink = '<a href="../index.php">' . $mycont . '</a> > ';
$mysec = 'World';
$sec = 'world';
$seclink = '<a href="../../index.php">' . $mysec . '</a> > ';
$url = '' . ((!empty($sec)) ? ($sec . '/') : '') . ((!empty($cont)) ? ($cont . '/') : '') . ((!empty($nat)) ? ($nat . '/') : '') . ((!empty($state)) ? ($state . '/') : '');
$urlworld = '' . ((!empty($cont)) ? ($cont . '/') : '') . ((!empty($nat)) ? ($nat . '/') : '') . ((!empty($state)) ? ($state . '/') : '');
$myreg = 'Western Europe';
$reg = 'Western Europe';
$reg2 = ', <a href="http://www.geoworld.org/' . $contcode . '/regions/index.php#alps">Alps</a>';
$nick = 'The Cultured Empire';
$quote = 'I don’t know exactly what democracy is. But we need more of it.';
$sig = 'Anonymous Chinese Student, during protests in Tianamen Square, Beijing, 1989';
include ($seg."a1/inc/head.php")
?>
</head>

I don't yet understand how all this stuff works, but if I'm going to make a database driven site, then I assume I'm going to be replacing my includes and echo statements with databases. Right now, I'm deseperately trying to figure out how to take the first step. "The Manual" is useless, because I don't even know where to begin; I don't know what topic to research.

So let me reduce the above head section to a single element:

$myname = 'France';

On another page, that would be...

$myname = 'Spain';

Now, suppose I have a database table that lists every county, its capital, population, etc. Suppose "Spain" is in the 52nd row, 2nd column. How do I replace $myname = 'Spain'; with the cell from the 52nd row, 2nd column?

What would the replacement look like - something like this?:

$myname = '$row_Recordset1['Country']';

I became even more confused when I inserted a "dynamic," repeating table with Dreamweaver. Here's what it looks like:

<table border="1">
<tr>
<td>Name</td>
<td>Capital</td>
<td>Birthday</td>
<td>Rank</td>
<td>Origin</td>
<td>Code</td>
<td>Country</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['Name']; ?></td>
<td><?php echo $row_Recordset1['Capital']; ?></td>
<td><?php echo $row_Recordset1['Birthday']; ?></td>
<td><?php echo $row_Recordset1['Rank']; ?></td>
<td><?php echo $row_Recordset1['Origin']; ?></td>
<td><?php echo $row_Recordset1['Code']; ?></td>
<td><?php echo $row_Recordset1['Country']; ?></td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>

As you can see, it doesn't really illustrate the entire table, though I can see the entire table when I preview the page in a browser. So do I just have to look at the original table, count the rows and columns and figure out the coordinates of the cell that contains the word "Spain"?

My second question is more complex. Here's another condensed view of the head section. Notice that it matches France with a Continent (Europe) and Section (World).

<head>
<?php
$seg = '../../../';
$myname = 'France';
$mycode = 'fr';
$mynat = '' . $myname . '';
$nat = '' . $mycode . '';
$mycont = 'Eurasia (Europe)';
$cont = 'eurasia';
$contlink = '<a href="../index.php">' . $mycont . '</a> > ';
$mysec = 'World';
$sec = 'world';
include ($seg."a1/inc/head.php")
?>
</head>

So, my first hunch was that I'd replace $myname = 'France'; with a cell from my database table, replace $mycont = 'Eurasia (Europe)'; with another cell, and so on.

But can I set it up so that all I have to is insert ONE cell that identifies this page as "France," and it will somehow magically pull everything associated with it along? In other words, if that single cell says, "This is France," then an element that's programmed to display the name of a continent will say, "This is Europe!"

I hope my explanation wasn't too scattered, but I suspect this is a fairly easy operation. I just wallowing in confusion at the moment.

Thanks!

sidney

05-09-2004, 04:14 PM

first i must say dreamweaver is not that good for learning how mysql interacts with php, but its great for editing php scripts so i surgest you do it all manually

first to get data from a data base you would either ask for all the data
or ask for specific items

but first you need to connect

<?php $dbserver="localhost"; $dbuser="user_name"; $dbpass="password"; $database="database";
mysql_connect($dbserver, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($database) or die(mysql_error()); ?>

after connecting you would make a query

$query="SELECT * FROM tablename"; // basicly select all from database

$sql=mysql_query($query); //connect resourceID

while($row=mysql_fetch_assoc($sql)) // start loop to fetch each row from database
{
echo"county = ".$row['country']." capital = ".$row['cap']."<br"; //display row from database
}

alternatively if you have say a username you can just retrive the users info

$query="SELECT * FROM tablename WHERE username='$username'";

using the above query instead of first will retrive the users info in an array which you can display any way you like

heres a link to the php mysql functions lots of examples and all the query functions are listed http://uk.php.net/manual/en/ref.mysql.php

feel free to ask for more info if your not clear

Evad

05-09-2004, 05:48 PM

first i must say dreamweaver is not that good for learning how mysql interacts with php, but its great for editing php scripts so i surgest you do it all manually

That's fine; I don't mind doing it manually at all. I just used Dreamweaver because it offers some shortcuts, and I didn't know the code I need to type manually.

"first to get data from a data base you would either ask for all the data
or ask for specific items

but first you need to connect

<?php $dbserver="localhost"; $dbuser="user_name"; $dbpass="password"; $database="database";
mysql_connect($dbserver, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($database) or die(mysql_error()); ?>

Hmmmm... I've already connected with Dreamweaver, though the code looks different than yours. I may have mangled it, too; I drug a lot of "recordset" onto my page and may have the code from two or more elements strung together:

<?php
mysql_select_db($database_Connect, $Connect);
$query_Recordset2 = "SELECT * FROM statetest";
$Recordset2 = mysql_query($query_Recordset2, $Connect) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>

"after connecting you would make a query"

Again, I used Dreamweaver to insert a "dynamic table":

<table border="1">
<tr>
<td>Name</td>
<td>Capital</td>
<td>Birthday</td>
<td>Rank</td>
<td>Origin</td>
<td>Code</td>
<td>Country</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['Name']; ?></td>
<td><?php echo $row_Recordset1['Capital']; ?></td>
<td><?php echo $row_Recordset1['Birthday']; ?></td>
<td><?php echo $row_Recordset1['Rank']; ?></td>
<td><?php echo $row_Recordset1['Origin']; ?></td>
<td><?php echo $row_Recordset1['Code']; ?></td>
<td><?php echo $row_Recordset1['Country']; ?></td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>

I can view the entire table when I preview my page in a browser, but it's confusing, because the bulk of the table isn't visible in my source code. So I don't know what the code is for a particular element, like the cell with the word "Spain" or "Europe."

To make a database-driven site, I assume you have to display certain elements in strategic places, rather than just dump an entire table into your head section.

It looks like the code you posted below specifies a database table in the first line, then displays each row from that table - in other words, it displays the entire table?

$query="SELECT * FROM tablename"; // basicly select all from database

$sql=mysql_query($query); //connect resourceID

while($row=mysql_fetch_assoc($sql)) // start loop to fetch each row from database
{
echo"county = ".$row['country']." capital = ".$row['cap']."<br"; //display row from database
}

But the code below lets me retrieve, not the entire table, but an "array" that I can manipulate?:

"alternatively if you have say a username you can just retrive the users info"

$query="SELECT * FROM tablename WHERE username='$username'";

"using the above query instead of first will retrive the users info in an array which you can display any way you like"

So I THINK what you're saying is that if I want to insert just a single cell from my database table in the head section, then make that cell affect other cells, I need to study ARRAYS. Is that right?

A little more detail on what I'm planning...

I'm using country and state codes (abbreviations), like us, ca and nm at various points in the body. For example, every page has a title:

<div class="title"><h1><span class="titlespan" id="span' . $mycode . '">' . $myname . '</span></h1></div>

If $myname is "Spain," then viewers will see "Spain" at the top of the page. I can also use CSS to modify class="titlespan" for every page on the site, or to modify just Spain's title span, which is translated id-"spansp."

The included head section links every page on the site to a master style sheet:

<link href="<?php echo $seg ?>a1/css/aaa.css" rel="stylesheet" type="text/css" />

But it also uses codes to link pages to style sheets based on continents and regions. For example, the following code would translate into something like ../../../css/europe/eurw.css (Europe/Western Europe) on my France page:

<link href="<?php echo $seg ?>a1/css/<?php echo $cont ?>/<?php echo $reg ?>.css" rel="stylesheet" type="text/css" />

So, think of all these continent and country codes/abbreviations as little soldiiers awaiting orders. <?php echo $cont ?> knows it's a continent, but it doesn't know WHICH continent it is.

What I need to do - I think - is figure out how to do two things:

1. Put the general in the head section. In other words, how do I insert the cell from the database table that says "Spain"?

2. Somehow rig it so that all the soldiers are connected to the general, so if I choose "Spain," <?php echo $cont ?> translates "eurasia." If the general is "Brazil," <?php echo $cont ?> translates "sa," for South America.

It sounds like I need to use an array to make the connection between the general and the soldiers, right? I found the Arrays page at
http://www.php.net/manual/en/language.types.array.php

But can you give me a hint as to what type of array I need to focus on? I think I read about different kinds, like associative arrays, and whatever the others were called.

If I can figure out exactly what code I need to insert not an entire row but just the cell with Spain in the head section, then figure out how to make Spain give orders to the various echo statements in the body, I think everything will fall in place. Or am I going down a blind alley?

Thanks.

sidney

05-09-2004, 06:08 PM

to get a indivdual row you need some info first ie if you need all the spanish info to fill the spanish page soliders then you need to give mysql somthing for it to find the line mysql can use any field so if you

$query="SELECT * FROM tablename Where country = 'spain'"; // basicly select all from database

$sql=mysql_query($query); //connect resourceID

$row=mysql_fetch_assoc($sql) // start loop to fetch each row from database

echo"county = ".$row['country']." capital = ".$row['cap']."<br"; //display row from database

now you have a associative array of the row so $row['fieldnameof table'] will hold that field cell

you can use mysql_fetch_row and it will return a numeric array $row[0] to $row[5] if there are file fields

so you need some info sent to page ie a link containing the country code or somthing to query database with

you could query database for all countrys and make a list of countrys each an individual link containing a $_get id or contrycode then when somone clicks link youcan create a query with $_GET var and display info for that contry

on the subject of dremweaver code you will find a script starting MM_ that will have the extra stuff missing from the script dw normally puts it in a folder at root

sidney

05-09-2004, 06:25 PM

the link that links to france page should contain a refference to france row in database either say france or an auto incrementing id that is set when data is entered to database

<a href= "didplay.php?country=france">france</a>

then in display.php

$france=$_GET['contry'];

and then use france to make a query to the database
pull the france row
asign each element of $row array to its var

$continent=$row['continent'];
and so on

Evad

05-09-2004, 06:37 PM

Ah, thanks. It sounds like I'm on the right track, and your responses fill in many of the blanks.

Now I'll just have to finish my database tables and see if I can put it all together.

Thanks!