Quick Question about SELECT

nca

How do you write a mysql select query that will look for a string in multiple columns? Here is a logical (incorrect) example:

SELECT COL1, COL2, COL5, COL32 FROM TABLE WHERE ???? LIKE "%$string%"

I would want it to look at the selected columns only.

And one more thing, how could I split the results up into pages with 10 records at a time?

Thanks!

mikeatrpi

SELECT a, b, c FROM table WHERE a LIKE '%string%' OR b LIKE '%string%' OR c LIKE '%string%';

Also look into FULLTEXT indices when mySQL 4 goes live.

nca

How could I split the results up into pages with 10 records at a time, using PHP?

ereptur

That's more of a question for a tutorial, but here is an example.

if (isset($_REQUEST["min"]))
    $min = $_REQUEST['min'];
else $min = 0;

$query = "SELECT a,b,c
          FROM table
          WHERE a>$min
          ORDER BY a
          LIMIT 10";
$result = mysql_query($query);
$nextset = $min + 20;
$prevset = $min - 20;

In this example, a would be your indexed number or unique identifier.

nca

Does anyone know of a good tutorial about this kind of thing?

ereptur

I can recommend a good book.

Try looking over Beggining PHP4.
You can find all the information for the book at http://www.amazon.com/exec/obidos/tg/detail/-/1861003730/ref=pd_sim_books_1/103-8230699-1890256?v=glance&s=books

This book has a great tutorial for doing limited lists

mikeatrpi

LIMIT can take two parameters, an offset and count.

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. The arguments must be integer constants. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): To be compatible with PostgreSQL MySQL also supports the syntax: LIMIT # OFFSET #.
mysql> SELECT FROM table LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset upto the end of the result set, you can use -1 for the second parameter:
mysql> SELECT FROM table LIMIT 95,-1; # Retrieve rows 96-last.
If one argument is given, it indicates the maximum number of rows to return:
mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.

Taken from:
http://www.mysql.com/doc/en/SELECT.html

cgraz

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10219415

There's commented code I wrote a while back for someone looking to do the same thing. Just change the variable $pagelimit to how many results you want to display per page. (I suggest you read through it and study the code before copying and pasting).

Cgraz

nca

Thanks cgraz! I went on amazon and bought "PHP For the world wide web." I have that same book about CGI. Hopefully that will enlighten me more about PHP. My whole entire site used to be ran with cgi and flat file. I have found that PHP and MySQL is much better!