newbie question

manga_10 · 07-01-2013 11:19pm #1

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 · 07-01-2013 11:26pm

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 · 07-01-2013 11:30pm

. 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 · 07-01-2013 11:33pm

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 · 07-01-2013 11:37pm

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 · 07-01-2013 11:43pm

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 · 07-01-2013 11:45pm

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

Graham · 07-01-2013 11:47pm

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 · 07-01-2013 11:52pm

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 · 07-01-2013 11:54pm

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 · 07-01-2013 11:59pm

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 · 08-01-2013 12:11am

$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 · 08-01-2013 12:17am

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 · 08-01-2013 12:27am

Still inserting but not updating memID

Graham · 08-01-2013 12:29am

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 · 08-01-2013 12:32am

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 · 08-01-2013 12:35am

<?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 · 08-01-2013 12:40am

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 · 08-01-2013 12:40am

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

Graham · 08-01-2013 12:45am

replace

echo $sql;

with

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

manga_10 · 08-01-2013 12:47am

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 · 08-01-2013 12:50am

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 · 08-01-2013 12:54am

$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 · 08-01-2013 12:56am

Is user logging in with text username or number?

Graham · 08-01-2013 12:59am

From earlier post:

manga_10 wrote: »

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

manga_10 · 08-01-2013 1:04am

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 · 08-01-2013 1:05am

You cant populate an int column with text.

Graham · 08-01-2013 1:14am

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 · 08-01-2013 1:17am

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 · 08-01-2013 1:18am

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 · 08-01-2013 1:20am

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.

newbie question

Comments