Get Data Input By User Into Php Multi-Row Table And Save In Mysql

sandari · September 28, 2012

I have a data input table like this:

____________________________________________

-----------------------------------------------------------------------

-----------------------------------------------------------------------

-----------------------------------------------------------------------

-----------------------------------------------------------------------

-----------------------------------------------------------------------

-----------------------------------------------------------------------

I need to save the answers for each topic into a MySQL table with the following structure:

TableName

Topic Varchar(100),

Answer1 Varchar(20),

Answer2 Varchar(20),

Answer3 Varchar(20),

Answer4 Varchar(20).

Right now I am getting quite confused with the makeup of the multi-dimensional array needed to do the job.

Any help will be appreciated.

coded4u · September 28, 2012

Not sure how you will be passing the data to the php but here's the query

<?php
//--- Connection must be made before saving anything
// You can either type the connection string here or save it & include it: include("connection.php");
//---- Data you want to save
$title="Topic";
$question1="Answer";
$question2="Answer";
$question3="Answer";
$question4="Answer";
//---- Run mysql query to save the data.
mysql_query("INSERT INTO tablename (Title, Question1, Question2, Question3, Question4,) VALUES ('$title','$question1','$question2','$question3','$question4')");
?>

If you use the post method to save the data make sure you add something to stop SQL injections. Example: mysql_real_escape_string

Christian F. · September 28, 2012

No, that's not the way to do this.

You'll want to structure your database properly, which means you'll have to learn about 3rd normal form. These videos will help explain the concept, and help you get started on that bit:

Though, what C4U said about using mysqli::real_escape_string ()¹ is correct, and is called output escaping. This is something you need to do, but only just before adding the data to the query. You don't want to save it in any variable that you want to use later on, in a different system.

You also need to validate the input, to ensure that whatever the user is sending you is what you expect. I've detailed a bit about it in a post made earlier today, so that should help you get started on that too.

There's also a thread asking about this exact same problem from yesterday, so having a look in that should help a lot too.

Lastly: This is a MySQL question, not a PHP coding problem. As such it should have been posted in the MySQL section.

¹ Use the MySQLi library or PDO, the old MySQL library is outdated and no longer developed. Which means it's missing functionality, and will be made deprecated some time in the future.

Edited September 28, 2012 by Christian F.

sandari · September 29, 2012

My database is in 4th normal form.

Connection has been made to MySQL database.

I need to know how to write the PHP code to assemble the multi-dimensional array in PHP from the rows in the table so I can create the query (in PHP) to write to te database.

I was thinking of something like:

$answers=array($topic=array($answer1,$answer1,$answer1,$answer1));

then:

$query="insert into TableName(Topic, Answer1, answer2, answer3, answer4)

values(foreach($topic as$row=>$answer){foreach($answer as $col=>$value){'$value'}});

So that the input table might look like:

____________________________________________________________________________

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

Or, have I just answered my own question???

Edited September 29, 2012 by sandari

sandari · September 29, 2012

Just to clarify things.

Here is the actual code - all 317 lines of it.

<?php

session_start();

$page_title='Substance Abuse History';

?>

<?php

// Next lines are error checking code

echo "<h5>Line 10: GET Array = ".print_r($_GET,TRUE)."<br />

POST Array= ".print_r($_POST,TRUE)."<br />

SESSION Array= ".print_r($_SESSION,TRUE)."</h5>";

// End of error checking code

require_once ('./secure/transformations_mysqli_connect.php');

$errors=array();

$drug=array();

$drug_usage=array('AgeFirstUsed','AgeRegularUse','YearsDailyUse','DailyAmount','DaysUsedLast4Weeks','DaysUsedLast7Days','LastUsed');

$drug_used=array('DrugOfUse'=>$drug,'usage'=>$drug_usage);

if(isset($_POST['submitted'])){

//ClientID is set for testing purposes only

$ClientID = 1;

$errors=array();

if(empty($_POST['Used'])){

if(empty($_POST['AgeFirstUsed'])){

$errors[]="Please tell us AgeFirstUsed {$_POST['DrugOfUse']}";

}

else{

$drug_used['DrugOfUse'][]=@mysqli_real_escape_string($dbc,$_POST['AgeFirstUsed']);

} // End if(empty($_POST['AgeFirstUsed']))

if(empty($_POST['AgeRegularUse'])){

$errors[]="Please tell us AgeRegularUse of {$_POST['DrugOfUse']}";

}

else{

$drug_used['DrugOfUse'][]=@mysqli_real_escape_string($dbc,$_POST['AgeRegularUse']);

} // End if(empty($_POST['AgeRegularUse']))

if(empty($_POST['YearsDailyUse'])){

$errors[]="Please tell us YearsDailyUse of {$_POST['DrugOfUse']}";

}

else{

$drug_used['DrugOfUse'][]=@mysqli_real_escape_string($dbc,$_POST['YearsDailyUse']);

} // End if(empty($_POST['YearsDailyUse']))

if(empty($_POST['DailyAmount'])){

$errors[]="Please tell us DailyAmount of {$_POST['DrugOfUse']}";

}

else{

$drug_used['DrugOfUse'][]=@mysqli_real_escape_string($dbc,$_POST['DailyAmount']);

} // End if(empty($_POST['DailyAmount']))

if(empty($_POST['DaysUsedLast4Weeks'])){

$errors[]="Please tell us DaysUsedLast4Weeks {$_POST['DrugOfUse']}";

}

else{

$drug_used['DrugOfUse'][]=@mysqli_real_escape_string($dbc,$_POST['DaysUsedLast4Weeks']);

} // End if(empty($_POST['DaysUsedLast4Weeks']))

if(empty($_POST['DaysUsedLast7Days'])){

$errors[]="Please tell us DaysUsedLast7Days {$_POST['DrugOfUse']}";

}

else{

$drug_used['DrugOfUse'][]=@mysqli_real_escape_string($dbc,$_POST['DaysUsedLast7Days']);

} // End if(empty($_POST['DaysUsedLast7Days']))

if(empty($_POST['DateLastUsed'])){

$errors[]="Please tell us DateLastUsed {$_POST['DrugOfUse']}";

}

else{

$drug_used['DrugOfUse'][]=@mysqli_real_escape_string($dbc,$_POST['DateLastUsed']);

} // End if(empty($_POST['DateLastUsed']))

} // End if(!empty($_POST['DrugOfUse']))

// Next lines are error checking code

echo"<h5>Line 82 - Drug: $drug | Usage = $drug_usage</h5>";

echo "<h5>Line 114: Errors contain ".print_r($errors,true)."<br />ClientID = $ClientID</h5>";

// End of error checking code

if(isset($ClientID)){

if(empty($errors)){ //If there were no errors

$query="insert into tblSubstanceHistory

(ClientID,

Substance,

AgeFirstUsed,

AgeRegularUse,

YearsDailyUse,

DailyAmount,

DaysUsedLast4Weeks,

DaysUsedLast7Days,

DateLastUsed)

values ($ClientID,

foreach($drug as $drug=>$drug_used){

foreach($drug_used as $key=>$value){

'$value'

}

);";

$result=mysqli_query($dbc, $query);

$rows=mysqli_affected_rows($dbc);

// Next lines are error checking code

echo "<h5>Line 111: Query = $query";

echo "<h5>Line 88 Rows affected = $rows</h5>";

//End of error checking code

if($result){ //If a insert was successful

// Next line is error checking code

echo "<h5>Line 106 - Next Page is Admin.php</h5>";

//End of error checking code

/*

$url.='./admin.php';

header("location: $url");

exit();

*/

} //End of if INSERT successful

else{ //If not INSERT successful

?>

<h2 align="center"><label>Your reponses could not be recorded.</label><br />

<label>Please call your System Administrator.</label></h2></p>

</form>

</div>

<?php

} //End if no match found

} //End if no errors

else{ //If there are errors

//echo "<h5>Line 119</h5>";

// include('./includes/header.html');

// include('./includes/expandingMenu.html');

echo"<br />";

foreach($errors as $key=>$value){

echo "<h4 align=\"center\">$value</h4>";

}

// echo "<h4 align=\"center\">Please answer ALL questions</h4>";

?>

<h3>

<?php

$query="select DrugOfUse from tblDrugs;";

$result=mysqli_query($dbc,$query) or

trigger_error("$query\n<br />MySQL Error: ".mysqli_error($dbc));

echo "<h6>Line 157 - Query : $query</h6>";

?>

<h2 align="center" ><strong>SUBSTANCE ABUSE HISTORY</strong></h2>

<h5>

<th valign="center" align="centre">Substance</th>

<th valign="center" align="centre">Age<br />First<br />Used</th>

<th valign="center" align="centre">Age<br />Regular<br />Use</th>

<th valign="center" align="centre">Years<br />of<br />Daily<br />Use</th>

<th valign="center" align="centre">Amount<br />Used<br />Daily</th>

<th valign="center" align="centre">Days<br />Used<br />in<br />Last<br />4<br />Weeks</th>

</h5>

</tr>

<?php

$DrugNo=0;

while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){

echo "<tr><h6>";

echo "<td>{$row['DrugOfUse']}</td>";

$drug[]=$row['DrugOfUse'];

$DrugNo++;

echo "<td><input type=\"radio\" name=\"Used\" value=\"True\"/>Yes<input type=\"radio\" name=\"Used\" value=\"False\"/>No</td>";

echo "<td align=\"center\"><input type=\"text\" name=\"AgeFirstUsed\" align=\"center\" size=\"2\" maxlength=\"2\" value=\" \"/></td>";

echo "<td align=\"center\"><input type=\"text\" name=\"AgeRegularUse\" align=\"center\" size=\"2\" maxlength=\"2\" value=\" \"/></td>";

echo "<td align=\"center\"><input type=\"text\" name=\"YearsDailyUse\" align=\"center\" size=\"2\" maxlength=\"2\" value=\" \"/></td>";

echo "<td align=\"center\"><input type=\"text\" name=\"DailyAmount\" align=\"center\" size=\"2\" maxlength=\"2\" value=\" \"/></td>";

echo "<td align=\"center\"><input type=\"text\" name=\"DaysUsedLast4Weeks\" align=\"center\" size=\"2\" maxlength=\"2\" value=\" \"/></td>";

echo "<td align=\"center\"><input type=\"text\" name=\"DaysUsedLast7Days\" align=\"center\" size=\"2\" maxlength=\"2\" value=\" \"/></td>";

echo "<td align=\"center\"><input type=\"text\" name=\"DateLastUsed\" align=\"center\" size=\"20\" maxlength=\"50\" value=\" \"/></td>";

echo "</h6></tr>";

}

?>

</table>

<br />

</div>

</form>

</div>

</h3>

<?php

} //End if there were errors

}//End if(isset($_POST['ClientID']

else{ // if(!isset($_POST['ClientID']

echo"<h3 align=\"center\">This page has been accessed in error<br /><br /><br /><br /><br /></h3>";

?>

</form>

</div>

<?php

}// End if(!isset($_POST['ClientID']

}

else{// if NOT Submitted

?>

<?php

$query="select DrugOfUse from tblDrugs;";

$result=mysqli_query($dbc,$query) or

trigger_error("$query\n<br />MySQL Error: ".mysqli_error($dbc));

//echo "<h6>Line 191 - Query : $query</h6>";

?>

<h2 align="center" ><strong>SUBSTANCE ABUSE HISTORY</strong></h2>

<h5>

<th valign="center" align="centre">Substance</th>

<th valign="center" align="centre">Age<br />First<br />Used</th>

<th valign="center" align="centre">Age<br />Regular<br />Use</th>

<th valign="center" align="centre">Years<br />of<br />Daily<br />Use</th>

<th valign="center" align="centre">Amount<br />Used<br />Daily</th>

<th valign="center" align="centre">Days<br />Used<br />in<br />Last<br />4<br />Weeks</th>

</h5>

</tr>

<?php

$DrugNo=0;

while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){

echo "<tr><h6>";

echo "<td>{$row['DrugOfUse']}</td>";

$drug[$DrugNo]=$row['DrugOfUse'];