DaniWeb Forum Index > Web Development > PHP

Pull info from MySQL database

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Dec 2008

Posts: 6

Reputation: redgem is an unknown quantity at this point

Solved Threads: 0

redgem

Offline

Newbie Poster

Pull info from MySQL database

Jan 22nd, 2009

I have a database that I want query and pull an users specific details into a "$profile" variable.

the url would be:

"www.website.com/profile.php?id=12345"

Thus, I use the $_REQUEST["id"] to put the id number into a variable and then I query the database using this id number.

The code is below:

Help with Code Tags

(Toggle Plain Text)

<?php
/*OPEN THE DATABASE*/ 
$con = mysql_connect("localhost","user","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
/*PULL INFO FROM URL FOR GENERATION OF PUBLIC PROFILE*/
$idnum=$_REQUEST["id"];
/*PULL INFO FROM THE DATABASE*/
$pullprofile="SELECT * FROM 'accounts' WHERE (idnum = '$idnum')";
if(mysql_query($pullprofile,$con))
{
$profile[]=mysql_fetch_array($pullprofile,$con);
echo "Success loading profile!";
echo "Your id number is ".$profile['idnum'];
}
else
{
echo "Profile does not exist...";
}

/*CLOSE THE DATABASE*/ 

mysql_close($con); 

?>

For some reason the code keeps on returning the "Profile does not exist..." results, even though I know the profile does exist...

I'm new to PHP/SQL, so what am I doing wrong...?

Red Gem Forum
How to Build a Successful Website - eBook

•

Join Date: Apr 2008

Posts: 297

Reputation: Aamit has a little shameless behaviour in the past

Solved Threads: 11

Aamit

Offline

Posting Whiz in Training

Re: Pull info from MySQL database

Jan 22nd, 2009

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$idnum=$_GET['id'];
$idnum=$_GET['id'];

Use this..

•

Join Date: Oct 2006

Posts: 1,102

Reputation: ardav will become famous soon enough

Solved Threads: 139

ardav

Offline

Veteran Poster

Re: Pull info from MySQL database

Jan 22nd, 2009

•

Originally Posted by redgem

I have a database that I want query and pull an users specific details into a "$profile" variable.

the url would be:

"www.website.com/profile.php?id=12345"

Thus, I use the $_REQUEST["id"] to put the id number into a variable and then I query the database using this id number.

The code is below:
Help with Code Tags

(Toggle Plain Text)
<?php
/*OPEN THE DATABASE*/ 
$con = mysql_connect("localhost","user","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
/*PULL INFO FROM URL FOR GENERATION OF PUBLIC PROFILE*/
$idnum=$_REQUEST["id"];
/*PULL INFO FROM THE DATABASE*/
$pullprofile="SELECT * FROM 'accounts' WHERE (idnum = '$idnum')";
if(mysql_query($pullprofile,$con))
{
$profile[]=mysql_fetch_array($pullprofile,$con);
echo "Success loading profile!";
echo "Your id number is ".$profile['idnum'];
}
else
{
echo "Profile does not exist...";
}

/*CLOSE THE DATABASE*/ 

mysql_close($con); 

?>
<?php /*OPEN THE DATABASE*/ $con = mysql_connect("localhost","user","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } /*PULL INFO FROM URL FOR GENERATION OF PUBLIC PROFILE*/ $idnum=$_REQUEST["id"]; /*PULL INFO FROM THE DATABASE*/ $pullprofile="SELECT * FROM 'accounts' WHERE (idnum = '$idnum')"; if(mysql_query($pullprofile,$con)) { $profile[]=mysql_fetch_array($pullprofile,$con); echo "Success loading profile!"; echo "Your id number is ".$profile['idnum']; } else { echo "Profile does not exist..."; } /*CLOSE THE DATABASE*/ mysql_close($con); ?>
For some reason the code keeps on returning the "Profile does not exist..." results, even though I know the profile does exist...

I'm new to PHP/SQL, so what am I doing wrong...?

[OPTIONAL]
1. Using a raw id like this may be a bit dangerous. You should md5 it with a 'salt' or something, to protect your data access.
2. Try to avoid $_REQUEST whenever possible. Use $_GET for url parameters and $_POST for form data (general rule of thumb).
3. You need to clean and validate the incoming data (see posts elsewhere).
[/OPTIONAL]

4. Pass the cleaned variable to the query:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

$pullprofile="SELECT * FROM accounts WHERE idnum = '{$idnum}'";

Notice the change in syntax.

5. Run query and check for results:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$result = mysql_query($pullprofile,$con));
if(mysql_num_rows($result)>0){
  $profile = mysql_fetch_array($result);
  echo "Success loading profile";
  echo "Your id number is " . $profile['idnum'];
}else{
  echo "Profile does not exist...";
}
$result = mysql_query($pullprofile,$con));
if(mysql_num_rows($result)>0){
  $profile = mysql_fetch_array($result);
  echo "Success loading profile";
  echo "Your id number is " . $profile['idnum'];
}else{
  echo "Profile does not exist...";
}

This should work. If this does not, check to see that the $_GET id really is in the DB.

•

Join Date: Jan 2009

Posts: 34

Reputation: jrdark13 is an unknown quantity at this point

Solved Threads: 3

jrdark13

Offline

Light Poster

Re: Pull info from MySQL database

Jan 22nd, 2009

I'm not entirely sure but wouldn't if(mysql_query($pullprofile,$con)) return true only if the query was accepted not if there was a row result?. That means that your query is generating an error probably due to the fact that you are requesting literally $idnum, use {$idnum} to make sure it goes through not as static but a variable. But that will still only tell you that the code went through, I think.

I would rather use something like this:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$pullprofile = "SELECT * FROM accounts WHERE idnum = '".$idnum."'";//I like escaping
$query = mysql_query($pullprofile,$con);
 
if(mysql_num_rows($query) > 0)
{
	$profile[]=mysql_fetch_array($query);
	echo "Success loading profile!";
	echo "Your id number is ".$profile['idnum'];
}
else
{
	echo "Profile does not exist...";
}
$pullprofile = "SELECT * FROM accounts WHERE idnum = '".$idnum."'";//I like escaping
$query = mysql_query($pullprofile,$con);

if(mysql_num_rows($query) > 0)
{
	$profile[]=mysql_fetch_array($query);
	echo "Success loading profile!";
	echo "Your id number is ".$profile['idnum'];
}
else
{
	echo "Profile does not exist...";
}