PHP and Mysql help

Bonsatsu

I have a script that allows you to search for items, they can be searched by one or more different fields. I made it so that if a field is blank it set to *, however it doens't work.

SELECT * FROM items WHERE itemname = 'sword' AND itemtype = '*' AND itemcost = '*'

that's the query when they only searched for sword, but the script crashes. Any ideas on what's up? Thanks in advanced.

Edit: Didn't see a PHP and SQL forum, sorry. If a mod or something can move this, that'd be great. Thanks

thorpe

if you change your AND's to OR's it should work.

Roger_Ramjet

Do you mean that you are actually storing an asterisk in the column if no data was supplied for that column - the default value is '*' ??

Or do you mean that you substitute an * when the user does not supply any data for that field when they search ??

In mysql, the wildcard symbol is % not - means select all fields in SQL.
If you are searching and want to match everything in a specific column - a wildcard search - then you just omit that column from the where clause.

If you are storing an * then you would be better to just leave the default values for the columns to their native defaults for the data type, or NULL in which case you use IS NULL or IS NOT NULL in your query.

bradgrafelman

Roger, I think he means just in his SELECT statement when searching.

What Roger said about the % in SELECT values is true. * and % are both wildcards in MySQL, yes, but they can not be used interchangably 😉

When using SELECT statements, a '%' in a value is equivalent to a '' in DOS-like searches ("dir .txt"), and I believe a '_' is equivalent to a '?' in a DOS-like search.