Jump to content

Archived

This topic is now archived and is closed to further replies.

Wilsee

Querying a database using a user form.

Recommended Posts

Good day,
I am currently trying to build a mock PHP Website upon where a user can query for an insurance
quote ([premium]) by using a form that interacts with a MysQL database. The fields for the database are
[age] [grp] [region] and [premium] (premium being what I want to return to the user).

Ok so I need a form, the form I have is as followed;

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

<P> Please fill in the form below with your details to generate your car insurance query;

</P>
<P class="P2"> Your insurance group -
</P>
<input type=Radio name=insuregroup> 1
<input type=Radio name=insuregroup> 2
<input type=Radio name=insuregroup> 3
<input type=Radio name=insuregroup> 4
<input type=Radio name=insuregroup> 5
<input type=Radio name=insuregroup> 6

<P class="P2"> Your age (17-70 Only) -
</P>

<input type=text name=custage size ="2" maxlength = "2">
<P class="P2"> Your region of residence -
</P>
<input type=Radio name=custregion> South East
<input type=Radio name=custregion> South West
<input type=Radio name=custregion> Midlands
<input type=Radio name=custregion> North
<input type=Radio name=custregion> Wales
<input type=Radio name=custregion> Scotland
<P> When you are ready please proceed and click the submit button below.
</P>
<input type="submit" VALUE = "Submit your query">
<input type= "reset" VALUE = "Resets your details">
</form>



Nothing wrong with that I may add?


So then I tried to create another PHP file that captures the form's data

<?php

//Connect to MySql

This is the code I have so far:


$server=mysql_connect("(server address)","demo","");
if (!$server):
print ("error connecting to server");
exit;
endif;

//Open cp1082 database

mysql_select_db("cp1082",$server);
$sql = "SELECT grp, age, region, premium FROM insurance";



$result = mysql_query($sql);
if (mysql_error()):
print "There has been an error<BR>".mysql_error();
exit;
endif;



$row=mysql_fetch_array($result);
print $row ["grp"];<"br">
print $row ["age"];<"br">
print $row ["region"];<"br">


print "Your premium is" . $row["premium"];

mysql_close($server);

?>

So I gather inside this code I need While and If statements to return the correct insurance quote to the user,
for example if a user inputted group 2, age 18, and region Wales or enter data for just one field as the form parameters it would return so many £'s or $'s for the insurance quote / premium.
But how do I go about implementing this code, I've tried using various if's and when statements but with no success, what if
a user only entered his/her age how do I go about implementing code for this so only the premiums for age 18 for example would be retured?

Help greatly appreciated,
cheers,

W.

Share this post


Link to post
Share on other sites
The form (radio values added)
[code]<form action="capture_car_input.php" method = "post">

<P> Please fill in the form below with your details to generate your car insurance query;

</P>
<P class="P2"> Your insurance group -
</P>
<input type=Radio name=insuregroup value='1'> 1
<input type=Radio name=insuregroup value='2'> 2
<input type=Radio name=insuregroup value='3'> 3
<input type=Radio name=insuregroup value='4'> 4
<input type=Radio name=insuregroup value='5'> 5
<input type=Radio name=insuregroup value='6'> 6

<P class="P2"> Your age (17-70 Only) -
</P>

<input type=text name=custage size ="2" maxlength = "2">
<P class="P2"> Your region of residence -
</P>
<input type=Radio name=custregion value='South East'> South East
<input type=Radio name=custregion value='South West'> South West
<input type=Radio name=custregion value='Midlands'> Midlands
<input type=Radio name=custregion value='North'> North
<input type=Radio name=custregion value='Wales'> Wales
<input type=Radio name=custregion value='Scotland'> Scotland
<P> When you are ready please proceed and click the submit button below.
</P>
<input type="submit" name = 'submit' VALUE = "Submit your query">
<input type= "reset" VALUE = "Resets your details">
</form>[/code]

The processing (query only uses values entered)
[code]<?php
include 'db.php'; // db connection code

$where = array();
$whereclause = '';

if (isset($_POST['insuregroup'])) {
    $grp = $_POST['insuregroup'];
    $where[] = ("grp = '$grp'");
}

if (!empty($_POST['custage'])) {
    $age = $_POST['custage'];
    $where[] = ("age = '$age'");
}

if (isset($_POST['custregion'])) {
    $rgn = $_POST['custregion'];
    $where[] = ("region = '$rgn'");
}

if (count($where) > 0) $whereclause = " WHERE " . join (' AND ', $where);


$sql = "SELECT grp, age, region, premium FROM insurance $whereclause";

$result = mysql_query($sql) or die("There has been an error<BR>".mysql_error());

echo '<table border="1">
       <TR><TD>Group</TD>
       <TD>Age</TD>
       <TD>Region</TD>
       <TD>Premium</TD></TR>';

while (list ($grp, $age, $rgn, $prem) = mysql_fetch_row($result)) {
       echo "<TR><TD>$grp</TD>
       <TD>$age</TD>
       <TD>$rgn</TD>
       <TD>$prem</TD></TR>";
}
echo '</table>';

?>[/code]

Share this post


Link to post
Share on other sites
Thank you very much for your kind help, I have found it to be of much benefit,

Just one more thing, could I have a brief breakdown of how that code actually works, I think I know, I just would like some clarification.

Many thanks!

Share this post


Link to post
Share on other sites
*EDIT* My mistake, not intended in this thread....

Share this post


Link to post
Share on other sites
I want to do this as well except I will be sorting by multiple wheres instead of just one

so barand will I have to make if statements for every single combinations of wheres?
or is there an easier way?


for example the most wheres would be 6 clauses

the least would be 1

and then theres a lot of different combinations between those two

Share this post


Link to post
Share on other sites
I've implemented this code, but when there is no sorting required, and the $whereclause is blank, I get the following error at the line of the query:

[code]Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource[/code]

Any ideas?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.