PHP & MYSQL Problem with fetching data.

[mbhanley]

Hi *

I have spent the last 18 hrs or so trying to get the wright sql code or think of an alternative but I just can't get my head around it. If any one can help me it would be
awsome.

What I am trying to acheive is getting the correct id to echo out in the row variable.

<?php echo $row['id']; ?>

but it has to based on the mysql table row
user_id which is based on the session username so it works off the users table id.
I have two tables one is users the other is contacts. And I have a user_id at the end of the table contacts.

function manage_contacts() {
			
		echo '<div id="manage">';
		
		$sql = '
		SELECT 
        * 
        FROM 
        contacts u JOIN users i ON i.id = u.user_id 
        WHERE 
        username = "' . mysql_real_escape_string($_SESSION['username']) . '"
		';
		$res = mysql_query($sql) or die(mysql_error());
		while($row = mysql_fetch_assoc($res)):	
			
	?>
		<div>
		<p><b><?php echo $row['fname'] . " "; ?><?php echo $row['lname'] ." - "; ?></b> 
        <span class="actions"<a href="update-contacts.php?id=<?php echo $row['id']; ?>">Edit</a> | 
        <a href="?delete=<?php echo $row['id']; ?>">Delete</a></span></p>
		
		</div>
	<?php
		endwhile;
		echo'</div>'; 
	}

The following is the call method.

<?php $obj->manage_contacts()?>

It all works if I SELECT * FROM contacts, but if i do that it will show up when another
user logs in and I don't want that to happen if any one has any suggestions it would be
greatly apreciated.

[hielo]

what is NOT working on the code you posted? Are you NOT getting any results? Are you getting the ENTIRE table? Are you getting the WRONG id?

Have you tried to echo your sql statement to verify what sql command is actually being executed?

[mbhanley]

what is NOT working on the code you posted? Are you NOT getting any results? Are you getting the ENTIRE table? Are you getting the WRONG id?

Have you tried to echo your sql statement to verify what sql command is actually being executed?

Hi sorry for any confusion I find it hard to explain some things. I am getting the same results on each echo of the row variable and its being displayed from the user_id of the table rather than getting the result from the id of the table.

[hielo]

and its being displayed from the user_id of the table rather than getting the result from the id of the table.

I don't understand what you mean. Post a sample of BOTH tables

[mbhanley]

I don't understand what you mean. Post a sample of BOTH tables

Sorry if im making things more complicated than they need to be im rubbish at explaining.

I am trying to get the user_id of the contacts table to match the id of the users table
based on the session username. and then display the id of the contacts table using an
echo.

Table structure for table `contacts`
--

CREATE TABLE IF NOT EXISTS `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(25) NOT NULL,
  `lname` varchar(25) NOT NULL,
  `email` varchar(40) NOT NULL,
  `landline` varchar(20) NOT NULL,
  `work_landline` varchar(20) NOT NULL,
  `mobile` varchar(20) NOT NULL,
  `door_no` varchar(10000) NOT NULL,
  `street` varchar(100) NOT NULL,
  `town` varchar(100) NOT NULL,
  `county` varchar(100) NOT NULL,
  `country` varchar(100) NOT NULL,
  `postcode` varchar(15) NOT NULL,
  `website` varchar(50) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `username` varchar(25) NOT NULL,
  `password` varchar(100) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

[hielo]

OK, now I see the problem. The issue is that you are executing SELECT * but both tables have an id column. What you need to do is specify precisely which columns you want by prefixing the columns with the table (notice that I used "u" as the alias for the "users" table and "c" for the contacts table):

Assuming you are interested in users.id, try:

$sql='
SELECT c.fname, c.lname, u.id
FROM contacts c INNER JOIN users u ON u.id=c.user_id
 WHERE 
        u.username = "' . mysql_real_escape_string($_SESSION['username']) . '"';

[mbhanley]

OK, now I see the problem. The issue is that you are executing SELECT * but both tables have an id column. What you need to do is specify precisely which columns you want by prefixing the columns with the table (notice that I used "u" as the alias for the "users" table and "c" for the contacts table):

Assuming you are interested in users.id, try:

$sql='
SELECT c.fname, c.lname, u.id
FROM contacts c INNER JOIN users u ON u.id=c.user_id
 WHERE 
        u.username = "' . mysql_real_escape_string($_SESSION['username']) . '"';

Thank you so much it works perfectly I just had to change u.id to c.id on the select and that was all I am so gonna have to look up more on sql.

Thank you.

[hielo]

FYI: If you REALLY needed BOTH ids, then you could alias one OR both of the ids:
SELECT c.fname, c.lname, u.id as User_id, c.id as Contacts_id...