Proper Syntax?

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
virgil
Forum Commoner
Posts: 59
Joined: Thu Jun 13, 2002 11:43 pm
Location: New York, U.S.

Proper Syntax?

Post by virgil »

Hi PHP's, :D


Does anyone know the proper syntax to query mysql for a substring in a string already residing in a column in a database.

I hope this isn't too confusing. :oops: I tried to keep it brief. I thought the easiest way to explain was to use examples....





This works great to select multiple records from multiple years when only one year has been set to "date_field" in the database.

Code: Select all

$date_field="2002" 
INSERT INTO etc....

To query the database...

Code: Select all

<select size="6" name="year_filter&#1111;]" multiple>
  <option>ALL</option>
  <option >2002</option>
  <option >2003</option>
  <option>2004</option>
  <option>2005</option>
  <option>2006</option>

Code: Select all

if($year_filter&#1111;0] == "ALL")&#123;

        $year_filter = array("2002", "2003", "2004", "2005", "2006");
&#125;

if($year_filter&#1111;0] != "ALL")&#123;
   $count_year = count($year_filter);
          if($count_year <= 1 )&#123;

                 $year_filter=$year_filter&#1111;0];
       &#125;
&#125;
$count_year = count($year_filter);
if($count_year > 1 )&#123;
        $year_filter = implode( "','", $year_filter);

&#125;

Code: Select all

$query = "SELECT * FROM table WHERE date_field IN ('$year_filter')";
This matches up a whole string(date_field) to an exactly matching array element($year_filter[any])...



But if you store an array initially in the database...

Code: Select all

$date_field=array("2002", "2003", "2004", "2005", "2006");
You implode $date_field[] to change it to a string so you can store in the database....

Code: Select all

$date_field=implode(",", $date_field);
Now $date_field is a string in the database (date_field="2002, 2003, 2004, 2005, 2006")


What is the proper syntax to retrieve records that contain the substring "2002" in the "date_field string" in the database?

Code: Select all

$query = "SELECT * FROM table WHERE date_field ????????????('$year_filter')";
Thanks so much for any help or advice...

Virgil :)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

http://www.mysql.com/documentation/mysq ... _functions
  • search for
  • LOCATE(substr,str)
  • POSITION(substr IN str)
  • LOCATE(substr,str,pos)
  • INSTR(str,substr)
virgil
Forum Commoner
Posts: 59
Joined: Thu Jun 13, 2002 11:43 pm
Location: New York, U.S.

Post by virgil »

Thanks Volka

Sorry for being dense...should have looked first.
But I knew this was going to get tricky...

That works for one substring...

$year_filter="2002"
date_field="2002, 2003, 2004, 2005, 2006"

Code: Select all

SELECT * FROM table WHERE LOCATE('$year_filter', date_field)";
But.....

What if $year_filter is an imploded multiple choice array converted to a string?

Then ...

$year_filter="2002,2004,2005"
date_field="2002, 2003, 2004, 2005, 2006"


I don't think I can make that work.

How to return all records with any of the years in $year_filter.

Is there a way to iterate through an arrays elements in a query statement?

Code: Select all

SELECT * FROM table WHERE LOCATE('$date_filter&#1111;0-5], date_field)";
The logic excapes me.

Thanks
:) Virgil
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

someone might gonna correct me but I think there is no such clever function.
Until a better solution is provided you may explode $year_filter and create a query-string that contains all elements as or-conditions.
i.e.
SELECT * FROM table WHERE LOCATE('2001', date_field)>0 OR LOCATE('2002', date_field)>0
Post Reply