SELECTING Data from SQL using PHP

[ben.matthews18]

Ok here is the situatation,

I have three tables set up;

'Users'
'Profile'
'Linkingtbl'

The 'Users' table holds the following data;

• Email (PK)
• Password

The 'Profile' table holds the following data;

• ProfileID (PK)
• Firstname
• Lastname
• DOB

The 'Linkingtbl' table holds the following data;

• ID (PK)
• Email (FK)
• Password
• ProfileID (FK)

I have a login system setup and want to be able to show the users 'ProfileID'.

The problem that I have is that I also have a select system setup and it is just selecting the first 'ProfileID' in the table and not the 'ProfileID' that is related to the user.

How can I do this?
Do I need to use a 'WHERE' statement?

Thanks

[Usernamex235]

OK let's say you have a user email address and you want to find the relative ProfileID

$email = [e-mail]; // Email address of user

$profileIDQuery = mysql_query("SELECT * FROM Linkingtbl WHERE Email = '" . $email . "'"); fetches row of data where the in "email" data field corresponds to $email

$resultArray = mysql_fetch_assoc($profileIDQuery);// Fetches the result of the above query (i.e. a bunch of row fields) as an array, associated with column name

echo $resultArray; // output the "ProfileID" field from the row stored as aray

Sorted :)

Do let me know if I made any syntax errors, and post back with how you get on.

[Stefano Mtangoo]

I think this is best place to learn that. It have try yourself and some question.
It is not very long though Check PHP Manual which have examples and also little explanations here

[ben.matthews18]

OK let's say you have a user email address and you want to find the relative ProfileID

$email = [e-mail]; // Email address of user

$profileIDQuery = mysql_query("SELECT * FROM Linkingtbl WHERE Email = '" . $email . "'"); fetches row of data where the in "email" data field corresponds to $email

$resultArray = mysql_fetch_assoc($profileIDQuery);// Fetches the result of the above query (i.e. a bunch of row fields) as an array, associated with column name

echo $resultArray; // output the "ProfileID" field from the row stored as aray

Sorted :)

Do let me know if I made any syntax errors, and post back with how you get on.

Thanks for your reply :)

Do I have to use those select statements in the 'Checklogin' script too?

Because I used it in the page where I wanted to 'echo' the profile ID and I got an internal server error :s

[Usernamex235]

You only need to use a select to grab data from a database, so if you're storing the passwords in the database your login script would be something like:

$submittedUserName = $_POST['username']; // form input with "username" as value for the username input

$submittedPassword = $_POST['password'];

// connect to db...

$SQL = "SELECT * FROM users WHERE username='" . $submittedUserName . '";
{
    
$userData = mysql_fetch_assoc(mysql_query($SQL));

//

[ben.matthews18]

You only need to use a select to grab data from a database, so if you're storing the passwords in the database your login script would be something like:

$submittedUserName = $_POST; // form input with "username" as value for the username input

$submittedPassword = $_POST;

// connect to db...

$SQL = "SELECT * FROM users WHERE username='" . $submittedUserName . '";
{

$userData = mysql_fetch_assoc(mysql_query($SQL));

//

Ah thanks..

This is what I currently have as my select statement;

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);


$rows=mysql_fetch_array($result);

[diafol]

I'm a little confused (my age, you know). Are you looking to join tables (JOIN) or simply filter (WHERE)?

[ben.matthews18]

I'm a little confused (my age, you know). Are you looking to join tables (JOIN) or simply filter (WHERE)?

Hi,

I have the tables joined in a sql database.

I also have a login system working which is linked to the values 'Email' and 'Password' in the 'Linkingtbl' table.

I want to be able to display the user information which in this case I am using the 'ProfileID'.

Like Facebook where you login and you have your own profile unique to each user.

[Usernamex235]

eugh I pressed tab then enter and my cursor tabbed out of the form onto the "post reply button".... anyway...

Ok put a WHERE clause in there so it will only give you the row for the right user.

Hint: mysql_fetch array maps the fields into the array by using integer keys, but mysql_fetch_assoc does the same thing but associates the fields with their names, so you can assign $array to a variable as opposed to having to work out which number goes in $array;

Assuming your other mysql queries are working and this isn't a database problem, and that the user has already logged in and is thus proven to exist...

$sql = "SELECT * FROM linkingtbl WHERE ID = '" . $currentUserID . "' "; // notice the apostrophes wrapping the quotation mark enclosed variable

$usersRowInTable = mysql_fetch_assoc(mysql_query($sql));
$profileId = $usersRowInTable['<profile ID fieldname'];

echo "Your profile ID is " . $profileID ."";

Does that help?

[ben.matthews18]

eugh I pressed tab then enter and my cursor tabbed out of the form onto the "post reply button".... anyway...

Ok put a WHERE clause in there so it will only give you the row for the right user.

Hint: mysql_fetch array maps the fields into the array by using integer keys, but mysql_fetch_assoc does the same thing but associates the fields with their names, so you can assign $array to a variable as opposed to having to work out which number goes in $array;

Assuming your other mysql queries are working and this isn't a database problem, and that the user has already logged in and is thus proven to exist...

$sql = "SELECT * FROM linkingtbl WHERE ID = '" . $currentUserID . "' "; // notice the apostrophes wrapping the quotation mark enclosed variable

$usersRowInTable = mysql_fetch_assoc(mysql_query($sql));
$profileId = $usersRowInTable['<profile ID fieldname'];

echo "Your profile ID is " . $profileID ."";

Does that help?

Thank you,

It helps a lot but i'm not getting the ProfileID returned in the echo.

[Usernamex235]

What error are you getting?

[ben.matthews18]

What error are you getting?

I'm not getting an error but the ProfileID is not being displayed.

This is the code that I have;

$sql = "SELECT * FROM $Linkingtbl WHERE Email = '" . $Email . "' ";

$usersRowInTable  = mysql_fetch_assoc(mysql_query($sql));
$ProfileID = $usersRowInTable ['$ProfileID'];

?>

I also have this SESSION code at the top of the page;

<? 
session_start();
if(!session_is_registered(Email)){
header("location:login.php");
}
?>

I'm also using this echo statement;

<?php echo "$ProfileID"; ?>

Your SELECT code was difficult to interpret, could you please refer to the tables and fields that I added into the original post?

I'm really sorry that this is going on for so long and that I have not been able to get it working.

[rajarajan2017]

$sql = "SELECT * FROM $Linkingtbl WHERE Email = $Email";

You can just use the code like this

[Usernamex235]

Right I've spotted it...

$sql = "SELECT * FROM $Linkingtbl WHERE Email = '" . $Email . "' ";

$usersRowInTable  = mysql_fetch_assoc(mysql_query($sql));
$ProfileID = $usersRowInTable ['$ProfileID']; // The value "profileID" refers to the column name and is not a variable

On the last line you have the code "$usersRowInTable ".

This is actually an array, a variable that can store multiple values. once created, you reference the items in the array using a number, or in a associative array using column names.

For example an array could contain the values (in order) apple, pear, orange.

To echo "apple" we can echo $array // starts from 0

To echo "pear" we can echo $array, and so on.

In an associative array, if we had a table where the columns were "favourite_food_1", "favourite_food_2", "favourite_food_3" we could get "apple" by echo $array.

You need to change that line to

$profileID = $usersRowInTable; //no space between $usersRowInTale and opening bracket or dollar sign in front of field name

It should work now :)

[ben.matthews18]

Right I've spotted it...

$sql = "SELECT * FROM $Linkingtbl WHERE Email = '" . $Email . "' ";

$usersRowInTable  = mysql_fetch_assoc(mysql_query($sql));
$ProfileID = $usersRowInTable ['$ProfileID']; // The value "profileID" refers to the column name and is not a variable

On the last line you have the code "$usersRowInTable ".

This is actually an array, a variable that can store multiple values. once created, you reference the items in the array using a number, or in a associative array using column names.

For example an array could contain the values (in order) apple, pear, orange.

To echo "apple" we can echo $array // starts from 0

To echo "pear" we can echo $array, and so on.

In an associative array, if we had a table where the columns were "favourite_food_1", "favourite_food_2", "favourite_food_3" we could get "apple" by echo $array.

You need to change that line to

$profileID = $usersRowInTable; //no space between $usersRowInTale and opening bracket or dollar sign in front of field name

It should work now :)

Unfortunately this does not work, should I be echoing like this;

<? echo ['ProfileID']; ?>

Or

<? echo $array['ProfileID']; ?>

?

[Usernamex235]

the second :) i.e.

echo $array['ProfileID'];

If you can post more of your code (altering any sensitive data ofc!) I might be able to help you later tonight.

[ben.matthews18]

the second :) i.e.

echo $array['ProfileID'];

If you can post more of your code (altering any sensitive data ofc!) I might be able to help you later tonight.

Thanks, this is the code I am using;

This SESSION statement at the top of the page;

<? 
session_start();
if(!session_is_registered(Email)){
header("location:login.php");
}
?>

And this PHP code to retrieve the data;

<?php

$host=""; 
$username=""; 
$password=""; 
$db_name=""; 
$tbl_name="Linkingtbl"; 


mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");



$sql = "SELECT * FROM $Linkingtbl WHERE Email = '" . $Email . "' ";


$usersRowInTable  = mysql_fetch_assoc(mysql_query($sql));
$ProfileID = $usersRowInTable['ProfileID'];

?>


Welcome to the homepage <? echo $array['ProfileID']; ?>.

<?php 
mysql_close();
?>

[Usernamex235]

Hey,

I have ammended your code below, for simplicity I have removed your ?> tags and just echoed any text. let me know what you think...

<?php

$host=""; 
$username=""; 
$password=""; 
$db_name=""; // I'm assuming these were proper values ;p
$tbl_name="Linkingtbl"; 


mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
// good to see useful error msgs!


$sql = "SELECT * FROM $Linkingtbl WHERE Email = '" . $Email . "' ";
/* OK here you are selecting * (anything/all) from the table name contained in the variable $Linkingtbl, where the value in the email field = the variable $Email. I notice you haven't defined either of these variables (eg $email = [e-mail]) but you have defined the variable $tbl_name as "Linkingtbl".*/

/* Again I'm assuming you already know the user's email address - if not then this is a whole other kettle of fish and we may well need a JOIN parameter, but I digress... Right, so we can get this up and running let's just hard-code the email address.*/

$Email = "[e-mail]"; // this must be a valid email address - i.e. one stored in your database (use one of the existing addresses to test).

$sql = "SELECT * FROM " . $tbl_name . " WHERE Email = '" . $Email . "' ";// for clarity I have used a full stop to join strings. EG $string = "abc" . "def" . "ghi"; is the same as $string = "abcdefghi"; It just akes it easier to spot variables in strings ;)

$usersRowInTable  = mysql_fetch_assoc(mysql_query($sql));
$ProfileID = $usersRowInTable['ProfileID'];


// Your code assumes that you have an array called $array, but when creating that array (using mysql_fetch_assoc above) you called it $usersRowInTable.

echo "Welcome to the homepage " . $array['ProfileID'] . ".";

// So change this to:
echo "Welcome to the homepage " . $usersRowInTable['ProfileID'] . ".";

// Or, because we defined the $profileID variable as the same as "$usersRowInTable['ProfileID']" you could just put:
echo "Welcome to the homepage " . $ProfileID . ".";


mysql_close();
?>

You are really close, it just looks like you've confused some of your variables. Remember you should declare a variable before use! I know it seems like there's a lot to learn but once you've grasped the key concepts you'll be doing stuff like this in 5 mins :)

Let us know how you get on, like I said, you are nearly there :)

[ben.matthews18]

Hey,

I have ammended your code below, for simplicity I have removed your ?> tags and just echoed any text. let me know what you think...

<?php

$host=""; 
$username=""; 
$password=""; 
$db_name=""; // I'm assuming these were proper values ;p
$tbl_name="Linkingtbl"; 


mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
// good to see useful error msgs!


$sql = "SELECT * FROM $Linkingtbl WHERE Email = '" . $Email . "' ";
/* OK here you are selecting * (anything/all) from the table name contained in the variable $Linkingtbl, where the value in the email field = the variable $Email. I notice you haven't defined either of these variables (eg $email = [e-mail]) but you have defined the variable $tbl_name as "Linkingtbl".*/

/* Again I'm assuming you already know the user's email address - if not then this is a whole other kettle of fish and we may well need a JOIN parameter, but I digress... Right, so we can get this up and running let's just hard-code the email address.*/

$Email = "[e-mail]"; // this must be a valid email address - i.e. one stored in your database (use one of the existing addresses to test).

$sql = "SELECT * FROM " . $tbl_name . " WHERE Email = '" . $Email . "' ";// for clarity I have used a full stop to join strings. EG $string = "abc" . "def" . "ghi"; is the same as $string = "abcdefghi"; It just akes it easier to spot variables in strings ;)

$usersRowInTable  = mysql_fetch_assoc(mysql_query($sql));
$ProfileID = $usersRowInTable['ProfileID'];


// Your code assumes that you have an array called $array, but when creating that array (using mysql_fetch_assoc above) you called it $usersRowInTable.

echo "Welcome to the homepage " . $array['ProfileID'] . ".";

// So change this to:
echo "Welcome to the homepage " . $usersRowInTable['ProfileID'] . ".";

// Or, because we defined the $profileID variable as the same as "$usersRowInTable['ProfileID']" you could just put:
echo "Welcome to the homepage " . $ProfileID . ".";


mysql_close();
?>

You are really close, it just looks like you've confused some of your variables. Remember you should declare a variable before use! I know it seems like there's a lot to learn but once you've grasped the key concepts you'll be doing stuff like this in 5 mins :)

Let us know how you get on, like I said, you are nearly there :)

Thank you ever so much, it works now and the profile ID of the user is shown but I would like it to work where I don't know what user is going to logiin which is where you said the 'JOIN' parameter comes in.

thanks for your help

[Usernamex235]

Hey,

Glad to hear you've got it working :)

Ok so now we need to look at using JOIN sql to further your project... but that is a separate issue:

Please mark this thread as "solved" (remembering to select one of my posts as the solution ;p) and start a new thread that uses the words "mysql join" in the title.

I'll be glad to help you again, it's just best to have a new thread for each topic/problem :)

Cheers

[madison12]

There are two query statements which may be used, they are mysql_db_query and mysql_query. The statement mysql_db_query is depreciated and it is recommend that the mysql_query statement is used. The syntax for both statements is shown below.

resource mysql_db_query ( string database, string query [, resource link_identifier])

resource mysql_query ( string query [, resource link_identifier])

web development services