|
I am pretty new using MS SQL. I perform a query over some SQL server table; it returns a record set; but I need to read the last record just to get the value of a certain column.
This is my procedure:
$connectionInfo = array( "Database"=>$myDB, "UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, $connectionInfo);
$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));
if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));
$connectionInfo = array( "Database"=>$myDB, UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, connectionInfo);
$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));
if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));
$row_count = sqlsrv_num_rows($rec_set);
if ($row_count === false) die( print_r( sqlsrv_errors(), true));
$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, 0);
$first = $reg['column_name'];
$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, $row_count - 1);
$last = $reg['column_name'];
It is clear that the sqlsrv_fetch_array DOES NOT work like that. Does anyone know how to achieve my goal, that is, getting last and first records from that record set without generating another SELECT?
Thank you in advance.
|
|
|
|
|
Have you tried:
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1 and then
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1 DESC
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
No. The problem here is that I have worked with mysql/postresql and I can retrieve from a recordset any record I want depending on its position in the recordset. So when you have: $var = pg_fetch_result($recordset, $k, 'col'); you are retrieving the 'col' value in the k-th record from that recordset (in PostgreSQL). You can do the same in MySQL. I want to achieve the same in sql server... I have noticed this parameter en the fetch of sql: SQLSRV_SCROLL_LAST in the row parameter of the sqlsrv_fetch_array(resource $stmt, int $fetchType = ?, int $row = ?, int $offset = ?). But for some reason I can not make it run.
|
|
|
|
|
Add a "row number"?
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
prior action plan to prevent sql database corruption
DISTINCT alternatives in sql query
conversion of query scan to seek
|
|
|
|
|
You should try writing in complete sentences if you want an answer to your question(s).
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
His first post was like this one, 3 years ago, and he still hasn't learned how to completely express ideas in complete sentences.
|
|
|
|
|
Kar_Malay wrote: database corruption
The term "corruption" applies to the data that is stored by the database server and has nothing to do with valid/possible structuring of tables. Which your other two points would seem to suggest.
Preventing inconsistent data, orphans, etc is not something that anyone is going to cover in an online post. Buy yourself a database book or several (beginning and advanced) and read them before you start designing anything.
|
|
|
|
|
yes, that's true
got good result by reconstructing database, eliminating unnecessary open connections, creating missing index, converting scan to seek, minimizing scan re-looping, distinct elimination / avoidant etc.
thanks
|
|
|
|
|
I have 5 tables in my db, I used one form to saved into 4 of them at once. I want the 5th one to be used as a master where I can store the foreign key and be used to display to a table in the frontend.
How do I make all of them add one ID?
How can I display a table row as a column in the frontend?
|
|
|
|
|
|
How depends on the database.
But typically an identity column. The table would have something like the following which is for SQL Server. Other databases all have something similar. Mongodb uses a 'object id' (typically.)
{code}
something_id INT IDENTITY(1,1) PRIMARY KEY,
{code}
That is how you create the column. But populating it in the other tables is more complicated.
1. You must first insert into the main table to create the id.
2. You must then get that id. That almost always involves using a specific form of the insert (first part) which returns the id at the same time as the insert occurs.
3. You then use that return value to create (insert) into the other tables. Obviously you will need to add a column to hold that new value.
You can google for examples of using the above.
|
|
|
|
|
It sounds like you have a scenario where you want to save data across multiple tables in a database using a single form. Additionally, you want to create a master table to store foreign keys and display the data in the frontend. Let's break down your requirements:
1. Generating a Common ID for Multiple Tables:
When you want to add data to multiple tables at once and have them share a common identifier, you typically use a primary key (ID) that is common across all related tables. This can be achieved through database design and relationships.
For example, let's say you have four tables: TableA, TableB, TableC, and TableD. Each of these tables has its own data, but they all share a common identifier, which could be a foreign key linking to a MasterTable.
Here's a simplified example:
CREATE TABLE MasterTable (
MasterID INT PRIMARY KEY,
-- Other columns as needed
);
CREATE TABLE TableA (
ID INT PRIMARY KEY,
MasterID INT,
-- Other columns for TableA
FOREIGN KEY (MasterID) REFERENCES MasterTable(MasterID)
);
-- Repeat the same structure for TableB, TableC, and TableD
When you insert data into MasterTable, you generate a unique MasterID and use it as a foreign key in the other tables. This way, you can maintain relationships between the tables.
2. Displaying Table Rows as Columns in the Frontend:
If you want to display data from a table row as columns in the frontend, you'll need to use SQL queries or your backend programming language to transform the data before sending it to the frontend.
For example, suppose you have a table named Data:
CREATE TABLE Data (
ID INT PRIMARY KEY,
MasterID INT,
ColumnName VARCHAR(50),
ColumnValue VARCHAR(50),
FOREIGN KEY (MasterID) REFERENCES MasterTable(MasterID)
);
This table stores data in a key-value pair format, where each row represents a piece of data related to a MasterID. To display this data with columns dynamically created based on the ColumnName values, you can use a pivot query.
Here's a simplified example in SQL:
SELECT
MasterID,
MAX(CASE WHEN ColumnName = 'Column1' THEN ColumnValue END) AS Column1,
MAX(CASE WHEN ColumnName = 'Column2' THEN ColumnValue END) AS Column2,
-- Add more columns as needed
FROM Data
GROUP BY MasterID;
This query transforms rows into columns based on the unique values in the ColumnName column.
Keep in mind that the specifics of these solutions might depend on the exact requirements of your application, the database system you're using, and the programming language/framework of your frontend.
|
|
|
|
|
When we have multiple users, What's the best way connect(logging, query on tables,...) to database?
Which is better and why?
1) Create users(DB User) in the SQL Server for each user(Windows App User).
2) Create a table which is fill with all App user and just connect to SQL Server with a user and query on that table.
|
|
|
|
|
The only possible answer to that question is "it depends". There is no single "best" option; that's why there are different options available. Each option has both benefits and drawbacks, depending on your specific requirements.
For example, if you use SQL Authentication, then you have to store the SQL credentials somewhere on each client machine that needs to access the database. That runs the risk that a technically-minded user could find the credentials and connect directly to your database, bypassing the restrictions implemented in your code. You would have to deliberately design your database to restrict what the SQL user could do in order to mitigate this.
On the other hand, if you're using a web application / api to access your database, then that will typically run as a highly restricted local user on the web server. Setting that up to use Windows authentication is more effort than using SQL authentication. And since all requests would effectively be running as the same local user, you wouldn't be able to use the authenticated user to restrict access to the data.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
There is no such thing as "the best option". There is only what is the most appropriate given the requirements for the application, the application type, and the environment it's running in, which we know nothing about.
There are far more options than just the two you listed.
|
|
|
|
|
I am going to guess you have an application. Users (plural) use the application not the database.
The application, not the users, use the database. So there is only one user which is only visible to the application. The reason for this is because attempting to manage users both at the database and application level rapidly becomes a problem and provides no benefit.
The application itself, should provide a mechanism to validate each user. Then you can do things like log actions, in the application, for each user in the database (a table for that.) The application will use the database to implement this but it does not have anything to do with database users.
|
|
|
|
|
I can't figure out the following:
I have order header and detail tables with UPC codes and the order numbers they belong to. There can be many different UPC codes for each order. The Order IDs are in the header table and the UPC codes are in the detail table.
I must find UPC codes that have been used for more than one order, in other words, where the same UPC code exists for two or more distinct orders.
I have tried:
SELECT OD.UPC, OH.OrderId, COUNT(DISTINCT OH.OrderId) [COUNT]
FROM OrderDetail OD
LEFT JOIN OrderHeader OH ON OH.OrderHeaderId = OD.OrderHeaderId
GROUP BY OD.UPC, OH.OrderId
HAVING COUNT(DISTINCT OH.OrderId) > 1 But this query returns nothing and I'm not sure if it's because there are no duplicate orders or because the query is wrong.
Any help will be tremendously appreciated. Thank you.
The difficult we do right away...
...the impossible takes slightly longer.
modified 31-Jul-23 11:18am.
|
|
|
|
|
How about:
SELECT UPC, COUNT(DISTINCT OrderHeaderId)
FROM OrderDetail
GROUP BY UPC
HAVING COUNT(DISTINCT OrderHeaderId) > 1
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Brilliant! I guess I was overthinking it.
Thanks, Richard. You saved the last few hairs I have left.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
There are some website that provide Demos for their premium Html/css/javascript themes while we can see and copy the source code without paying anything. Why?
Why don't they only provide a snapshot of the website theme to prevent any illegal copy?
|
|
|
|
|
|
Is there possible to get an UNION in such a way that second part of UNION to be ordered ?
I have:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2 ORDER BY 3
The select from table1 will always get one row, and I need to order just records that come from table2, which could be more than one row ... it is possible to achieve that by SQL ?
P.S. I am using SQL Server.
modified 29-May-23 13:54pm.
|
|
|
|
|
_Flaviu wrote: it is possible to achieve that by SQL ?
No.
There are however solutions using derived languages such as TSQL or PL/SQL. You would need to specify which database you are using however for any consideration of that.
|
|
|
|
|
SQL Server (from Microsoft)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
