[SOLVED] Insert form to DB (Multiple rows, array)

bobbinsbro · October 30, 2008

ok, i looked around a bit, and it turns out that disabled fields are not sent to $_POST[], which causes the if() that checks for empty rows to catch rows which are not infact empty. i will think on how to work around this.

in the meantime, i suggest that you add a default value of <option value="0">...</option> or something similar for all the lists, so that when we do manage to get around the problem with the disabled fields, you don't end up setting both "call" and "other" values in the same row in your DB.

Edit: i take it back. i think i figured a workaround, and in addition to taking care of the disabled fields, i will also set irrelevant fields to "0", so you don't have to add a default <option value="0">...</option> to the lists.

bobbinsbro · October 30, 2008

this should fix things:

first, make a new file called numRows.php. this file will contain only:

<?php
define('NUM_ROWS', 5);
?>

silly to make a whole file for this, but it will future maintenance easier.

include this file where you put the define() in index.php, and also in a similar location under the mysql_connect() in insert.php.

next, in index.php, add nam="type[]" to the "type" select:

      <td width=\"6%\"><select id=\"type".$i."\" name=\"type[]\" onChange=\"fieldEnable(".$i.")\">

now in insert.php change:

for ($i = 0; $i < 5; ++$i){
$afdeling = mysql_real_escape_string($_POST['afdeling'][$i]);
$overige = mysql_real_escape_string($_POST['overige'][$i]);
$uren = mysql_real_escape_string($_POST['uren'][$i]);
$minuten = mysql_real_escape_string($_POST['minuten'][$i]);
$specificatie = mysql_real_escape_string($_POST['specificatie'][$i]);
$omschrijving = mysql_real_escape_string($_POST['omschrijving'][$i]);
$callnr = mysql_real_escape_string($_POST['callnr'][$i]);

if (empty($omschrijving) || ($overige == 0 && ($specificatie == 0 || $afdeling == 0 || $callnr == 0))){
++$checkEmpty; //count each empty row
continue;
}

to:

for ($i = 0; $i < NUM_ROWS; ++$i){
if ($_POST['type'][$i] == 1){ //type is set to "call"

	array_splice($_POST['overige'], $i, 0, array(0)); //add a 0 element to overige array

	$afdeling = mysql_real_escape_string($_POST['afdeling'][$i]);
	$overige = mysql_real_escape_string($_POST['overige'][$i]);
	$uren = mysql_real_escape_string($_POST['uren'][$i]);
	$minuten = mysql_real_escape_string($_POST['minuten'][$i]);
	$specificatie = mysql_real_escape_string($_POST['specificatie'][$i]);
	$omschrijving = mysql_real_escape_string($_POST['omschrijving'][$i]);
	$callnr = mysql_real_escape_string($_POST['callnr'][$i]);
}
else if ($_POST['type'][$i] == 2){ //type is set to "other"
	array_splice($_POST['afdeling'], $i, 0, array(0)); //add a 0 element to afdeling array
	array_splice($_POST['specificatie'], $i, 0, array(0)); //add a 0 element to specificatie array
	array_splice($_POST['callnr'], $i, 0, array("0")); //add a 0 string element to callnr array

	$afdeling = mysql_real_escape_string($_POST['afdeling'][$i]);
	$overige = mysql_real_escape_string($_POST['overige'][$i]);
	$uren = mysql_real_escape_string($_POST['uren'][$i]);
	$minuten = mysql_real_escape_string($_POST['minuten'][$i]);
	$specificatie = mysql_real_escape_string($_POST['specificatie'][$i]);
	$omschrijving = mysql_real_escape_string($_POST['omschrijving'][$i]);
	$callnr = mysql_real_escape_string($_POST['callnr'][$i]);
}

if (empty($omschrijving) || ($_POST['type'][$i] == 0)){
++$checkEmpty; //count each empty row
continue;
}

that should do it.

the problem was that id there are 5 rows, and in one of them afdeling was set to "disabled", instead of there being 5 elements in $_POST['afdeling'], there were 4. so what i did was check which fields were set to "disabled" (by checking the value of $_POST['type'] which will always have all 5 elements), and inserting an empty element where ever one was missing (that's what the array_splice() functions do).

also, i changed the empty line check to check the value of $_POST['type'], which is more efficient.

GamerGun · October 30, 2008

Thanks, it looks like it works now. Will test it better tomorrow at work (its almost 10pm here).

Thanks for all the effort! You do a great job and i'm trying to understand it. Will read the entire code asap.

Greetings -T

GamerGun · October 31, 2008

Well i've been testing but seems like OVERID isn't submitted no more.

De volgende werkzaamheden zijn toegevoegd: - The following records are added:

'2', '', '', '', '3', '30', 'test', '', '2008-10-08', '41'
'2', '', '', '', '1', '45', 'test2', '', '2008-10-08', '41'

http://gamergun.com/uren/records.php

With overid%281%29.png

Any idea? Thanks

bobbinsbro · October 31, 2008

i think perhaps this will fix it:

for ($i = 0; $i < NUM_ROWS; ++$i){
if ($_POST['type'][$i] == 1){ //type is set to "call"

	array_splice($_POST['overige'], $i, 0, array(0)); //add a 0 element to overige array

	$afdeling = mysql_real_escape_string($_POST['afdeling'][$i]);
	$overige = mysql_real_escape_string($_POST['overige'][$i]);
	$uren = mysql_real_escape_string($_POST['uren'][$i]);
	$minuten = mysql_real_escape_string($_POST['minuten'][$i]);
	$specificatie = mysql_real_escape_string($_POST['specificatie'][$i]);
	$omschrijving = mysql_real_escape_string($_POST['omschrijving'][$i]);
	$callnr = mysql_real_escape_string($_POST['callnr'][$i]);
}
else if ($_POST['type'][$i] == 2){ //type is set to "other"
	array_splice($_POST['afdeling'], $i, 0, array(0)); //add a 0 element to afdeling array
	array_splice($_POST['specificatie'], $i, 0, array(0)); //add a 0 element to specificatie array
	array_splice($_POST['callnr'], $i, 0, array("0")); //add a 0 string element to callnr array

	$afdeling = mysql_real_escape_string($_POST['afdeling'][$i]);
	$overige = mysql_real_escape_string($_POST['overige'][$i]);
	$uren = mysql_real_escape_string($_POST['uren'][$i]);
	$minuten = mysql_real_escape_string($_POST['minuten'][$i]);
	$specificatie = mysql_real_escape_string($_POST['specificatie'][$i]);
	$omschrijving = mysql_real_escape_string($_POST['omschrijving'][$i]);
	$callnr = mysql_real_escape_string($_POST['callnr'][$i]);
}
else{ //if $_POST['type'] == 0
	array_splice($_POST['afdeling'], $i, 0, array(0)); //add a 0 element to afdeling array
	array_splice($_POST['specificatie'], $i, 0, array(0)); //add a 0 element to specificatie array
	array_splice($_POST['callnr'], $i, 0, array("0")); //add a 0 string element to callnr array
	array_splice($_POST['overige'], $i, 0, array(0)); //add a 0 element to overige array
}

if (empty($omschrijving) || ($_POST['type'][$i] == 0)){
++$checkEmpty; //count each empty row
continue;
}

i was only adding missing elements on rows that were either type 1 or type 2, but ignoring rows that were set to type 0. i now added an else{} block to add a 0 element to all fields when type==0 (the row remains empty, but the arrays get padded to the proper size).

GamerGun · October 31, 2008

I see what you mean. And it works now Thanks.

BTW, (something else) I was already trying on something to select records from the database, but somehow it blew up.

records2.php

<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

error_reporting(E_ALL);
if (!isset($_POST['Submit'])) {
// form not submitted
?>

<?php
}

else {

// form submitted
// set server access variables
$host = "localhost";
$user = "root";
$pass = "password";
$db = "urendatabase";

$search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
$dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//select Database

mysql_select_db($db) or die ("Unable to connect to database");

$query1 = mysql_query(
"SELECT `userid`,
`voornaam`,
`achternaam`
FROM `werknemers`
ORDER BY `userid` ASC"
) or die (mysql_error());

while ($data = mysql_fetch_assoc($query1))
{
echo "<form method=\"post\" action=".$_SERVER['PHP_SELF'].">
Zoeken <input type=\"text\" name=\"search\"><br>
<select size=\"1\" name=\"dropdown\">
<option value=\"\" selected>Zoek op...</option>
<option value='".$data['userid']."'>".$data['voornaam']." ".$data['achternaam']."</option>
<option value=\"userid\">Medewerker</option>
<option value=\"specid\">Gerelateerd</option>
<option value=\"overid\">Overige</option>
<option value=\"afdelingid\">Afdeling</option>
<option value=\"uren\">Uren</option>
<option value=\"minuten\">Minuten</option>
<option value=\"omschrijving\">Omschrijving</option>
<option value=\"callnr\">Call nr</option>
<option value=\"datum\">Datum</option>
<option value=\"week\">Week</option>
</select>
<input type=\"Submit\" value=\"Verstuur\" name=\"Submit\">
</form>";
}

//Create Query

$query = "select * FROM uren where $dropdown='$search'" or die (mysql_error());

$result = mysql_query($query) or die (mysql_error());

$num=mysql_numrows($result);

mysql_close($connect);

echo "<b><center>Database output</center></b><br><br>";

$i=0;
while ($i < $num) {

$userid=mysql_result($result,$i,"userid");
$specid=mysql_result($result,$i,"specid");
$overid=mysql_result($result,$i,"overid");
$afdelingid=mysql_result($result,$i,"afdelingid");
$uren=mysql_result($result,$i,"uren");
$minuten=mysql_result($result,$i,"minuten");
$omschrijving=mysql_result($result,$i,"omschrijving");
$callnr=mysql_result($result,$i,"callnr");
$datum=mysql_result($result,$i,"datum");
$week=mysql_result($result,$i,"week");

echo "<table border=\"1\" bordercolor=\"#FFCC00\" style=\"background-color:#FFFFCC\" width=\"100%\" cellpadding=\"3\" cellspacing=\"3\">
<tr>
	<td width=\"15%\">User ID</td>
	<td width=\"85%\">$userid</td>
</tr>
<tr>
	<td width=\"15%\">Spec ID</td>
	<td width=\"85%\">$specid</td>
</tr>
<tr>
	<td width=\"15%\">Over ID</td>
	<td width=\"85%\">$overid</td>
</tr>
<tr>
	<td width=\"15%\">Afdeling ID</td>
	<td width=\"85%\">$afdelingid</td>
</tr>
<tr>
	<td width=\"15%\">Uren</td>
	<td width=\"85%\">$uren</td>
</tr>
<tr>
	<td width=\"15%\">Minuten</td>
	<td width=\"85%\">$minuten</td>
</tr>
<tr>
	<td width=\"15%\">Omschrijving</td>
	<td width=\"85%\">$omschrijving</td>
</tr>
<tr>
	<td width=\"15%\">Call nr</td>
	<td width=\"85%\">$callnr</td>
</tr>
<tr>
	<td width=\"15%\">Datum (YY/MM/DD)</td>
	<td width=\"85%\">$datum</td>
</tr>
<tr>
	<td width=\"15%\">Week</td>
	<td width=\"85%\">$week</td>
</tr>
</table><br>";

$i++;

}
}
?>

</body>
</html>

It just gives a blank screen but no errors in the error log. Any idea why? Thanks!

bobbinsbro · October 31, 2008

i'm not done reading throught the code yet, but you should xxx out passwords and usernames when you post code here... (talking about database connection)

GamerGun · October 31, 2008

I always replace my real password by "password" . That root is the user doesn't care if people know that.

Forgot it one time but i looked through the entire topic and my password is gone from all pages but thanks for the tip

bobbinsbro · October 31, 2008

could you show me the code for the search page you're using to post the search?

GamerGun · October 31, 2008

Thats in the same code. The table part with .$_SERVER['PHP_SELF'].

I just don't know why it doesn't show up. It worked until i placed it into the PHP code in order to be able to search real words instead of ID's

bobbinsbro · October 31, 2008

lol, i thought you were getting the search request from another file, because the search area in your file resides inside the block that executes when isset($_POST['submit']) == TRUE. basically, will only display the search form if you submit a search .

move the search form into the if(!isset($_POST['search']){} block, and out of the else{} block.

GamerGun · October 31, 2008

My bad, stupid me!

I don't get it tho, now it outputs the search field 5 times, and also lists the employee names, which i don't want.

I just want the option to search within employees, departments and such, which i get working with ID's but not real names (words).

http://gamergun.com/uren/records2.php

Enter 2 at "Medewerker" (= Employees)

<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

echo "<form method=\"post\" action=".$_SERVER['PHP_SELF'].">
Zoeken <input type=\"text\" name=\"search\"><br>
<select size=\"1\" name=\"dropdown\">
<option value=\"\" selected>Zoek op...</option>
<option value=\"userid\">Medewerker</option>
<option value=\"specid\">Gerelateerd</option>
<option value=\"overid\">Overige</option>
<option value=\"afdelingid\">Afdeling</option>
<option value=\"uren\">Uren</option>
<option value=\"minuten\">Minuten</option>
<option value=\"omschrijving\">Omschrijving</option>
<option value=\"callnr\">Call nr</option>
<option value=\"datum\">Datum</option>
<option value=\"week\">Week</option>
</select>
<input type=\"Submit\" value=\"Verstuur\" name=\"Submit\">
</form>";

error_reporting(E_ALL);
if (!isset($_POST['Submit'])) {
// form not submitted
?>

<?php

}

else {

// form submitted
// set server access variables
$host = "localhost";
$user = "root";
$pass = "password";
$db = "urendatabase";

$search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
$dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//select Database

mysql_select_db($db) or die ("Unable to connect to database");

//Create Query

$query = "select * FROM uren where $dropdown='$search'" or die (mysql_error());

$result = mysql_query($query) or die (mysql_error());

$num=mysql_numrows($result);

mysql_close($connect);

echo "<b><center>Database output</center></b><br><br>";

$i=0;
while ($i < $num) {

$userid=mysql_result($result,$i,"userid");
$specid=mysql_result($result,$i,"specid");
$overid=mysql_result($result,$i,"overid");
$afdelingid=mysql_result($result,$i,"afdelingid");
$uren=mysql_result($result,$i,"uren");
$minuten=mysql_result($result,$i,"minuten");
$omschrijving=mysql_result($result,$i,"omschrijving");
$callnr=mysql_result($result,$i,"callnr");
$datum=mysql_result($result,$i,"datum");
$week=mysql_result($result,$i,"week");

echo "<table border=\"1\" bordercolor=\"#FFCC00\" style=\"background-color:#FFFFCC\" width=\"100%\" cellpadding=\"3\" cellspacing=\"3\">
<tr>
	<td width=\"15%\">User ID</td>
	<td width=\"85%\">$userid</td>
</tr>
<tr>
	<td width=\"15%\">Spec ID</td>
	<td width=\"85%\">$specid</td>
</tr>
<tr>
	<td width=\"15%\">Over ID</td>
	<td width=\"85%\">$overid</td>
</tr>
<tr>
	<td width=\"15%\">Afdeling ID</td>
	<td width=\"85%\">$afdelingid</td>
</tr>
<tr>
	<td width=\"15%\">Uren</td>
	<td width=\"85%\">$uren</td>
</tr>
<tr>
	<td width=\"15%\">Minuten</td>
	<td width=\"85%\">$minuten</td>
</tr>
<tr>
	<td width=\"15%\">Omschrijving</td>
	<td width=\"85%\">$omschrijving</td>
</tr>
<tr>
	<td width=\"15%\">Call nr</td>
	<td width=\"85%\">$callnr</td>
</tr>
<tr>
	<td width=\"15%\">Datum (YY/MM/DD)</td>
	<td width=\"85%\">$datum</td>
</tr>
<tr>
	<td width=\"15%\">Week</td>
	<td width=\"85%\">$week</td>
</tr>
</table><br>";

$i++;

}
}
?>

</body>
</html>

bobbinsbro · October 31, 2008

i'm sorry, but i don't understand what you want the search to output to the screen. it looks to me like it's fetching the data you ask for...?

GamerGun · October 31, 2008

Yes, it works, but instead of 2 (= userid from Tommy van Extel), i wanna give up Tommy (or Tommy van Extel, whatever matches) and then the output shows up.

Same for afdeling (departments) and such.

bobbinsbro · October 31, 2008

i've been trying to put together a super ultimate sql query that will convert userid to username, but i don't think it can be done if you're expecting more than 1 row (however, i am far from being a SQL guru, so i'm not positive):

- when fetching a name from the users table, u get 1 row returned. same for afdeling, overige and specificatie.

- when fetching a report from the uren table, u get an unknown amount of rows (from 0 to ... lots).

if we were to try to get the uren report and preporm a "join on userid" with the username row, we'd get (i think) first row with the name, and all the rest of the rows with NULL instead of names (since sql pads with null).

and so i think you'll have to call the username, afdeling, etc. tables from the DB separatly, and convert ids to strings in the script.

what i would do is call the whole users table (and those other 3 tables as well) before:

$query = "select * FROM uren where $dropdown='$search'" or die (mysql_error());

save them multi-dimensional arrays (or single dimension where the id is the key and the string is the value), and then while in the loop (where you show the search results) use these arrays to perform the conversion from id to string.

i don't have time to write out an example right now, so i hope you understand what i mean. if you don't, i'll throw something together sometime tomorrow.

anyway, i'm not completely sure this cannot be done using only sql. i would ask on an sql forum. there are some crazy ppl out there who can do really amazing things with sql queries.

bobbinsbro · November 1, 2008

an example as promised:

get the users table from db and store it in an array (i made up column names. make sure you change them, and put them in the proper order in list()):

$sql = "select * from users";
$result = mysql_query($sql);

$namesArray = array(); //this array will store the id's as keys and the names as values

//get all the rows from the DB table into the array
while(list($id, $firstName, $lastName) = mysql_fetch_array($result)){
$namesArray[$id] = $firstName." ".$lastName;
}

then use the values of the array in the report creation (this is a bit from your code - the printing of the report tables):

echo "<table border=\"1\" bordercolor=\"#FFCC00\" style=\"background-color:#FFFFCC\" width=\"100%\" cellpadding=\"3\" cellspacing=\"3\">
<tr>
	<td width=\"15%\">User ID</td>
	<td width=\"85%\">{$namesArray[$userid]}</td>
</tr>

the keys of the array are all user-id's, $userid will always contain a value that is a key of $namesArray.

use similar code for specificatie, overige and afdeling.

GamerGun · November 4, 2008

Sorry for my late reply.

Well, i tried your code, see below:

<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

echo "<form method=\"post\" action=".$_SERVER['PHP_SELF'].">
Zoeken <input type=\"text\" name=\"search\"><br>
<select size=\"1\" name=\"dropdown\">
<option value=\"\" selected>Zoek op...</option>
<option value=\"userid\">Medewerker</option>
<option value=\"specid\">Gerelateerd</option>
<option value=\"overid\">Overige</option>
<option value=\"afdelingid\">Afdeling</option>
<option value=\"uren\">Uren</option>
<option value=\"minuten\">Minuten</option>
<option value=\"omschrijving\">Omschrijving</option>
<option value=\"callnr\">Call nr</option>
<option value=\"datum\">Datum</option>
<option value=\"week\">Week</option>
</select>
<input type=\"Submit\" value=\"Verstuur\" name=\"Submit\">
</form>";

error_reporting(E_ALL);
if (!isset($_POST['Submit'])) {
// form not submitted
?>

<?php

}

else {

// form submitted
// set server access variables
$host = "localhost";
$user = "root";
$pass = "password";
$db = "urendatabase";

$search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
$dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//select Database

mysql_select_db($db) or die ("Unable to connect to database");

//Create Query

$sql = "select * from users";
$result = mysql_query($sql);

$namesArray = array(); //this array will store the id's as keys and the names as values

//get all the rows from the DB table into the array
while(list($userid, $voornaam, $achternaam) = mysql_fetch_array($result)){
   $namesArray[$userid] = $voornaam." ".$achternaam;
}

$num=mysql_numrows($result);

mysql_close($connect);

echo "<b><center>Database output</center></b><br><br>";

$i=0;
while ($i < $num) {

$userid=mysql_result($result,$i,"userid");
$specid=mysql_result($result,$i,"specid");
$overid=mysql_result($result,$i,"overid");
$afdelingid=mysql_result($result,$i,"afdelingid");
$uren=mysql_result($result,$i,"uren");
$minuten=mysql_result($result,$i,"minuten");
$omschrijving=mysql_result($result,$i,"omschrijving");
$callnr=mysql_result($result,$i,"callnr");
$datum=mysql_result($result,$i,"datum");
$week=mysql_result($result,$i,"week");

echo "<table border=\"1\" bordercolor=\"#FFCC00\" style=\"background-color:#FFFFCC\" width=\"100%\" cellpadding=\"3\" cellspacing=\"3\">
   <tr>
      <td width=\"15%\">User ID</td>
      <td width=\"85%\">{$namesArray[$userid]}</td>
   </tr>
   <tr>
      <td width=\"15%\">Voornaam</td>
      <td width=\"85%\">{$namesArray[$voornaam]}</td>
   </tr>
   <tr>
      <td width=\"15%\">Achternaam</td>
      <td width=\"85%\">{$namesArray[$achternaam]}</td>
   </tr>
<tr>
	<td width=\"15%\">Spec ID</td>
	<td width=\"85%\">$specid</td>
</tr>
<tr>
	<td width=\"15%\">Over ID</td>
	<td width=\"85%\">$overid</td>
</tr>
<tr>
	<td width=\"15%\">Afdeling ID</td>
	<td width=\"85%\">$afdelingid</td>
</tr>
<tr>
	<td width=\"15%\">Uren</td>
	<td width=\"85%\">$uren</td>
</tr>
<tr>
	<td width=\"15%\">Minuten</td>
	<td width=\"85%\">$minuten</td>
</tr>
<tr>
	<td width=\"15%\">Omschrijving</td>
	<td width=\"85%\">$omschrijving</td>
</tr>
<tr>
	<td width=\"15%\">Call nr</td>
	<td width=\"85%\">$callnr</td>
</tr>
<tr>
	<td width=\"15%\">Datum (YY/MM/DD)</td>
	<td width=\"85%\">$datum</td>
</tr>
<tr>
	<td width=\"15%\">Week</td>
	<td width=\"85%\">$week</td>
</tr>
</table><br>";

$i++;

}
}
?>

</body>
</html>

But it outputs nothing. Just empty "Database output" when i fill in the user id or the user name at "Werknemers"

Thanks

bobbinsbro · November 4, 2008

the code i posted is meant to be used in addition to the query you already use.

put my query + loop before:

$sql = "select * from users";
$result = mysql_query($sql);

$namesArray = array(); //this array will store the id's as keys and the names as values

//get all the rows from the DB table into the array
while(list($userid, $voornaam, $achternaam) = mysql_fetch_array($result)){
   $namesArray[$userid] = array('voornaam' => $voornaam, 'achternaam' => $achternaam);
}

unset($result);

//Create Query

$query = "select * FROM uren where $dropdown='$search'" or die (mysql_error());

$result = mysql_query($query) or die (mysql_error());

i added unset($result) before the line //Create Query (because both your code and mine use $result to store the query result, and we want to avoid things getting messed up because of that).

also, since you use first name and last name separately, i changed the name array, so you should changed the output table to correspond:

echo "<table border=\"1\" bordercolor=\"#FFCC00\" style=\"background-color:#FFFFCC\" width=\"100%\" cellpadding=\"3\" cellspacing=\"3\">
   <tr>
      <td width=\"15%\">User ID</td>
      <td width=\"85%\">$userid</td>
   </tr>
   <tr>
      <td width=\"15%\">Voornaam</td>
      <td width=\"85%\">{$namesArray[$userid]['voornaam']}</td>
   </tr>
   <tr>
      <td width=\"15%\">Achternaam</td>
      <td width=\"85%\">{$namesArray[$userid]['achternaam']}</td>
   </tr>

GamerGun · November 4, 2008

Ahhh okay via that way.

Well, inserting 2 works, but still not for my full name. I guess i need something in order for the script to know that 2 = Tommy van Extel for example.

Same for departments and such.

My script at the moment. I know, it's kinda dirty, but i didn't know how to do it else.

<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

echo "<form method=\"post\" action=".$_SERVER['PHP_SELF'].">
Zoeken <input type=\"text\" name=\"search\"><br>
<select size=\"1\" name=\"dropdown\">
<option value=\"\" selected>Zoek op...</option>
<option value=\"userid\">Medewerker</option>
<option value=\"specid\">Gerelateerd</option>
<option value=\"overid\">Overige</option>
<option value=\"afdelingid\">Afdeling</option>
<option value=\"uren\">Uren</option>
<option value=\"minuten\">Minuten</option>
<option value=\"omschrijving\">Omschrijving</option>
<option value=\"callnr\">Call nr</option>
<option value=\"datum\">Datum</option>
<option value=\"week\">Week</option>
</select>
<input type=\"Submit\" value=\"Verstuur\" name=\"Submit\">
</form>";

error_reporting(E_ALL);
if (!isset($_POST['Submit'])) {
// form not submitted
?>

<?php

}

else {

// form submitted
// set server access variables
$host = "localhost";
$user = "root";
$pass = "password";
$db = "urendatabase";

$search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
$dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

// Open Connection

$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//select Database

mysql_select_db($db) or die ("Unable to connect to database");

$sql1 = "select * from werknemers";
$result1 = mysql_query($sql1);

$namesArray1 = array(); //this array will store the id's as keys and the names as values

//get all the rows from the DB table into the array
while(list($userid, $voornaam, $achternaam) = mysql_fetch_array($result1)){
   $namesArray1[$userid] = array('voornaam' => $voornaam, 'achternaam' => $achternaam);
}

$sql2 = "select * from afdelingen";
$result2 = mysql_query($sql2);

$namesArray2 = array(); //this array will store the id's as keys and the names as values

//get all the rows from the DB table into the array
while(list($afdelingid, $afdeling) = mysql_fetch_array($result2)){
   $namesArray2[$afdelingid] = array('afdeling' => $afdeling);
}

$sql3 = "select * from overige";
$result3 = mysql_query($sql3);

$namesArray3 = array(); //this array will store the id's as keys and the names as values

//get all the rows from the DB table into the array
while(list($overid, $overige) = mysql_fetch_array($result3)){
   $namesArray3[$overid] = array('overige' => $overige);
}

$sql4 = "select * from specificatie";
$result4 = mysql_query($sql4);

$namesArray4 = array(); //this array will store the id's as keys and the names as values

//get all the rows from the DB table into the array
while(list($specid, $specificatie) = mysql_fetch_array($result4)){
   $namesArray4[$specid] = array('specificatie' => $specificatie);
}

unset($result);

//Create Query

$query = "select * FROM uren where $dropdown='$search'" or die (mysql_error());

$result = mysql_query($query) or die (mysql_error());

$num=mysql_numrows($result);

mysql_close($connect);

echo "<b><center>Database output</center></b><br><br>";

$i=0;
while ($i < $num) {

$userid=mysql_result($result,$i,"userid");
$specid=mysql_result($result,$i,"specid");
$overid=mysql_result($result,$i,"overid");
$afdelingid=mysql_result($result,$i,"afdelingid");
$uren=mysql_result($result,$i,"uren");
$minuten=mysql_result($result,$i,"minuten");
$omschrijving=mysql_result($result,$i,"omschrijving");
$callnr=mysql_result($result,$i,"callnr");
$datum=mysql_result($result,$i,"datum");
$week=mysql_result($result,$i,"week");

echo "<table border=\"1\" bordercolor=\"#FFCC00\" style=\"background-color:#FFFFCC\" width=\"100%\" cellpadding=\"3\" cellspacing=\"3\">
   <tr>
      <td width=\"15%\">User ID</td>
      <td width=\"85%\">$userid</td>
   </tr>
   <tr>
      <td width=\"15%\">Voornaam</td>
      <td width=\"85%\">{$namesArray1[$userid]['voornaam']}</td>
   </tr>
   <tr>
      <td width=\"15%\">Achternaam</td>
      <td width=\"85%\">{$namesArray1[$userid]['achternaam']}</td>
   </tr>
<tr>
	<td width=\"15%\">Spec ID</td>
      <td width=\"85%\">{$namesArray4[$specid]['specificatie']}</td>
</tr>
<tr>
	<td width=\"15%\">Over ID</td>
      <td width=\"85%\">{$namesArray3[$overid]['overige']}</td>
</tr>
<tr>
	<td width=\"15%\">Afdeling ID</td>
      <td width=\"85%\">{$namesArray2[$afdelingid]['afdeling']}</td>
</tr>
<tr>
	<td width=\"15%\">Uren</td>
	<td width=\"85%\">$uren</td>
</tr>
<tr>
	<td width=\"15%\">Minuten</td>
	<td width=\"85%\">$minuten</td>
</tr>
<tr>
	<td width=\"15%\">Omschrijving</td>
	<td width=\"85%\">$omschrijving</td>
</tr>
<tr>
	<td width=\"15%\">Call nr</td>
	<td width=\"85%\">$callnr</td>
</tr>
<tr>
	<td width=\"15%\">Datum (YY/MM/DD)</td>
	<td width=\"85%\">$datum</td>
</tr>
<tr>
	<td width=\"15%\">Week</td>
	<td width=\"85%\">$week</td>
</tr>
</table><br>";

$i++;

}
}
?>

</body>
</html>

GamerGun · November 4, 2008

Tried something like this;

$query = "select * FROM werknemers OUTER JOIN uren ON (werknemers.userid = uren.userid) where $dropdown='$search'" or die (mysql_error());

But that gives:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN uren ON (werknemers.userid = uren.userid) where userid='Tommy van Ext' at line 1

or

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN uren ON (werknemers.userid = uren.userid) where userid='2'' at line 1

Can't work at all i guess, because even if this should work i still have the problem with departments and such but i'm just hopeless trying haha.

Thanks

bobbinsbro · November 4, 2008

i tidied your code a little bit. i hope you don't mind

<html>
<head>
</head>
<body>
<basefont face="Arial">

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
Zoeken <input type="text" name="search"><br />
<select size="1" name="dropdown">
	<option value="" selected>Zoek op...</option>
	<option value="userid">Medewerker</option>
	<option value="specid">Gerelateerd</option>
	<option value="overid">Overige</option>
	<option value="afdelingid">Afdeling</option>
	<option value="uren">Uren</option>
	<option value="minuten">Minuten</option>
	<option value="omschrijving">Omschrijving</option>
	<option value="callnr">Call nr</option>
	<option value="datum">Datum</option>
	<option value="week">Week</option>
</select>
<input type="Submit" value="Verstuur" name="Submit" />
</form>

<?php
error_reporting(E_ALL);

if (!isset($_POST['Submit'])) {
// form not submitted
}
else {

// form submitted
// set server access variables
$host = "localhost";
$user = "root";
$pass = "password";
$db = "urendatabase";

$search = empty($_POST['search'])? die ("ERROR: Je moet wel iets invullen") : mysql_escape_string($_POST['search']);
$dropdown = empty($_POST['dropdown'])? die ("ERROR: Je moet wel een keuze maken") : mysql_escape_string($_POST['dropdown']);

// Open Connection
$connect = mysql_connect($host, $user, $pass) or die ("Unable to connect to host");

//select Database
mysql_select_db($db) or die ("Unable to connect to database");

//save all queries in an array, to tidy things up a bit
$sql = array('werk' => 'SELECT * FROM werknemers',
			 'afd' => 'SELECT * FROM afdelingen',
			 'over' => 'SELECT * FROM overige',
			 'spec' => 'SELECT * FROM specificatie');

//go through the $sql array, and get values from DB for each query
foreach ($sql as $key => $sql){
	$result = mysql_query($sql);
	switch ($key){
		case 'werk':
			$werkArray = array();
			while(list($userid, $voornaam, $achternaam) = mysql_fetch_array($result)){
  					$werkArray[$userid] = array('voornaam' => $voornaam, 'achternaam' => $achternaam);
			}
			break;
		case 'afd':
			$afdArray = array();
			while(list($afdelingid, $afdeling) = mysql_fetch_array($result)){
				$afdArray[$afdelingid] = $afdeling;
			}
			break;
		case 'over':
			$overigeArray = array();
			while(list($overid, $overige) = mysql_fetch_array($result)){
				$overigeArray[$overid] = $overige;
			}
			break;
		case 'spec':
			$specArray = array();
			while(list($specid, $specificatie) = mysql_fetch_array($result4)){
				$specArray[$specid] = $specificatie;
			}
			break;
		default:
			die('there\'s a bug in the system!');
	}
	unset($result);
}

//Create search query
$query = "select * FROM uren where $dropdown='$search'" or die (mysql_error());

$result = mysql_query($query) or die (mysql_error());

$num=mysql_numrows($result);

mysql_close($connect);

echo "<b><center>Database output</center></b><br /><br />";

for($i = 0; $i < $num; ++$i) {

	$userid=mysql_result($result,$i,"userid");
	$specid=mysql_result($result,$i,"specid");
	$overid=mysql_result($result,$i,"overid");
	$afdelingid=mysql_result($result,$i,"afdelingid");
	$uren=mysql_result($result,$i,"uren");
	$minuten=mysql_result($result,$i,"minuten");
	$omschrijving=mysql_result($result,$i,"omschrijving");
	$callnr=mysql_result($result,$i,"callnr");
	$datum=mysql_result($result,$i,"datum");
	$week=mysql_result($result,$i,"week");
?>
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="100%" cellpadding="3" cellspacing="3">
   <tr>
      <td width="15%">User ID</td>
      <td width="85%"><?php echo $userid; ?></td>
   </tr>
   <tr>
      <td width="15%">Voornaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['voornaam']; ?></td>
   </tr>
   <tr>
      <td width="15%">Achternaam</td>
      <td width="85%"><?php echo $werkArray[$userid]['achternaam']; ?></td>
   </tr>
<tr>
	<td width="15%">Spec ID</td>
      <td width="85%"><?php echo $specArray[$specid]; ?></td>
</tr>
<tr>
	<td width="15%">Over ID</td>
      <td width="85%"><?php echo $overigeArray[$overid]; ?></td>
</tr>
<tr>
	<td width="15%">Afdeling ID</td>
      <td width="85%"><?php echo $afdArray[$afdelingid]; ?></td>
</tr>
<tr>
	<td width="15%">Uren</td>
	<td width="85%"><?php echo $uren; ?></td>
</tr>
<tr>
	<td width="15%">Minuten</td>
	<td width="85%"><?php echo $minuten ?></td>
</tr>
<tr>
	<td width="15%">Omschrijving</td>
	<td width="85%"><?php echo $omschrijving; ?></td>
</tr>
<tr>
	<td width="15%">Call nr</td>
	<td width="85%"><?php echo $callnr; ?></td>
</tr>
<tr>
	<td width="15%">Datum (YY/MM/DD)</td>
	<td width="85%"><?php echo $datum; ?></td>
</tr>
<tr>
	<td width="15%">Week</td>
	<td width="85%"><?php echo $week; ?></td>
</tr>
</table>
<br />

<?php
} //close for loop
} //close if block
?>

</body>
</html>

i don't understand what isn't working. i went to the records2.php page (you posted the link a few days ago) and ran a test search for worker id 2, and the tables populate with all the data... ???

GamerGun · November 4, 2008

Yes, that works, the output, but users that will use this form won't know that i am for example 2.

They just fill in Tommy van Extel for example. My name, not my ID.

And that should give the same results as when you type in 2.

Same for departments and such.

BTW, thanks for editing my page. Of course i had to use breaks.

Thanks

bobbinsbro · November 4, 2008

ok, now i understand. try this. get the user voonaam and achternaam from the search:

$werknaam = explode(" ", $_POST['search']);

and use this for the main query:

$query = "SELECT * FROM uren WHERE userid = (SELECT userid FROM werknemers WHERE voornaam LIKE ".$werknaam[0]."% AND acthernaam LIKE %."end($werknaam).")";

i think you can leave the rest of the code alone, and everything should still work.

if this works, we'll add conditions to check what type of search was submitted, and we'll do something similar for the rest of the search types.

GamerGun · November 4, 2008

So i replace

$query = "select * FROM uren where $dropdown='$search'" or die (mysql_error());

By yours? Well, it says unexpected T_STRING. Tried some things but i don't see the mistake:

   $query = "select * FROM uren where userid = (SELECT userid FROM werknemers WHERE voornaam LIKE ".$werknaam[0]."% AND achternaam LIKE %."end($werknaam).")";
   $query = "SELECT * FROM uren WHERE userid = (SELECT userid FROM werknemers WHERE voornaam LIKE ".$werknaam[0]."% AND achternaam LIKE %".end($werknaam).")";

JasonLewis · November 4, 2008

There is no error in either of those lines. Perhaps post the code above this, as the error is not always on the line PHP says it is.

Sign In

[SOLVED] Insert form to DB (Multiple rows, array)

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

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

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