Page 1 of 1

how to properly query database?

Posted: Sat Jul 19, 2008 11:11 pm
by pgunther
I want to create user profile pages. Using Php and mysql.
I am able to get the information into the database using the site register forms. I cannot correctly code the user profile page, to display their uploaded pictures and personal info. The profile page displays all members of the database.
Is the problem solved through sessions?
or the mysql select query? such as $member = member_id?

Thank you in advance.

Re: how to properly query database?

Posted: Mon Jul 21, 2008 6:44 pm
by dyluck
Please post your existing code for the profile page and we will help comb it out!

Re: how to properly query database?

Posted: Mon Jul 21, 2008 9:04 pm
by pgunther
My friend and I are very close to finishing our webpage, and we have hit several snags. For example, when we log into our login page takes us to another page with the following code:

CODE 1:

<?php

$dbcnx = @mysql_connect('localhost', 'root');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}

if (!@mysql_select_db('website')) {
exit('<p>Unable to locate the joke ' .
'database at this time.</p>');
}

?>
<h1>Welcome to Members' area!</h1>
<a href="member-profile.php?firstname=<?php echo urlencode($_GET['firstname']);?>">My Profile</a> | <a href="logout.php">Logout</a>
<p>This is a password protected area only accessible to members. </p>
</body>
</html>

After we click the "My Profile" link we are sent to another page with the following code:

CODE 2:
<?php

$dbcnx = @mysql_connect('localhost', 'root');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}

if (!@mysql_select_db('website')) {
exit('<p>Unable to locate the joke ' .
'database at this time.</p>');
}
// Request the ID and firstname
$result = @mysql_query("SELECT member_id, firstname FROM members");
if (!$result) {
exit('<p>Error performing query: ' .
mysql_error() . '</p>');
}
// Display username
while ($row = mysql_fetch_array($result)) {
$member_id = $row['member_id'];
$firstname = $row['firstname'];
echo '<p>' . $firstname . '</p>';

}
?>

The problem we are having is that between these two pages, the "My Profile" page (CODE 2) is showing all the names in the database as opposed to just the name of the user who is logged in to the website. Any suggesstions?

Re: how to properly query database?

Posted: Tue Jul 22, 2008 6:54 pm
by califdon
Your SQL string, "SELECT member_id, firstname FROM members" is asking the database to return the member_id's and firstnames of all the records in the table named members. Is that what you want to do?

Then your while loop goes through all the rows returned by the query and echoes the firstnames of all the members.

It looks like your code is doing exactly what you told it to do. If you only want one name to be displayed, you need a very different kind of script, one that displays a form to ask the member to login, then looks up just that name and echoes just that name.

You probably need to write down on a piece of paper what steps you need to go through. You will certainly need a lot of very different code than what you have here.

Re: how to properly query database?

Posted: Tue Jul 22, 2008 8:40 pm
by pgunther
thank you for going through that.
i probably should have posted this in the newbie section.
we have a login script that connects to the profile page.
a script where the variable is passed between pages seems appropriate.
obviously i am going to google it, but if anyone can direct me that would be great.

Re: how to properly query database?

Posted: Tue Jul 22, 2008 9:38 pm
by califdon
pgunther wrote:thank you for going through that.
i probably should have posted this in the newbie section.
we have a login script that connects to the profile page.
a script where the variable is passed between pages seems appropriate.
obviously i am going to google it, but if anyone can direct me that would be great.
You're fine, posting here. We don't have a separate newbie forum. The main thing you need to do is to write down a clear step-by-step description of what you want to do. Experienced programmers can sometimes get away with skipping this step, because they've done essentially the same thing many times before. As a beginner, you won't get anywhere without doing this.

You'll need to have a form (which is no doubt in your login script), then you'll need to pass the value(s) from that form to the next script (or it can be in the same script, with conditional blocks to determine which action is needed), probably as $_POST variables, then you'll need to recover those values in the second script, lookup your data in the database to validate the login, then do whatever the next step is, perhaps storing the login data in a session variable so that later pages can use it. This must all be very clear in your mind before you can even begin writing scripts. In case you are just copying scripts that you've found on the web (which I suspect is what you might be doing), you'll become horribly frustrated when they don't work, because you won't understand what they were intended to do. In your case, it's quite obvious to me that the database portion of your script bears no relation to looking up a user's credentials. I'm not trying to criticize you, I'm just explaining the most appropriate way for you to learn about PHP and databases. Having taught database classes in college for years, I have seen how various learning methods work and don't work.

Once you have written things down on paper, you should be able to identify pieces of the overall task that you need to learn about. That's when you can go to Google and search for terms like php tutorial and php form submit and php mysql tutorial etc. etc. You will learn much more quickly if you will do these things.