Databases

[Hooch]

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

[justsomeguy]

<?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.

[Hooch]

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.

[Hooch]

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 April 29, 2006 by Hooch

[justsomeguy]

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.

[Hooch]

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

[justsomeguy]

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();?>

[Hooch]

Thank you again. So as new names are entered..how will they fill up?**nvm..I got it. looking good!!

[Hooch]

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

[justsomeguy]

What do you the middle part of the table? It would be really easy to use CSS and position any image as a background image behind any part of the table.

[Hooch]

Linky Have a boo

[justsomeguy]

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();?>

[Hooch]

Man, you are a very generous person. Thank you so much for helping me. It works great. Have a great weekend (what's left) Hooch