Jump to content


Photo

Need a little MySQL query help


  • Please log in to reply
11 replies to this topic

#1 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 27 September 2006 - 11:14 PM

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 Bill Jones'

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:

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

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

#2 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 27 September 2006 - 11:21 PM

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!

#3 Fehnris

Fehnris
  • Members
  • PipPipPip
  • Advanced Member
  • 48 posts

Posted 27 September 2006 - 11:25 PM

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.


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


should be


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



#4 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 28 September 2006 - 01:32 AM

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:

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

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



#5 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 28 September 2006 - 02:02 AM

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

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

Legend has it that reading the manual never killed anyone.
My site

#6 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 28 September 2006 - 02:17 AM

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.

#7 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 28 September 2006 - 02:27 AM

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.
Legend has it that reading the manual never killed anyone.
My site

#8 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 28 September 2006 - 02:39 AM

When I echo the query with your code suggestion I get this:

query = SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= ''

Here's the entire chunk of 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);

?>


#9 Fehnris

Fehnris
  • Members
  • PipPipPip
  • Advanced Member
  • 48 posts

Posted 28 September 2006 - 06:01 AM

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.

#10 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 28 September 2006 - 06:28 AM

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:

//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");
}

I get this message:

query = SELECT firstname, lastname, photo FROM `plateau_pros` WHERE username= ''

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

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


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.



#11 Fehnris

Fehnris
  • Members
  • PipPipPip
  • Advanced Member
  • 48 posts

Posted 28 September 2006 - 07:08 AM

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= ''

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
echo "Post username = ".$_POST['username'];
$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.  :)

#12 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 28 September 2006 - 10:29 PM

This may help. It's the <form> code that is requesting the username/password.

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

The code for the login form parsing is:

<?

// 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!";
		}
	}
}
?>

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users