Hi,

I have the following 2 codes :

include "connectdb.php";

$num_results = 5;
$result = mysql_query("select diary.*, users.name from diary, users where diary.poster = users.username order by postno desc limit $num_results");

if(!$result) echo "Database error!";
if($num_results > mysql_num_rows($result))
$num_results = mysql_num_rows($result);

for($i=0;$i<$num_results;$i++) {
$rows = mysql_fetch_array($result);
echo "<strong>".$rows["name"].">>".$rows["datetime"]."</strong><P ALIGN=JUSTIFY>
".$rows["text"]."</p>";


What this code does is to convert the value 'poster' into 'name', where poster is a user's login and 'name' is the user's real name. This code will produce an output like :

Mark Lee>>Nov 4,12:33 pm 2004

Where Mark Lee is the name.

--

I have another code :


include "connectdb.php";

$result = mysql_query("select * from diary where datetime like '$month%' and datetime like '%$year' order by postno desc");

if(!$result) echo "Database error!";
$num_results = mysql_num_rows($result);

for($i=0;$i<$num_results;$i++) {
$rows = mysql_fetch_array($result);
echo "<strong>".$rows["poster"].">>".$rows["datetime"]."</strong><P ALIGN=JUSTIFY>
".$rows["text"]."</p>";


As you can see from the code, this code will only produce the value of poster, ie the user login name and NOT the user's real name.

I would like to change this but I am not sure how to. I cannot change 'poster' to 'name' for the code doesn't know what is 'name' as it hasnt been coded beforehand, unlike the first source code. Could someone please give a tip or two on how to instruct the second code to convert 'poster' into 'name' like the first code please?

thank you!

Try this SQL.

SELECT d.*, u.name FROM diary d INNER JOIN users u ON d.post=u.username WHERE d.datetime like '$month%' and d.datetime like '%$year' order by d.postno desc

it didn't work...

I think I made an error in the SQL (post instead of poster).

SELECT d.*, u.name FROM diary d INNER JOIN users u ON d.poster=u.username WHERE d.datetime like '$month%' and d.datetime like '%$year' order by d.postno desc

Ok, just so I understand your schema, let me lay out my assumptions and you tell me if they are correct.

users.name is the user's real name.
diary.poster and users.username should be equal in determining which entries in the diary table belong to a particular user.

If these are true, then the JOIN is valid, but as I said, I made an error in the SQL that has now been corrected.

One suggestion, assign primary keys to each of your tables and use those to perform cross-table correlations. It is a much more efficient process to compare integers than it is to compare strings.

hmm, yes user.name is user's real name. poster is like a nick or something. no they dont have to be equal.

the poster and the name fields are in seperate tables. that makes it a little more complicated.

and YES, i did the above changes, and i changed

echo "<strong>".$rows["poster"].">>".$rows

from poster to name, and it solved the problem!

thank you!

now i have to carefully examine your code..i will take ur suggestion. in fact i think i ought to have the same keys for each table. whats the point of having a 'name' in the user table, and a 'poster' in the other table..hmm

Agreed. It seems that you may be able to combine some of the fields in the two tables into the columns of the users table.

I'm glad we got your problem solved -- good luck.

Originally Posted by Kyrnt

Agreed. It seems that you may be able to combine some of the fields in the two tables into the columns of the users table.

I'm glad we got your problem solved -- good luck.

it was built for this blogging thing and i cant change the keys without screwing the whole thing. thats bad programming i guess, not planning in advanced.

but yeh will take note, and thanks for your tip. appreciate it lots!