Php variable use in MySQL? (Perhaps wrong Forum)

[hpladds]

The following code accepts a variable (selected from a radio button list) and uses it in a query. In this example the variable's value is "Reference" -- without the quotes.

<?php
require_once ('../mysql_connect.php'); // Connect to the database.
$var1 = ($_POST['location']);
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<?php
$query = "SELECT number FROM files WHERE location = '$var1'";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
echo "The value of \$var1 is: <b>$var1</b><br>"; // display the value of the variable -- for debugging purposes.
echo "The value of \$query is: <b>$query</b><br>"; // Display the query terms -- for debugging
echo "The value of \$query is: <b>\"$query\"</b><br>"; //My attempt to add double quotes -- for debugging.
echo "The value of \$result is: <b>$result</b>"; // Display the value of $result -- for debugging.
?>
</form><!-- End of Form -->

The code returns the following:
The value of $var1 is: Reference
The value of $query is: SELECT number FROM files WHERE location = 'Reference
The value of $query is: "SELECT number FROM files WHERE location = 'Reference
The value of $result is: Resource id #4

The query fails -- presumably because the term 'Reference does not have a trailing single quote. (Please see the end of lines 2 and 3). Also note that line 3 only has an opening double quote -- before the word SELECT.

Things I've tried:
$query = "SELECT number FROM files WHERE location = '.$var1'";
$query = "SELECT number FROM files WHERE location = '$var1'";
$query = "SELECT number FROM files WHERE location = '" . $var1 . "'";
$query = "SELECT number FROM files WHERE location ='$POST[location]'";
$query = sprintf("SELECT number FROM files WHERE location = '%s'",
$var1;
and supplying the variable with additional quote(s)
$var1 = ($POST['location']);
$adquote = ''$var1'';
and a whole bunch of other things that failed miserably.

Any help much appreciated!

[halojoy]

A bit mysterious.

What does mysql say: or die('Query failed: ' . mysql_error());
mysql_error should have something to say, about syntax error
in the end of $query

[Pikachu2000]

I believe the query may actually be succeeding, and the result is an array. Otherwise the result would (presumably) be NULL. Have you tried anything like

query = "SELECT number FROM files WHERE location = '$var1'";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while( $array = mysql_fetch_assoc($result) ) {
     echo( $array['number'] . "<br>" );
}

[bradgrafelman]

The query doesn't fail - it succeeds. If it had failed, this:

The value of $result is: Resource id #4

wouldn't say you have a Resource - it'd show that you have false (or 0, more than likely).

[hpladds]

Thanks all for your help. I updated the code to accommodate that $var1 is an array. I also added echo statements to help with debugging.

I still have the same problem: the code will not use the imported variable in the query, but recognizes the imported variable in echo statements. If I locally declare the variable, everything is great.

<?php 
require_once ('../mysql_connect.php'); // Connect to the database.
$var1 = ($_POST['location']);
$var2 = Reference;
$var3 = NULL;	
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

<?php
$query = "SELECT number FROM files WHERE location = '$var3'";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

while( $array = mysql_fetch_assoc($result) ) {
     echo( $array['number'] . "<br>" );
     echo "\$var1 (above closing bracket) = $var1 <br>";
     echo "\$var2 (above closing bracket) = $var2 <br>";
     echo "\$query (above closing bracket) = $query <br>";
     echo "\$result (above closing bracket) = $result <br>";

} 

 echo "\$query (below closing bracket) = $query <br>";


?>
</form><!-- End of Form -->

When using $var1 in the query, the following is returned. (Please note missing end single quote and that the echo statement that generated this return is outside the while brackets. All other echo statements have been ignored or failed.)

$query (below closing bracket) = SELECT number FROM files WHERE location = 'Reference

When using $var2 in the query, the following is returned. (Not much to see here. Please note success of the query.)

1
$var1 (above closing bracket) = Reference
$var2 (above closing bracket) = Reference
$query (above closing bracket) = SELECT number FROM files WHERE location = 'Reference'
$result (above closing bracket) = Resource id #4
2
$var1 (above closing bracket) = Reference
$var2 (above closing bracket) = Reference
$query (above closing bracket) = SELECT number FROM files WHERE location = 'Reference'
$result (above closing bracket) = Resource id #4
3
$var1 (above closing bracket) = Reference
$var2 (above closing bracket) = Reference
$query (above closing bracket) = SELECT number FROM files WHERE location = 'Reference'
$result (above closing bracket) = Resource id #4...
(PORTION REMOVED FOR BREVITY)
$var1 (above closing bracket) = Reference
$var2 (above closing bracket) = Reference
$query (above closing bracket) = SELECT number FROM files WHERE location = 'Reference'
$result (above closing bracket) = Resource id #4
$query (below closing bracket) = SELECT number FROM files WHERE location = 'Reference'

When using $var3 in the query, the following is returned. (Note: Even with a NULL value, the return is treated as an array).

$var1 (above closing bracket) = Reference
$var2 (above closing bracket) = Reference
$query (above closing bracket) = SELECT number FROM files WHERE location = ''
$result (above closing bracket) = Resource id #4

$var1 (above closing bracket) = Reference
$var2 (above closing bracket) = Reference
$query (above closing bracket) = SELECT number FROM files WHERE location = ''
$result (above closing bracket) = Resource id #4

$var1 (above closing bracket) = Reference
$var2 (above closing bracket) = Reference
$query (above closing bracket) = SELECT number FROM files WHERE location = ''
$result (above closing bracket) = Resource id #4
(PORTION REMOVED FOR BREVITY)
$var1 (above closing bracket) = Reference
$var2 (above closing bracket) = Reference
$query (above closing bracket) = SELECT number FROM files WHERE location = ''
$result (above closing bracket) = Resource id #4
$query (below closing bracket) = SELECT number FROM files WHERE location = ''

[Pikachu2000]

This, to me, indicates a problem somewhere in the form. Can you post the relevant lines from the form, and any other details that might help?

[hpladds]

Thanks Pikachu2000,

Here's the form code.

Note: There are two MySQL tables at work in this project: "location" and "files". I can run queries on both tables and receive the return "Reference", but the two "Reference" are entirely unrelated to each other.

Perhaps this is the problem. Can the "Reference" that is returned by a query of the table "location" serve as an variable in a query in the table "files" ? Perhaps one is utf8 and the other ASCII or Binary -- or something like that?

<form action="file_add11.php" method="post">

<fieldset><legend><b>Plese select a location:</b></legend>

<table>

<?php
$query= "select locat FROM locations" ;
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($column = mysql_fetch_assoc ($result)){

echo '<table>
<tr>
	<td> <input type="radio" name="location" value=',$column['locat'],'</td><td> ',$column['locat'],' </td>
</tr>

';
}
?>
</table>
</field>
</form><!-- End of Form -->

[Pikachu2000]

A few things jump out here. The way it is, a new <table> is started with each row returned from the db, the <input> tag was never closed, and there was an extraneous comma in the value=. There were also no quotes in the value= of the <input>. This should work, but I've not tested it locally . . .

<form action="file_add11.php" method="post">

<fieldset><legend><b>Plese select a location:</b></legend>

<table>

<?php
$query= "select locat FROM locations" ;
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($column = mysql_fetch_assoc ($result)){

echo "<tr><td><input type=\"radio\" name=\"location\" value=\"" . $column['locat'] . "/"></td><td>" . $column['locat'] . "</td></tr>";
}
?>
</table>
</field>
</form><!-- End of Form -->