PHP &amp; MySQL Question

PHP & MySQL Question

TitusBass

I admire the knowledge of this forum. I hope to one day be as experienced in PHP as the majority of you all.

Until then, I am still fumbling through learning PHP, but am making advancements every time. Until now.

I was able to create a mySQL database with phony business information, and get it to create a new table for each db entry as below. (It took me a few days, but I got it to work.)🙂

<?php
// Make a MySQL Connection
		mysql_connect("localhost", "root", "root") or die(mysql_error());
		mysql_select_db("members") or die(mysql_error());

// Get all the data from the "example" table
		$result = mysql_query("SELECT * FROM member_directory ORDER BY business_cat1 DESC") 
		or die(mysql_error());  

	echo "<table width='640' border='0' cellspacing='2' cellpadding='2' align='center'>";
// keeps getting the next row until there are no more to get
		while($row = mysql_fetch_array( $result )) {

// BUSINESS CATEGORY
  		echo "<tr><td colspan='5' class=\"listing\" align='left'>";
		echo $row['business_cat1'];
		echo "</td></tr>";
// SPACER	
		echo "<tr height='10'></tr>";
// IMAGE PULL
  		echo "<tr class=\"ListingSpec\"><td colspan='2' align='left'>"; 
		echo ("<img width=\"240\" height=\"180\" src=\"http://localhost:8888/images/business.png\">");
		echo "</td></tr>";
// SPACER	
		echo "<tr height='10'></tr>";
// START BUSINESS CRITERIA 

// BUSINESS NAME
		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>Business Name:</b> ";
		echo $row['business_name'];
		echo "</td></tr>";
// BUSINESS ADDRESS    

		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>Business Address:</b> "; 
		echo $row['business_address']; 											
		echo "</td></tr>";
// BUSINESS CITY       

		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>City:</b> "; 
		echo $row['business_city']; 												
		echo "</td></tr>";
// BUSINESS STATE       

		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>State:</b> "; 
		echo $row['business_state'];
		echo "</td></tr>";
// BUSINESS ZIP	       

		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>Zip:</b> "; 
		echo $row['business_zip'];
		echo "</td></tr>";
// BUSINESS PHONE		       

		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>Phone:</b> "; 
		echo $row['business_phone'];
		echo "</td></tr>";
// BUSINESS PHONE EXTENSION			       

		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>Extension:</b> "; 
		echo $row['business_phone_ext'];
		echo "</td></tr>";
// BUSINESS FAX				       

		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>Fax:</b> "; 
		echo $row['business_fax'];
		echo "</td></tr>";
// BUSINESS CONTACT		
		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>Contact:</b> "; 
		echo $row['business_contact'];
		echo "</td></tr>";		
// BUSINESS HOURS				
		echo "<tr class=\"ListingSpec\"><td colspan='2'><b>Business Hours:</b> "; 
		echo $row['business_hours'];
		echo "</td></tr>";
// BUSINESS EMAIL
		echo "<tr class=\"ListingSpec\"><td colspan='2'>"; 
		echo ("<a class=\"nav2\" target=\"_blank\" href=\"mailto:{$row['business_email']}?subject=Information Request&body=I'd like more information.\">Email Me</a>");
		echo "</td></tr>";	
// BUSINESS WEBSITE				
		echo "<tr class=\"ListingSpec\"><td colspan='2'>"; 
		echo ("<a class=\"nav2\" target=\"_blank\" href=\"{$row['business_website']}\">View Website</a>");
		echo "</td></tr>";			
// SPACER		
		echo "<tr height='10'></tr>";
// BUSINESS DESCRIPTION		
		echo "<tr><td colspan='5' class='ListingSpec'><b>Description:</b> ";
		echo $row['business_description'];
		echo "</td></tr>";
// SPACER	
		echo "<tr height='10'></tr>";

// TOP OF PAGE
		echo "<tr><td colspan='5' align='left'>";
		echo $row['top'];
		echo "</td></tr>";
// SPACER
		echo "<tr height='10'></tr>";
//  DIVIDER
		echo "<tr><td colspan='5' align='center'><hr>";
		echo "</td></tr>";
// SPACER
	echo "<tr height='10'></tr>";
} 
// END OF TABLE
	echo "</table>";
?>

Where I get stuck on a few other items is:

I don't want to have any DB entries with empty values populating. Like if some business doesn't have an email, I don't want it to show Email: <BLANK>

Can I specify this in this area...

		
$result = mysql_query("SELECT * FROM member_directory ORDER BY business_cat1 DESC") 
		or die(mysql_error());

And if so... how in the heck would I do that?

The next hurdle was creating a drop down menu that is populated by the Business category, called business_cat1.

I was able to figure out how to pull info from the DB, (much to my surprise) but I only got the fields. I know I used fields, but I didn't know what to use for business_cat1

<?php
$connection = mysql_connect("localhost","root","root");
$fields = mysql_list_fields("members", "member_directory", $connection);
$columns = mysql_num_fields($fields);
echo "<form action=page_to_post_to.php method=POST><select name=Field>";
for ($i = 0; $i < $columns; $i++) {
echo "<option value=$i>";
echo mysql_field_name($fields, $i);
}
echo "</select></form>";
?>

And... for the finale... I wanted to be able to select... let's say Florists from the drop down, and have that selection move down the page to the Florists section. Like an Anchor tag. (I don't know if this is even possible).

If any one has any tips or suggestions, I'd truly appreciate it. I'm going back to see if I can make any headway with this.

Thanks.

dalecosp

One idea is to use something like [man]mysql_fetch_assoc/man to grab DB data as an associative array.

You can then use foreach() to loop through the array. You can put the echo statement in conditional clauses:

$array("foo" => "Fred", "bar" => "Barney", "baz" => "Wilma", "garply" => '');

foreach ($array as $key => $value) {
   if ($key !='') {
   echo "$key: $value <br />";
   }
}

That's highly simplified, but perhaps it will help.

TitusBass

Thanks dalecosp for your reply. I don't want to sound like too much of an idiot, :eek: (I do have my moments though) but is your suggestion for the drop down, or for getting rid of the empty values? Thanks again.

dalecosp

Well, without giving away too much, does:

if ($key !='') {

give the answer away?

(Hint: '' is an empty string....)

TitusBass

One hurdle is cleared. Finally got it figured out as how to exclude if the field in the database is empty.

[B]if ($row['business_artwork'] != ""){[/B]
echo "<tr><td colspan='2' align='left'> <img src=";
echo $row['business_artwork'];
echo ">";
echo "</td></tr>";
[B]}[/B]

And it works great.

Now, the last hurdle is to find a way to create a drop down list that is populated by the data base business_cat1 column, and not the field... as shown below.

<?php
$connection = mysql_connect("localhost","root","root");
$fields = mysql_list_fields("members", "member_directory", $connection);
$columns = mysql_num_fields($fields);
echo "<form action=page_to_post_to.php method=POST><select name=Field>";
for ($i = 0; $i < $columns; $i++) {
echo "<option value=$i>";
echo mysql_field_name($fields, $i);
}
echo "</select></form>";
?>

And I want to be able to have the user select a category from the drop down and return the results of that category on the page. If any one has any suggestions on that one, I'd appreciate it. It's a learning experience, for sure. Thanks.

TitusBass

Now able to get the drop down to populate the database categories. Thanks to my lovely and talented wife... 🙂

For anyone trying to do the same, here is the code:

  <?php
		// open the members database and the member_directory table
		$link = mysql_connect("localhost", "username", "password") or die(mysql_error());
		mysql_select_db("members") or die(mysql_error());


	// Get all the data from the "member_directory" table - order by category.
	$result = mysql_query("SELECT * FROM member_directory ORDER BY business_cat1 ASC") 
	or die(mysql_error());

	// now, for each new category,add it to the dropdown menu
	echo "<form action=page_to_post_to.php method=POST><select name=Field>";

	// initialize
	$i = 0;
	$last_cat = "   ";

	// make the first entry "All"
	echo "<option value=$i>";
	echo "All";
	$i++;

	// Go through the records and find the unique categories.
	while($row = mysql_fetch_array( $result )) {
		if ($row['business_cat1'] != $last_cat) {
			echo "<option value=$i>";
			echo $row['business_cat1'];
			$last_cat = $row['business_cat1'];
			$i++;
		}
	}

	// Close the form.
	echo "</select></form>";

	// All done. Clean up.
	mysql_close($link);
?>

It works great. Now on to the kicker of populating the page via the drop down choice. Keep ya posted on the outcome.