[SOLVED] MySQL Query - struggling with logic

[giraffemedia]

Hi guys

 

i'm trying to select * from a table where a match from a string of numbers is found. This works fine but what i'd like to do now is find all rows where the selected number is found but not the next one i.e. find all records containing 75 but not 76.

 

I am using the following select query...

 

//Gets the selected number from a variable in the URL - something like 34 or 78

$issue = $_GET['search_by_issue'];

$query = "SELECT * FROM booking_form WHERE bf_issues_booked LIKE '%$issue%' ORDER BY bf_id ASC";

 

but i don't know how to say something like...

 

$query = "SELECT * FROM booking_form WHERE bf_issues_booked LIKE '%$issue%' BUT NOT LIKE $issue + 1 ORDER BY bf_id ASC";

 

This is so I can tell who who bought what magazines and at what issue they stopped buying them.

 

Does anyone know how I can get this working?

 

Thanks

 

James

[samshel]

what is format of the column bf_issues_booked ? and give an example value.

[giraffemedia]

It's a comma delimited string and looks like  34,35,36,37,38

 

What I want is to find all rows where 38 is found but not 39.

 

i.e. 38,39,40,41 wouldn't apply because it contains 38 AND 39.

 

Thanks

 

James

[samshel]

$issue = $_GET['search_by_issue'];
$nextissue = $issue + 1;
$query = "SELECT * FROM booking_form WHERE 
(bf_issues_booked LIKE '$issue,%' OR bf_issues_booked LIKE '%,$issue' OR bf_issues_booked LIKE '%,$issue,%' OR bf_issues_booked LIKE '$issue') AND 
(bf_issues_booked NOT LIKE '$nextissue,%' AND bf_issues_booked NOT LIKE '%,$nextissue' AND bf_issues_booked NOT LIKE '%,$nextissue,%' AND bf_issues_booked NOT LIKE '$nextissue')

 

you need 4 conditions to check 1 number in a comma seperated string

 

example:

 

38,39,40,41

 

 

if you do "%39%", it will also match 139 or 391, these 4 conditions check for 39,.. ...,39 ...,39,.. and even only 39.

 

hope i did not confuse you

 

PS: query is not tested.

[giraffemedia]

Sorted it!

 

Simple really - I added another variable and added one to it and forgot to repeat the column name in the NOT LIKE bit of the mysql query.

 

 

This works fine...

 

$issue = $_GET['search_by_issue'];

$issue2 =  $_GET['search_by_issue'] + 1;

$query = "SELECT * FROM booking_form WHERE bf_issues_booked LIKE '%$issue%' AND bf_issues_booked NOT LIKE '%$issue2%' ORDER BY bf_id ASC";

 

Regards

 

James

[samshel]

please consider the example i gave using '%$issue%' can cause you issue some times..please read above post.

[giraffemedia]

I'll change my query to reflect that samshel - you posted just as I was pressing post myself!! - thanks for your help.