nlomb_hydrogeo

Hello PHP freaks I'm a newbie so bear with me. I have a generic problem and I'm sure code already exists to solve it. I need to have a dynamic sql string variable that will only produce an insert statement with database column names corresponding to input fields on the form that contain values, ignoring the blank ones. I know some looping function like foreach might be used, but if I use it on the $_POST array I'll get the values, not the keys. (the first field, well_no, is a primary key and is held in a session variable, so I know I'll be inserting a value for that column) $ins = "well_no,"; foreach($_POST as $field) if (!empty($field)){ $ins .=$_POST[$field].","; } And I get a warning "Warning: Undefined array key "production in... Here "production" is actually a form input. the problem is my insert statement is hard-coded: $sql = "INSERT INTO well_parent (well_no, easting, northing, purpose, country, admin1, admin2, admin3, settlement, orig_wellno, date_completed, coord_sys, elev, status) VALUES (:well_no, :easting, :northing, ... How can I adjust the sql statement and also the $data array holding the variables. Here's the context: $sql = "INSERT INTO well_parent (well_no, easting, northing, purpose, country, admin1, admin2, admin3, settlement, orig_wellno, date_completed, coord_sys, elev, status) VALUES (:well_no, :easting, :northing, :purpose, :country, :admin1, :admin2, :admin3, :settlement, :orig_wellno, :date_completed, :coord_sys, :elev, :status)"; $stmnt = $pdo->prepare($sql); $data = [':well_no'=>$well_no, ':easting'=>$easting, ':northing'=>$northing, ':purpose'=>$purpose, ':country'=>$country, ':admin1'=>$admin1, ':admin2'=> $admin2, ':admin3'=>$admin3, ':settlement'=>$settlement, ':orig_wellno'=>$orig_wellno, ':date_completed'=> $date_completed, ':coord_sys'=> $coord_sys, ':elev'=>$elev, ':status'=>$status]; $stmnt->execute($data);

Hello Strider - your code is very concise and clear. I think my main problem was not using the session_renerate_id function. If you could provide the Class definition for your object instance, it would be much appreciated.

I'm a newbie to PHP, so bear with me. I am trying to create a user registration/login system. index.php is a login page, with a notice that users must be registered, and this notice contains a link to a registration page. User clicks on the link to get to the page and fill in the info - 8 fields in total. All the information gets into the users database table using a PDP prepared statement without a problem while the user is redirected back to the login page. After a successful login (checking username and password in the users table) we have a redirect to a test landing page. Everything works, except throughout this whole process the application is connected as postgres superuser. This initial PDO connection is established in an include file, init.php as shown below: <?php ob_start(); session_start(); $host = '127.0.0.1'; $dbname = 'IGAD_GWIS'; $charset = 'utf8mb4'; $dsn = "pgsql:host=$host; dbname=$dbname; port =5432;"; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES=> false ]; $pdo = new PDO($dsn, 'postgres', 'f1234u5678', $options); In my login page I collect my POST variables from the login form (username and password) if (!empty($_SERVER['REQUEST_METHOD'])) { if ($_SERVER['REQUEST_METHOD']=='POST') { $username=$_POST['username']; $password=$_POST['password']; $_SESSION['username']=$username; I then call a function in the php_functions include page: $user_pdo = set_connection($pdo, $_SESSION['username']); function set_connection($pdo,$uname) { try { $sqlconn="SELECT password FROM public.users WHERE username={$uname}"; //$stmnt=$pdo->prepare($sql); $stmnt= $pdo->query($sqlconn); $conn_row =$stmnt->fetch(); $conn_user = $conn_row['username']; $conn_pass = $conn_row['password']; $dbname = 'IGAD_GWIS'; $charset = 'utf8mb4'; $host = '127.0.0.1'; $dsn = "pgsql:host=$host; dbname=$dbname; port =5432;"; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES=> false]; return $user_pdo = new PDO($dsn, $conn_user, $conn_pass, $options); This doesn't do anything. No errors are thrown, but no new connection is established. I checked in postgres - select * from pg_stat_activity - it only shows the postgres superuser. I need to have distinct connections with each user, as I intend to establish write privleges on tables. Any suggestions?

Thanks very much mac_gyver. I thought about using organization codes, and even set up a lookup table to do so. But this will require more coding to to display the options in a drop down list, and I guess I was looking for an inferior quick fix.

My web registration form has an input field for organization, which, along with other POST variables are inserted into a postgres user table. Rather than an input field, I have a <select> html element with <option> tags to create a drop-down box with values from an organization table generated by a SELECT statement. Code is as follows <select name='org' id='org' class='form-control' required> <?php try { $result=$pdo->query("SELECT org FROM user_org ORDER BY org"); foreach ($result as $row) { echo "<option value={$row['org']}>{$row['org']}</option>"; $org= eval("echo '{$row['org']}';"); } } catch(PDOException $e) { echo "Error: ".$e->getMessage(); } ?> </select> My PDO object has the attributes PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES=> false Problem is spaces don't seem to be accommodated for values in the key=>value pair for associative arrays. The code works, but the organization value is truncated as soon as a space is encountered. Example "University of Nairobi" gets truncated - only the first word "University" is inserted into the user table. Any workaround for this?