SQL join url parameter issues

[tayl0r]

Hello everyone.

I have 3 tables in my database.

table_User

user_id (Primary key)

username

password

table_account

account_id

first_name

last_name

email

account_id_fk (foreign key) linked to the user_id on table_user

table_listing

item_id (primary key)

item_type

item_number

item_colour

item_id_fk  (foreign key) linked to the user_id on table_user

All users have a different Username and password to login to access individual account.

There is a link after users login to their account that will take users to item_view.php page using url

parameter.

I created a recordset with dreamweaver to join table_listing and table_account tables on the item_view.php

page as i want all columns in both tables to be accessible on the item_view.php page for each individual

user account.

I am using below sql

SELECT *

FROM table_listing, table_account

WHERE table_listing.item_id_fk  = table_account.account_id_fk

ORDER BY trxn_id ASC

below created in the variable column

name: colname

default value: -1

run-time value: $_Get['item_id_fk']

Issues:

when click test or ok, this error comes up

colname is an invalid variable name; it does not appear in the SQL

when i deleted the variable column, i was able test the recordset at it works well.

and I am able to retrieve all columns from the database as it is visible in the binding panel.

But i am not able to make different login users to view their individual specific account details.

What could be the variable name?

Do i have to add another variables in the field?

How do i go about this please

[BenPleysier]

Are you using the SB panels in Dreamweaver CS6 or older?

If that is the case, then I would suggest to stop right there. This is because the server behaviours are based on a version of PHP that is no longer supported. You need to use PHP 7 to go forward. Have a look at PHP 7 Connect to MySQL or Google the subject.

[tayl0r]

I am using older version

[BenPleysier]

You have a few options, namely

1. Hand code PDO as per https://www.taniarascia.com/create-a-simple-database-app-connecting-to-mysql-with-php/

2. Hand code MySQLi CRUD with PHP and MySQLi using OOP Syntax

3. Purchase a MySQLi extension from MySQLi Server Behaviors | Dreamweaver extension | WebAssist

4. Purchase extensions for PDO from https://www.dmxzone.com/go/23295/dmxzone-server-connect

5. Subscribe to a program that has the lot, https://wappler.io/index

My advice:

1. if you plan to handcode, go for the PDO option because it is more versatile

2. if you plan to purchase an extension, go for option 3 because it most resembles the deprecated DW server behaviours.

I personally went for option 5.

[osgood_]

ORDER BY trxn_id...........what table is that in as l dont see it listed in either of the 2 tables  used in your sql query.

It seems as though you are using sql and not sqli or pdo to connect and query your database tables which is not advisable.

[tayl0r]

ORDER BY trxn_id ASC, an error, it has been removed

[osgood_]

When the user/s logs in are you then passing a unique user_id identifier in the url parameter like:

<a href="item_view.php?user_id = 3">LInk to your profile</a>

Then in the item_view.php page 'getting' that id as a means to identify the specific user?

$user_id = $_GET['user_id'];

If so try:

WHERE table_listing.item_id_fk  = '$user_id' AND table_account.account_id_fk = '$user_id'

That's not secure as anyone can pass a parameter via the url string so I would not divulge any sensitive information

It would be better to asign the unique user id to a php session variable and pass that to the item_view.php page.

EDITED (see marked in red above)