newbie question

manga_10

Hey having a bit of bother here I have tables linked... the query works in the phpadmin thing but when I enter a task using the web form it enters it but instead of updating the memID to the users memberID to link the task it just displays 0!

Kidchameleon

manga_10 wrote: »

Hey having a bit of bother here I have tables linked... the query works in the phpadmin thing but when I enter a task using the web form it enters it but instead of updating the memID to the users memberID to link the task it just displays 0!

More detail?

Have you copy and pasted the query from phpmyadmin before it worked?

manga_10

. it's the fact that once it inserts the task into the database it isn't updating the memID to whoever entered it in. So say you login and your memberID = 1 you enter your tasks etc then the memID field in the tasks table should always update and put a 1 in that field.
Just so everytime you login with your email which is = to memberID 1 it calls your list of tasks from the DB!
But it's not doing that because I can't get the memID field to update from the web form when the add task button is hit

Kidchameleon

manga_10 wrote: »

. it's the fact that once it inserts the task into the database it isn't updating the memID to whoever entered it in. So say you login and your memberID = 1 you enter your tasks etc then the memID field in the tasks table should always update and put a 1 in that field.
Just so everytime you login with your email which is = to memberID 1 it calls your list of tasks from the DB!
But it's not doing that because I can't get the memID field to update from the web form when the add task button is hit

Could it be a problem with the $_POST[] function?

One common error I see is people leaving out the $ on the variable...

manga_10

thought a more detailed account would be better

When querying on phpmyadmin to display all tasks that are linked to a member ID that were created using the PHPmyadmin they all display with a task id and the member ID of who created them. When you login to your site to enter information it displays the information from the database on the page under you the user but when you enter new information it doesn't display on webpage but when you go to the table in PHPmyadmin it entered all information correctly but a 0 beside the member ID. This means when the user logs back into their account the information entered won't show because it isn't linked to there member ID in the site.

the the database consists of two tables and the tables have been joined so the memberID is linked to the memID but its the memID not updating.

manga_10

If I change the query to this....
$result = mysql_query("SELECT * FROM tasks WHERE memID = '$email'") or die(mysql_error());
The only task I have created under this username...
Still doesn't update the memID to say my memberID - 1

manga_10

All posts are working and have the $ do I don't think it's to do with that.

Graham

Is that the query to retrieve the results from your database?

I suspect the problem is with the part of your script that inserts the task records in the first place.

manga_10

Yes that is the query that we are using to bring the results from the database. What does he mean like the problem might be the code used to insert the information when add task is hit??

manga_10

do we need to do anything with the memID field?
Like I think that is meant to update itself once we insert but maybe we do need to do something with the memID when creating the task.
I just can't find anywhere that helps with why it is not inserting memberID into the memID since they are both linked instead it inputs a 0.

Graham

When the task is created I would assume there is an SQL insert statement that submits the task to your database.

If all the records have a memberID of 0, I would guess the correct value is not being passed to the insert statement.

manga_10

$sql = mysql_query ("INSERT INTO tasks (taskname, category, priority, scheddate, memID) VALUES ('$taskname', '$category', '$priority', '$scheddate')") or die (mysql_error());
That is the insert query.
I didn't add the memID as I thought that would auto update itself when a record is added under the logged in user?
It worked at adding tasks until the tables were linked to attach a task to a member.

Graham

It's still working at adding tasks, there's just nothing there to tell it to insert a member ID.

$sql = mysql_query ("INSERT INTO tasks (taskname, category, priority, scheddate, memID) VALUES ('$taskname', '$category', '$priority', '$scheddate', $memberID)") or die (mysql_error());

manga_10

Still inserting but not updating memID

Graham

Where are you setting the value of $memberID? Is this value coming from a cookie or a session variable after the user logs in?

Graham

Can you 'echo' the value for $sql to the browser after this line:

$sql = mysql_query ("INSERT INTO tasks (taskname, category, priority, scheddate, memID) VALUES ('$taskname', '$category', '$priority', '$scheddate', $memberID)") or die (mysql_error());

echo $sql;

manga_10

<?php
session_start();
//Check whether the session variable

if(!isset($_SESSION["email"]))
{
header("Location: login.html");
exit();
}
?>
Which is why in this: $result = mysql_query("SELECT * FROM tasks WHERE memID = '$email'") or die(mysql_error()); we said the memID='$email'
The user signs in using email which is in the member table and linked to memberID so we were trying to make it that when a user is signed in using their email they can create a task list linking to their memID so it will be there each time they login.

Graham

In the database (tasks table) what's the field type?

If it's a text type this should work:

$sql = mysql_query ("INSERT INTO tasks (taskname, category, priority, scheddate, memID) VALUES ('$taskname', '$category', '$priority', '$scheddate', $email)") or die (mysql_error());

If it's a numerical field

$sql = mysql_query ("INSERT INTO tasks (taskname, category, priority, scheddate, memID) VALUES ('$taskname', '$category', '$priority', '$scheddate', $memberID)") or die (mysql_error());

BUT you will need to set
$memberID
before the $sql= line.

manga_10

echo $sql;
echo $sql; prints this: 1

Graham

replace

echo $sql;

with

echo ("INSERT INTO tasks (taskname, category, priority, scheddate, memID) VALUES ('$taskname', '$category', '$priority', '$scheddate', $email)");

manga_10

Graham wrote: »

In the database (tasks table) what's the field type?

If it's a text type this should work:

$sql = mysql_query ("INSERT INTO tasks (taskname, category, priority, scheddate, memID) VALUES ('$taskname', '$category', '$priority', '$scheddate', $email)") or die (mysql_error());

If it's a numerical field

$sql = mysql_query ("INSERT INTO tasks (taskname, category, priority, scheddate, memID) VALUES ('$taskname', '$category', '$priority', '$scheddate', $memberID)") or die (mysql_error());

BUT you will need to set
$memberID
before the $sql= line.

The field type for both linked ID's is INT.
They don't work.
$memberID is set above.

manga_10

INSERT INTO tasks (taskname, category, priority, scheddate, memID) VALUES ('Break computer', 'Work', 'High', '2013-01-24', '')
So it is printing everything but the memID

Graham

$memberID needs to be a number then, I don't see anything in the code you've posted so far that sets the $memberID to a numerical value.

memID='$email' will fail as it's trying to insert an email address into a numerical (integer) field.

I would guess that memID should be set to the same value as memberID in your other (not tasks) table.

When the user logs in you need to retrieve their memberID from the members table, you could then set this as a session variable. memID can then be set to the value stored in the session variable.

mark renton

Is user logging in with text username or number?

Graham

From earlier post:

manga_10 wrote: »

The user signs in using email which is in the member table and linked to memberID

manga_10

Okay, so the memberID in members table and the memID in tasks table are both set to INT so I now understand what you mean by the memID='$email' in the query failing. Since I have the session set here:

<?php
session_start();
//Check whether the session variable
//SESS_MEMBER_ID is present or not
if(!isset($_SESSION["email"]))
{
header("Location: login.html");
exit();
}
?>

Do I set the session variable as the memID here? So when you say set to the value stored in session that means that each time the user logs into the site using their email address it will find their memberID number from the member table and store it so each time they add the task it will store that in the memID?

Sorry for all the questions, completely new to this and I'm finding this part hard. Thanks for all the help so far its greatly appreciated.
Reckon there would be a tutorial on that? Like retrieving the ID to set as session variable instead of email?

mark renton

You cant populate an int column with text.

Graham

manga_10 wrote: »

<?php
session_start();
//Check whether the session variable
//SESS_MEMBER_ID is present or not
if(!isset($_SESSION["email"]))
{
header("Location: login.html");
exit();
}
?>

Do I set the session variable as the memID here? So when you say set to the value stored in session that means that each time the user logs into the site using their email address it will find their memberID number from the member table and store it so each time they add the task it will store that in the memID?

When the user has logged in, you will need to
1) retrieve the users memberID from the database,
2) create a session variable for the memberID
3) set that session variable to the value you retrieved from the database.
4) use that value in your insert statement

Graham

Found an example here:

http://www.webinfopedia.com/php-secure-login-script.html

//Create query
$qry="select * from admin_log where username='".$login."' and password='".$password."'";
$result=mysql_query($qry);

//Check whether the query was successful or not
if($result) {
if(mysql_num_rows($result) == 1) {
//Login Successful
session_regenerate_id();
$member = mysql_fetch_assoc($result);

$_SESSION = $member;
$_SESSION = $member;
session_write_close();

header("location: home.php");
exit();
}else {
//Login failed
header("location: index.php?msg=error");
exit();
}
}else {
die("Query failed");
}

mark renton

On the code above, remove the "location" part and echo the session variable, also echo the session variable type. You will need to know exactly what is contained in each variable and their types before you proceed.

Graham

mark renton wrote: »

On the code above, remove the "location" part and echo the session variable, also echo the session variable type. You will need to know exactly what is contained in each variable and their types before you proceed.

I suspect there is nothing setting a session variable for the memberID in the first place. If there is, Manga_10 has kept very quiet about it so far.