manage profiles using php & mysql

[jake66]

what i want to do:
i currently have a mysql database with the following tables:
member1
member2
member3
member4

inside EACH table, they all have the following fields:
name
position
webpage
url
contact

etc, etc.

what i cannot figure out how to do:
a simple output (post them on a page for our visitors) and ability to edit behind-the-scenes of records! (i'm new to this)

could somebody point me in the direction of a detailed (but not overbearing) tutorial how to achieve this?

[FiSH42]

Hi Jake66,

WebMonkey have quite a good tutorial on php/mysql, that should assisst.

[hotwired.lycos.com...]

PhpFreaks, also do some good introductory tutorials.

[phpfreaks.com...]

Hope this helps

[coopster]

You may also want to reconsider your database/table structure. Perhaps a single

members

table is all that is needed and you could add a unique identifier to the columns (as opposed to multiple member tables).

CREATE TABLE members ( 
  memberID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
  name CHAR(25), 
  position CHAR(25), 
  webpage CHAR(25), 
  url CHAR(25), 
  contact CHAR(25), 
  PRIMARY KEY (memberID) 
);

I made up column definitions here but hopefully you understand what I'm getting at.

[jatar_k]

coopster is right on that, it would be much easier that way, no need for a table for each member.

The basics of how to update a file are fairly straight forward. You need to make a form that mirrors your members table. Include only the fields you want your members to be able to edit. I assume they would have to be logged in in some way. You would then use their memberID to issue the update commands to your database.

[jake66]

i'm trying the example: [hotwired.lycos.com...]

but no matter what i do, i get this error:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/**/public_html/**/staff.php on line 15

line 15:
while ($myrow = mysql_fetch_row($result)) {

the code i'm using:
<?php

$db = mysql_connect("localhost", "user");
mysql_select_db("database",$db);

$result = mysql_query("SELECT * FROM staff",$db);

echo "<table border=1>\n";

echo "<tr><td>Name</td><td>Position</tr>\n";

while ($myrow = mysql_fetch_row($result)) {

printf("<tr><td>%s %s</td><td>%s</td></tr>\n",
$myrow, $myrow[2], $myrow[3]);
}

echo "</table>\n";

?>

[1][edited by: jatar_k at 5:06 pm (utc) on Feb. 15, 2006]
[edit reason] fixed link [/edit]

[jatar_k]

that usually means that there is an error from mysql, you need to trap the error coming back from mysql to know what is going wrong

change this
$result = mysql_query("SELECT * FROM staff",$db);

to
$result = mysql_query("SELECT * FROM staff",$db) or die('<p>the query returned an error: ' . mysql_error());

that should show you the error coming back from mysql

[jake66]

i got this:
the query returned an error: No Database Selected

but i have the database name mysql_select_db("**HERE**",$db); ... but i did take notice there is no spot for a password, could this be the problem?

[jatar_k]

no, you don't need a password for the mysql_select_db

though I just noticed something

in your mysql_connect you don't have the password, I am guessing that your connect statement is the one that isn't working.

[php.net...]

take a look at that page, your password should be the third parameter. You could also add the or die tou your connect like so

$db = mysql_connect("localhost", "user") or die ('<p>couldnt connect' . mysql_error());

[jake66]

i don't really understand what's posted on [php.net...] (this is my first time trying to connect to a database) ..and what puzzles me even more is i got my entire code from the tutorial posted in this topic, why wouldn't the tutorial mention the password or die method?

when i try to add the password to the end of "localhost", "user", --"password"-- - i get:

Warning: mysql_connect(): Access denied for user: 'staffdbuser@localhost' (Using password: YES) in /home/**/public_html/**/test.php on line 3

couldnt connectAccess denied for user: 'staffdbuser@localhost' (Using password: YES)

on line 3:
$db = mysql_connect("localhost", "username", "password") or die ('<p>couldnt connect' . mysql_error());

[jatar_k]

this isn't the first time someone has ended up here because that tutorial sucks and I doubt it will be the last

be that as it may, we are getting somewhere

mysql_connect("localhost", "username", "password")

there must be something wrong with either your username, password or your host. I would check your username and password to be sure they are exactly right. As for the hostname, some hosts use a remote database server which means you would need something like theirdb44.myhost.com in there instead of localhost. If it is actually on the same machine then localhost is correct.

most host have FAQs or something that give you connection strings for mysql, maybe see if your host has the same.

[jake66]

thank you, that worked!

now i can connect to the database, but how can i output it all on one page?

for instance:
member1
<table>
<tr>
<td>Name (output)</td>
<td>Photo (output)</td></tr>
<td>top1 (output)</td>
<td>top2 (output)</td>
</tr></table>
<br>

member2
<table>
<tr>
<td>Name (output)</td>
<td>Photo (output)</td></tr>
<td>top1 (output)</td>
<td>top2 (output)</td>
</tr></table>
<br>

etc.. for each member?
where i have (output), i don't need a title, like..
Name: Sally

i just want it to say:
Sally

at present, my output looks like:

<table><tr>
<td>Sally Floor Sweeper
John Mailman</td>
<td>data
data</td>
</tr></table>

and all members are inside the same table... i want them to have their own table

i have tried:
<?php echo $staff['position'];?>

but it doesn't work. (i just copied it from an existing script i use on my site, i don't really know how to output this properly)

is there a good tutorial that can tell me how to do the basics? the tutorials posted previously are what led me on the wild goose chase because i wasn't using a password

[coopster]

Our own PHP Forum Library [webmasterworld.com] is loaded with some goodies, including a thread on the Basics of extracting data from MySQL [webmasterworld.com].

There is also Learning PHP - Books, Tutorials and Online Resources [webmasterworld.com]. member ergophobe mentions Zend in there as a good tutorial resource and I would agree.

[jake66]

thanks for that!

now i've encountered another problem. how can i do this:

i want to allow each user to post their pic.

- we ftp the image to mysite.com/images

- in the admin field (where they can edit their info), they simply type the filename (without extension) and it will be posted.

i have tried:
echo "<td>", "<img src=images/titles/", $row['photo'], "gif";

but i keep getting parse errors.

any suggestions?