[RESOLVED] MySql query fails

dgcostello

I am having trouble executing a Mysql query with a variable passed from a form. I can echo the value to insure it is passed correctly but when I try to execute the query with the variable ot fails

$product = $_POST['ceinumber'];

$result = mysql_query("SELECT * FROM sku_master where model_id =
'$product") or die ("Invalid query $query");

I'm running PHP 5 with Apache

Thanks

dagon

you have an extra single quote mark, or your missing a quote mark, depending on if you wanted to quote $product or not.

dgcostello

Hi I tired adjusting as follows with the same results.

$result = mysql_query("SELECT * FROM sku_master where model_id = '{$product}'") or die ("Invalid query $query");

$result = mysql_query("SELECT * FROM staples_sku_master where model_id = {$product}") or die ("Invalid query $query");

$result = mysql_query("SELECT * FROM staples_sku_master where model_id = $product") or die ("Invalid query $query");

bradgrafelman

Instead of guessing what to do, why not look at [man]mysql_error/man to see where the problem is? Here's the standard why I debug SQL errors:

$query = 'SELECT foo FROM bar...' // put query into a variable
$exec = mysql_query($query) or die("MySQL Query: $query<br>MySQL error: " . mysql_error());

Modify your code to something like the above and copy-paste the exact query and error message returned.

dagon

and a good look at the mysql manual would help also

dgcostello

I've tried what you outlined to no avail. I've included my file and would appreciate any help. I've check various resources for hel on passing a variable from a POST. I have verified the post variable is passed and can echo it.

<?php
include_once("printreport.php");
$host = "d610-j1fpc91";
$user = "testuser";
$password = "testpw";
$database = "facilities_solutions";
$table = "staples_sku_master";
$product = $_POST['ceinumber'];


$cid = connect_database($host,$user,$password,$database);

if ($cid) {
	$rows = 0;
    $prt = new PrintLineTable("SKU Master Report");


$result = SelectProduct($cid,$database,$rows);


if ($rows > 0) {
    while ($column = mysql_fetch_assoc($result)) {
        $row = array("Model ID" => $column["MODEL_ID"],
                     "Staples #" => $column["STAPLES_SKU"],
                     "Description" => $column["SKU_DESCRIPTION"],
                     "U/M" => $column["SELL_UOM"],
                     "List Price" => $column["LIST_PRICE"],
                     "Prop Type" => $column["PROP_TYPE"],
                     "As of Date" => $column["AS_OF_DATE"],
                    );
        $field_select = array("MODEL_ID" => $column["MODEL_ID"]);
        $prt->PrintLine($row,$field_select,"update_customer.php");   // print line with href link
        #  $prt->PrintLine($row);   // print line without href link
    }
    $prt->PrintLineClose();
}
else {
   print "<HR>\n";
   print "</p><p><b><font size='4' color='#FF0000'>There aren't any item records</font></b></p>";
}
mysql_free_result($result);
connect_close($cid);
}
print "<p align='center'><a href='model.html' target='_parent'><img border='0' src='\images\goback.gif' width='26' height='26'>Goback</a></td>";


function connect_database($host,$user,$password,$database) {  /* Construtor da conexo do database */
    $cid = mysql_connect($host,$user,$password) or die("I could not do the connection with the database $database");
    if (!$cid) {
        exit("Error in the connection with the database: ". $database. "Error:". mysql_error() . "\n");
    }
    mysql_select_db($database, $cid);
    return $cid;
}  /* End of Function */

function connect_close($cid) {
    mysql_close ($cid);
}  /* End of Function */

function SelectProduct($cid,$database,&$rows) {

$query = "SELECT * FROM staples_sku_master where model_id = " ."$product."; 
//$query = "SELECT * FROM staples_sku_master where model_id = 'GEP12820'";

  $result = mysql_query($query)or die("MySQL Query: $query<br>MySQL error: " . mysql_error());


 $rows   = mysql_num_rows($result);
    return $result;
}   /* End of Function */

?>

dagon

$query = "SELECT * FROM staples_sku_master where model_id ='$product' ";

bradgrafelman

In this code:

function SelectProduct($cid,$database,&$rows) {

$query = "SELECT * FROM staples_sku_master where model_id = " ."$product.";
//$query = "SELECT * FROM staples_sku_master where model_id = 'GEP12820'";

  $result = mysql_query($query)or die("MySQL Query: $query<br>MySQL error: " . mysql_error());


$rows   = mysql_num_rows($result);
    return $result;
}

Where is $product ever defined?

EDIT: Note that this:

$product = $_POST['ceinumber'];

is not the answer to the question because of variable scoping. Looks like you need to read up on what variable scoping is: [man]variables.scope[/man].

Also note that you should be using forward slashes in your HTML IMG code, not backslashes because a) URI's use forward slashes, and b) backslashes inside of a double-quoted string have special meaning.

dagon's suggestion is valid as well, unless $product is strictly numeric. Either way, you also need to sanitize your variables else your script is vulnerable to SQL injection attacks.

dgcostello

Thanks you. It worked.