Select Previous And Next Id After Id=# ?

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?

---

Previous To Last With A Certain WHERE Condition
How do i fetch the previous row record with the same WHERE when the last record has only a lately date.

for example

field CConsorcio CRubro ImporteA FechaA

1 105 1000 2006-05-10
2 110 30 2006-05-12
5 105 300 2006-05-13
4 104 600 2006-05-14
2 103 800 2006-05-15

the WHERE CConsorcio=2
the last record: 2,103,800,2006-05-15
and i want to select the row: 2,110,30,2006-05-12

---

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?

---

Mysql Is Giving The Previous Month, Not This Month, Strange!
PHP Code:

$myquery = @mysql_query("select year(sendon) as yr, month(sendon) as mth, count(*) as hits from ebook_user group by yr, mth");
    while ($myrow = mysql_fetch_array($myquery)) {
    $mymonth =  $myrow['mth'];
    $myyear =  $myrow['yr'];
    $myhits = $myrow['hits'];
    print '<td bgcolor="#E7E7E5">'.date("M", mktime(0, 0, 0, $mymonth, 0, $myyear)).' '.$myyear.' </td><td bgcolor="#E7E7E5"> '.$myhits.'</td>'
    }


The above code is printing the previous month instead of current month ... can any one help?

---

Query Based On Results Of A Previous Query
So far I have managed to construct one query which gives me all individuals that have one of three titles.

based on this I now want to find all the individuals that are affiliated to those listed in the first query ....

---

SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity.

Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ?

How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?

---

Combining SELECT Statements Into One SELECT Statement.
I want to take the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN authuser
ON name.empnumber = authuser.uname AND authuser.team = 'PHQ'
ORDER BY name.lastname, name.firstname;


and the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN crew_attendance_6QJ
ON name.empnumber = crew_attendance_6QJ.empno
ORDER BY name.lastname, name.firstname;


And combine them into one query that outputs all of the results both queries would output. Then order those results.

So far I have come up with:

---

Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.

---

Select From Two Different Tables Depending On Result Of First Select
I have three tables A contains three keys (there is other stuff but it's not relevent to this problem):

  it's own pk,
  fk to table B
  fk to table C

if the table B fk is not zero then select various values from table B,
if the table C fk is not zero then select varios values from table C

The two fk are never both zero and never both non zero

The best query I've got so far is

select distinct l.job_parts_id, l.part_id, l.oil_id, l.quantity, l.purchase_price, l.sale_price,

p.part_no, p.description, p.supplier_id,
o.part_no, o.description, o.supplier_id

from job_parts l, parts p, oil o
where task_id = 3
and p.part_id = l.part_id
or o.oil_id = l.oil_id

order by job_parts_id;

the problem at the moment is that when the fk to table c is zero, the result set still contains the values from it's selected fields, I'm trying to work out how to use an IF statement to sort this out,

---

Speed Diff Between Select * And Select Column
I have this question that I cannot find a right answer among my friends.

Will like to know:

Assuming I have a table with 8 columns.

When I am querying these records with specific WHERE conditions,
will SELECT * be faster, or SELECT (3-5 x columns) be faster?

---

Select Lastest Input To Data Base And Select Ordering From Last Member To First Member, Limiting Output Diplay To A Specific Number
how to display the latest members that signup to website, a query that that can select ordering them base on the last registered member to the first... then limiting output by say 50 members.

a sql statement that can select lastest input to data base and select ordering from last member to first member, limiting output diplay to a specific number

---

SELECT COUNT(*) Or SELECT .. LIMIT 1?
If the purpose of a query is to see if atleast 1 record exists which would be faster?

SELECT COUNT(*) or SELECT .. LIMIT 1?

Both would use a WHERE clause.

---

What's The Best SELECT In This Case / How Can I Make This SELECT ?
well, step 1, I need to get a few ids like:

PHP

SELECT id FROM $table_one WHERE this_field=$that_value // this is OK


I will get multiple rows from this command

Then, I need my main SELECT command (that i need to use) like:

PHP

SELECT * FROM $table_two AS t2 LEFT JOIN $table_three AS t3 ON t2.pid = t3.pid WHERE t2.pid = (all the ids that I have got from my first SELECT)

---

Difference Between Select * And Select Distinct *
What is the difference between SELECT * and SELECT DISTINCT *
Which one is faster?

---

Select Constraint (select ... Where X) If 'where X' Isn't Specified
I'm working on a site where records (say articles) can exist in one of three states: active, archive, trash. I'm doing this so that I can implement soft delete and non-destructive action.

For some brief background, we used to simply have an 'archive' column with enum('n','y'). Which meant that every query had to be appended with "where archive='n'". Later we modified the architecture of the application be more robust so that you could publish records (basically duplicate the records into a table called articles_instance; so you can publish as many versions of a master copy as you want). This worked well enough that when I decided to implement soft-delete, I decided to move the deleted record into the articles_instance table. I created a new column ('state' again) with 'publish', 'archive', and 'trash'.

So whenever I query the instance table, I opt in "where state='publish'" instead of having to opt-out, like before ("where archive='n' and trash='n'". This was better, but I realized that I had to save the old record id in a column 'old_id' because I wanted to be able to send the row back into the regular 'articles' table if the user chose to undo the action or unarchive an article. I decided to keep the old id, rather then send it back with a newly generated id because there were certain relations I needed to keep, like associated audio files with the article (temporary broken links aren't as much of a problem because you can only see the audio when you look at the article; when the article is permanently deleted, then I can remove the relations).

This approach works, but I feel it's messy, and with all the code I'm writing to shuttle the rows back and forth, I feel as if there's only more chances for error. I like the simplicity of simply setting a state in the master 'articles' table. But then I have the problem of having to specify "where state='active'" in all of my queries. If I don't do that, I risk pulling all possible articles, regardless of state, causing strange problems. So here's the latest breakdown:

The articles table has a 'state' column with enum('active', 'archive', 'trash').

The articles_instance is now called 'articles_publish' and no longer has a state column. So it is used exclusively for published articles.

It boils down to this: Is there a way, in MySQL 4 (I don't know the exact version, but I can find it if necessary) to constrain/modify all of my select statements to have an implicit "where state='active'" if a state isn't explicitly specified? If I could do this, I feel I could have the best of both worlds: have two tables with specific needs, where one table has data that can exist in different states, and I wouldn't need to move rows back and forth, possible causing problems. And if I implement the constraint, I don't have to worry about going through and changing a lot of existing code and making sure the problem doesn't crop up in the future.

I am providing all this background in the hope that someone might just suggest a better and more efficient way of doing this and tell me that a select constraint is entirely unnecessary . I might also be convinced that using MySQL as a crutch in this case might be a poor idea and could potentially confuse programmers down the line (although there's only two of us).

If it's not already clear, I'm new to all of this, so I am not really familiar with best or standard practices when it comes to these sort of things. If anyone wants to refer me to any resources (books or articles) about versioning/publishing records or database design in general, I'd love to hear them.

Thanks!

---

Select With Select In Where Clause
This is a very simplified SQL statement intended to reproduce an error, not do anything interesting:

select * from value where id in (select id from value where id=1000000);

It fails with:

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select id from value where id=1000000)' at line 1

However, the following works

select * from value where id in (1000000);

---

Select In Select Statement
Could any give me an example of how this works please as for the life of me cant get any results.Basically I have 3 tables, I record home team and away team results in the RESULTS table and up and coming fixtures in the FIXTURES table.

Im wanting to get all my home teams previous results where they are playing at home in an upcoming fixture but also want to return for the same fixture the away teams previous results, I dont even need TEAM table to be honest but thought may be easier to create a join between the two tables.

---

Count(*) In A Select Returns &quot;1&quot;. It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost:

SELECT *,count(*)
FROM cancons c, musics m, discos d, r_discos_cancons rdc
WHERE c.c_id_music = m.m_id
AND rdc.rdc_id_disc = d.d_id
AND d.d_id_music = m.m_id
AND m.m_id = 24
GROUP BY c_id

note:
cancons (ca) = songs (en)
discos (ca) = cd's (en)
music (ca) = musician (en)

don't worry for the WHERE part. i need it because of the foreign keys.
this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think.

I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.

---

Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with.

My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear.

What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear.

My Table...

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
4..............QD...................4......................2007 (current month and year)
5..............QE...................5......................2007
6..............QF...................6......................2007

I can simply get the question for the current month...



SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007;
but if I try to get all others using


SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007
I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is


id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
*********************(remove runMonth4 and runYear 2007)
5..............QE...................5......................2007
6..............QF...................6......................2007

What am I missing?

---

"Select * FROM" Vs "Select Field1,field2 FROM"
No matter how many fields I want to retrieve from the table I always use "Select * FROM Table" instead of "Select field1,field2 FROM Table".

Are there any disadvantages of always using "Select * FROM Table"?

---

Select Only 1 Where There Is 0 Or More
I have a simple query that selects some unprocessed items from a batch or orders. I want to add one small twist to it. There could be multiple open batches at a given time; I only want to get the oldest outstanding batch. There could be multiple orders in a batch (in fact their should always be ); Again, I want the oldest first. Multiple items per order (ordering is not important), and each item can contain zero or more "options" or "attributes" (attributes have a 1 to 1 relation to options). When I do my SELECT, I would only like a single item returned a long with all of its attributes/options. A LIMIT can't work here because there may be multiple options. I would prefer not to break it out in to multiple queries, but I can understand if that's the easiest way. This query would be run very often, but the dataset will always be rather small.
Current query (returns everything):
SELECT Batches.batch_id
, Orders.order_id
, Orders.store_prefix
, Orders.number
, OrderItems.item_id
, OrderItems.code
, OrderItems.name
, OrderItems.quantity
, OrderItemOptions.option_id
, OrderItemOptions.attribute_code
, OrderItemOptions.attribute_prompt
, OrderItemOptions.option_code
, OrderItemOptions.option_prompt
FROM Batches
INNER
JOIN Orders
ON Orders.batch_id = Batches.batch_id
INNER
JOIN OrderItems
ON OrderItems.order_id = Orders.order_id
LEFT OUTER
JOIN OrderItemOptions
ON OrderItemOptions.item_id = OrderItems.item_id
WHERE Batches.batch_id > 0
AND Orders.status = 1
AND OrderItems.qty_have IS NULL
AND OrderItems.qty_standby IS NULL
AND OrderItems.qty_backorder IS NULL
AND OrderItems.qty_refund IS NULL
ORDER
BY Batches.batch_timestamp
, Orders.date
, Orders.store_prefix
, Orders.number

---

Select * Except For??
is there a way to select everything EXCEPT for something

like SELECT * FROM table WHERE id != 'bob'

what would the proper syntax be?

---

Select * From ... In()
All right here is the question I'm having. I have a mysql queries that use SELECT * FROM ... IN() or SELECT * FROM ... NOT IN(). I don't know why the people before did it that way but when you have into the IN() an array with 25,000 items and do that twice or three times in one page you need to wait a long time. So, I'm trying to optimize those queries. I have couple ideas how that might be done, but I wanted to know if someone out there had that problem and he/she solved it.

here is an example query I have where $compids has around 25,000 values.

SELECT * FROM mfg_people WHERE company_id IN (".implode(", ", $compids).")

---

Need Help With Select
HTML
<form id="searchform" method="post" action="search.php">
<input type="text" name="search" id="search"><input type="submit" value="Go!">
</form>
search.php calls the following function

PHP

function SearchResults() {    $sres = $_POST["search"];     $q = sprintf("select * from stock where name like "%%%s%%"",$sres);    $resultid = mysql_query ($q);    if($resultid) {        SearchResultsName($sres);        if(mysql_num_rows($resultid) > 0) {            ProductTemplate ($resultid);            }        else {            print "<div>
<p>The are currently no items within these search terms</p>
</div>";            }        }}

At the moment the database only has two entries who's name field values are.

1. Brio Safari Figure 8 Set
2. Brio Grand Turntable

So if someone searches for brio it will return both entries, if somone searches for brio safari it will only return entry 1, however if someone searches for brio turntable it will not return entry 2.

---

Sub Select ?
I am trying to select a user profile from a table and if applicable, show how many properties and buyers are associated with that profile.

I have three tables, the main table is profiles and 2 other tables are properties and buyers. The profiles table is always populated but the other 2, properties and buyers, might be populated or might be empty.

This code works when buyers and properties are populated, but I just realized that if buyers or properties are empty (no records at all) for that profile ID, no profile record is returned.

The end goal is to always have the profile shown and show the count of properties and buyers where applicable.

Maybe using subselects aren't the answer to my solution.

Hopefully I won't have to resort to my specialty - the old "3 query's instead of 1" trick.

++++++++++++++++++++++++
SELECT name, profiles.ID,

(SELECT count( id )
FROM properties
WHERE user_id = '".$ID."' AND properties.active <> &#390;' )prop_count,

(SELECT count( id )
FROM buyers
WHERE user_id = '".$ID."' AND buyers.active <> &#390;' )buyers_count

FROM profiles, properties, buyers
WHERE profiles.id = '".$ID."'
AND properties.user_id = profiles.id
AND buyers.user_id = profiles.id

LIMIT 1

---