|
PIEBALDconsult wrote: Oh, well then...
You'll need two more tables:
I know that.. I wanted others to think the answer through...
|
|
|
|
|
Chances are the O' folks never made it into the database because parameters were not used for the input, and no one did a .Replace("'","''") on the SQL string, so it's not an issue.
|
|
|
|
|
Or because the GUI specifically excludes those because the developer doesn't know anyone with a name like that.
|
|
|
|
|
Isn't that just the luck of the Irish. 
|
|
|
|
|
I thought I was being facetious in my over-engineering way. 
|
|
|
|
|
The code was just a heads-up to get the OP started with something. I'd have suggested something better if the OP came back with the specifics.
|
|
|
|
|
Dear sir
Please explain me how i can get the reference of a primary key colun in a database. what exactly i want to do....
I have a table "[plan]"
"PK column is PlanId"
how many tables are using this column reference(foregin key reference) in mydatabase, i wana get these tables name.
Waiting for your favorable reply.
Regards
Digvijay
|
|
|
|
|
There is not an easy answer here, it is down to your database schema.
One way I would approach this is to buy a bottle of good scotch and take it to the DBA. Ask the DBA "what tables link to plan using planId?"
If he can't help then you'll need to do some mining.
First find the candidate field names. Not knowing which DB you are using I can give a generic idea:
SELECT DISTINCT COLUMN_NAME FROM SYSTEM_COLUMNS
Here the table SYSTEM_COLUMNS is the system table containing all the column definitions and COLUMN_NAME is the field with the column name.
If you know the definition of the field planId you could add it into the query to narrow down the search.
Once you have your fields, you can then query to get the tables that contain these fields. It is /relatively/ easy SQL but does need a bit of leg work to read through the results and ensure you have no false positives.
If the table and column names do not follow a structure then I am afraid you may well have to use educated guesses.
Panic, Chaos, Destruction. My work here is done.
Drink. Get drunk. Fall over - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
I cannot live by bread alone. Bacon and ketchup are needed as well. - Trollslayer
Have a bit more patience with newbies. Of course some of them act dumb - they're often *students*, for heaven's sake - Terry Pratchett
|
|
|
|
|
Could you please tell us what RDBMS you are using? Oracle? MySQL? Microsoft SQL Server? This will help us to better help you with an answer.
|
|
|
|
|
Dera Sir,
Thanks for your quick response.
I'm using SQL server 2008 and SQL server 2008R2
|
|
|
|
|
Since you mentioned in one of your comments that you are using SQL Server 2008, you can query the sys.foreign_keys table to get the information you want.
|
|
|
|
|
I second that suggestion. 
|
|
|
|
|
|
I`m building a web chat service and I use this query:
SELECT * FROM messages WHERE chat_room_id='$chat_room_id'
But I need to display no more than 20 rows (last 20 rows). Can`t use LIMIT because it will return the first 20 rows.
Is there a sql command? or do I have to sort it out in php?
|
|
|
|
|
the concept of first row/last row does not exist, unless you have an ORDER BY clause, without that the rows are unordered i.e. they come in an arbitrary order.
So whatever criterion it is you want to define the "last rows", write the inverse and use LIMIT(n) or TOP(n) to get the first N rows of that. Use TOP in T-SQL, LIMIT in MySQL.
|
|
|
|
|
what if I create a temp table with all the selected rows and then return it`s last rows?
Is that a good practice?
|
|
|
|
|
No. Look at a table as a pile of rows, not an ordered set of rows.
|
|
|
|
|
I found the best way..since I`ll have to clean up the tables once in a while...if the select returns too many rows I start deleting from the top and select again.
|
|
|
|
|
select top 20 * from messages where chat_room_id=$chat_room_id ORDER BY yourTimeStampField DESC
This makes the HUGE assumption that you have a timestamp field defined in your messages table.
Give it a shot. 
Crap. I think I just replied to the wrong thread. This reply was meant for the original poster.
Sorry for any confusion. It's early for me.
|
|
|
|
|
I have a timestamp but I don t get your idea:
I have:
4:35 HELLO!
4:38 WASSUP?
5:10 FINE!
7:30 OLA!
8:00 BONSOIR!
supposing I`ll output 3 messages max.
why select top 3 messages here and sort desc? it`ll be:
5:10 FINE!
4:38 WASSUP?
4:35 HELLO!
when I want:
5:10 FINE!
7:30 OLA!
8:00 BONSOIR!
this works great and keeps the table clean:
$result=$this->db->query("SELECT * FROM messages WHERE chat_room_id='$chat_room_id'");
$chat_num=$result->num_rows();
if ($chat_num>30)
{ $to_delete=$chat_num-30;
$query=$this->db->query("DELETE FROM messages WHERE chat_room_id='$chat_room_id' LIMIT $to_delete");
$result=$this->db->query("SELECT * FROM messages WHERE chat_room_id='$chat_room_id'");
}
return $result;
modified on Tuesday, August 2, 2011 8:58 AM
|
|
|
|
|
 Try it.
|
|
|
|
|
|
Further to Luc's excellent advice, I would add that you should avoid the death trap SELECT * FROM ... syntax. It's quick now but will come back and bite your head off in the future. Name the columns you want explicitly and query nothing else.
Panic, Chaos, Destruction. My work here is done.
Drink. Get drunk. Fall over - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
I cannot live by bread alone. Bacon and ketchup are needed as well. - Trollslayer
Have a bit more patience with newbies. Of course some of them act dumb - they're often *students*, for heaven's sake - Terry Pratchett
|
|
|
|
|
Hello,
I have not really coded for error trapping the past since the applications were not that big. Currently, the app being worked on is huge with multiple phases development. I wanted to find out the best practices for error trapping, some simple forms and some more complex ones. I need to start using them
It is also ok, to write them to the database tables if possible.
I know I can google this as well but some times it takes a longer amount of time to find the correct link/article.
Thank you for your time!
modified on Monday, August 1, 2011 3:43 PM
|
|
|
|
|
There is no way to trap everything.
And it is non-sensical as well.
So you need to determine some actual implementation cases where it is reasonable.
vanikanc wrote: It is also ok, to write them to the database tables if possible.
Still depends on what you mean by error.
It is certainly possible for some cases.
Also why is this just a database relevant question? If the system is large then there will be other parts of the system which will fail in relation to the database. For example bouncing the database is very likely to cause ripple failures in a 24x7 system unless those other systems are specifically designed to deal with that.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
