I am running Slackware 13.0 and I have implemented postgresql + php + apache. I am attaching my database to my website via php. I have the following php code. I also have an html page that passes the data inputed on the forms to this php code. However, I want the users to be able to input either upper case, lower case, or a combination of upper/lower in the forms.
Code:
$result = pg_query($dbconn, "select game_id,title,developer,publisher,year,genre,platform,mode,requirements from pc_t where title='$_GET[title]' or
genre='$_GET[genre]' or year='$_GET[year]' or developer='$_GET[developer]' or publisher='$_GET[publisher]' order by game_id");
So I changed to code to this(I only changed the title part for testing purposes). Notice that I replace '=' with '~*' in the 'where title' section.
Code:
$result = pg_query($dbconn, "select game_id,title,developer,publisher,year,genre,platform,mode,requirements from pc_t where title~*'$_GET[title]' or
genre='$_GET[genre]' or year='$_GET[year]' or developer='$_GET[developer]' or publisher='$_GET[publisher]' order by game_id");
The title form works great. I could use any combination of upper/lower case strings. However, it seems that the wildcard character '*' did not end and continued to work across the other fields (genre,year,developer,publisher). For instance with the code directly above implemented I would go to the forms page and leave the title form blank. I would enter something in the genre field and run the query. The database would return all the records instead of the ones that were in that genre I inputed.
I tried enclosing just the title section in quotes. Why does the '~*' continue to work across the other fields?
I have tried using lower(col) which does work if the user inputs lower case ONLY chars. However, if it's all CAPS or a combination of lower/upper the query will not work successfully.
I may have to make case statements for each situation where a user only fills out one of the forms. However, since you can implement 'OR' in SQL I would rather not do this especially because the user can use two of the fields at once providing even more filtering efficiency. For instance, say a user enters a year, genre, developer, the results will be more efficient and accurate instead of putting only a genre.