Jump to content

Databases


Hooch
 Share

Recommended Posts

Hello all. I hope this is in the correct spot to ask a database question. I do want to use php to extract the info. The database is almost complete to my specs. I was hoping to display all my info from the database. Most of that I have done. It looks exactlythe way I want it. But I cannot find how to get a cpl things done. Here's what I'm hoping to do. 1. retrieve a website and email and make them clickable. 2. In my db below I have 2 entries = 2 people. Is it possible to call upeach person's lastname and have them displayed in a table alphabetically? Also I would need to display members in each tour they are involved.

-- -- Table structure for table `users`-- CREATE TABLE `users` (  `id` int(11) NOT NULL auto_increment,  `username` varchar(32) NOT NULL default '',  `password` varchar(32) NOT NULL default '',  `firstname` varchar(32) NOT NULL default '',  `lastname` varchar(32) NOT NULL default '',  `email` varchar(32) NOT NULL default '',  `medium` varchar(32) NOT NULL default '',  `studio` varchar(32) NOT NULL default '',  `tours` varchar(32) NOT NULL default '',  `website` varchar(32) default NULL,  `address` varchar(32) NOT NULL default '',  `city` varchar(32) NOT NULL default '',  `province` varchar(32) NOT NULL default '',  `zip` varchar(7) NOT NULL default '',  `phone` varchar(13) default NULL,  `biography` text NOT NULL,  PRIMARY KEY  (`id`),  UNIQUE KEY `username` (`username`),  UNIQUE KEY `email` (`email`)) TYPE=InnoDB AUTO_INCREMENT=3;-- -- Dumping data for table `users`-- INSERT INTO `users` VALUES (1, 'USERNAME here', 'MD5PASSWORD', '1st name', 'lastname', 'email here', 'Painter', 'Studio name, 'tours involved', 'website', 'address, phone', 'BIO info goes here');INSERT INTO `users` VALUES (2, 'USERNAME here', 'MD5PASSWORD, '1st name', 'lastname', 'email here', 'Painter', 'Studio name, 'tours involved', 'website', 'address, phone', 'BIO info goes here');

Again thank you in advance..Hooch

Link to comment
Share on other sites

<?php$result = mysql_query("SELECT * FROM users ORDER BY lastname ASC");while ($row = mysql_fetch_assoc($result)){  ...  echo "<a href=\"{$row['website']}\">{$row['website']}</a>";  echo "<a href=\"mailto:{$row['email']}\">{$row['email']}</a>";  ...}?>

That SQL statement has the order by lastname clause in it. I'm not sure what you mean by the tours, what does the tour field look like when it has data in it? If people could be involved in more than one tour, then you probably want to use another table to handle that. Something like this:table tours id nametable user_tours user_id tour_idThen you can build queries to get all users on a certain tour, or find out how many tours a user is on, or whatever.

Link to comment
Share on other sites

I still need to work on how I do the tours part. I think I'll just do a radio button style entry. There are 3 different tours. So each tour would have a yes or no entry. Then I would need 3 fields for tours. Thank you guy!! I'll giver er a shot when I get home tonight.

Link to comment
Share on other sites

I had to go this way. Thanks again ladd!!

	<a href="<? echo "$r[website]"; ?>">WEBSITE</a><br>	<a href="mailto:<? echo "$r[email]"; ?>">EMAIL</a><br /><br />

Edited by Hooch
Link to comment
Share on other sites

Cool. You should change that to one of these two though:

	<a href="<? echo $r['website']; ?>">WEBSITE</a><br>	<a href="mailto:<? echo $r['email']; ?>">EMAIL</a><br /><br />	<a href="<? echo "{$r['website']}"; ?>">WEBSITE</a><br>	<a href="mailto:<? echo "{$r['email']}"; ?>">EMAIL</a><br /><br />

You get a small performance hit if you leave out the quotes on the array index, because the engine first looks for a constant by that name (i.e. website or email), and it goes through a couple things before trying a string. So putting the quotes in enforces that it just uses the string. You don't need the double quotes surrounding it at all if you are only outputting one variable, but if you do put it in quotes it's best to surround it with the {} and then use single quotes for the array index.

Link to comment
Share on other sites

Holy cow...I can't figure another one out. 1st take a look at my original page I made without any database. This is the website (my 1st official) I am about to complete. TIArts.org I have everything done from html. This is good for now..to get them up and running. But I want to get them running a database that each member you see there can upload/upgrade their info themselves...then the pages will automatically change accordingly. Well, as good as can possibly be to my knowledge. (thank you w3schools!!) All was going pretty good till this page. Here's my code that needs a tweak.

<?php######################################################################## Copyright (c) 2006 TIArts.org# Coded by # http://.com#######################################################################include '../includes/header-members.php';// This will display by order of lastname then the rest just fills ininclude '../includes/db.php';$result = mysql_query("SELECT * FROM users ORDER BY lastname ASC");while ($r = mysql_fetch_assoc($result)){?><link href="../includes/tia.css" rel="stylesheet" type="text/css" /><table width="700" height="10"align="center"border="0" cellspacing="0" cellpadding="0">  <tr>    <td><div align="center"></div></td>  </tr></table><TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" width="700" height="300" align="center">	<TR VALIGN="top"  ALIGN="left">   <td width="125" height="225" rowspan="2" align="left" valign="top"><?php include '../includes/links-members.php'; ?></td>  <td width="153" align="center" valign="middle">           <div align="left">     <a href="profile.php?u=<? echo $r[id]; ?>"title=<? echo $r[medium]; ?>  onmouseover="main.src='../images/mem/<? echo $r[id]; ?>a.jpg'"onmouseout="main.src='../images/mem/bg.jpg'" class="footer2"><? echo $r[firstname]; ?><? echo " "; echo $r[lastname]; ?></a>        </div>  </td>  <td width="228" align="center" valign="middle">       <div align="left"><img src="../images/mem/bg.jpg" name="main" id="main" style="height:200px;width:150px;" /></div></td>  <td width="194" align="center" valign="middle">       <div align="left">	    <a href="profile.php?u=<? echo $r[id]; ?>"title=<? echo $r[medium]; ?>  onmouseover="main.src='../images/mem/<? echo $r[id]; ?>a.jpg'"onmouseout="main.src='../images/mem/bg.jpg'" class="footer2"><? echo $r[firstname]; ?><? echo " "; echo $r[lastname]; ?></a>        </div>  </td></TR></TABLE><?phpinclude '../includes/footer.php';	}	mysql_close();	?>

The problem with that code is thw whole page is duplicated for each member.Here's the RESULT I tried changing the whole page to php (alot of echo's) and it ended up with the same result. ...please help. Thank you very much..Hooch

Link to comment
Share on other sites

Yeah, you have everything in the while loop. Move all the html out of the loop. It looks like you are trying to set up a 2-column layout, so you just need a variable to say if you are or are not in the left column. You have the main table set up with 3 columns though, so since you are using tables here I recommend that you put all the nav in one cell, and the user list in another cell, with a nested table that lists all the users.

<?php######################################################################## Copyright (c) 2006 TIArts.org# Coded by # http://.com#######################################################################include '../includes/header-members.php';// This will display by order of lastname then the rest just fills ininclude '../includes/db.php';?><link href="../includes/tia.css" rel="stylesheet" type="text/css" /><table width="700" height="10" align="center"border="0" cellspacing="0" cellpadding="0"> <tr>   <td><div align="center"></div></td> </tr></table><TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" width="700" height="300" align="center">  <TR VALIGN="top"  ALIGN="left">    <td width="125" height="225" rowspan="2" align="left" valign="top"><?php include '../includes/links-members.php'; ?></td>    <td width="575" align="center" valign="middle">      <table border="0" width="100%" cellspacing="0" cellpadding="0"><?php$left_column = true;$result = mysql_query("SELECT * FROM users ORDER BY lastname ASC");while ($r = mysql_fetch_assoc($result)){  if ($left_column)  {    echo "<tr>";  }?>  <td width="50%">  <div align="left">    <a href="profile.php?u=<? echo $r[id]; ?>"title=<? echo $r[medium]; ?>  onmouseover="main.src='../images/mem/<? echo $r[id]; ?>a.jpg'"onmouseout="main.src='../images/mem/bg.jpg'" class="footer2"><? echo $r[firstname]; ?><? echo " "; echo $r[lastname]; ?></a>  </div>  </td><?php  if (!$left_column)    echo "</tr>";  $left_column = !$left_column;}if (!$left_column)  echo "</tr>";?>      </table>    </td>  </tr></table><?phpinclude '../includes/footer.php';mysql_close();?>

Link to comment
Share on other sites

I finally got to spend a little more time here. This script you made is missing 1 part. My spot for the pics.

<td width="228" align="center" valign="middle">       <div align="left"><img src="http://www.tiarts.org/images/mem/bg.jpg" name="main" id="main" style="height:200px;width:150px;" /></div></td>

It's the middle part of the table Or am I missing something? << probably the problem. Thanks..Hooch

Link to comment
Share on other sites

Maybe try something like this:

<?php######################################################################## Copyright (c) 2006 TIArts.org# Coded by # http://.com#######################################################################include '../includes/header-members.php';// This will display by order of lastname then the rest just fills ininclude '../includes/db.php';$left_column = true;$col1 = "";$col2 = "";$result = mysql_query("SELECT * FROM users ORDER BY lastname ASC");while ($r = mysql_fetch_assoc($result)){  if ($left_column)    $col1 .= "<a href=\"http://www.tiarts.org/members/{$r['id']}.php\" title=\"{$r['title']}\"  onmouseover=\"main.src='http://www.tiarts.org/images/mem/{$r['image']}'\" onmouseout=\"main.src='http://www.tiarts.org/images/mem/bg.jpg'\" class=\"footer2\">{$r['name']}</a><br />\n";  else    $col2 .= "<a href=\"http://www.tiarts.org/members/{$r['id']}.php\" title=\"fabric artist\"  onmouseover=\"main.src='http://www.tiarts.org/images/mem/{$r['image']}'\" onmouseout=\"main.src='http://www.tiarts.org/images/mem/bg.jpg'\" class=\"footer2\">{$r['name']}</a><br />\n";  $left_column = !$left_column;}?><link href="../includes/tia.css" rel="stylesheet" type="text/css" /><table width="700" height="10" align="center"border="0" cellspacing="0" cellpadding="0"><tr>  <td><div align="center"></div></td></tr></table><table border="0" cellspacing="0" cellpadding="0" width="700" height="300" align="center"> <tr valign="top"  align="left">   <td width="125" height="225" rowspan="2" align="left" valign="top"><?php include '../includes/links-members.php'; ?></td>   <td width="153" align="center" valign="middle">       <div align="left"><?php echo $col1; ?></div></td>  <td width="228" align="center" valign="middle">       <div align="left"><img src="http://www.tiarts.org/images/mem/bg.jpg" name="main" id="main" style="height:200px;width:150px;" /></div></td>  <td width="194" align="center" valign="middle">       <div align="left"><?php echo $col2; ?></div></td> </tr></table><?phpinclude '../includes/footer.php';mysql_close();?>

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...