Jump to content

Archived

This topic is now archived and is closed to further replies.

simcoweb

Need a little MySQL query help

Recommended Posts

I've looked for this in several spots but couldn't find precisely what I need. I have a login form with username and password as the two fields. Upon submission they are taken to a 'members' page where I want to display their first and last name and their photo in one section like a 'Welcome [B]Bill Jones[/B]'

Then below that I want to show the other various fields of their profile.

What i'm having trouble with is the query where it would pull precisely that user's info and display it based upon his/her username posted in the login form. I know I need a 'WHERE' clause in the query. I've been working with this:

[code]$sql = ("SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= '$_POST['username']' ");
$result = mysql_query($sql, $conn) or die(mysql_error());[/code]

But get nothing. Need help on how to implement this. Thanks!

Share this post


Link to post
Share on other sites
Where is the rest of the code? You run the query, but I don't see where your testing and hadling the result. Also don't use a SUPER GLOBAL like your doing in your query, first test it or use mysql_real_escape_string() so you don't end up with someone playing with your database!

me!

Share this post


Link to post
Share on other sites
You dont need to have the single quotes arround the table name for one thing and also the brackets arround the $sql, as its just a string declaration.  Also the $_POST['username'] as its a variable needs to be outside the string.

[code]

$sql = ("SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= '$_POST['username']' ");
$result = mysql_query($sql, $conn) or die(mysql_error());

[/code]

should be

[code]

$sql = "SELECT firstname, lastname, photo FROM plateau_pros WHERE username= '".$_POST['username']."'";
$result = mysql_query($sql, $conn) or die(mysql_error());

[/code]

Share this post


Link to post
Share on other sites
Sorry, I should've posted the display code. Basically i'm trying to display it in the body of the HTML code embedded within the page. Like this:

[code]<tr>
                                                        <td>
                                                            <p>
                                                                <font size="2">Welcome<b> <span id="span1"><?= @$result['firstname'] ?></span> <span id="span2"><?= @$result['lastname'] ?></span></b></font>
                                                            </p>
                                                            <p>
                                                                <?php echo "<img src='images/photo/$photo' width='150' height='175'>"; ?>
                                                            </p>
                                                            <p>
                                                                <strong><font size="2">Member Actions</font> </strong>
                                                            </p>
                                                            <font size="2">
                                                            <ul>
                                                                <li>
                                                                    Edit Profile
                                                                </li>
                                                                <li>
                                                                    View Referrals
                                                                </li>
                                                                <li>
                                                                    View Calendar
                                                                </li>
                                                            </ul>
                                                            <p>
                                                            </p>
                                                            <p>
                                                            </p>
                                                            <p>
                                                            </p>
                                                            </font></td>
                                                    </tr>[/code]

Keep in mind, please, that the code i'm using is about the 5th different method. I've tried echo $firstname, etc. as well.

Share this post


Link to post
Share on other sites
I'll suggest a couple of things:

#1 Change <?= @$result to <?php echo $result in all cases.

#2 While you're testing things, always echo the actual query string you're using. Then you can be certain of what it really contains instead of expecting it to contain what you hope. So, add a line ..

[code]$sql = "SELECT .....";
echo "query = ". $sql. "<br/>"; // show the actual query being used
$result = mysql_query( ....);[/code]

Share this post


Link to post
Share on other sites
Hi AndyB. Actually I was doing the echo of the query as you'd suggested that in previous posts. I just didn't happen to show it.

What I want to essentially know is if my WHERE statement is the proper way to write it since i'm trying to get the user's id by matching his username in the database and displaying just his profile data.

Share this post


Link to post
Share on other sites
Then can we see the complete chunk of code that includes the current query you are using, how you acquire the result resource from the database and how you abstract specific data from that resource.

Share this post


Link to post
Share on other sites
When I echo the query with your code suggestion I get this:

[code]query = SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= ''[/code]

Here's the entire chunk of code:

[code]<?php
// Enable sessions
session_start();

// Turn on magic quotes to prevent SQL injection attacks
if(!get_magic_quotes_gpc())
set_magic_quotes_runtime(1);

include 'dbconfig.php';
// Connect to database
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

//new sql query
$sql = "SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= '".$_POST['username']."'";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo "query = ". $sql. "<br/>";
// Get Record Set
$result = mysql_fetch_array($sql, MYSQL_ASSOC);

?>[/code]

Share this post


Link to post
Share on other sites
Looks like the '$_POST['username'] value for some reason isnt coming through.  You could also try echoing the $_POST['username'] out before you create your sql string. 

That would show you that its not a problem with the username value not making it through to your PHP page. 

A quick question, what method does your login form use i.e POST or GET.  As your using $_POST to retrieve the username field, you also have to make sure that the login form also uses POST as the method.  Havent seen the code for your Login page so appologies if you are using POST.

Share this post


Link to post
Share on other sites
Thanks for your post. The login form uses the POST method. It's pretty straightforward with a query against the database to make sure it exists then lets them in. Plus there's a bit of validation as well.

Ok, if I run this query:

[code]//new sql query
$sql = "SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= '".$_POST['username']."'";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo "query = ". $sql. "<br/>";


while ($row = mysql_fetch_array($result)) {
print($row['firstname'].",".$row['lastname'].",".$row['photo']."\n");
}[/code]

I get this message:

[code]query = SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= ''[/code]

But, if I take out the WHERE part of the query I get this:

[quote]query = SELECT firstname, lastname, photo FROM plateau_pros
bozo,clown,507142.jpg rock,metal,guy.gif crotch,rot,507142.jpg cowpatty,pattyman,girl_looking_up.gif cowpatty,pattyman,42-15602495.jpg bee,bop,11542956.jpg Ralph,Cramdon,90173-24.jpg scary,movie, scary,movie,AX016857.jpg scary,larry,AX045589.jpg kevin,bacon,AX061181.jpg logjam,cracker,AAKF001141.jpg moose,loose,129770991.jpg spider,man,CB0099781.jpg yosemite,sam,6620.jpg terrell,owens,AX028319.jpg[/quote]

Which is basically the first name, last name and photo name. So the data is there and the query works but it's not working with the WHERE clause which is what I need. The query should work off either the username or their memberid so when someone clicks on a summary page with minimal info it opens to their full profile. Right now it's not displaying anything related to the POST username.

Share this post


Link to post
Share on other sites
The fact that when you echo out the query string tells me that for some reason its a problem with the $_POST['username'] variable as your query string echo is blank between the single quotes after username= (high lighted in red). It should contain the username that was sent via the login form.

query = SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= [color=red]''[/color]

Main reason why I thought it might be a good idea to have an echo to check there is actually something being passed in the $_POST['username'] variable.  Something like (shown in red):-

// Enable sessions
session_start();

// Turn on magic quotes to prevent SQL injection attacks
if(!get_magic_quotes_gpc())
set_magic_quotes_runtime(1);

include 'dbconfig.php';
// Connect to database
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

//new sql query
[color=red]echo "Post username = ".$_POST['username'];[/color]
$sql = "SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= '".$_POST['username']."'";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo "query = ". $sql. "<br/>";
// Get Record Set
$result = mysql_fetch_array($sql, MYSQL_ASSOC);

If the echo for the username is blank you know its a problem somewhere along the line from the $_POST['username'] being posted to when your php page requests it. 

I see your using sessions.  I have no experience with using them but am wondering if that might have something to do with getting a blank value. 

Maybe someone with more knowledge on sessions could jump in here and say absolutely nothing to do with it or a possibility and why.  :)

Share this post


Link to post
Share on other sites
This may help. It's the <form> code that is requesting the username/password.

[code]<form action"<? print $PHP_SELF ?>" id="Form1" style="WIDTH: 100%" name="Form1" method="post" >
<table id="Table1" cellspacing="0" cols="2" cellpadding="0" align="center" border="0">
<tbody>
<tr>
<td>
User name:&nbsp;&nbsp;</td>
<td>
<input id="Text1" name="username" value="<?= @$_POST['username'] ?>"></td>
</tr>
<tr>
<td>
Password:</td>
<td>
<input id="Password1" style="WIDTH: 155px; HEIGHT: 22px" type="password" size="21" name="password"></td>
</tr>
<tr>
<td>
</td>
<td align="right">
<br>
<input id="Submit1" type="submit" value="Login"></td>
</tr>
</tbody>
</table>
</form>[/code]

The code for the login form parsing is:

[code]<?

// Declare loginError so a value is always available
$loginError = "";

// Turn on magic quotes to prevent SQL injection attacks
if(!get_magic_quotes_gpc())
set_magic_quotes_runtime(1);

include 'dbconfig.php';
// Connect to database
$eg_objConn1 = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname, $eg_objConn1) or die(mysql());

// Validate users input
if(!empty($_POST))
{
// Check username has a value
if(empty($_POST['username'])) $eg_error['username'] = "Please enter a user name!";
// Check password has a value
if(empty($_POST['password'])) $eg_error['password'] = "Please enter a password!";
// Check if any errors were returned and run relevant code
if(empty($eg_error))
{


// Get Record Set
$sql = ("SELECT * FROM `plateau_pros`  WHERE username = '$username' AND password = '$password'");
mysql_query($sql) or die(mysql_error());
$results = mysql_query($sql) or die(mysql_error());
$num_rows = mysql_num_rows($results) or die(mysql_error());

//$eg_Result1 = @mysql_fetch_array($eg_recResult1, MYSQL_ASSOC) or die ('Error in query: $eg_Result1. ' . mysql_error());

if ($num_rows == 1) {
      // Enable sessions
if (isset($_SESSION['loggedin']))
{
  header("Location: members.php");
  exit;
} else {
  $_SESSION['loggedin'] = $_POST['username'];
  // Go to page
header("Location: members.php");
break;
}
}
else
{
$loginError = "Your user name and password do not match any in our database!";
}
}
}
?>[/code]

Each time i've tested i've logged in first so it would pass the 'username' variable. However, that's not happening.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.