8 replies to this topic

[Blue Indian]

I am trying to implement a search function that searches a coffee database for all coffees containing all or part of the user's entered search string. The user enters a search string which I store below in a variable called $name.

I want to use this variable in a SQL statement and store it in a variable called $sql.

The following is a code segment that is producing a syntax error:

I am pretty sure the error is being caused by the where statement where I am trying to concatenate the $name variable into my sql statement.

$name = $_GET['name'];
        
        $sql = "
                select Coffee_SKU, Coffee_Name, category_name, coffee_details, coffee_price,
                coffee_roast, coffee_grind, coffee_picture, coffee_weight, coffee_price,
                coffee_shop_name
                from coffee, coffee_shops, categories
                where coffee_category_id = category_id and coffee_name like \'%" . $name . "%\'
                ";
        
        $rs = mysqli_query($conn, $sql);

[Orjan]

as the large text is in between quotes, you don't need to escape the aphostrofes.

$sql = "select Coffee_SKU, Coffee_Name, category_name, coffee_details, coffee_price,

                coffee_roast, coffee_grind, coffee_picture, coffee_weight, coffee_price,

                coffee_shop_name

                from coffee, coffee_shops, categories

                where coffee_category_id = category_id and coffee_name like '%" . $name . "%'";

but another thing. as you use three tables in your select, you would need another connecting where-statement to tie them all three together...

[Blue Indian]

I used it this way and it works fine:

$sql = "
                select Coffee_SKU, Coffee_Name, category_name, coffee_details, coffee_price,
                coffee_roast, coffee_grind, coffee_picture, coffee_weight, coffee_price,
                coffee_shop_name
                from coffee, coffee_shops, categories
                where coffee_category_id = category_id and coffee_name like '%$name%'
                ";

[Orjan]

yep, that works too!

[SoN9ne]

Try to think of others reading your code. Your code may work fine but it is an eye-sore.
Of course its entirely a personal choice but I always thought it was better to learn better from the beginning instead of waiting for a company to tell you differently. Plus it helps when showing code samples in interviews.

I slightly modified your query to be a little easier to read and to prevent SQL injection.

        $sql = "SELECT Coffee_SKU, Coffee_Name, category_name, coffee_details, coffee_price, coffee_roast, coffee_grind, coffee_picture, coffee_weight, coffee_price, coffee_shop_name

                FROM coffee, coffee_shops, categories

                WHERE coffee_category_id = category_id 

                    AND coffee_name LIKE '%".mysql_real_escape_string($name)."%'";

It looks like it would benefit by using (LEFT) JOINS, try taking a peek into that. Your way works but it would seem to cause more overhead.

Just my two cents, good luck if you intend to use it. :thumbup:

[Blue Indian]

SoN9ne,

Thanks for the advice!

I also like the fact that you use the function mysql_real_escape_string($name). This will allow the user to search for stuff like Folger's without having to use "\", right?

I did run into the following problem though: Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\website on line 17

Can you or anyone explain why I am getting this warning and how to overcome it?

[Blue Indian]

Quote

Of course its entirely a personal choice but I always thought it was better to learn better from the beginning instead of waiting for a company to tell you differently.

Could you explain what you mean by this statement? It is confusing to me. Thanks!! :)

[Orjan]

mysql_real_escape_string needs an open connection to the mysql server, try an mysql_connect before using it.

[Blue Indian]

Thanks, Orjan! Everything is working for now!

:thumbup: