Submitting Multiple Variables To Search One Table

allygard · November 27, 2012

Hi,

I know this is a very basic query but most of the tutorials etc. I can find seem to focus on querying multiple tables rather than one table. I want to filter the data from one table (Mutation) by three variables (prot_name, no_changes and amyloid) (all in this one table).

In my main HTML page I have the form:

<p><b>Advanced Search:</b>search for subgroups of mutations associated with a particular protein</p>
<form action= "adv_mutationsearch.php" method='post'>
<select name="result1">
	<option value="alpha synuclein">alpha synclein</option>
	<option value="amyloid precursor protein">amyloid precursor protein</option>
	<option value="apolipoprotein A-1 precursor">apolipoprotein A-1 precursor</option>
	<option value="apolipoprotein A-1V precursor">apolipoprotein A-1V precursor</option>
</select>


<p><b>AND</b></p>

<select name="result3">
	<option value="amyloid">Amyloid</option>
	<option value="amorphous">Amorphous</option>
	<option value="non-amyloid">Non-amyloid</option>
	<option value="oligomers">Oligomers</option>
	<option value="undetermined">Undetermined</option>
</select>

<p><b>AND</b></p>	

<select name="result2">
	<option value="single">Single</option>
	<option value="double">Double</option>
	<option value="multiple">Multiple</option>
	<option value="wild">Wild (original sequence)</option>

</select>
<br>
<br>
<input type='submit' value = 'filter'>
</form>

in my php file:

<html><head>
<link rel="stylesheet" type="text/css" href="tabs.css" media="screen" />
<title>Amyprot Table Viewer</title></head><body>

<?php
$db_host = '********';
$db_user = '********';
$db_pwd = '*********';

$database = 'amyprotdb';
$table = 'Mutation';

//receiving results from form submission and allocating to variables for query

$query1 = $_POST['result1'];
$query2 = $_POST['result2'];
$query3 = $_POST['result3'];

// connect to database

if (!mysql_connect($db_host, $db_user, $db_pwd))
die("Can't connect to database");

if (!mysql_select_db($database))
die("Can't select database");

// query for sorting mutations by protein, number of changes and amyloidogenicity and printing result


$result = mysql_query("SELECT * FROM {$table} WHERE prot_name_id={$query1} AND no_changes={$query2} AND amyloid={$query3}");


//to show results in table the current problem lies with the code below, the above code dows not show errors so need to undertsand the below sections. or change it to ordinary print out.

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
//echo "<table border='1'><tr>";

echo '<table cellpadding="0" cellspacing="1" class="db-table">';
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
$field = mysql_fetch_field($result);
echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
echo "<tr>";

// $row is array... foreach( .. ) puts every element
// of $row to $cell variable
foreach($row as $cell)
	echo "<td>$cell</td>";

echo "</tr>\n";
}
//mysql_free_result($result);


?>
<div class="search">
<p>Return to database search page <a href="database.php"><span>Search</span></a></p>
<p>Return to full mutations table <a href="mutationtable.php"><span>Mutation table</span></a></p>
</div>  
</body></html>

The error I get states that $result (when I am trying to print the table of results) is null meaning the whol post submission hasnt worked. I am an early learner as you can see, I have tried multiple ways to do this.. The set up above has been pared right back to the basics as I tried to clean up my code to see where the fault lay and now I am stuck. Thanks in advance x

Edited November 30, 2012 by fenway
code tags

Barand · November 27, 2012

String values in a query need to be in quotes ie

$result = mysql_query("SELECT * FROM {$table} WHERE prot_name_id='$query1' AND no_changes='$query2' AND amyloid='$query3' ");

You should also be using mysql_real_escape_string on user-submitted data

allygard · November 27, 2012

Hi,

Thanks it actually works. What a relief! I did originally set up the query like that but then made so many changes I just added new mistakes as I corrected old ones. I know it is very basic and will google the escape string thing and see if i can work out how to use it (once I figure out what it is :tease-03: ).

You are a star, thanks for your help.

Barand · November 27, 2012

One other thing, if you are just starting to learn mysql, don't.

Start learning the newer mysqli (or PDO) as mysql will shortly disappear.

allygard · November 27, 2012

thanks for the warning. One of the things that drives me nuts is that soon as i learn something it is "updated" or deprecated. I just can never keep up. Mind you I am not a programmer anyway, just a scientist who dips in now and again, i just cobble together code until it works and don't worry too much about making it elegant.

Sign In

Submitting Multiple Variables To Search One Table

Recommended Posts

allygard

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

allygard

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

allygard

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information