Using functions to perform queries

Sherrie

Hi all!

I'm still pretty new to PHP, and am creating an online survey for work. I have succesfully managed to get the data into the database, and the only way I know to retrieve it is very very tedious as per the attached code below. I have close to 100 questions and each question needs to be sorted by 8 sets of demographic details, which means i'll have to program a total of 800 times, which will take me forever!

Here's my code for each question - it works, but seems extremely long winded

<?php 
include("survey.inc"); 
//Count the total no of responses for the question
$connection=mysql_connect($host,$user,$password) 
            or die ("Couldn't connect to server"); 
$db=mysql_select_db($database,$connection) 
            or die ("Couldn't select database"); 
$result=mysql_query("Select COUNT(Q1) as res from HGsurvey") 
            or DIE ("Couldn't execute total responses"); 
$orgNoQ1=mysql_fetch_array($result); 

//Count the total number of negative responses for the question
$connection=mysql_connect($host,$user,$password) 
            or die ("Couldn't connect to server"); 
$db=mysql_select_db($database,$connection) 
            or die ("Couldn't select database"); 
$result1=mysql_query("Select COUNT(Q1) as q1negative from HGsurvey where Q1='1' or Q1='2'") 
            or DIE ("Couldn't execute negative reponses"); 
$orgnegQ1=mysql_fetch_array($result1); 

//Count the total no of neutral reponses for the question
$connection=mysql_connect($host,$user,$password) 
            or die ("Couldn't connect to server"); 
$db=mysql_select_db($database,$connection) 
            or die ("Couldn't select database"); 
$result2=mysql_query("Select COUNT(Q1) as q1neutral from HGsurvey where Q1='3' ") 
            or DIE ("Couldn't execute neutral"); 
$orgneutralQ1=mysql_fetch_array($result2); 

//Count the total no of positive reponses for the question
$connection=mysql_connect($host,$user,$password) 
            or die ("Couldn't connect to server"); 
$db=mysql_select_db($database,$connection) 
            or die ("Couldn't select database"); 
$result3=mysql_query("Select COUNT(Q1) as q1positive from HGsurvey where Q1='4' or Q1='5' ") 
            or DIE ("Couldn't execute positive"); 
$orgpositiveQ1=mysql_fetch_array($result3); 

//Count the total no of not applicable reponses for the question
$connection=mysql_connect($host,$user,$password) 
            or die ("Couldn't connect to server"); 
$db=mysql_select_db($database,$connection) 
            or die ("Couldn't select database"); 
$result4=mysql_query("Select COUNT(Q1) as q1na from HGsurvey where Q1='6'") 
            or DIE ("Couldn't execute not applicable"); 
$orgnaQ1=mysql_fetch_array($result4); 

echo "Total no. of repondents: ";
echo $orgNoQ1['res'];
echo "<br>";
echo "Total negative reponses: ";
echo $orgnegQ1['q1negative'];
echo "<br>";
echo "Total neutral reponses: ";
echo $orgneutralQ1['q1neutral'];  

echo "<br>"; 
echo "Total positive reponses: ";
echo $orgpositiveQ1['q1positive']; 
echo "<br>";
echo "Total not applicable reponses: ";
echo $orgnaQ1['q1na']; 
echo "<br>";
echo "<br>";

//percentage of negative responses
$percneg=number_format($orgnegQ1['q1negative']/$orgNoQ1['res']*100,2);

//percentage of neutal responses
$percneu=number_format($orgneutralQ1['q1neutral']/$orgNoQ1['res']*100,2);

//percentage of positive responses
$percpos=number_format($orgpositiveQ1['q1positive']/$orgNoQ1['res']*100,2);

//percentage of not applicable responses
$percna=number_format($orgnaQ1['q1na']/$orgNoQ1['res']*100,2);

echo "Percentage of negative responses: \t\t";
echo $percneg;
echo "<br>";
echo "Percentage of neutral responses: ";
echo $percneu;
echo "<br>";
echo "Percentage of positive responses: ";
echo $percpos;
echo "<br>";
echo "Percentage of not applicable responses: ";
echo $percna;
?>

Can someone help me by telling me a way to use functions for my code or even a neater way of doing this?

Would appreciate any help i can get!

Thanks in advance!

MarkR

Firstly, there is no need to connect to the database each time - you can just reuse the existing connection object.

Secondly, you can write a simple function to just execute a query and return the first (and only, in this case) result, and just call that.

Your application seems to consist largely of error handling - this is inefficient and error prone itself - consider using exceptions instead of die, and writing a function which throws on any error (or use PDO and enable the option which makes this happen automatically).

Mark

Shrike

I hate to say it but I think you need to look at your database schema. You seem to have an individual column for each question and therefore each answer, which is making the gathering of result data more difficult. It should be perfectly possible to obtain the whole result using one query.

Now this probably isn't much help to you as you've already written your questionnaire code but I can go into further detail if you like 🙂

Sherrie

MarkR,

Thank you for your reply! Didn't know that I don't have to connect to the db for each query... Thanks a bunch for that!

Am trying to use functions, but for some reason i can't make it work... Here's what I've got so far:

function no_of_responses($qn)
{
//Count the total no of responses for the question
connectdb();
$result=mysql_query("Select COUNT($qn) as $qn from HGsurvey where $qn>'0' ") 
            or DIE ("Couldn't execute total responses"); 
$no=mysql_fetch_array($result); 
$_SESSION['$qn']=$no['$qn'];
echo $_SESSION['$qn'];
}

no_of_responses(Q1);

I'm getting a blank page.

Shrike,

Thank you for your reply! Wasn't sure how else to structure the db. Yup, have already written the questionnaire code, and the pilot data is already in. But I would appreciate more details on how I should have done it 🙂

Sherrie

I've managed to figure out where I stuffed up with my function. I missed out the single quotes in my argument when calling the function. It now works and reads like this:

no_of_responses('Q1');

I've run into another problem though, when I compute all the other aspects of the question, e.g. no. of unfavourable responses.

How do I write my select query to add the word neg before the $qn? Hope I'm making some sense.

Here's the code, maybe this will give you a better picture of what I'm trying to do.

//Count the total number of negative responses for the question
$query="SELECT COUNT($qn) as 'neg{$qn}' from HGsurvey where $qn='1' or $qn='2'";  

$result=mysql_query($query) 
            or DIE ("Couldn't execute negative reponses"); 
$unfav=mysql_fetch_array($result);
$_SESSION['neg{$qn}']=$unfav['neg{$qn}'];
echo $_SESSION['neg{$qn}'];
echo "<br>";

Any help will be greatly appreciated!

bradgrafelman

Column names (or aliases) should be identified with backticks ( ` ) and not single quotes.
Variables will not be parsed in single-quoted strings. Either escape out of the string and concatenate the variable, or use double quotes as your delimiter. For more information on the two different ways to delimit strings, see the manual for what a [man]string[/man] is.
As is mentioned above, you might want to show us your DB structure and how you go about storing data... doesn't seem to be very efficient right now.