Using LIKE in MySQL with a variable

Question

OK so I would like to get a match for a query of the database using a LIKE operator in my SQL statement.

I'm looking at a field that has a date and time in it, like this

2012-04-27 12:00:00

I only want to compare the first part of the string, just the date bit, so I was using LIKE but I don't get any matches.

Here's the code

$colname_cruises = "-1";
if (isset($_GET['id'])) {
$colname_cruises = str_replace('-', ' ', $_GET['id']);
}

$colname_date = "-1";
if (isset($_GET['date'])) {
$colname_date = $_GET['date'];
}

$query_cruises = sprintf("SELECT * FROM cruises WHERE title = %s AND departs LIKE %s", GetSQLValueString($colname_cruises, "text"), GetSQLValueString($colname_date, "text"));
$cruises = mysql_query($query_cruises, $connection) or die(mysql_error());
$row_cruises = mysql_fetch_assoc($cruises);
$totalRows_cruises = mysql_num_rows($cruises);

I'm pretty certain just doing a LIKE %s isn't enough... what else can I add in here ?

The % (what you need also for the LIKE)is getting replaced by the sprintf-function Parameters, right? — devOp
– devOp, Commented Aug 22, 2012 at 9:05
what is the datatype of your column where you save date and time — xkeshav
– xkeshav, Commented Aug 22, 2012 at 9:05
datatype is datetime, I've just modified the GetSQLValueString to date... still an empty query coming back — Rich
– Rich, Commented Aug 22, 2012 at 9:07
Try LIKE %%%s%% which will be translated to %your_date% by sprintf and will mean "your_date maybe prefixed by something and maybe followed by something". You may have to tweak a little depending on wether your GetSQLValueString function adds quotes ... — yent
– yent, Commented Aug 22, 2012 at 9:07
ok that returns this 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 '%'2012-04-27'%' at line 1 — Rich
– Rich, Commented Aug 22, 2012 at 9:08

xkeshav · Accepted Answer · 2012-08-22 09:08:26Z

1

if you only want to compare the first part of the string, just the date then why dont you use MySQL DATE() which

Extracts the date part of the date or datetime expression expr

 SELECT DATE('2003-12-31 01:02:03');
   -> '2003-12-31'

answered Aug 22, 2012 at 9:08

xkeshav

54.2k47 gold badges183 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rich Over a year ago

perfect, I used "SELECT * FROM cruises WHERE title = %s AND DATE(departs) LIKE %s" works a treat , thanks everyone / #diEcho

Mina Abadir · Accepted Answer · 2012-08-22 09:08:56Z

1

For date comparison you can use between. Try to use something like that:

departs between <date> and DATE_ADD(<date>,INTERVAL 1 DAY)

answered Aug 22, 2012 at 9:08

Mina Abadir

2,9812 gold badges17 silver badges20 bronze badges

Comments

Er. Anurag Jain · Accepted Answer · 2012-08-22 09:16:56Z

0

Please try

 "SELECT * FROM cruises WHERE title = '".$colname_cruises."' AND departs LIKE '".$colname_date."%'";

If you want to fetch record where departs value starting string containg this date then you have to use Like $str%.

thanks

answered Aug 22, 2012 at 9:16

Er. Anurag Jain

1,7931 gold badge11 silver badges19 bronze badges

Collectives™ on Stack Overflow

Using LIKE in MySQL with a variable

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related