Searching

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Dr. PHP
Forum Newbie
Posts: 11
Joined: Sun Nov 24, 2002 8:46 am

Searching

Post by Dr. PHP »

I've got a question....I have a database full of news articles, dates they published, category they are under, and author. If someone wanted to search by articles published during 11/25/02 and 11/29/02 under the Communications category, is that real hard to do? Is there anyway to string together SELECT * FROM WHERE clause things using an && operator to get proper results like this?
Gen-ik
DevNet Resident
Posts: 1059
Joined: Mon Aug 12, 2002 7:08 pm
Location: London. UK.

Post by Gen-ik »

Something like this might help... or get you started...

articles published during 11/25/02 and 11/29/02 under the Communications

Code: Select all

<?php
mysql_query("SELECT * FROM yourtablename WHERE published='11/25/02' AND category='Communications'");
?>
Not too sure about the 'between these dates' thing myself to be honest.

I wouldn't mind findinf that out either :)


Hope this helps.
Dr. PHP
Forum Newbie
Posts: 11
Joined: Sun Nov 24, 2002 8:46 am

mmm.

Post by Dr. PHP »

It seems that searching is so easy, but every other search script I look at is HUGE. Why are they so big when it can be accomplished with one command?
Gen-ik
DevNet Resident
Posts: 1059
Joined: Mon Aug 12, 2002 7:08 pm
Location: London. UK.

Post by Gen-ik »

Getting information from a Database is the easy part.. it's what you want to do with the info afterwards that makes the scripts BIG.
Dr. PHP
Forum Newbie
Posts: 11
Joined: Sun Nov 24, 2002 8:46 am

Oh.

Post by Dr. PHP »

mmm, well that's interesting! I wish there was a PHP chat I could sit there for days asking questions trying to get my project work.

I'm trying to think of a good solution for my problem....there will be about 6 different ways of searching (for last name, for first name, for paper name, by date, or by headline) but they can do more than one at a time. I guess I will have to somehow check to see which fields were filled in before I can process the search?
Gen-ik
DevNet Resident
Posts: 1059
Joined: Mon Aug 12, 2002 7:08 pm
Location: London. UK.

Post by Gen-ik »

You could do a multiple search by calling more than one mysql query, for example..

Code: Select all

<?php
$SEARCHa=mysql_query(" SELECT * FROM here WHERE a='this' AND b='that' ");

$SEARCHb=mysql_query(" SELECT * FROM there WHERE a='something' AND b='something else' ");
?>
..you can do as many of these as you want.

You would then need to do a mysql_num_rows command for each result.

...........if that makes sense.................................................................
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

I would be inclined to build the SQL where clause depending on which fields are be filled out. For instance:

Code: Select all

<?php
if (isset($pubfield) and ($pubfield != "")) {
   $pubfieldwhere = "and published = '$pubfield' " ;
}
if (isset($catfield) and ($catfield != "")) {
   $catfieldwhere = "and category = '$catfield' " ;
}
// ... check more fields

$sql = "select * from yourtablename where 1 = 1" .
   $pubfieldwhere . $catfieldwhere ; 
// sql assignment will have one "fieldwhere" for
// each form field you want to include in the query

$SEARCHb=mysql_query($sql) ;
?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

On a side note, if you want to search for all records between two criteria you can use the BETWEEN operator:

Code: Select all

SELECT field1, field2, field3 FROM table WHERE date BETWEEN '2002-11-25' AND '2002-11-29'
Mac
Post Reply