homepage Welcome to WebmasterWorld Guest from 54.166.65.9
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / WebmasterWorld / New To Web Development
Forum Library, Charter, Moderators: brotherhood of lan & mack

New To Web Development Forum

    
Output Listing
Need help with php coding
Ollie_3rd




msg:4050289
 1:33 pm on Dec 28, 2009 (gmt 0)

My Table is as follows

id bigint(20) No
state varchar(255) No
web varchar(255) No
name varchar(255)

The items under the state names are hyper links to web pages.

Any help would be really appreciated !

I am trying to code output for a web page that would produce a table to look like this:

ALABAMA

Alabama Beekeepers Association
Baldwin County Beekeepers Association
Central Alabama Beekeepers Association
Choctaw County Beekeepers Association

ALASKA

Alaska Beekeepers Association

ARIZONA

Beekeepers Association of Central Arizona

ARKANSAS

No Listings

CALIFORNIA

California State Beekeepers Association, Inc.
Mount Diablo Beekeepers Association
San Francisco Beekeepers Association

etc for all states

 

rocknbil




msg:4050432
 7:28 pm on Dec 28, 2009 (gmt 0)

Welcome aboard Ollie_3rd, a couple comments . . .

First thing I would do is this.

Better solution:

table tablename (whatever you've named it)
id int(11) primary key auto_increment
state_id tinyint(2)

web varchar(255)
name varchar(255)

then,

table states
id int(11) primary key auto_increment
state char(2)
full_state varchar(30)

So that the states table is joined by the id in the "tablename" table. The reasoning is that integer fields will always be faster; you will have many other uses for the state table and it can be accessed on it's own, for example, generating a "select your state list" in which you may not want the full state values; and you may want to expand the states table or make changes to it without affecting your main table data.

Furthermore, you'll never be able to output "no data" as you have it now unless you have a full list of states to iterate through, unless you enter "empty records" which is also a waste, see below.

So a select would then change to something like (where "tablename" is the main table name)

select tablename.name, tablename.web, states.full_state from tablename,states where tablename.state_id=states.id order by states.state asc;

Of course, the previous is only going to display states for which there's data in "tablename", which may be of use in a different approach (different select below . . . )

"As Is" solution:

With what you have now, varchar(255) is overkill for a full state name.

alter table tablename change state state varchar(30);

I presume you are familiar with mysql connection methods using PHP. For what you are asking for, you'll only be able to output values for states that exist in your table. If you want to output data for every state and have "no data" for nonexisting states, you have to have a full list somewhere. The first solution does this for you, but otherwise,


var $allstates = Array (
'Alabama',
'Alaska',
'Arizona',
'Arkansas'
// And so on, full state list.
);
$currState=$rowData=NULL;
foreach ($allstates as $state) {
if ($currState != $state) {
$output .= "<h3>$state</h3>";
$currState=$state; // only prints once per section
}
$query = "select web, name from tablename where state='$state' order by name asc";
$result=mysql_query($query);
if (!$result) { die("can't get data from tablename: " . mysql_error()); }
while ($row=mysql_fetch_array($result)) {
//$row[0] is web, $row[1] is name. You can also use
//assoc. values: $row['web'] and $row['name']
$rowData .= "<p><a href=\"" . $row[0] . "\">" . $row[1] . "</a></p>";
}
if ($rowData) { $output .= $rowData; }
else { $output .= "<p>No results in $state</p>"; }
}
mysql_free_result($result);
header("content-type:text/html");
echo "
<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01
Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">
<html lang=\"en\">
<head>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=ISO-8859-1\">
<title>Listings</title>
</head>
<body>
<h1>State Listings</h1>
$output
</body>
</html>
";

I typed this on the fly, it may have syntax errors and require debugging, but this will get you started.

Addendum to better solution:

If you add a states table as mentioned, you'd do almost the same thing, without an array. A left join will pull in all states, even if there are no entries for that state in "tablename." The script would be modified like so:


$currState=$rowData=NULL;
$query = "select web, name, full_state from tablename left join states on";
$query .= " states.id=tablename.state_id order by states.state asc, tablename.name asc";
$result=mysql_query($query);
if (!$result) { die("can't get data from tablename: " . mysql_error()); }
while ($row=mysql_fetch_array($result)) {
if ($currState != $row[2]) {
$output .= "<h3>" . $row[2] . "</h3>";
$currState=$row[2]; // only prints once per section
}
//$row[0] is web, $row[1] is name, $row[2] is state. You can also use
//assoc. values: $row['web'], $row['name'], $row['full_state']
if ($row[0] and $row[1]) {
$rowData .= "<p><a href=\"" . $row[0] . "\">" . $row[1] . "</a></p>";
}
}
if ($rowData) { $output .= $rowData; }
else { $output .= "<p>No results in " . $row[2] . "</p>"; }
}
mysql_free_result($result);

Should be enough to get you started, anyway.

Ollie_3rd




msg:4050517
 9:10 pm on Dec 28, 2009 (gmt 0)

I haven't tried it yet but thanks for the start. I would have never figured this out. I will get back to you.

Ollie_3rd




msg:4050920
 5:55 pm on Dec 29, 2009 (gmt 0)

Rockinbil:
Thanks again for your help!
Things are close to working but no cigar yet. I get 325 lines of web addresses that have no rhyme or reason (ie same ones for Alabama multiple times and in no order) except at the end I only get one for ALASKA; ARIZONA; AND ARKANSAS. Then I get the state name ALABAMA with all (22) web sites for that state. But then I get the same 22 for the other 3 states I have for testing. It doesn't select the web addresses for the next state(s). To keep this from being too long here is a sample output:

(mucho before these)
West Alabama Beekeepers Association
Wiregrass Beekeepers Association
Alaska Beekeepers Association
Beekeepers Association of Central Arizona
Arkansas Beekeepers Association
State Listings

ALABAMA

Alabama Beekeepers Association
Baldwin County Beekeepers Association
Central Alabama Beekeepers Association
Choctaw County Beekeepers Association
Cullman County Beekeepers Association
East Alabama Beekeepers Association

ALASKA

Alabama Beekeepers Association
Baldwin County Beekeepers Association
Central Alabama Beekeepers Association
Choctaw County Beekeepers Association
Cullman County Beekeepers Association
East Alabama Beekeepers Association

Another thing is I don' see a closing for the <?php and I have tried to remove/modify the last echo before " <!DOCTYPE html PUBLIC \"-//W3C//DTD HT that was close to the bottom of the code you suggested but nothing works when I do that. I had to remove the var before $allstates = Array because it didn't like that either.

Here is my code so far:

<html>
<head>
<title>Anne Arundel Beekeepers Association</title>
<META NAME="Listing of US Beekeeping Organizations" CONTENT="Links to Beekeeping Web Sites">
<META NAME="Bee,Bees,Beekeeping,Honey,Web Sites,Queen,Drone,Worker" CONTENT="PHP code to retreive data from a MySql Database">
</head>
<body>
<?php
mysql_connect("localhost", "root") or die(mysql_error());

//echo "Connected to MySQL<br />";
mysql_select_db("test") or die(mysql_error());
//echo "Connected to Database<p>";
$allstates = Array (
'ALABAMA',
'ALASKA',
'ARIZONA',
'ARKANSAS'
// And so on, full state list.
);

$currState=$rowData=NULL;

foreach ($allstates as $state) {

if ($currState != $state) {
$output .= "<h3>$state</h3>";

$currState=$state; // only prints once per section
}

$query = "SELECT web,name FROM aaba WHERE state='$currState' order by name asc";

$result=mysql_query($query);
if (!$result) { die("can't get data from tablename: " . mysql_error());

} while ($row=mysql_fetch_array($result)) {

//$row[0] is web, $row[1] is name. You can also use
//assoc. values: $row['web'] and $row['name']

echo $rowData .= " $row[0] <br />";
}
if ($rowData) { $output .= $rowData; }
else { $output .= "<p>No results in $state</p>";
}
}
mysql_free_result($result);

echo "
<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\"> <html lang=\"en\">
<head>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=ISO-8859-1\">
<title>Listings
</title>
</head>
<body> <h1>State Listings</h1>
$output

</body>
</html>
";

Ollie_3rd




msg:4051051
 11:30 pm on Dec 29, 2009 (gmt 0)

Ignore post above. Been trying figure it out on my own and have gotten this far but can't figure out what's wrong. Hope you help!

------------Latest output and coding------------------

State Listings

Alabama Beekeepers Association
Baldwin County Beekeepers Association
Central Alabama Beekeepers Association
Choctaw County Beekeepers Association
Cullman County Beekeepers Association
East Alabama Beekeepers Association
Escambia County Beekeepers Association
Etowah County Beekeepers Association
Jefferson County Beekeepers Association
Limestone County Beekeepers Association
Madison County Beekeepers Association
Monroe County Beekeepers Association
North-East Alabama Beekeepers Association
North-West Alabama Bee Club
Sand Mountain Beekeepers Association
Saugahatchee Beekeekers Association
South-East Alabama Beekeepers Association
Tallapoosa River Beekeepers Association
Tennessee Valley Beekeepers Association
Walker County Beekeepers Association
West Alabama Beekeepers Association
Wiregrass Beekeepers Association
No results in ALABAMA

ALABAMA

Alaska Beekeepers Association
No results in ALABAMA

ALABAMA

No results in ALASKA

ALASKA

Beekeepers Association of Central Arizona
No results in ALABAMA

ALABAMA

No results in ALASKA

ALASKA

No results in ARIZONA

ARIZONA

Arkansas Beekeepers Association
No results in ALABAMA

ALABAMA

No results in ALASKA

ALASKA

No results in ARIZONA

ARIZONA

No results in ARKANSAS

ARKANSAS
----------------------------latest coding-----------------------
<html>
<head>
<title>Anne Arundel Beekeepers Association</title>
<META NAME="Listing of US Beekeeping Organizations" CONTENT="Links to Beekeeping Web Sites">
<META NAME="Bee,Bees,Beekeeping,Honey,Web Sites,Queen,Drone,Worker" CONTENT="PHP code to retreive data from a MySql Database">
</head>
<body>

<h1>State Listings</h1>

<?php
mysql_connect("localhost", "root") or die(mysql_error());

//echo "Connected to MySQL<br />";

mysql_select_db("test") or die(mysql_error());

//echo "Connected to Database<p>";

$allstates = Array (
'ALABAMA',
'ALASKA',
'ARIZONA',
'ARKANSAS',
'blank',
// And so on, full state list.
);

$currState=$rowData=NULL;

foreach ($allstates as $state)
{

if ($currState != $state)

{

$output .= "<h3>$currState</h3>";

$currState=$state; // only prints once per section
}

echo "$output";

$query = "SELECT web, name FROM aaba WHERE state = '$state' ORDER BY name ASC";

$result=mysql_query($query);
if (!$result)

{ die("can't get data from tablename: " . mysql_error());

} while ($row=mysql_fetch_array($result))

{

//$row[0] is web, $row[1] is name. You can also use
//assoc. values: $row['web'] and $row['name']

echo " $row[0] <br />";

}
if ($rowData)

{ $output .= $rowData;

}
else

{ $output .= "No results in $state<br />";

}

}

mysql_free_result($result);

?>

</BODY>
</html>

rocknbil




msg:4051186
 3:34 am on Dec 30, 2009 (gmt 0)

Referring back to my original post, I think there were only three errors there, and it looks like you discovered the first, is the "var" word before $allstates.

The second was flushing "$rowData" on each iteration. This is a "temporary place" to hold the query results so that if there is none, you can add "no data" to output. Likely what was happening is it was concatenating all the way through, adding each state's results to itself . . . not what you want.

The third is I complicated the process of printing the state. The "prints only once" really only applies if you are iterating through a states database table, where the state name will be present for every query result. This is really how you should do it, but since you've opted for an array, we can simplify it.

You have a lot going on here that's wrong. Here's the concept:

- Loop through a list of states. For each state,
-- add the state name to $output. No need to compare if you're using a static array.
-- Query DB for entries for this state, store it in $rowData only. this is so if there's nothing, you can output ($output, not ECHO, see below) the "no data" paragraph.
-- if there's data in $rowData, add it to the $output variable only, don't echo it . . .
- loop to next state, rinse and repeat.
- when done, echo $output only. Once.

Give you an example, and how your recent change has made it worse. :-) This point is irrelevant due to the array, but if you do it right using a states database table, you need to know this.

foreach ($allstates as $state){
if ($currState != $state) {
$output .= "<h3>$currState</h3>";

You've just put the OLD state in $output, so it's going to be wrong. If a new state comes in, add $state to the $output, not $currState.

Another, which is relevant . . . you have multiple echo's throughout, and I've no idea what this one will do:

echo $rowData .= " $row[0] <br />";

You're echoing a concatenation command. The echoes you have will only serve to confuse you further, sort of like tasting a half baked cake before it's done. Concatenate all to $output, then echo $output once. Many/most PHP coders echo on the fly; this often makes for a sick spaghetti of code and often, pages that half render while the server "thinks about it" (processes a query, for example.) This is why I do this.

<rant>The break tag is presentational, it's meaningless . . . and an XHTML style break without a doctype in plain old vanilla html . . . these should be lists, and you can style them as you want . . . but I digress.</rant>

Also, in this,

SELECT web, name FROM aaba WHERE state = '$state' ORDER BY name ASC

$row[0] is web, $row[1] is name. I would think name is what you want and web is a url, but . . . however it works, it works. :-)

Okay, if what you have in the last post is sorta working, try this. Warning, it may have errors . . . but at this late hour, I can't see any. :-)


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01
Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">
<head>
<title>Anonymize, or the mods here will</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
</head>
<body>
<h1>State Listings</h1>
<?php
mysql_connect("localhost", "root") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
$allstates = Array (
'ALABAMA',
'ALASKA',
'ARIZONA',
'ARKANSAS',
'blank',
// And so on, full state list.
//What is "blank?"
// Think about your select, it will select
// where $state = 'blank'
);
foreach ($allstates as $state) {
$rowData=NULL; // my error, this needs to FLUSH with every state
$output .= "<h3>$state</h3>"; // The if not needed in this scenario.
$query = "SELECT web, name FROM aaba WHERE state = '$state' ORDER BY name ASC";
$result=mysql_query($query);
if (!$result) { die("can't get data from tablename: " . mysql_error()); }
while ($row=mysql_fetch_array($result)) {
$rowData .= "<li>" . $row[0] . "</li>\n";
}
if ($rowData) { $output .= "<ul> $rowData </ul>\n"; }
else { $output .= "<p>No results in $state</p>\n"; }
}
mysql_free_result($result);
echo $output;
?>
</body>
</html>

Ollie_3rd




msg:4051200
 4:27 am on Dec 30, 2009 (gmt 0)

rockinbil:

You are the guru. It works great and things are where they should be.
I added the 'blank' to get the fourth state in the array to print. I don't need it now.

Thanks a million and I have created the table you suggested and will try working on it until I get totally stumped.

Thanks a million again and have Very Happy New Year !

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / WebmasterWorld / New To Web Development
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved