Php Extracting Information From Multiple Db's Via Checkboxes

Lorinda · October 2, 2012

Good Afternoon,

I am a newbie at php coding and sql, and I have started a site at work where the users can enter everything that they have done at work for the day including times and dates.

I managed to get the information pulled through to my local DB (Wamp Server)

Now I want the head of the department to be able to get reports and results from those pages using php. I am using check boxes for the head to choose what she wants to see..i.e:

"<tr>

User Name

</span>

</p>

<p>

Requested By

</p>

<p>

Type

</p>

<p>

Member No

</p>

<p>

Scheme

</p>

<p>

From Time

</p>

<p>

From Date

</p>

<p>

To Time

</p>

<p>

To Date

</p>

<p>

Comments

</p>

<p>

"

This look good, but i need the code to assist with (for example) the head of the department selects 3x tick boxes.i.e comments, username and fromtime, the information should then be taken from existing db's like..'stats' or systemletters' and then be complied in one page all together so that we can place that on a report? I hope this make sense. My wording is sometimes less understandable

Please help.. I can send more info aswell if needed. (I have attached what I have come up with thus far)

Thanking you in advance.

show1.php

White_Lily · October 2, 2012

please re-post and use the code tags around your code blocks, so much more easier to read that way.

Edited October 2, 2012 by White_Lily

Lorinda · October 2, 2012

Hope this help

Good Afternoon,

I am a newbie at php coding and sql, and I have started a site at work where the users can enter everything that they have done at work for the day including times and dates.

I managed to get the information pulled through to my local DB (Wamp Server)

Now I want the head of the department to be able to get reports and results from those pages using php. I am using check boxes for the head to choose what she wants to see..i.e:

<tr>
<td class="grey"><p><span class="grey">
  <input name="username" type="checkbox" class="User" id="username" value="48"/>
  User Name
  </span>
  </p>
  <p>
 <input name="requestedby" type="checkbox" id="requestedby" value="49"/>
 Requested By
  </p>
  <p>
 <input name="type" type="checkbox" id="type" value="50"/>
 Type
  </p>
  <p>
 <input name="memberno" type="checkbox" id="memberno" value="51"/>
 Member No
  </p>
  <p>
 <input name="scheme" type="checkbox" id="scheme" value="52"/>
 Scheme
  </p>
  <p>
 <input name="fromtime" type="checkbox" id="fromtime" value="53"/>
 From Time
  </p>
  <p>
 <input name="fromdate" type="checkbox" id="fromdate" value="54"/>
 From Date
  </p>
  <p>
 <input name="totime" type="checkbox" id="totime" value="55"/>
 To Time
  </p>
  <p>
 <input name="todate" type="checkbox" id="todate" value="56"/>
 To Date
  </p>
  <p>
 <input name="comments" type="checkbox" id="comments" value="57"/>
 Comments
 </p>
<p>
						  <input type="submit" value="Send">
						  <input name="Reset" type="reset" value="Clear">

This look good, but i need the code to assist with (for example) the head of the department selects 3x tick boxes.i.e comments, username and fromtime, the information should then be taken from existing db's like..'stats' or systemletters' and then be complied in one page all together so that we can place that on a report? I hope this make sense. My wording is sometimes less understandable

Please help.. I can send more info aswell if needed. (I have attached what I have come up with thus far)

Thanking you in advance.

White_Lily · October 2, 2012

Hmm, you would not get it so that when you click submit it tries to identify which checkboxes have been ticked, then you would use a different page to display the selected data, may i also suggest that all input fields/checkboxes etc need to be within the <form></form> tags

Lorinda · October 2, 2012

I did not add the full script, but they are all within the form tags...

Can you maybe suggest another way I can approach this then ? I can change my page s I like, I thought tick boxes would work better for more selections ?

Barand · October 2, 2012

What table stores the work data entered by the users?

What is the significance of the checkbox values 48, 49 , 50... etc?

Lorinda · October 2, 2012

The tables I use would be: 'stats', 'systemletter' and 'systemchanges' those are the tables created in my DB (wamp Server)

The 48, 49, 50 etc.. is basically for my code to send the info to the spec. db i.e:

<?php
$conn = mysql_connect("localhost","root","");
$db	  = mysql_select_db("mysql",$conn);
?>
<?php
$username = $_POST["username"];
$requestedby = $_POST["requestedby"];
$type = $_POST["type"];
$memberno = $_POST["memberno"];
$scheme = $_POST["scheme"];
$fromtime = $_POST["fromtime"];
$fromdate = $_POST["fromdate"];
$totime = $_POST["totime"];
$todate = $_POST["todate"];
$comments = $_POST["comments"];

if($username == "s"){
$username = 'MHAS002 - Anelia Sinclair';
}else if (%username == "t"){
$username = 'MHMS004 - Melanie Smit';
}else if ($username == "u"){
$username = 'MHMN005 - Marelize Momberg';
}else if ($username =="v"){
$username = 'MHMW005 - Martin van Wyk';
}else if ($username == "w"){
$username = 'MHGC001 - George Curtin';
}else if ($username == "x"){
$username = 'MHGK002 - Gerna du Plessis';
}else if ($username == "y"){
$username = 'MHLW009 - Lorinda Wilkinson';
}else if ($username == "z"){
$username = 'MHZP001 - Zenobia Potgieter';
};
if($requestedby == "a"){
$requestedby = 'Leona Makkink';
}else if ($requestedby == "b"){
$requestedby = 'User';
}else if ($requestedby == "c"){
$requestedby = 'Other';
};

if ($type == 1){
$type = '158DL';
}else if ($type == 2){
$type = '032D1';
}else if ($type == 3){
$type = '140D1';
}else if ($type == 4){
$type = '517D1';
}else if ($type == 5){
$type = '484D1';
}else if ($type == 6){
$type = 'SMS197';
}else if ($type == 7){
$type = 'Relation Code';
}else if ($type == {
$type = 'System Letters';
}else if ($type == 9){
$type = 'Purge Briewe';
}else if ($type == 10){
$type = 'Purge "A" nr';
}else if ($type == 11){
$type = 'IT BB';
}else if ($type == 12){
$type = 'Purge BB';
}else if ($type == 13){
$type = 'Printer';
}else if ($type == 14){
$type = 'Outlook';
}else if ($type == 15){
$type = 'Health & Safety';
}else if ($type == 16){
$type = 'Salary Code';
}else if ($type == 17){
$type = 'Limits';
}else if ($type == 18){
$type = 'Drukstukke Doen';
}else if ($type == 19){
$type = 'Drukstukke Nasien';
}else if ($type == 20){
$type = 'XML Log';
}else if ($type == 21){
$type = 'Ad Hoc Testings';
}else if ($type == 22){
$type = 'Other';
}else if ($type == 23){
$type = 'SCR Report';
}else if ($type == 24){
$type = 'Usr Group oudit';
}else if ($type == 25){
$type = 'Usr Xfer';
}else if ($type == 26){
$type = 'Bertus Stats';
}else if ($type == 27){
$type = 'Yoricks Stats';
}else if ($type == 28){
$type = 'Meetings';
}else if ($type == 29){
$type = 'Nerver Centre';
}else if ($type == 30){
$type = 'Line Count';
};
$sql	 = "INSERT into stats values('$username','$requestedby','$type','$memberno','$scheme','$fromtime','$fromdate','$totime','$todate','$comments')";
$qury  = mysql_query($sql);

if(!$qury)
 echo mysql_error();
else
{
 echo "Successfully Inserted<br />";
  }
?>
<p> </p>
<table border="0" cellspacing="0" cellpadding="3">
<tr><td>
User Name:
<td>
<?=$username?>
<tr><td>
Requested By:
<td>
<?=$requestedby?>
<tr><td>
Type:
<td>
<?=$type?>
<tr><Td>
Member No:
<td>
<?=$memberno?>
<tr><td>
Scheme:
<td>
<?=$scheme?>
<tr><td>
From Time:
<td>
<?=$fromtime?>
<tr><td>
From Date:
<td>
<?=$fromdate?>
<tr><td>
To Time:
<td>
<?=$totime?>
<tr><td>
To Date:
<td>
<?=$todate?>
<tr><td>
Comments:
<td>
<?=$comments?>
<td>
</table>
</form>

<p><A HREF="/Statsp1.html" target="_self"><strong>Go back</strong></A></p>
</body>
</html>

Don't know if it is wrong but it pulls everything through to the specific db (i sue drop down boxes)

<body>
<script src="datepicker.js" language="javascript"></script>
<div id="datepicker" style="position:absolute; width:277px; height:271px; z-index:1; visibility: hidden;" onmouseover="javascript:dpmouseover=true;" onmouseout="javascript:dpmouseover=false;">
<object id="fdatepicker" classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,19,0" type="application/x-shockwave-flash" width="230" height="230">
<param name="movie" value="datepicker.swf" />
<param name="wmode" value="transparent" />
<param name="quality" value="high" />
<param name="swfversion" value="8.0.35.0" />
<embed name="fdatepicker" wmode="transparent" src="datepicker.swf" quality="high" pluginspage="http://www.macromedia.com/go/getflashplayer" type="application/x-shockwave-flash" width="230" height="230"></embed>
</object>
</div>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<form name="stats" action="post.php" method="post">
 <table align="center">
 <td align="center">
 <tr>
   <td height="25" colspan="2" align="center" valign="middle"><h1 class="grey"><strong>Stats</strong></h1>
   <tr>
  <td class="grey"><p> User Name:
  <td><select name="username">
   <option value="s">MHAS002 - Anelia Sinclair</option>
    <option value="t">MHMS004 - </option>
    <option value="u">MHMN005 - </option>
    <option value="v">MHMW005 - </option>
    <option value="w">MHGC001 - </option>
    <option value="x">MHGK002 - </option>
    <option value="y">MHLW009 - </option>
    <option value="z">MHZP001 - </option>
		  </select>
   <tr><td>
<tr>
    <td class="grey"><p> Requested by:
    <td><select name="requestedby">
	  <option value="a">Leona Makkink</option>
	  <option value="b">User</option>
	  <option value="c">Other</option>
    </select>
   <tr><td>
<tr>
	  <td class="grey"><p> Type:
	  <td><select name="type">
	    <option value="1">158DL</option>
	    <option value="2">032D1</option>
	    <option value="3">140D1</option>
	    <option value="4">517D1</option>
	    <option value="5">484D1</option>
	    <option value="6">SMS197</option>
	    <option value="7">Relation Code</option>
	    <option value="8">System Letters</option>
	    <option value="9">Purge Briewe</option>
	    <option value="10">Purge "A" nr</option>
	    <option value="11">IT BB</option>
	    <option value="12">Purge BB</option>
	    <option value="13">Printer</option>
	    <option value="14">Outlook</option>
	    <option value="15">Health & Saftey</option>
	    <option value="16">Salary Code</option>
	    <option value="17">Limits</option>
	    <option value="18">Drukstukke Doen</option>
	    <option value="19">Drukstukke Nasien</option>
	    <option value="20">XML Log</option>
	    <option value="21">Ad Hoc Testing</option>
	    <option value="22">Other</option>
	    <option value="23">SCR Report</option>
	    <option value="24">USR Group Oudit</option>
	    <option value="25">USR Xfer</option>
	    <option value="26">Bertus Stats</option>
	    <option value="27">Yorick Stats</option>
	    <option value="28">Meetings</option>
	    <option value="29">Nerve Centre</option>
	    <option value="30">Line Count</option>
	  </select>
   <tr><td>

<tr>
	    <td class="grey"><p> Member No:
	    <td><input name="memberno" type="text">
   <tr><td>
   <tr>
  <td class="grey">
			    Scheme:
  <td><span id="sprytextfield3">
  <input name="scheme" type="text" id="scheme" value="Medihelp">
  <span class="textfieldRequiredMsg">A value is required.</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span>
   <tr><td>		   
<tr>
		  <td class="grey"><p> From Time:
		  <td><span id="sprytextfield1">
		  <input name="fromtime" type="text" value="00:00:00">
		  <span class="textfieldRequiredMsg">A value is required.			  </span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span>
   <tr class="grey">
		    <td>From Date:<td><span id="sprytextfield4">
		    <label>
			  <input type="text" name="fromdate" id="fromdate">
		    </label>
		    <span class="textfieldRequiredMsg">A value is required.			    </span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span>
<tr>
		    <td class="grey"><p> To Time:
		    <td><span id="sprytextfield2">
		    <input name="totime" type="text" value="00:00:00">
		    <span class="textfieldRequiredMsg">A value is required</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span>

   <span class="textfieldRequiredMsg">A value is required.</span></span>


   <tr>
				  <td colspan=2><span class="grey">To Date:         </span>    <span id="sprytextfield6">
				    <label>
				    <input type="text" name="todate" id="todate">
				  </label>
				  <span class="textfieldRequiredMsg">A value is required.</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span>
   <tr>
				    <td class="grey"><p> Comments:
				    <td><textarea name="comments" rows="7" id="comments"></textarea>
   <tr>
					  <td><p>
					    <p>
						  <input type="submit" value="Send">
						  <input name="Reset" type="reset" value="Clear">
 </table>
 <p><a href="/index.php" target="_self"><img src="/home.jpg" alt="roundhome" width="64" height="71" border="0" /></a></p>
</form>
<script type="text/javascript">
<!--
var sprytextfield1 = new Spry.Widget.ValidationTextField("sprytextfield1", "time", {format:"HH:mm:ss"});
var sprytextfield2 = new Spry.Widget.ValidationTextField("sprytextfield2", "time", {format:"HH:mm:ss"});
var sprytextfield3 = new Spry.Widget.ValidationTextField("sprytextfield3", "custom");
var sprytextfield4 = new Spry.Widget.ValidationTextField("sprytextfield4", "date", {format:"yyyy/mm/dd"});
var sprytextfield5 = new Spry.Widget.ValidationTextField("sprytextfield5");
var sprytextfield6 = new Spry.Widget.ValidationTextField("sprytextfield6", "date", {format:"yyyy/mm/dd"});
//-->
</script>
</body>
</html>

hope it makes sense this way...

Like I said I am new at this and is learning something new everyday (litterally)

Barand · October 2, 2012

All those values that you have hard coded in the script should be in db tables

ie

<option value="30">Line Count</option>

if ($type == 30) $type = 'Line Count';

etc

Lorinda · October 2, 2012

All of them are in the tables, and would that then sort the problem with getting all the information from the 3 tables into one page to be able to generate a report ? Terribly sorry for all the queations, I am just trying to wrap my silly little brain around all the information

Barand · October 2, 2012

Why are you hard coding the data into your script then? If the data changes you have to rewrite the code! Use the data to create your dropdown selects.

Use joins to these tables in your query to get the values for the various codes instead of all those substitutions within the code.

And as for those checkboxes for column selection, instead of

<p>
        <input name="requestedby" type="checkbox" id="requestedby" value="49"/>
        Requested By
  </p>
  <p>
        <input name="type" type="checkbox" id="type" value="50"/>
        Type
  </p>

make the checkbox values the name of the column to be selected from your stats table and give all the checkboxes the same name, say "selectedColumn[]" eg

<p>
        <input name="selectedColumn[]" type="checkbox" id="requestedby" value="requestedby"/>
        Requested By
  </p>
  <p>
        <input name="selectedColumn[]" type="checkbox" id="type" value="type"/>
        Type
  </p>

To build your query all you then need to do use join() to create a comma separated string of the required column names

$selection = mysql_escape_string(join(',', $_POST['selectedColumns']));
$query = "SELECT $selection FROM stats...";

<p>

Lorinda · October 3, 2012

WoW Oaky, that makes a little sense...if I then run the query it will give me all the info in the 'selectedcolumn' of i.e. type and requestedby ?

Barand · October 3, 2012

Here's a cut-down version to for you to play with so you can understand the principle

<?php
include("testDBconnect.php");

   if (isset($_POST['selectedColumn'])) {
    $selection = mysql_escape_string(join(', ', $_POST['selectedColumn']));
    $query = "SELECT $selection FROM stats";
    echo "<p>$query</p>";

    echo "<table border='1' cellpadding='2'>\n";
    $res = mysql_query($query);
    $row = mysql_fetch_assoc($res);
    // table headings
    echo '<tr><th>' . join('</th><th>', array_keys($row)) . "</th></tr>\n";
    // data
    do {
	    echo '<tr><td>' . join('</td><td>', $row) . "</td></tr>\n";
    } while ($row = mysql_fetch_assoc($res));
    echo "</table>\n";    
   }
?>
<form method='post'>
<h3>Select required columns</h3>
<p>
   <input name="selectedColumn[]" type="checkbox" id="requestedby" value="requestedby"/>
   Requested By
</p>
<p>
   <input name="selectedColumn[]" type="checkbox" id="type" value="type"/>
   Type
</p>
<p>
   <input name="selectedColumn[]" type="checkbox" id="memberno" value="memberno"/>
   Member No
</p>
<p>
   <input name="selectedColumn[]" type="checkbox" id="scheme" value="scheme"/>
   Scheme
</p>
<p>
   <input name="selectedColumn[]" type="checkbox" id="fromtime" value="fromtime"/>
   From Time
</p>
<p><input type="submit" name="btnSub" value="Build query" /></p>
</form>

Lorinda · October 3, 2012

WoW thanx, lemme play with it a bit and I'll let you know, thank you very very much for your help, That is really kind of you :happy-04: :happy-04:

Sign In

Php Extracting Information From Multiple Db's Via Checkboxes

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information