Jump to content


Photo

Querying a database using a user form.


  • Please log in to reply
5 replies to this topic

#1 Wilsee

Wilsee
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 31 March 2006 - 10:08 PM

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.


#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 01 April 2006 - 10:52 PM

The form (radio values added)
<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>

The processing (query only uses values entered)
<?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>';

?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Wilsee

Wilsee
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 05 April 2006 - 10:04 PM

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!

#4 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 02 October 2006 - 06:37 PM

*EDIT* My mistake, not intended in this thread....

#5 dsaba

dsaba
  • Members
  • PipPipPip
  • Advanced Member
  • 724 posts

Posted 09 March 2007 - 10:55 PM

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

#6 skyer2000

skyer2000
  • Members
  • PipPipPip
  • Advanced Member
  • 114 posts

Posted 10 March 2007 - 09:45 PM

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:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Any ideas?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users