1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

A little help with an SQL Query [Webcalendar]

Discussion in 'HTML, Graphics & Programming' started by Mister_Pister, 3 May 2006.

  1. Mister_Pister

    Wise Guy

    Joined: 6 Feb 2003

    Posts: 1,105

    Location: London

    Ok I dunno if anyone can help with this but im using webcalendar and its search function while ok only searches the entries table and doesnt search for anything in the extra fields which have been added in the table webcal_site_extras. The field in there that needs searching aswell is cal_data.

    This is currently what the search query does when searching for "test", the stuff with the user table is just to do with who can access the events, im not too bothered about those.

    Code:
    SELECT webcal_entry.cal_id, webcal_entry.cal_name, webcal_entry.cal_date FROM webcal_entry, webcal_entry_user 
    WHERE webcal_entry.cal_id = webcal_entry_user.cal_id 
    AND webcal_entry_user.cal_status in ('A','W') 
    AND webcal_entry_user.cal_login IN ( 'chris') 
    AND ( webcal_entry_user.cal_login = 'chris' OR ( webcal_entry_user.cal_login != 'chris' 
    AND webcal_entry.cal_access = 'P' ) ) 
    AND ( UPPER(webcal_entry.cal_name) LIKE UPPER('%test%') 
    OR UPPER(webcal_entry.cal_description) 
    LIKE UPPER('%test%') ) ORDER BY cal_date
    
    My attempt at rewriting it was:

    Code:
    SELECT webcal_entry.cal_id, webcal_entry.cal_name, webcal_entry.cal_date, [COLOR=YellowGreen]webcal_entry_site_extras.cal_data[/COLOR] 
    FROM webcal_entry, webcal_entry_user, [COLOR=YellowGreen]webcal_site_extras[/COLOR] 
    WHERE webcal_entry.cal_id = webcal_entry_user.cal_id 
    AND webcal_entry_user.cal_status in ('A','W') 
    AND webcal_entry_user.cal_login IN ( 'chris') 
    AND ( webcal_entry_user.cal_login = 'chris' OR ( webcal_entry_user.cal_login != 'chris' AND webcal_entry.cal_access = 'P' ) ) 
    AND ( UPPER(webcal_entry.cal_name) LIKE UPPER('%test%') 
    OR UPPER(webcal_entry.cal_description) LIKE UPPER('%test%') )
    ORDER BY cal_date
    
    That just permanantly lags the server though and doesnt do what its meant to, probably to do with the where clause. Site extras primary key is the ID of the calendar entry btw. If anyone has used webcalendar before or has an idea how I can get it to search the additional field in the site_extras table please let me know :)
     
    Last edited: 3 May 2006