Need to find a value in one table, and use it in HTML output elsewhere

[PHPNooblar]

Hi guys I am new here

 

I have had a search around and can't really find out how to do this. Its quite difficult for me to explain and I am really new to PHP / mySQL - just starting out really. I hope to learn from doing small projects like this and seeing how it gets done.

 

So I have a mySQL DB with two different tables - one is called "notesnew" the other is called "users".

 

I have modified a common tutorial on how to make a "to do list" for what I am doing here.

 

Basically users can login and post messages on this list as them self. Each user's post appears as a list item, showing their username, the time they posted the message and the message content. The message content, time and owner of the message is stored in the "notesnew" table.

 

The list of user's, their passwords and their "avatar/profile" image URL location is stored in the "users" table.

 

Now I am having trouble getting each user's avatar image from being output next to each of their posts. The part that is confusing me is being able to match up the username's post with their content that is display - because I am posting the user's name along with their post, based on the logged in "session name" as their username. So basically, all the content being listed is being displayed from the "notesnew" table. I need to somehow figure out what each user's profile image is based on what is stored against their entry in the "users" table.

 

Here is the code I have so far. It is not working properly - it displays all content etc, but displays the avatar / image of the last user that has registered. Can anyone help me out here with some code that works, or pointers to try follow? I know the $finduserprofile part is incorrect in the way it goes through all user's and finds their profileimg, and that the image displayed next to each post is therefore incorrect (as it displays the last member to have registered's avatar, but its just that I don't know how else to do what I am trying to do.

 

<?php
//Connect to the database
$connection = mysql_connect('localhost', 'zzzz' , 'zzzzzzz');
$selection = mysql_select_db('zzzzz', $connection);

$username = $_COOKIE['ID_my_site'];

//Was the form submitted?
if($_POST['submit']){

//Map the content that was sent by the form a variable. Not necessary but it keeps things tidy.
$content = $_POST['content'];

//Insert the content into database
$ins = mysql_query("INSERT INTO `notesnew` (content, owner, dp_time) VALUES ('$content', '$username', NOW())");

//Redirect the user back to the members or index page
header("Location:index.php");
}

/*Doesn't matter if the form has been posted or not, show the latest posts*/

//Find all the notes in the database and order them in a descending order (latest post first).
$find = mysql_query("SELECT * FROM `notesnew` ORDER BY id DESC");
$finduserprofile = mysql_query("SELECT * FROM `users` ORDER BY id DESC");

//Setup the un-ordered list
echo '<ul>';

while($row = mysql_fetch_array($finduserprofile))
{
$imagelocation = $row['profileimg'];
//Continue looping through all of them
while($row = mysql_fetch_array($find))
        {

	$owner = $row['owner'];
	//For each one, echo a list item giving a link to the delete page with it's id.
	echo '<li>' . '<img src ="' . $imagelocation . '">' . $row['owner'] . ' said at ' . $row['dp_time'] . ': ' . $row['content'] . ' <a id="' . $row['id'] . '" href="delete.php?id=' . $row['id'] . '"><img src="delete.png" alt="Delete?" /></a></li>';

}
}

//End the un-ordered list
echo '</ul>';

?>

[gristoi]

Hi, what you will be best doing is writing one singe query use a table JOIN.

 

Example:

SELECT content, owner, do_time, profileimg FROM notesnew JOIN users on notesnew.owner = users.ownerid

 

Without seeing your table structures I can't accurately guess the correct query, but hopefully this should point you in the right direction

[PHPNooblar]

hi gristoi,

 

Thanks for your response, here are the table structures just quickly, while I have a think about your suggestion

 

notesnew table:

 

users table:

 

Note that some users do not all have avatar locations specified, but this is just a test so I would just expect some users images to show on their posts.

[harristweed]

http://www.tizag.com/mysqlTutorial/mysqljoins.php

[gristoi]

Ok, read up on the article posted by harries tweed, learning about table joins will help you a lot in the furture, a good rule of thumb that I personally use is that mysql queries should not go inside loops, joins should be used wherever possible. Looking at your table:

SELECT content, owner, dp_time, profileimg FROM notesnew JOIN users on notesnew.owner = users.username

 

Should do the trick

[PHPNooblar]

Thank you very much gristoi and harristweed That seems to have worked. I also read through harristweed's link and think I get the concept now.

 

Question - when using your code gristoi, I am now not able to use the "id" field in my "notesnew" table. I was using it in each item output to be able to delete each particular item with its relevant id.

 

Is this because the "id" field exists in both tables and I am now using a JOIN?

 

The output that I was using the id field in looks like this:

 

echo '<li>' . '<img src ="' . $row['profileimg'] . '">' . $row['owner'] . ' said at ' . $row['dp_time'] . ': ' . $row['content'] . ' <a id="' . $row['id'] . '" href="delete.php?id=' . $row['id'] . '"><img src="delete.png" alt="Delete?" /></a></li>';

 

*edit, I believe it is because of this - I tried modifying my structure of "users" for the field to be called "userID" instead of "id" and now my delete href's display correctly. I'll need to keep both fields in each table as "id" so I'll need to figure out a way around this - any ideas guys?

 

[PHPNooblar]

Ok I popped a "ORDER BY id DESC" at the end of the JOIN query and now my posts are ordered from latest to oldest, using a different field in "users" for "id" results in everything working as I expected.

 

I would still be interested to know how I can "exclude" those two fields (if both were still called id) from conflicting when using this JOIN (where I only care about the "owner" and "username" fields)

[gristoi]

When joining tables you will come across. Instances where a field in both tables have the same name. Because of this you cannot select just id, this would be an ambiguous field join. Instead you need to declare which table you wish to pull the Id from:

SELECT notesnew.ID ,  content, owner, dp_time, profileimg FROM notesnew JOIN users on notesnew.owner = users.username

 

A not to remember is that when you echo out the row it will just be ID and not notesnew.ID.

 

If you wanted to pull the Id from both tables then you would be beat using an alias for each one:

SELECT notesnew.ID AS 'notesId' , users.ID AS 'usersId'

[PHPNooblar]

Thanks again gristoi! That make sense - so basically when both fields exist as the same name in both tables, I refer to them by "tablename.fieldname". Echoing out, this data I need to refer back to it just as "fieldname".

 

[Muddy_Funster]

You got it.  Using tableName.fieldName to identify the field in the statement only tells the SQL where to find the fieldName that you are looking for.  When the SQL returns the values it only uses the fieldName to identify it.  As gristoi said, the use of aliases helps when trying to identify between two fields that have the same fieldName from different tables.  This is perticularly usefull when echoing back results in PHP using the mysql_fetch_assoc() command.

 

Oh, and if your new to MySQL, it's not too late to save you - STOP using SELECT * 

[PHPNooblar]

Haha thanks for that Muddy_Funster. So I have canned the SELECT * query anyway as it wasn't needed after using this join. Mind me asking why its bad to use these? Is it just an inefficient way of retrieving data? Seems that most beginner tutorials always use these. Probably why you see so many new guys using them?