MYSQL :: Previous To Last With A Certain WHERE Condition

Select Previous And Next Id After Id=# ?

For a gallery I'm trying to query for a picture by its id and also get some images before and after that id (according to current sort) are there references like DOM previousNode or stuff like that to use in the WHERE statement? Or how could I do this?

Currently I have: ($picId is a php variable)

'(SELECT id, picUri, picUriSmall FROM pictures WHERE id < '.$picId.' ORDER BY id DESC LIMIT 2)
UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id = '.$picId.')
UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id > '.$picId.' LIMIT 2)'
This works but doesn't seem to be the best way. I want to be able to have it working for other sorts than by id. And the two first images gotten by this query will be in reversed order (can be fixed in php).

Is there a way to ask for my picIDs position in a query and then use another query to make the LIMIT 10,14 if my image was in position 12?

Paging With Only Next/previous
I'm running PHP + MySQL (innodb) and in some pages, the paging is slowing down the system a lot.

Now I've seen on some sites that have paging implemented in an interesting way. They only let you go forwards and backwards one page at a time, and some don't even tell you how many results there are.

For instance if you look at google search results, they only says "about XXX results". Plus they never actually let you jump to the last page.

I'm thinking of implementing my system this way in certain pages as well, to cut down on the load times. However, I'm exactly sure how that cuts down the load. Can someone shed some light on this?

Using Data From Previous Row
I only want to select the current row if a field in the previous row is not null.

Is it possible to write such a query?

Previous Record
I need to get a previous record from database - i need to retrieve records which differs from the previous one about 5s example:

select eventDate,previousEventDate,eventDate-previousEventDate as delta from events having delta<5 order by eventDate;

eventDate is normal column from table

previousEventDate is an "alias" for the previous date ... how can I write it in SQL?
Please help. I can use SELECT as subquery, but it would be probably really slow, the table will have 100000s maybe milions of records soon.

Previous Row Comparison
I have a table in this format:

Column A | Column B
1 | 2
1 | 3
1 | 4
2 | 5
.....
.....

What I want to do is something like this:

If in Column A there is a change in the number i.e. from the above table there were many 1s which transitioned into a 2. I want to record this change. I want to extract these two rows into another table. How can I make this possible?

Get Next And Previous Records
Our website has a portfolio database which uses a column entitled 'list_order' to set the order of how we want the projects to display in a list. Each project has a link to click into it to see the full details of the project.

Inside this details page we would like to add a View Previous Project and View Next Project link which would allow the user to navigate through our projects. The problem is that we are using an order set by 'list_order' and not an auto incremented column.

How would I setup the Query to grab the previous and next rows no matter which project the user clicked on? For example, our database has these records:

id - list_order
102 - 100
89 - 200
7 - 250
30 - 300
97 - 400
20 - 500
62 - 600
88 - 650
103 - 700
85 - 800
112 - 900
95 - 1000

So, if someone clicked into id 97, we'd need a query that would grab id numbers 30 and 20 for the previous and next id to create the links.

ID Of Previous Insert
Im thinking i need a transaction here. I want to insert a record into a table, which has an autoincrement field as the primary id. Then use this id to insert into another table. In the time between the first insert and then getting the id, couldnt there possibly be another user that causes an insert and hence the id the first user gets back isnt correct ?transactions to the rescue ?

Subtract Previous Value.
I'm trying to get one column of numbers to subtract from another column and to keep a running total. Column A is a dollar amount from an invoice. Column B starts out with an opening amount ($10,000) and I want to have Column A to subtract from that.
Code:

COLUMN A--------COLUMN B
$0.00 $10,000
$1000 $9,000
$500 $8,500
$2000 $6,500

Get Previous And Next ID Of Current Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query?

example:

ID's: 4,10,7,9,13

I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9.

I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.

How To Avoid Previous Results
I want to omit all results(id) from the first query in 2nd query , without using sub query...

Phase Previous And Next Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query? example:

ID's: 4,10,7,9,13

I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9.

I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.

Previous And Next Record Delimma
I have a main page that has a query to get all my clients in alphabetical order.

"SELECT * FROM clients ORDER BY cname ASC"

This obviously mixes up their IDs. So when I click on a client's name "Goofy" (id = 6), I need the next client "Mickey Mouse" (id = 2), and "Donald Duck" (id = 9) to be the previous client.

And I would prefer this to happen on the same page where it displays my client's details rather than have to put those vars in the URL.

To get my client, it's simply:

$client = $_GET['c'];
"SELECT * FROM clients WHERE clientid = '".$client."'"

Selecting Item From Previous Row
Is it possible to get an entry from a previous row in a select statement?

Getting Next, Previous Record After INSERT
When I insert a record in a given index order I want to be able to traverse to the previous or next record of that index order, before or after the one I inserted. So for example if I insert a record with an address of "20 Heather Court" using an address index order, I want to be able to push a Next button (on a Web HTML form) to go to the record corresponding to "21 Heather Court" or, if I press the Previous button I
I want to be able to go the record corresponding to "19 Heather Court". Assuming of course that both the Next and Previous records exist before I insert "20 Heather Court".

If I change the index order to customer first name and insert a new record for a customer named "Harry" I then want to be able to push the Next button and see the record for a customer named "Jan" or if I push the Previous button I likewise want to pull up the record for a customer
named "Garry". Assuming that the records for Jan and Garry already existed before I inserted the one for Harry and that they became the Next and Previous records with respect to Harry's after the insertion.

Using an auto increment field does no good because every time I change the index order any kind of sequential relationship between the values in the auto increment field gets totally messed up.

I have also tried statements like " SELECT * FROM Customers WHERE Customer_ID <349 ORDER BY `Customer_ID` ASC LIMIT 0 , 1" without success if there are 10 records before the one with a Customer_ID of 349. A LIMIT of 0,1 will start at record 0 for only 1 record. I want to pull up the record immediately preceding the one with a Customer ID of 349.

In short is there some way to tell which record is Next or Previous to one that I insert under a given index order?

Subtracting Current Row From Previous
I am trying to create a query that will subtract the current row of a column from the previous row, and if the value is negative, just return the value of the current column in the result column.  For example, if I had the table below, here are the results I would like to see:

Key  value   REQUIRED RESULT
1      12        12
2      21        9
3      3         3
4      1         1
5      67        66 .

Previous Version Coding
I am trying to load some sql into myphpadmin and whilst I believe I have a later version of 5.0 which is causing the error (as this coding is apparently up to version 5.0), can someone suggest how I go about finding the coding to use with my version?

Here are some of the errors:
CODESET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"

Determine Previous Month
basically I need to display a list of "unpaid" statements for the previous Month, no matter what day the search/request is made on. I know MySQL has the DATE_SUB function where I can subtract 1 Month from the current date, but will that subtract the date exactly 1 month to the day ? Or will it simply give me the Month as I need ?
What happens if the search takes place on the 31st of a month (for example: July), yet the previous month only has 30 days (June). Will it roll back to the previous month correctly or think that the date is 31st June or try to roll back further until it finds another month which has 31 days ?

Choosing Name Of Table From A Previous Query
Got this working at last ...

Previous Versions Of MySQL With XAMMP
I've downloaded XAMPP for Linux 1.5.1 for use on Fedora Core 4. I notice it only has the latest version of MySQL on it. I'm working on two web sites (for different clients) and they are hosted on different shared Linux hosting servers (with PHP), each with different versions of MySQL available, i.e:

MySQL 4.0.25-standard
MySQL 3.23.52

Is it possible for me to use older versions of MySQL with XAMPP? Could I just turn off MySQL in XAMMP and just run and older version of MySQL independently of XAMMP? If I can then how would I install multiple versions of MySQL in Fedora Core 4?

Total Balance (sum Previous Row With Atual Row)
How to make a select table that give some results like this:

INCOME | OUTCOME | BALANCE
1000000 0 1000000
1000000 0 2000000
500000 0 2500000
0 1000000 1500000

Is it possible?

Getting Data Entered In The Previous Week
When a product is added to the database it has three seperate fields storing the date added in(01 - 31 format) month (January-December) and year( xxxx).

Is there anyway that I can run a mysql/php query that will just retrieve those products entered within the last xx weeks?

Compare Previous String In Resultset With Next
The code below does not work.

while(resultSet.next())
{
if (!resultSet.getString.next().equals(resultSet.getString.previous())
{
out.print("...");
}
}

Is it possible to compare previous string in a resultSet with the next string? If so, could you please show me an example.

Query To Find Data For Previous Week
I am trying to get a query that can find if a Loandate falls in last week...so basically check if loandate is between sunday or monday of last to friday or saturday of lastweek...

Any clues:

Select * from loan where last weeks sunday<Loandate>last week's saturday.

Selecting Previous And Next Rows Based On One Input
let me start by what i am doing:

SELECT msgid FROM messages WHERE uid=1;
output:
+----------------------------------+
| msgid |
+----------------------------------+
| 1588ad275a80f22e6206364abbba530a |
| 37ce77bea4481ab7ed2625055512467e |
| 4bf7e4cb13590da24625821c795eb8d7 |
| 57628792ba507161e3f73daff3c972b7 |
| 91e95b3fbe9f04467023cfe84e6ed6e2 |
| e710bb38be4f3cbd05b6db594f7e8805 |
+----------------------------------+

how would i get it so if i enter a value it will return the previous and next rows.

SOME_SQL WHERE msgid='57628792ba507161e3f73daff3c972b7' AND uid=1;
output:
+----------------------------------+
| msgid |
+----------------------------------+
| 4bf7e4cb13590da24625821c795eb8d7 |
| 57628792ba507161e3f73daff3c972b7 |
| 91e95b3fbe9f04467023cfe84e6ed6e2 |
+----------------------------------+

Get Previous And Next Records Relative To An Index Record
Suppose I have a table "product" with fields id,name,price. I want to
get a resultset of previous and current and next record when the
resultset is sorted by id and id=10. The table data is shown in the
following:

id Name Price
1 : :
5 : :<--------------previous record
10 : :<--------------id=10
12 : :<--------------next record
14 : :

How do I write the sql statement if I do not know 5 and 12 are the
previous and next id respectively?

How To Insert New Records Based On Previous Record Values?
I have
TABLE:

year
value

It has for example,

year, code, vlaue
2007, 33, 4883
2006, 33, 4883
2007, 32, 4883
2006, 32, 4883
2008, 31, 4883
2007, 31, 4883
2006, 31, 4883

I want to insert new records for for 2008 for all codes that do not already have a record for 2008 and increase the value for the 2008 record by 3%.

My several attempts have led to this but which does not work.

UPDATE TABLE a
SET a.value = b.value * 1.05
WHERE b.year = 2007
AND code NOT IN
(SELECT code
FROM TABLE b
WHERE year =2008
AND value !=0)

Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17):

"update user inner join events on user.username = events.username
set user.LastMessageDate = '$current_time'
where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'"

but fails on my production server running v3.23.56.

Anyone any ideas why it doesn't run and what I need to do to fix it?

If Condition Help
SET @RecordCount = 0, @PageCount = 0, @PageNumber = 0, @PageSize = 0; SET @PageSize = 10;
IF(1 > 1.0) THEN
SET @PageCount := (@RecordCount / @PageSize) + 1;
END IF;

Like Condition
How would I select everything that starts with a and b from the name column in the persons table?

I know how to get it for just people starting with a-

SELECT * FROM persons WHERE name LIKE 'a%'

But how do I do a and b?

Where Condition
I want to query my database like so...

$query = "SELECT * FROM wines WHERE restid =1 AND site_id_sask =1 ORDER BY name";

The only hitch is that in the restid field I would like to hold more than one entry. Meaning for some entries I would like to have the values 1, 2, 5, 9 for example. In another entry I may want to have 1, 5, 8.

Can I search this the restid field?

LIKE Condition
I have a field in a table called Title. I have a record whose Title field contains a value of Ford Mustang. When I run a LIKE on my table such as LIKE 'Mustang' my record does not shop up. I am assuming LIKE is for an exact match. What if I want any record that has EITHER Ford or Mustang in the Title.

WHERE Condition
I've had a bit of experience with MySQL and pgSQL databases and have never seen this type of thing before:

Code:

SELECT * FROM users WHERE id

This is just an example, but the point is what happens when the condition in the WHERE clause does not equate to anything? ie, shouldn't it be 'WHERE id = X' or something like that?
The query in question doesn't produce any errors, so is there something I've missed??

Update On A Condition?
I was wondering if its possible to do a different update based on a value in one table - specifically if I have:

table1
-T1_ID
-T1_Image
-T1_AdditionalImages (Bool)

and

table2
-T1_ID*
-T2_Image

obviously a v.stripped down version of my data structure, where the underlined T1_ID is a primary key, and the * is a foreigh key.

The logic behind it is that an item can have multiple (0..*) images - T1_AdditionalImages indicates whether there could be more images for this item in table2 (it is set to true when the item has 1 image added).

Now what I'd like to do is check whether T1_AdditionalImages is true, if it is true, then INSERT a new entry to table2 with the relevant details, if it is false then UPDATE table1.T1_Image to be the image details, and table1.T1_AdditionalImages to true.

Update And Sum Condition
I wonder if it's possible to update a field depending of a condition containing sum().
Example of what I want to do (strongly simplified, but easier to understand what I wanna do):

update customer, sales
set customer.field1 = "true"
where sales.custID = customer.ID
AND sum(sales.totalprice) > 1000
group by sales.custID

Without group by : "invalid use of group function"
With group by : error in SQL syntax.

SORT BY With Condition?
is there a way to do sorting depence of some condtion directly in the MySQL query?
for example, I have this query:

SELECT cat_id, cat_name, cat_date, cat_parent
FROM categories
ORDER BY cat_name ASC
but I'd like change sorting if cat_parent = 23 for example.
so, is there a way use something like:
SELECT cat_id, cat_name, cat_date
FROM categories
ORDER BY ((IF cat_parent = 23) THEN cat_date ELSE cat_name) ASC

I dont know MySQL at all, so this is just an example of condition idea that I'd like to use.

More Than One Condition In A HAVING Clause
I have tried to run a query where the reuslts are restricted by a
HAVING clause with 2 conditions, but the second condition seems to be
ignored.

for example

SELECT userid, AVG(position) FROM `table1` WHERE (status='finished')
GROUP BY userid HAVING (COUNT(*) >= 4 AND AVG(position) < 5.5)

returns exactly the same as

SELECT userid, AVG(position) FROM `table1` WHERE (status='finished')
GROUP BY userid HAVING (COUNT(*) >= 4)

even though there are values for AVG(position) returned that are > 6.

Is it the case that I can't have more than one condition in a HAVING
clause? have I got the syntax wrong? Is the behavior different in a
later version?

I am using "mysql Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu
(i386)"

Using OR In Join Condition
I Have this query which just takes too long and kills the server. I have 2 left joins and want to do an inner join matching values in either of the left joins. It seems to be the use of an OR in this inner join which is the bottleneck.

select * from
categories c
    INNER JOIN cat_subCat csc ON c.catId = csc.relCatId
    INNER JOIN subCategories s ON csc.relSubCatId = s.subCatId

    LEFT JOIN
    (
    SELECT sb_th.relSubCatId, thunderCatName, keywords AS th_keywords, relProdId as th_prod
    FROM subcat_thunderCat sb_th
    INNER JOIN thunderCategories th ON sb_th.relThunderId = th.thunderCatId
    INNER JOIN prod_thunderCat p_th ON th.thunderCatId = p_th.relThunderCatId
    ) as th_join
    ON s.subCatId = th_join.relSubCatId

    LEFT JOIN
    prod_subCats psc ON s.subCatId = psc.relSubCatId

    INNER JOIN tProducts p ON th_prod= p.productID OR psc.relProdId = p.productID

If Condition In View
Basically i want to add put a where condition in a view if the user variable '@org_id' is > 0. Here is some pseudo code:

CREATE VIEW view_students AS
SELECT *
FROM students
If (@org_id > 0) THEN
WHERE org_id = @org_id
END IF

I've tried lots of different approaches but i just cant figure out how to do it.

Index With Condition
Will a table index run faster depending on the order of the columns in it? My table has 500k rows and 600 columns. The search and sort queries run on subgroups: order by ssn where ssn like %555% and group = 2;

But these queries are _very slow. Do I create an index on group, ssn and lname, group? Or add more memory allocation to MYSQL?

Order By If Condition
I'm trying to return a list of products, where the top of the list will be one of 2 products, the choice being randomly selected. I tried a rand() condition in the ORDER BY clause, which partly works, but of course the rand() function is evaluated for every row, so it doesn't give the consistent results i need.

SELECT productID, prodName, adMessage, nPrice, nPrice*(1+vatRate) as salePrice, showSale, originalPrice, pic.picType, pic.ordering
FROM tProducts p
JOIN pictures pic ON p.productID=pic.relProdId and pic.picExists = 1
JOIN taxCodes tax ON p.taxCode=tax.taxCode
WHERE p.featuredProduct=1
AND p.stocklevel>-3
GROUP BY p.productID
ORDER BY productID=if(rand()<0.5,117029,117035 ) desc, rand()
LIMIT 10 .

Double Condition
I have a relatively straightforward SELECT statement but am running into problems:

SELECT sons.id
FROM sons, fathers
WHERE
(sons.father_id = fathers.id AND fathers.name = 'Bill')
OR (sons.father_id = -1);

So essentially I want to select all sons with a father called 'Bill' or with no father i.e. father_id is -1.

This works fine without the last line, but when I add the last line in, it outputs duplicate rows of each son for every entry in the fathers table. As I understand it, this is because I'm referencing the 'fathers' table in the FROM statement but if the father_id is -1 then there's nothing to join the two tables on.

How do I get around this? I'm at a loss.

Multiline Condition
there's something that I don't manage to do with mysql

exemple :
I've a values list like (22,3,12)
I've a table like :

idx | idy
----------
6 | 22
6 | 3
5 | 22
5 | 1
6 | 12

So, from the values list, and with only one request, I'd like to get the value "6", in this case, the value that is recored with each values of the list.

Search Condition
I like to call all teachers except math teachers.

I mean that every registered members who entered his job as teachers, but exclude(minus) math teachers.

It would be something like below

WHERE job like '% teacher%' and job like '% ! math%'.

Join Condition
i have 2 tables

users
relations

in the table relations i record
relations.user_id and
relations.connection

those two fields refer both to the same id in the users table.

Now to the problem:

I want to do a query for one user, and find his friends,

therefore i have to search both fields in the relations table, and then depending on where his id was found select the counterpart. (if his id was found in the relations.user_id i need the relations.connection and the other way round...)

now i do not only want the id of the friend but i would like to pull his details from the users table....

to be honest i tried a few things but i need a hint whether this is possible or whether i need two queries... or maybe a subquery.

Insert If Condition
I am trying to optimise my application. One of the parts of it is inserting a value into a table if the number of rows in that table with the same `date` field as the row to be inserted is less than 24
So currently I do two statements, one to check the number of rows and another to insert.
Is there any way to INSERT something if condition. I have looked as CASE but it does not seem to do what i want. So a mock up would be:

Code:

SELECT COUNT(*) FROM `table` WHERE `date`='2006-01-01'
If the value is < 24
INSERT INTO `table` VALUES (...)

Condition Preferred
Imagine a table of company addresses. A single company can add many addresses and edit them. A company can mark one and only one particular address as Primary. I want to do a select query as follows:
if the company has a marked Primary address then select it and no other address, else select all addresses (perhaps with some additional conditions) for that company
The query would give just one result for some companies and a number of results for others.

How To Avoid Race Condition?
How do I lock a table for one of my insert (followed by a read) queries on a table such that other simultaneous insert/read queries on that table are put off until the first one is complete? I am trying to avoid a potential race condition.

Putting A Condition On A Join
I have two tables: users and events. events has id, event_id, name, and date.

I need to select all users, but check if each has an event today or later, and if so select the date of the next event. But if there is no such event the user should still be selected.

Of course I'm using a left join, but the problem that is killing the query is the stipulation with the date being today or in the future. I'm trying something like:

select users.name, events.date from users left join events on users.id = events.user_id having events.date >= curdate();
But it returns no result if there is no such event for that user.