php mysql like statement that takes several values from an array of numbers

Question

I have a php mysql query below that works great when all rows within the current day (-13-) in a date (05-13-2014):

$h = $time->igetDay();
$h = "%{$h}%"; 
$result = query("SELECT * FROM table_name WHERE table_column LIKE '%s'", $h);
print json_encode($result);

What I am trying to do now is get all the rows within a current week range. I created a function that returns an array of the 7 upcoming days as shown below:

$h = $time->igetWeekRange();
print_r($h);
//this displays: Array ( [0] => 13 [1] => 14 [2] => 15 [3] => 16 [4] => 17 [5] => 18                     
[6] => 19 [7] => 20 )

//I can also display the array like this:
Array ( [0] => -13- [1] => -14- [2] => -15- [3] => -16- [4] => -17- [5] => -18-                     
[6] => -19- [7] => -20- )

My problem is that I don't know how I would use the query: $result = query("SELECT * FROM table_name WHERE table_column LIKE '%s'", $h); In order to display all the rows that have the seven values of the array above.

What this query should do is display all the rows of the table that have a date within the next seven days of the current day. How can I do this? Let me know if there is anything else I could provide to help you understand better what I am trying to do.

Thanks in advance, any help would be greatly appreciated.

would probably do a query where date is less than current date plus 7 days in mysql format instead of what you're trying to do here. — Bryan
– Bryan, Commented May 13, 2014 at 9:19

Rahul Kaushik · Accepted Answer · 2014-05-13 09:22:21Z

1

Try below:

$h = $time->igetWeekRange();

foreach ($h as $val) {
    $query_parts[] = "'%".mysql_real_escape_string($val)."%'";
}

$string = implode(' OR table_column LIKE ', $query_parts);

$result  = query("SELECT * FROM table_name WHERE table_column LIKE {$string}");

print json_encode($result);

answered May 13, 2014 at 9:22

Rahul Kaushik

1,4648 silver badges16 bronze badges

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

1 Comment

user3007683 Over a year ago

Hi, I tried this and it worked, I just had to make changes because my query function takes in variables as the values to query, so I just set day1 = $query_parts[0];...etc up to day 7. Everything works perfectly now. I do have 1 question, is the way that I am dealing with querying values bad regarding performance?

ksealey · Accepted Answer · 2014-05-13 09:39:59Z

0

When working with things like this I store my time in epoch so it is as simple as doing a little math to get whatever you want.

<?php
    $the_date =new DateTime("2014-05-13 10:46:09"); //Or new DateTime() for current date
    $date_in_epoch_time= $the_date->format('U');
    $a_week_ago=$date_in_epoch - (7 * 24 * 60 * 60)); //subtract a week in seconds
?>

ASSUMING That you'll convert the time into epoch time before inserting it into the database, you now have the date in seconds so if you want items in a database that are from a week previous you can do:

"SELECT * FROM table_name WHERE other_table_column >= $a_week_ago AND table_column LIKE '%s', $h";

answered May 13, 2014 at 9:39

ksealey

1,7381 gold badge17 silver badges16 bronze badges

Collectives™ on Stack Overflow

php mysql like statement that takes several values from an array of numbers

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related