CODEWITHSUNDEEP
javamysqldatabase
Muhammad Hassam
Muhammad Hassam

Reputation: 613

Insert variable in mysql

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

Upvotes: 1

Views: 74

Answers (2)

Muhammad Hassam
Muhammad Hassam

Reputation: 613

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" />

Upvotes: 0

Jan
Jan

Reputation: 13858

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;
    }       
}

Upvotes: 1

Related Questions