Insert variable in mysql

Question

I'm trying to send get the restaurants who's city is Lahore, Rwp etc(what the user will enter into the form. But I'm not getting anything. If I hard code in the query and enter the city name myself then it works else it doesn't return anything. Same problem I have too when I try to add anything.

Basically I need help in using variables in mysql query.

What am I doing wrong?

public ResultSet getRestaurants(String city){
    Statement statement;
    try {
        statement = con.createStatement();
        String query = "select * from restaurantinfo WHERE city = '"+city+"'";
        ResultSet rs = statement.executeQuery(query);
        return rs;
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        return null;
    }       
}

THIS IS HANDLING RESULT SET. I know there are errors. I will fix them. I'm concerned with showing the restaurants..

   <%
	DbConnection dbc = new DbConnection();
	String city = request.getParameter("city");
	ResultSet rs = dbc.getRestaurants(city);
	

	%>
        <% 
        while(rs.next()){
		%>
	<section class="main-wrap">
    	<section class="sidebar">
 			<form id="filtercousine">
                <div ><br>
				<h4 style="margin-bottom:-5px;"> Filter by Cuisines</h4><br>
                <input id="fastfood" type="checkbox" style="" name="fastfood" value="fastfood">Fast Food <br/>
                <input id="pakistani" type="checkbox" name="gender" value="Pakistani">Pakistani <br/>
                </div>
            </form>
        </section>

		<section class="content-wrap">
        	<ul>
            	<li> <input type="submit" id="menu" value="Go to Menu" class="main-button" style="float:right; margin-right:6px;"><h3><%= rs.getString("name") %></h3></li>
            	<li style="font-style:italic; margin:-15px 0 5px 0;"> <%= rs.getString("cousines") %></li>
            	<li style="color:#999; margin-bottom:2px;"> Delivery:</li>
            	<li >Free</li>
            </ul>	
        </section>
    </section>
    <%		
	}	
	%>

This is my database picture View

In general, looks fine. Does city have any leading/trailing blanks? May help to print out query before running it and see exactly what it looks like. — DBug
– DBug, Commented Dec 10, 2015 at 19:10
I have added the code which handles the result set.. I have edited the post — Muhammad Hassam
– Muhammad Hassam, Commented Dec 10, 2015 at 19:23

Jan · Accepted Answer · 2015-12-10 19:27:09Z

1

First off - it seems odd that you pass along the ResultSet instead of directly handling that. Please also share the code handling the ResultSet - maybe the error is in that part.

For your method: Please consider this variant using a PreparedStatement instead:

public ResultSet getRestaurants(String city){
    try {
        System.out.println("CITY: " + city);
        PreparedStatement ps = con.prepareStatement(
             "select * from restaurantinfo WHERE city = ?");
        //Set first ? with value of city
        ps.setString(1, city);
        //return resultset
        return statement.executeQuery(query);
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }       
}

edited Dec 10, 2015 at 19:27

answered Dec 10, 2015 at 19:12

Jan

13.9k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Muhammad Hassam Over a year ago

I have added the code which handles the result set.. I have edited the post

Jan Over a year ago

If that "city" parameter in fact a String? Do you get that from a combobox (<select>) or from an input field? If you get it from a combobox, you should consider assigning ids to the cities and query by those ids.

Jan Over a year ago

One edit: Print out the city we put into the SQL Statement. My best guess is, that you have encoding trouble, so you receive some string that does not match what's in your db.

Muhammad Hassam Over a year ago

Its printing null.. I'm using input field btw

Muhammad Hassam Over a year ago

|

Muhammad Hassam · Accepted Answer · 2015-12-10 20:55:04Z

0

Thanks a lot @jan for your help!

Btw I got it solved by changing the name to "cityname" I don't know why it worked.. And yes the other database also worked by adding a name attribute.

For this i changed city to cityname. Strange but it worked. I think id and name should be different maybe. And of course i had to change in the request parameter as well.

<input id="city"  type="text" name="cityname" />

answered Dec 10, 2015 at 20:55

Muhammad Hassam

6136 silver badges12 bronze badges

Collectives™ on Stack Overflow

Insert variable in mysql

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related