Trouble with PHP Strings and SQL

Question

I am a beginner at PHP, but have been attempting a project on a forum. I have come across a problem though.

            $getLatest = dbquery("SELECT * FROM site_forum_thread_comments,site_forum_threads,site_forum_subs WHERE site_forum_thread_comments.thread_id=site_forum_threads.thread_id AND 
        site_forum_threads.sub_id=site_forum_subs.sub_id AND
        site_forum_subs.main_id='".$forum['ID']."'
        ORDER BY site_forum_thread_comments.timestamp DESC LIMIT 1");
            while ($latest = mysql_fetch_assoc($getLatest))
                {
                    $getUser = dbquery("SELECT * FROM users WHERE id='".$latest['user_comment_id']."'");
                    while ($user = mysql_fetch_assoc($getUser))
                    {
                            echo ' <BR>Latest Post: '.$latest['comment'].'
                            <BR>User: '.$user['username'].'
                            <BR>Forum: '.substr($latest['site_forum_threads.title'],0,20).'...';
                    }
                }

This is just a snippet of the code. What happens is, $getLatest gets the code from the SQL query which are from a number of different tables in my database. My problem: At the last part Forum '.substry($latest['...blahblahblah']

Am I able to add the table function in it? So I can retrieve the column "title" from site_forum_threads as I have done it? Because in one of my other tables, there is also a column called "tables", and the query is getting mixed up between which column it should be getting the data from.

Thanks in advance!

EDIT : @zerkms

$getLatest = dbquery("SELECT * FROM site_forum_thread_comments,site_forum_threads,site_forum_subs 
        site_forum_threads.title AS title_from_site_forum_threads 
        WHERE site_forum_thread_comments.thread_id=site_forum_threads.thread_id AND 
        site_forum_threads.sub_id=site_forum_subs.sub_id AND
        site_forum_subs.main_id='".$forum['ID']."' AND
        side_forum_threads.title= title_from_site_forum_threads
        ORDER BY site_forum_thread_comments.timestamp DESC LIMIT 1");
            while ($latest = mysql_fetch_assoc($getLatest))
                {
                    $getUser = dbquery("SELECT * FROM users WHERE id='".$latest['user_comment_id']."'");
                    while ($user = mysql_fetch_assoc($getUser))
                    {
                            echo ' <BR>Latest Post: '.$latest['comment'].'
                            <BR>User: '.$user['username'].'
                            <BR>Forum: '.substr($latest['title_from_site_forum_threads'],0,20).'...';
                    }
                }

zerkms · Accepted Answer · 2012-06-03 03:52:45Z

1

Yes, just specify the name explicitly and alias it

site_forum_threads.title AS title_from_site_forum_threads

and access it using $latest['title_from_site_forum_threads']

The select part in total could look like

SELECT *, site_forum_threads.title AS title_from_site_forum_threads FROM ...

but personally I recommend you to always select only necessary things, by enumerating all the fields separated by comma manually

edited Jun 3, 2012 at 3:52

answered Jun 3, 2012 at 3:35

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

zuc0001 Over a year ago

Thanks for the quick response, although when i add the code in, I get an error: Not unique table/alias: 'site_forum_threads'.

zerkms Over a year ago

@user1433113: I cannot say what you've done wrong until I see the result query. Put it as your question update please

zerkms Over a year ago

@user1433113: you should put what I told you to the SELECT part. Look at my answer once again

zerkms Over a year ago

@user1433113: no worries, just keep in mind that the most often issues are made in programming just by inattention

zuc0001 Over a year ago

so SELECT *, site_forum_threads.title AS title_from_site_forum_threads is all that goes in the select part? Because the query has to handle other parts of the database as well, which is why there is so many different tables in that query.

|

Collectives™ on Stack Overflow

Trouble with PHP Strings and SQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related