Portal Home > Knowledgebase > Articles Database > mysql help?

mysql help?

Posted by josephgarbett, 09-10-2010, 04:04 PM
Hi everyone; test of your knowledge. In my database, I have quite a few tables and I'm building a private message system. However, I'm display all the users messages as a whole then eliminating the messages that don't match the user_id. How though do I rename the column's titles? At the moment its (from the message table): id | text | date | user_id I then have another table called message_recipient which has message_id and user_id in it relating messages and its intended user. But how do I rename in php the title of the colloums without rename them in the database??? So instead of it saying "id | text | date | user_id" It can say: "Message-ID | Message | Message Date | User Name Just renaming though!!! Any idea's??? Thanks in advance!

Posted by webbanner, 09-10-2010, 07:24 PM
Not sure what 'exactly' your trying to do, but you shouldnt need to rename anything.. When you select the items from the table, it returns an array, for each item in the array, you can call it anything - then print it's value...

Posted by 7HillsIT, 09-11-2010, 06:23 AM
How you display the data is down to your UI code. For example building a table with custom headers and just iterating through the array returned from your MySQL call. If this isn't what you mean can you repost with more info.

Posted by Drinian, 09-11-2010, 06:23 PM
Sorry if I've misunderstood your question but presuming your sql is generated within your PHP, would something like the following work? SELECT id AS MessageID, text AS Message, date AS MessageDate, user_id AS UserName FROM xyz WHERE ..... That way you're not renaming the columns in the database, only referencing them as an alias. Like I said, I may have totally misunderstood your question

Posted by josephgarbett, 09-11-2010, 06:49 PM
Hello; Sorry if it was a little tricky to understand. Basically what I'm trying to do is translate the column name when It is generated. So instead of saying "user_name", It will say "User Name". This any more descriptive for you?

Posted by Drinian, 09-12-2010, 04:53 PM
Try using apostrophes around the alias part if the alias has spaces in it, e.g. SELECT user_name AS 'User Name' FROM xyz etc. Is that what you need?

Posted by josephgarbett, 09-13-2010, 10:05 AM
Thanks; that works. I've now hit another wall. Search tool this time. I've got it to work so it will search the database and display a list of result from the user table. Lets say admin is id 55. When it search's, it goes to "search.php?id=55" but I cant get it to display any data from that row... Any idea's on this one?

Posted by josephgarbett, 09-18-2010, 05:11 PM
Scrap that. Fixed it; However this one I dont understand... If i have a row that is ID | NAME | DESCRIPTION | DATE How do i display "DESCRIPTION" when I retrieve that data via "ID"? Any help is appreciated!

Posted by dreamconception, 09-18-2010, 06:11 PM
How do you retrieve the data? What is your SQL statement? If you just have "SELECT *" it will be in the array under array['DESCRIPTION'] or at array[2]. If you have the "SELECTED id FROM" add description like this "SELECTED id, description FROM". Of course if it is capitalized the column name to capitalize it also in the query.

Posted by lynxus, 09-18-2010, 06:14 PM
Well, For private message systems ive always had one table for DATA. This DATA table, Holds ID, DATE/TIME, MESSAGE , SENDER , RCPT , THREAD This way, All i need to do to get messages for a particular thread ( or conversation ) I just Select MESSAGE from data where RCPT = 'Foo' and SENDER = 'Bar' AND THREAD = 'threadID'; I set ID as pri key, Sender and RCPT and THREAD indexed.

Posted by mastersofdotnet, 09-23-2010, 08:07 AM
agree with lynxus

Was this answer helpful?

Add to Favourites Add to Favourites    Print this Article Print this Article

Also Read


Customer Testimonials

John Doe
It's a great service with fantastic support. It's definately good value for your money. Overall rating, 10 out of 10.
Mike Smith
Much better than my previous hosting company - I also got help with migration at no extra cost. Friendly support too.
Copyright © 2015 DC International LLC in partnership with Bragin IT Solutions Inc. - All Rights Reserved.