prepared query join problems.

[Love2c0de]

Good evening,

 

I am trying to create a user profile system on my website. I have the ability to register and login fine. I've hard coded a dummy profile into the profiles table.

 

The way in which I am going to retrieve the profile data is by matching the users username in both tables.

 

At the moment my query is returning all of the members in the user table and all of the profile information in the other table - profiles.

 

This is my query:

 
$stmt = $conn->prepare("SELECT * FROM users LEFT JOIN profiles ON users.? = profiles.username") or die("Error: ".mysqli_error($conn));
 

Not sure if the way I am going about it is the correct way either so any advice and guidance is greatly received.

 

Kind regards,

 

L2c.

[Love2c0de]

Oh, this seems to have fixed it:

 

 
$stmt = $conn->prepare("SELECT * FROM users,profiles WHERE users.username AND profiles.username = ?") or die("Error: ".mysqli_error($conn));
 

 

Regards,

 

L2c.

[jcbones]

I know it is fixed, but just would like to note that you cannot bind parameters to column names nor table names in prepared statements.

 

  Quote

 

Note:

The markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value.

However, they are not allowed for identifiers (such as table or column names), in the select list that names the columns to be returned by a SELECT statement, or to specify both operands of a binary operator such as the = equal sign. The latter restriction is necessary because it would be impossible to determine the parameter type. It's not allowed to compare marker with NULL by ? IS NULL too. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

[kicken]

  On 5/6/2013 at 8:37 PM, Love2c0de said:

This is my query:

 
$stmt = $conn->prepare("SELECT * FROM users LEFT JOIN profiles ON users.? = profiles.username") or die("Error: ".mysqli_error($conn));
 

 

The proper way to code that query would be this:

SELECT * FROM users LEFT JOIN profiles ON users.username=profiles.username WHERE users.username=?

Then you bind the username you want to search for as the value for the ? parameter.