Mysql upper / lower case

Peter B

I have a database of products from which I can peform a search. It allows me to search the name, description, category etc.

However, if I want to search for a product description which contains the string "red", it will not return any results which have the word "Red". ie - at the start if a sentence. It does not see this as a match due to it being case sensitive.

I am using the query

SELECT * FROM `products` WHERE `description` like '%red%'

Is there a mysql query which I can use which is case insensitive?

Thanks

Ph3n0m

LIKE should be returning case insensitive matches

What kind of coloumn is the description - text, blob, longtext?

Peter B

I'm very stupid...

Realise now 'like' is case insensitive.

Thanks for the help

Baz_

Not sure on the regexp side of your query - what do the % symbols mean?

Should it not be *

Again I don't know, I just put it out there for consideration.

Cake Fiend

Noe, in a MySQL query, % is the wildcard for any string containing zero or more characters.

Baz_

Sico wrote:

Noe, in a MySQL query, % is the wildcard for any string containing zero or more characters.

Ah, cheers Sico

Peter B

Maybe I should start a new thread for this, but my question is along the lines of this thread.

I have just completed an SQL query like

SELECT * FROM `products` WHERE `description` like '%red%'

It is completed in PHP and the results are displayed in a dynamic table.

When the results come up I have a choice if I want to delete certain results or not. If I click delete, I go through some more PHP pages, which using javascript, first confirm whether this is what is to be done, then proceed with deleting the product(s).

However then I return to my search results page which performs the same sql query again and displays the new results, which should not include the deleted products.

The problem I am having is how to store the sql query throughtout these pages. What I have been doing is saving it in hidden forms as hidden values.

<FORM Name="form2" method = POST action = "nextpage.php">
<INPUT TYPE=HIDDEN NAME=originalquery value = "<? echo $originalquery ?>">
</form>

On the following page I retrieve this variable to PHP using

while (list($key, $value) = each($_POST)) //$_POST is also HTTP_POST_VARS here
{

if (stristr($key, "originalquery"))
{
$originalquery = $value;
}
}

Then again I insert that as a hidden input into a form again.

However each time I go from page to page the SQL statement gets ammended from
SELECT * FROM `products` WHERE `description` like '%red%'
to
SELECT * FROM `products` WHERE `description` like \'%red%\'
to
SELECT * FROM `products` WHERE `description` like \\\\\\\'%red%\\\\\\\'

This query obviously causes an error when I finally return to my results page.

How do I avoid this problem? What other ways do I have of storing the SQL query from page to page so when I return to the results page the previous query can be performed and the results returned.

Ph3n0m

Oh that is very messy, can I ask why you have to do such a vague search and delete items that way?

When you delete them are they actually removed from the database itself, or just from the dynamic list

Peter B

To be honest, this is the first PHP site I have made. I have used JSP before.

What I am trying to create is a dynamic PHP Mysql database insert, edit, search, and delete. This will allow inexperienced MySQL users to operate it without being in danger of dropping tables / databases or having to know any SQL.

What I have is search results in table form with a checkbox for each item. You can select certain checkboxes corresponding to each item and decide whether you want to modify or delete those items by selecting either delete or modify radio buttons. After clicking submit on form (if you have selected delete) a javascript confirm askes you are sure you want to delete these certain products, listing the products. After that the products get deleted using an SQL statement in PHP. Then the browser gets forwarded back to the results page which queries the Database again (with the same original query) and displays the new results (the deleted items should not appear). The problem is storing the query.

Maybe I am doing this in a roundabout way. If anyone has any ideas / criticisms they would be greatly appreciated.

Ph3n0m

Ok that I understand, but are the items selected for deletion actually being deleted from the database or just from the query itself?

Peter B

Being deleted from the database

Ph3n0m

Ah ok - phew!

Ok what you might need to do is the following

Create your sql search statement in a seperate .php file as a function

e.g.


search($search_string){
insert sql statement and output, etc
}


Call this file function.inc.php

Now first page is called

search.php

Type the following

<?
include("function.inc.php");
search($search_string);
?>


Now on another page create your search form

<form method="post" action="search.php">
<input type="search_string">
<input type="submit" name="submit"></form>

When you do a search, the search.php should show your results based on initial search

Now after doing all your selection, etc and passing to various other php (assuming you are doing this via forms)

All you have to do now is include this

<input type="hidden" name="search_string" value="<? $search_string; ?>">

And when you have done your javascript processes, and you are being brought back to search.php via those specific pages, it should keep the search string intact and process it again.

Any problems pm me and I will see if I can help you out