jonnewbie12 Posted October 5, 2011 Share Posted October 5, 2011 Sorry to be dim. I am sue there is a simple answer to this. I am trying to build a PHP page that returns the value of a column and a row from an SQL database. All the connections and servers are running fine. I am running PHP5.2.16. Put simply I am trying to get the price of material when it is a certain width and drop. The first column shows the width the second shows the drop and the third shows the price. I am using Dreamweaver CS5. I have a simple form on the page that has two fields, one for width and one for drop. When the user enters the value in the form I want it to query the database and retrieve the price where the form fields match BOTH database columns for width and drop. I can do it for one column but not for both. I have tried all different permutations of the WHERE and AND commands but cant seem to get it to work. This is my latest attempt. I am sure there is an easier way. Can someone PLEASE help? The Browser keeps saying: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'drop =NULL' at line 1" Here is the code for the whole page which is very short: <?php require_once('Connections/blindserver.php'); ?> <?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } mysql_select_db($database_blindserver, $blindserver); $query_Recordset1 = sprintf("SELECT price FROM prices WHERE width = %s AND drop =%s", GetSQLValueString($_REQUEST['width'], "int"), GetSQLValueString($_REQUEST['drop'], "int")); $Recordset1 = mysql_query($query_Recordset1, $blindserver) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">; <html xmlns="http://www.w3.org/1999/xhtml">; <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>Untitled Document</title> <style type="text/css"> .kj { font-size: 16px; color: #000; } </style> </head> <body> <form id="form1" name="form1" method="post" action=""> <p> <label for="width">width</label> <input name="width" type="text" id="width" /> </p> <p> <label for="drop">drop</label> <input name="drop" type="text" id="drop" /> </p> <p> </p> <p> <input type="submit" name="submit" id="submit" value="Submit" /> </p> </form> <p><span class="kj"></span><?php echo $row_Recordset1['price']; ?></p> </body> </html> <?php mysql_free_result($Recordset1); ?> Link to comment https://forums.phpfreaks.com/topic/248473-php-sql-string-query-using-where-and-commands-going-nuts/ Share on other sites More sharing options...
Freedom-n-Democrazy Posted October 5, 2011 Share Posted October 5, 2011 Here mate.. this will dump all data in HTML from a table where fields match your lookup . $query = "field1,field2,field3 from tablename"; $result = mysql_query($query); while ($row = mysql_fetch_assoc($result)) {foreach ($row as $data) {echo "$data<BR>";}} ... and this will dump all data into HTML where fields match a specific query: $query = "select * from table where fieldname like '".$_POST['search query']."' and fieldname like '".$_POST['search query']."'"; Link to comment https://forums.phpfreaks.com/topic/248473-php-sql-string-query-using-where-and-commands-going-nuts/#findComment-1275970 Share on other sites More sharing options...
jonnewbie12 Posted October 5, 2011 Author Share Posted October 5, 2011 Thank you so much. What can I say 12 hours of mucking about and you solved it in 2 minutes. Thanks very much indeed for the really quick response. You have made my headache go away! Link to comment https://forums.phpfreaks.com/topic/248473-php-sql-string-query-using-where-and-commands-going-nuts/#findComment-1275977 Share on other sites More sharing options...
jonnewbie12 Posted October 5, 2011 Author Share Posted October 5, 2011 I know I am pushing my luck but I dont suppose you know how to get only the last record within that code as I am actually trying to enable the user to enter a value that falls withtin certain ranges using the <= commands? Here is how it looks now. mysql_select_db($database_blindserver, $blindserver); $query_Recordset2 = "SELECT prices.price FROM prices where prices.width <= '".$_POST['width']."' and prices.drop <= '".$_POST['drop']."'"; $Recordset2 = mysql_query($query_Recordset2, $blindserver) or die(mysql_error()); $row_Recordset2 = mysql_fetch_assoc($Recordset2); $totalRows_Recordset2 = mysql_num_rows($Recordset2);mysql_select_db($database_blindserver, $blindserver); Link to comment https://forums.phpfreaks.com/topic/248473-php-sql-string-query-using-where-and-commands-going-nuts/#findComment-1275984 Share on other sites More sharing options...
awjudd Posted October 5, 2011 Share Posted October 5, 2011 Order your returned results by the last value (i.e. ORDER BY prices.price DESC) and then add a LIMIT 1 to grab the last row. ~juddster Link to comment https://forums.phpfreaks.com/topic/248473-php-sql-string-query-using-where-and-commands-going-nuts/#findComment-1275993 Share on other sites More sharing options...
jonnewbie12 Posted October 5, 2011 Author Share Posted October 5, 2011 Afraid I could use a little help if you know the code for that? Sorry Link to comment https://forums.phpfreaks.com/topic/248473-php-sql-string-query-using-where-and-commands-going-nuts/#findComment-1275994 Share on other sites More sharing options...
awjudd Posted October 5, 2011 Share Posted October 5, 2011 $query_Recordset2 = "SELECT prices.price FROM prices where prices.width <= '".$_POST['width']."' and prices.drop <= '".$_POST['drop']."' ORDER BY prices.price DESC LIMIT 1"; Or whatever field you want to sort by? ~juddster Link to comment https://forums.phpfreaks.com/topic/248473-php-sql-string-query-using-where-and-commands-going-nuts/#findComment-1275995 Share on other sites More sharing options...
Buddski Posted October 5, 2011 Share Posted October 5, 2011 Just as a matter of safety, never enter $_POST or $_GET variables into a query string, or any other user specifiable variable for that matter. ALWAYS escape your variables! Have a look at mysql_real_escape_string() Link to comment https://forums.phpfreaks.com/topic/248473-php-sql-string-query-using-where-and-commands-going-nuts/#findComment-1276044 Share on other sites More sharing options...
awjudd Posted October 5, 2011 Share Posted October 5, 2011 Agreed! ~juddster Link to comment https://forums.phpfreaks.com/topic/248473-php-sql-string-query-using-where-and-commands-going-nuts/#findComment-1276048 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.