PHP/MySQL Record Insert question

Forum|Forum|20 years ago
April 26, 2006
7 replies
939 views

I am working with which PHP 4.4.2, MySQL Server 5.0 running on Apache 2.0.55, and I am developing my web-site with Dreamweaver 8. All of which is running locally on my machine.

I am working on a real-estate website that allows landlords around my local area to login to the web-site and add their apartment or housing rental units into a database that students that go to my small university can browse and search through. The landlords register through a form that loads into a 'landlords' table in the database and MySQL auto-increments an ID into a 'landlord_id' column which obviously assigns them their 'landlord_ID'.

My question is: When the landlord is filling out the form to add one of their rental units ( which loads into a separate table I called 'sites'), how can I make their 'landlord_id' (which resides in the 'landlords' table) be entered into the 'landlord_id' column in the 'sites' table for each site that they enter into the database?

I'm trying to do this because when the landlord first logs in, I'm going to have a dynamic table that displays all of the sites they already have entered in the database, and only displays their sites that they have already entered in by only displaying the sites that have their 'landlord_id' in the site record.

Thanks so much for your help, and if I'm being confusing at all, please reply or email me so I can clarify.

Server side applications

This topic has been closed for replies.

Correct answer Newsgroup_User

Cade06 wrote:
> You were saying that the parameter name value needed to be the username column
> (which is EMAIL), the default value as 1 (which gives me an error every time so
> I assumed that maybe you meant 'col1' meaning the username column which would
> again be EMAIL, so correct me if I was wrong to assume this),

Yes, you were wrong to assume it. The reason you got an error with a
default value of 1 is because the username will be a string (presumably
the email address). Since you're so completely at sea with this, it's
probably better to use the Simple Recordset dialog box.

You need to create the recordset in whichever page the user is sent to
after logging in successfully. In the Simple Recordset dialog box,
choose a connection, then choose the table that contains the details of
your landlords. In the Columns area, choose the Selected radio button,
and highlight the landlord_id column. In the Filter section, choose
EMAIL in the first dropdown menu, = in the menu alongside, then Session
Variable from the third dropdown menu. Dreamweaver will probably display
EMAIL in the text field alongside Session Variable. Replace that with
MM_Username. Click the Test button to make sure it works. You will be
prompted for a test value. Enter a valid EMAIL, and make sure that you
get only one result.

When you have created the recordset, go into Code view, and find the
following line (recordsetName will be whatever you called the recordset):

$totalRows_recordsetName = mysql_num_rows($recordsetName);

Insert a line directly below, and put this (again using the actual
recordset name):

$_SESSION['landlord_id'] = $row_recordsetName['landlord_id'];

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

N

Newsgroup_User

Inspiring

Cade06 wrote:
> Thank you so much for all of your help. I was able to finally figure out why
> the Landlord_ID wasn't getting inserted into the database, I had downloaded a
> free extension from this website that uploads pictures into a database

Storing images in a database isn't a very practical idea. You should
store the image's filename in the database, but keep the image in an
ordinary web folder.

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

A

Anonymous

Thank you so much for all of your help. I was able to finally figure out why the Landlord_ID wasn't getting inserted into the database, I had downloaded a free extension from this website that uploads pictures into a database and for some reason when that extension was plugged into the page where I was trying to insert the Landlord_ID, the session variable didn't carry through to that page (as far as I can tell) and I have no idea why. As soon as I unloaded the upload extension from the page, I was able to insert the Landlord_ID just fine. The problem now is figuring out how I can upload pictures to the database without screwing up the session variable. Maybe if I spend too long on, it I'll start a new thread for that :-)

Thanks again for all your help, I'm absolutely positive I never would have been able to get as far as I have without your assistance.

N

Newsgroup_User

Inspiring

Cade06 wrote:
> SO do I just need to create the hidden field
> myself and name it 'MM_Username'?

No. If you look at the code created by Log In User and Restrict Access
to Page, you will see it contains $_SESSION['MM_Username']. A session
variable, once set, is available in any page that begins with
session_start(). There is no need to put it in a hidden field; you can
use it as is.

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

A

Anonymous

>create a Parameter with the following values:
>
>Name: col1
>Default value: 1
>Runtime value: $_SESSION['MM_Username']
>
>You can then extract the result of the recordset, and assign it to a
>session variable like this:
>
>$_SESSION['landlord_id'] = $row_recordsetName['landlord_id'];

OK, I'm really sorry to keep dragging this out but I'm just not able to get this working right.

You were saying that the parameter name value needed to be the username column (which is EMAIL), the default value as 1 (which gives me an error every time so I assumed that maybe you meant 'col1' meaning the username column which would again be EMAIL, so correct me if I was wrong to assume this), and the runtime value as $_SESSION['MM_Username']. I did all that and hit the test button and the test returned the landlord ID column, so everything looks to be working there (unless the default value really is supposed to be 1...). Then you say I can extract the result of the recordset and assign it to the session variable by entering in the line you gave above, but I still don't know where to put that line. I thought it would have gone right where the other 2 session variables were declared, but when I put that line there, I got an error that read "Notice: Undefined variable: row_recordsetName in C:\Program Files\...\Login.php on line 45" (the line where I declared the Landlord_ID session variable) followed by another error about "cannot modify header information...", so I know that's not where it goes, but I have no idea where else to put it.

Sorry, I feel like a complete idiot not being able to figure out what you're telling me, I just have no idea how this process works so I'm just clueless how to construct everything and where to put the code. Thanks alot for all your help.

A

Anonymous

OK, I was thinking when you said that the log-in User behavior automatically creates the $_SESSION['MM_Username'] session variable that it would generate that field somewhere near the bottom of the form, just like the insert record behavior automatically generates the MM_Insert hiden field, but when I created the log-in behavior, I couldn't find any hidden fields or anything that were called MM_Username or anything like that, SO do I just need to create the hidden field myself and name it 'MM_Username'?

N

Newsgroup_User

Inspiring

Cade06 wrote:
> Thanks alot for your reply, but I am completely new to MySQL, and I am not
> quite sure how the syntax works. When you say " WHERE landlord_username =
> 'whatever' "I'm not sure what needs to go in the 'whatever' portion of the
> code. Do I need to insert static text here or something else? I am totally out
> of my element here, sorry :-/

It's probably not so much MySQL syntax that's the problem for you, but a
lack of understanding how to work with Dreamweaver recordsets, server
behaviors, and sessions. Building database-driven sites with Dreamweaver
isn't difficult, but there are a lot of things involved.

The Log In User server behavior automatically creates a session variable
called $_SESSION['MM_Username']. You can use this as a parameter to pass
to the SQL query in the Advanced Recordset dialog box. In the SQL area
you would type (I'm guessing the name of your table and the username
column):

SELECT landlord_id FROM landlords
WHERE landlord_username = 'col1'

Then click the plus button above the Variables area, and create a
Parameter with the following values:

Name: col1
Default value: 1
Runtime value: $_SESSION['MM_Username']

You can then extract the result of the recordset, and assign it to a
session variable like this:

$_SESSION['landlord_id'] = $row_recordsetName['landlord_id'];

> Also, I understand that I need to do this on the login page, but do I need to
> define the recordset again on the add_site page as well? I guess I'm still not
> quite clear on how I specify the session variable as the value that needs to be
> entered in the sites table as the landlord_ID.

As long as the session is still valid (which it would be if you use
Restrict Access to Page), you will always have access to that landlord's
id through $_SESSION['landlord_id'].

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

A

Anonymous

Thanks alot for your reply, but I am completely new to MySQL, and I am not quite sure how the syntax works. When you say " WHERE landlord_username = 'whatever' "I'm not sure what needs to go in the 'whatever' portion of the code. Do I need to insert static text here or something else? I am totally out of my element here, sorry :-/

Also, I understand that I need to do this on the login page, but do I need to define the recordset again on the add_site page as well? I guess I'm still not quite clear on how I specify the session variable as the value that needs to be entered in the sites table as the landlord_ID.

N

Newsgroup_User

Inspiring

Cade06 wrote:
> My question is: When the landlord is filling out the form to add one of their
> rental units ( which loads into a separate table I called 'sites'), how can I
> make their 'landlord_id' (which resides in the 'landlords' table) be entered
> into the 'landlord_id' column in the 'sites' table for each site that they
> enter into the database?

The simple way is to create a session variable when the landlord logs
in. Create a recordset that retrieves the landlord's id:

SELECT landlord_id FROM landlords
WHERE landlord_username = 'whatever'

Assign the result of the query to $_SESSION['landlord_id']. As long as
the session remains live, you can access that value in any page that
begins with session_start(); (which will be the case if you're using
Restrict Access to Page).

--
David Powers
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "Foundation PHP 5 for Flash" (friends of ED)
http://foundationphp.com/

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded