searching column if a value exists

Report · May 26, 2011

In my database I have columns named speakers, event, place and date booked.

I have entered info into the database.

Records:

speakers event place datebooked

John and Frank banquet home 2011-05-30

Pat Grad hall 2011-05-30

variables:

name: colname

type: text

default value: -1

runtime value: $_POST['speakers']

name: colname2

type: date

default value: -1

runtime value: $_POST['datebooked']

my sql statement:

SELECT speaks, datebooked

FROM bookings

WHERE speakerRequested = colname AND bookings.datebooked =colname2

Now my question is - if my speakers column contains two names, John and Frank, but I just want to see if Frank is booked for 2011-05-30 how do I format my sql statement to check the speakers field if there are multiple speakers for one event.

Thanks Winrol

Report · May 26, 2011

winrol wrote:
Now my question is - if my speakers column contains two names, John and Frank, but I just want to see if Frank is booked for 2011-05-30 how do I format my sql statement to check the speakers field if there are multiple speakers for one event.

Enter data into the database one row at a time like so:

Records:
speakers               event            place     datebooked
John                     banquet           home       2011-05-30
Frank                     banquet         home     2011-05-30
Pat                         Grad               hall         2011-05-30

Then you'll be able to filter by name and datebooked according to your SQL. What you were doing before is equivalent to asking how to separate a siamese twin.

Report · May 26, 2011

I thought of that but, in another page I have a table that shows all the speakers and events.

my sql for my Current events page:

Select * from bookings

and it shows all events that are currently booked. I have about 30 records which 10 have two or more speakers for one event. If I list them all individually I would have quite a long list. Which is not my goal.

Thanks winrol

Report · May 26, 2011

winrol wrote:
I thought of that but, in another page I have a table that shows all the speakers and events.
my sql for my Current events page:
Select * from bookings
and it shows all events that are currently booked. I have about 30 records which 10 have two or more speakers for one event. If I list them all individually I would have quite a long list. Which is not my goal.

What are you using the events and place columns for? You should be using them to dynamically categorize your 30 records and consolidate your page that displays all bookings. It's a lot easier to categorize rows than it is to split up values that are stored in one row. Both problems solved!

Report · May 26, 2011

Ok, sounds good but how would I accomplish that? My sql use has just been basics and most of is was in Access.

So if say I had:

speakers event place datebooked

John Banquet East Hall 2011-04-09

Frank Grad Theater 2011-05-11

John Grad Theater 2011-05-11

Sue Banquet School 2011-6-15

Adam Golf Pebble Beach 2011-07-30

What I would like the table to say:

speakers event place datebooked

John Banquet East Hall 2011-04-09

Frank and John Grad Theater 2011-05-11

Sue Banquet School 2011-6-15

Adam Golf Pebble Beach 2011-07-30

So my sql would be maybe something like:

Select * from bookings group by event, place order by datebooked asc;

Winrol

Report · May 26, 2011

You can change your recordset variable into LIKE statement. Because if you use EQUAL TO (=) the visitor of your site have to type exactly the same in your database, if don't, they will get nothing.

You made your WHERE clause:

WHERE speakerRequested = colname AND bookings.datebooked =colname2

Change that clause into:

WHERE speakerRequested '%colname%' AND bookings.datebooked '%colname2%'