Stupid (probably) PHP/MySQL question.

Factual Questions
1

Lets say I have a table with two columns: “id” and “name”. Data is entered in each in no particular order, so the table looks something like this:

id - name
5 - fff
20 - bbb
1 - www
37 - mmm
15 - hhh
…etc

I’m trying to figure out how to get the previous row and the next row, sorted alphabetically by name, for a given row. In other words, if I’m on mmm, I want to fetch the arrays for hhh and www.

Does that make any sense?

A little help?

Thanks.

2

I would do it with two selects.

  1. Select name from table
    where name > ‘mmm’
    order by name
    (take only the first record)

  2. select name from table
    where name < ‘mmm’
    order by name desc
    (take only the first row, again)

If that doesn’t make any sense, I probably didn’t understand your question correctly.

3

Also, make sure there is an index in the column “name”. Otherwise the machine is going to spend a bunch of time sorting on that column to deliver just one row of data in each query.

4

You can have the server take the first row for you:
SELECT * FROM table WHERE name > ‘mmm’ ORDER BY name LIMIT 1
SELECT * FROM table WHERE name < ‘mmm’ ORDER BY name DESC LIMIT 1

5

This site uses a MySQL clause called “LIMIT”. Maybe this would work:



Select id, name from table
where name >= 
  (select max(name) from table
   where name < 'mmm')
order by name
limit 0,3

The idea is to find the record with the “biggest” name that is less than the one you know. Then return all records with that name and “bigger” ordered by name. The limit clause limits the rows returned to the first, plus 2 more.

Complications arise when the row you know is the first or last. I leave that up to you!

6

This should work in any flavour of SQL:



SELECT Id, Name
FROM table
WHERE Name=(select max(name) from table
            where name < 'mmm');
union
SELECT Id, Name
FROM table
WHERE name = 'mmm';
union
SELECT Id, Name
FROM table
WHERE Name=(select min(name) from table
            where name > 'mmm')
order by Name;

It seems to handle the first and last problem.

7

Shows what a n00b I am. I didn’t even know that you can use > and < on alphabetical data, not just numbers.

Here’s my query:

$prev = mysql_query(“SELECT * FROM table WHERE name < ‘$current_name’ ORDER BY name DESC LIMIT 0, 1”, $db);

$next = mysql_query(“SELECT * FROM table WHERE name > ‘$current_name’ ORDER BY name LIMIT 0, 1”, $db);

This table does in fact have an index column, and I would have used that, but the table itself serves as an index to another table where each ID is in multiple rows. Kinda like the way that this board stores thread info in one table and post info in another.

Thanks for the help, folks.

8

Rather than start a new thread, I thought it would be better if I just asked my stupid (not probably, it really is) PHP/MySql question here. If this is bad etiquette let me know and I’m sorry.

Needless to say I’m a PHP/MYSQL newbie and I’m having trouble connecting. My pages are hosted by my University on a Universal Disk Space and I want to try and connect to mysql which is on my computer (our school uses Oracle, so maybe someone can show me a script on how to connect to a Oracle DB, the one the university gave me didn’t work :frowning: )
So I’m trying to connect to the localhost and I get the error msg:

Warning: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) in /net/muserver5/users/v/vingrirr/public.www/phpstuff/sqltest.php on line 4

public.www is our Universal disk space (where the php file is) and I assume /var/lib… means its looking for mysql.sock. Thing is, I can’t seem to find mysql.sock on my computer anywhere. And if I did have it, would I need it in the UDS or on my C drive?

Sadly I’m not really sure what’s going on here and php.net and mysql.com are becoming a blur to me I’ve read so much. The book I have doesn’t really help much if you get an error either.

Any help on how to atleast connect to mysql or an Oracle DB would be great. I’m running win98 and if you need more info I’ll tell you what I can. Thanks :slight_smile:

9

A better place to get your questions anwered might be http://www.phpbuilder.com/board/

They have a very friendly community and have helpfully and civilly replied to all of my posts.