Jump to content

Querying a database using a user form.


Wilsee

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.
Link to comment
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]
Link to comment
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!
Link to comment
Share on other sites

  • 5 months later...
  • 5 months later...
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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.