PHP SQL query fails to return numerical value

Question

When I query a price between two values from a form it shows:

request "Could not execute SQL query" SELECT * FROM data WHERE id>0SELECT * FROM data WHERE price >= '100' AND price <= '150'

Form code (only the price code):

<form action="searchhotel/results.php"  target="_self">
<label>Price($)</label>
<select  name="pricefrom">
<option  value="">--</option>
<option  value="100">100</option>
</select>

<label>Price TO($)</label>
<select  name="priceto">
<option  value="">--</option>
<option  value="150">150</option>
</select>

<button type="submit">Search</button>

</form>

PHP code (results.php) all the other codes work well apart from the $search_price

<?php
if ($_REQUEST["string"]<>'') {
    $search_string = " AND (hotel LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%' OR email LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%')"; 
}
if ($_REQUEST["city"]<>'') {
    $search_city = " AND     city='".mysql_real_escape_string($_REQUEST["city"])."'";   
}
if ($_REQUEST["star"]<>'') {
    $search_star = " AND     star='".mysql_real_escape_string($_REQUEST["star"])."'";   
}
if ($_REQUEST["pricefrom"]<>'' and $_REQUEST["priceto"]<>'') {
    $search_price = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE price >=     '".mysql_real_escape_string($_REQUEST["pricefrom"])."' AND price <= '".mysql_real_escape_string($_REQUEST["priceto"])."'";
}
if ($_REQUEST["from"]<>'' and $_REQUEST["to"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE from_date >= '".mysql_real_escape_string($_REQUEST["from"])."' AND to_date <= '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string.$search_city.$search_string.$search_star.$search_price;
} else if ($_REQUEST["from"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE from_date >= '".mysql_real_escape_string($_REQUEST["from"])."'".$search_string.$search_city.$    search_string.$search_star.$search_price;
} else if ($_REQUEST["to"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE to_date <=     '".mysql_real_escape_string($_REQUEST["to"])."'".$search_string.$search_city.$search_string.$search_sta.$search_pricer;
} else {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE     id>0".$search_string.$search_city.$search_string.$search_star.$search_price;
}


$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not     execute SQL query" '.$sql);
if (mysql_num_rows($sql_result)>0) {
    while ($row = mysql_fetch_assoc($sql_result)) {
?>

Everything works well, the SQL database is successfully connected and it echoes all the values via the PHP. I think I wrongly declared the datatype for the 'price' in the SQL database as shown below:

CREATE TABLE IF NOT EXISTS `data` (
`id` int(11) NOT NULL auto_increment,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
`hotel` varchar(250) NOT NULL,
`city` varchar(250) NOT NULL,
`star` varchar(250) NOT NULL,
`links` varchar(250) NOT NULL,
`images` varchar(250) NOT NULL,
`price` varchar(250) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

It looks like you have 2 queries being submitted together. In case you thought that was legal, its not when using the mysql_ database extension — RiggsFolly
– RiggsFolly, Commented Jan 30, 2016 at 14:49
Please dont use the mysql_ database extension, it is deprecated (gone for ever in PHP7) Especially if you are just learning PHP, spend your energies learning the PDO or mysqli_ database extensions, and here is some help to decide which to use — RiggsFolly
– RiggsFolly, Commented Jan 30, 2016 at 14:49
Your $search_price also has a SELECT - look at the query: SELECT * FROM data WHERE id>0SELECT - that's not valid SQL. — Kenney
– Kenney, Commented Jan 30, 2016 at 14:50
Here is error .$search_string.$search_city.$search_string.$search_star.$search_price in variable $search_price you have another query and put in middle of other query — Michael
– Michael, Commented Jan 30, 2016 at 15:16

Michael · Accepted Answer · 2016-01-30 15:49:11Z

0

Error in your script here .$search_string.$search_city.$search_string.$search_star.$search_price in $search_price is another query not a value from db or get/post, and you put query in query.

You concatinate this query string

 if ($_REQUEST["pricefrom"]<>'' and $_REQUEST["priceto"]<>'') {
$search_price = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE price >=     '".mysql_real_escape_string($_REQUEST["pricefrom"])."' AND price <= '".mysql_real_escape_string($_REQUEST["priceto"])."'";

}

With

 } else {
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE     id>0".$search_string.$search_city.$search_string.$search_star.$search_price;
 }

And obtain bad query

$search_price = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE price >=     '".mysql_real_escape_string($_REQUEST["pricefrom"])."' AND price <= '".mysql_real_escape_string($_REQUEST["priceto"])."'"."       SELECT * FROM ".$SETTINGS["data_table"]." WHERE     id>0".$search_string.$search_city.$search_string.$search_star.$search_price;
 }

SELECT * FROM data WHERE id>0SELECT * FROM data WHERE price >= '100' AND price <= '150'

Or separe 2 queryse with ; or correct condition if or add else where is need to not have 2 queryes to execute if no need it

edited Jan 30, 2016 at 15:49

answered Jan 30, 2016 at 15:02

Michael

1,0891 gold badge11 silver badges28 bronze badges

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

1 Comment

ThomasWeb Over a year ago

Thanks Mitch i separated each query with an "if" (y)

Collectives™ on Stack Overflow

PHP SQL query fails to return numerical value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related