Using form to query MySQL database via PHP is producing errors

[cowbell]

Hi folks,
This is a pathetic one that in all honesty I should be able to figure out (having done so in the past) but my brain is refusing to give up the answers today…..

I have a form into which the user can enter the name of a musical instrument and then submit to query a MySQL database containing lists of bands (this is not a commercial project) who use that instrument and then display the results on a results page.

Should be simple.

Here’s what I have so far:
The form
<form name="form1" method="POST" action="qresults.php">
<p>Instrument:
<input name="instrument" type="text" id="instrument_id">
<p><input type="submit" name="submit" value="submit">
</form>

The results page
<?
$db_name = "dbname";
$table_name = "tablename";
$connection = @mysql_connect("localhost", "username", "password") or die("Could not connect.");


$db = @mysql_select_db($db_name, $connection)
or die("Couldn't select database.");

$sql =
"SELECT *
FROM $table_name WHERE instrument=" . $_POST['instrument_id'] . "";
$result = @mysql_query($sql,$connection)
or die("Couldn't execute query.");


while ($row = mysql_fetch_array($result)) {

$id = $row['id'];
$fname = $row['fname'];
$lname = $row['lname'];
$county = $row['county'];
$instrument = $row['instrument'];
$style = $row['style'];
$howlong = $row['howlong'];
$standard = $row['standard'];
$bandb4 = $row['bandb4'];
$tranpost = $row['tranport'];
$songwriter = $row['songwriter'];
$description = $row['description'];

$display_block .= "


All I get is the “Couldn’t execute query” message when I try to run this.

Please, any thoughts would be muchly appreciated.
Many thanks.

 

[kenrbnsn]

In your form, in this line

<input name="instrument" type="text" id="instrument_id">

the 'id=' should be 'name='

<input name="instrument" type="text" name="instrument_id">

I think 'id' is used for Javascript.

Ken

 

[cowbell]

Many thanks Ken for looking at this for me. Alas it does not do the trick.
I still receive the 'Couldn't execute query' message.

 

[jpadie]

pls post code within the

tags - otherwise it does not come through perfectly

the problem is in your query string and your use of quotation marks.

try the following

$sql =
    "SELECT *
         FROM $table_name WHERE instrument= '$_POST[instrument_id]";

or

$sql =
    "SELECT *
         FROM $table_name WHERE instrument='" . $_POST['instrument_id'] . "'";

hth
justin

 

[cowbell]

Hi Justin,
apologies about not using the code tags.
I think you've got something with the 2nd option above.
I'm no longer getting the error, no results are displaying but I'll look into that before panicking again.
Assuming this resolves everything then my thanks to you and Ken and anyone else who took a look.

 

[jpadie]

i am not sure what the effect of following kenrbnsn's advice would be. you would then have the field having two "name" attributes. I, personally, would stick to one name attribute and have an id attribute as well (as you originally went with).

of course, you need to reference the correct name in the receiving script. you should also escape the value before using it in a query

$instrument_id = mysql_escape_string($_POST['instrument']); //nb the incoming var is from the NAME attribute NOT the id attribute

and then you need to use the correct variable in the sql expression (i noted that the variable i used was picked up from the second name in kenrbnsn's code). Note that the incoming variables follow the name attribute and not the id attribute.

FYI the id attribute can be used by javascript (getElementByID("id")) or by css to reference uniquely that control.

 

[kenrbnsn]

My mistake, I didn't see the first 'name' attribute, even after copying the code.

I sit corrected.

Ken

 

[sleipnir214]

As a general rule, changing this line:

$result = @mysql_query($sql,$connection)
or die("Couldn't execute query.");

To read

$result = @mysql_query($sql,$connection)
or die("Couldn't execute query[red]:" . mysql_error()[/red]);

will give you more debugging information.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[cowbell]

Many thanks to everyone on this. I'm finally seeing a result on screen.
I really appreciate your help.

Take the rest of the day off everyone