Column 'user_name' in field list is ambiguous

[aery]

hi i am getting a error message as

 

Column 'user_name' in field list is ambiguous

 

here

is the database

 

$query = 'CREATE TABLE IF NOT EXISTS user_info (
	user_id			INTEGER			NOT NULL,
	user_name		VARCHAR(100)	NOT NULL,
	first_name		VARCHAR(100)	NOT NULL,
	last_name		VARCHAR(200)	NOT NULL,
	email			VARCHAR(50)		NOT NULL,
	password		VARCHAR(50)		NOT NULL,
	location		VARCHAR(100)	NOT NULL,
	state			VARCHAR(50)		NOT NULL,
	zip				VARCHAR(10)		NOT NULL,

	PRIMARY KEY (user_id) 
)
ENGINE = InnoDB';
mysql_query($query, $db) or die (mysql_error($db));

 

and the php code to retrieve  user info from the table

 

<?php
$query = 'SELECT
	user_name, first_name, last_name,  email, location, state, zip
    FROM
        user_info u JOIN
        user_info i ON u.user_id = i.user_id
    WHERE
        user_name = "' . mysql_real_escape_string($_SESSION['user_name'], $db) . '"';
$result = mysql_query($query, $db) or die(mysql_error($db));

$row = mysql_fetch_array($result);
extract($row);
mysql_free_result($result);
mysql_close($db);
?>
  <ul>
  
   <li>First Name: <?php echo $first_name; ?></li>
   <li>Last Name: <?php echo $last_name; ?></li>
   <li>User Name: <?php echo $user_name; ?></li>
   <li>Email: <?php echo $email; ?></li>
   <li>Location: <?php echo $location; ?></li>
   <li>State: <?php echo $state; ?>
   <li>Zip Code: <?php echo $zip; ?></li>
  </ul>

 

what does ambiguous means i have google but got different ans.

thanks

 

 

 

 

 

 

[Jessica]

https://www.google.com/search?q=define%3A+ambiguous&sugexp=chrome,mod=17&sourceid=chrome&ie=UTF-8

 

Because you're joining to a table which also has the same column names, it can't tell which one you want.

 

The bigger problem is WHY you are joining the user table to itself. That's occasionally a good idea but not for what you're doing. WHY??

[aery]

so, am i suggest to create a different table for the user etc..

[Jessica]

$query = 'SELECT
	user_name, first_name, last_name,  email, location, state, zip
    FROM
        user_info u JOIN
        user_info i ON u.user_id = i.user_id
    WHERE
        user_name = "' . mysql_real_escape_string($_SESSION['user_name'], $db) . '"';

 

WHY are you doing this? Do you understand what a join does, or how to create a query?

[aery]

what i am trying is to retieve the user info from the table

where i log/join in and displaying them in a table

using the string

 

 

[Mahngiel]

you should research how to use the tools and what they're for before you implement them.  a JOIN is for combining more than one table (99% of the time) into a single table instead of making successive queries.

 

Scenario: You want a user and his rank.  The user table stores a rank_id, which relates to the rank table which stores the name of the rank.

bad way:

<?php
// getting user row
$user = select * from users where user_id = 1;
// get rank with that info
select * from rank where rank_id = $user[rank_id];

better way

<?php
//get user and rank together
SELECT * FROM users where user_id = 1 LEFT JOIN rank on rank.id = user.rank_id

 

[Barand]

May as well stop you getting into bad habits at an early stage.

 

If you only want user and rank, use

 

SELECT user_name, rank FROM ...

 

Do not use "SELECT * " - it's wasteful on resources as the query has to return unwanted data and, when you come back to it later, it makes it difficult to understand exactly what the query is doing.

[Mahngiel]

May as well stop you getting into bad habits at an early stage.

...

Do not use "SELECT * "

 

Well, OP didn't... i did purely for brevity. But valid enough point!