[SOLVED] select question

[onthespot]

Hey, got a problem.

 

I have information in the DB i want to pull using the following

"SELECT comment FROM ".TBL_COMMENTS." WHERE touser LIKE username"

 

How do i display the information. That just isnt working! Please help

 

 

[cunoodle2]

What errors IF any are you getting?  I also think you may want to put % at the end and beginning of the word "username" in your query so you can find items also containing said user names.

[seventheyejosh]

is it properly quoted?

 

$query="SELECT comment FROM tablename WHERE `touser` LIKE '$username'";

 

also u can do

 

LIKE '%$username%'

 

% is the sql wildcard

[onthespot]

Ok sorry im using a session variable in the username place, just replaced it here because I thought would be easier to just say i was using username!

 

<?

$showcomment=mysql_query("SELECT comment FROM ".TBL_COMMENTS." WHERE touser LIKE ".$requserinfo['username']."");

mysql_fetch_assoc($showcomment);

?>

 

This doesnt work, im just confused about how to get it to show that sql query. Any ideas?

[seventheyejosh]

u have to store the results in an array

 

$res=mysql_query("SELECT * FROM table WHERE 1");

$row=mysql_fetch_assoc($res);

then u can go off the table name or #

$username=$row[1];

or

$username=$row['username'];

 

 

perhaps check out:

 

http://www.w3schools.com/SQl/default.asp

 

[onthespot]

I will use this, and thankyou for the link, I am the first to admit I need to improve my knowledge on SQL.

[seventheyejosh]

and this is my preference, but i HATE concatanation! (so much i cant even spell it!)

 

instead of

 

<?
$showcomment=mysql_query("SELECT comment FROM ".TBL_COMMENTS." WHERE touser LIKE ".$requserinfo['username']."");
mysql_fetch_assoc($showcomment);
?>

[/php

i'd do 

[code=php:0]

<?

$likematch=$requserinfo['username'];

$res=mysql_query("SELECT comment FROM TBL_COMMENTS WHERE touser LIKE '$likematch'");
$row=mysql_fetch_assoc($res);
$comment=$row[0];

?>

[onthespot]

ill try this then

[onthespot]

$likematch=$req_user_info['username'];

 

$res=mysql_query("SELECT comment FROM ".TBL_COMMENTS." WHERE touser LIKE '$likematch'");

$row=mysql_fetch_assoc($res);

$comment=$row[0];

 

Used that and it doesnt work unfortunately, no errors, just nothing comes back on the webpage?

 

Whats the $comment=$row[0] do?

[seventheyejosh]

$row is returned as an array, and the first instance will be in [0] if that isnt working, you can do

 

$comment=$row['comment'];

 

assuming 'comment' is the name of your DB table's field

[onthespot]

$likematch= $req_user_info['username'];

 

$res=mysql_query("SELECT comment FROM ".TBL_COMMENTS." WHERE touser LIKE '$likematch'");

$row=mysql_fetch_assoc($res);

$comment=$row['comment'];

 

ok so thats what i have and there is nothing being returned.

 

In my database got primary key comment id, touser, fromuser, comment, commentdate. I have successfully got the inputting into the database working, but this is what i now cant do.

 

 

[onthespot]

Everyone given up on this then?

[seventheyejosh]

lmao

 

had to run to the store for tomato paste and toothpaste!

 

also, try putting absolute values into your query, such as a working touser, then run it in the sql tab of phpmyadmin and make sure there are results to be got.

 

and dont get upset and assume we've abandoned you because there are other people, and we have given you some stuff to build off of.

 

just run it in the sql tab

 

and try to not use that concacatanation shenanigans :|

[onthespot]

haha, toothpaste. Ill try that what u said.

I didnt get upset, I just thought I was ask, rather be told that im a waste of time haha! cheers dude

[onthespot]

Just tried in the database, and its returning zero rows, thats probably why.

Im totally at a loss to why this is!

The field with the username is touser, and thats the username im trying to get all the comments for. The username is in a variable I have specified.

 

any ideas mate?

[seventheyejosh]

try something basic, can you do:

 

SELECT * FROM TBL_COMMENTS

 

in the sql tab? and will it return results?

[onthespot]

I was trying that when you said to do it!

I managed to find out that

SELECT * FROM 'comments' WHERE touser = username

works.

[seventheyejosh]

ok, now spice it up a bit try running

 

SELECT * FROM `comments` WHERE `touser` LIKE 'someusername'

 

or

 

SELECT * FROM `comments` WHERE `touser` LIKE '%part_of_a_username%'

[onthespot]

I want it to be exact with what it returns, the username that it is going for needs to be exactly the same.

 

The idea is that on user profiles, there will be comments from other users.

I have already submitted the comments, from one other to another. Now trying to get the right comments on the right profiles.

 

So the WHERE touser = username, is the most precise yeah?

But for some reason it wont return anything on the php page, only in phpmyadmin!

[seventheyejosh]

ya i musta lost something along the way... i think i confused this with a search thread i was in :|

 

try making the $res=mysql_query(  THE STRING THAT WORKS IN PHPMYADMIN WITH NO concatctanation   );

 

then doing      $row=mysql_fetch_assoc($res);

 

then doing print_r($row);  exit();

 

and see what shows up

[onthespot]

Got it working now with the variable for the profile you are on.

 

This is what I got,

Array ( [touser] => user1 [fromuser] => user2 [comment] => blah blah  [commentdate] => 2009-07-06 19:58:18 [commentid] => 12 )

 

It only shows the first comment from each user though.

And how do i seperate each field? Do i run SELECT field each time seperately?

Thanks so much for helping

[seventheyejosh]

you'll do

 

$res=mysql_query($query);

while($row=mysql_fetch_assoc($res)){

$touser=$row['touser'];
$fromuser=$row['fromuser'];
$comment=$row['comment'];
$commentdate=$row['commentdate'];
$commentid=$row['commentid'];

echo "TO: $touser FROM: $fromuser\n";
echo "COMMENT: $comment\n";
echo "SENT ON: $commentdate\n";
echo "ID OF: $commentid\n";

echo "<br />\n";


}

 

then for each iteration it'll update the variables from the db

[onthespot]

You're a genius. That's it, perfect, now i will just format into a table. Dude, thankyou so much for your time. It works a treat 

[seventheyejosh]

no problem! dont forget to mark as solved in the bottom left

[onthespot]

Oh one last think, anyway I can get the results to be newest first??