PHP - Database Problem

Hi all

Good morning / night / afternoon

I have a duplication problem when I need to print my records in PHP
First of all let's see the database table



As you can see the duplication happened duo to the approvers that mean if there is only one approver there will be no duplication
For example when I want to pint request number 1 it will print

1 - Cris - Hadi
1 - Cris - Jack

but I want it to be like this

1 - Cris - Hadi,Jack

and thank you for your time

Hi, if someone could assist me, it would be much appreciated. I have a database website. I generated it using AppGini. Unfortunatly, they haven't answered my emails. What I'm trying to do is change my database to not dynamically load the selected record, but to instead load the direct URL to the record.

(Here is the website address: http://2g2egkxd3.site.aplus.net/updates/bad_view.php)

If you hover above the record, you will see the full URL for that record.

Example: http://2g2egkxd3.site.aplus.net/updates/bad_view.php?SelectedID=Buck%20Allen

But when you click it, it doesn't load that URL into the browser. I'm guessing it dynamically loads it into the page.
The reason I'm trying to do this is because I want to have a social/comment system for each record/prospect and it won't work if the record is dynamically loaded.

For convenience and efficiency purposes I'm changing my MySQL database to store PHP Objects as opposed to tons of individuals variables. In converting the user/login system I've come across a really weird problem. I can register a new user to the site, but only the first one in the database will work. I can register others and they get put in the database correctly, but for some reason the app doesn't see them or even recognize that they exist. Only the first entry in the database will work as it's supposed to. For instance, if I drop the first database entry, the second one (now first) will begin to work.

In most cases, I'm pulling all Object entries from the database and looping through them with foreach() to find the specific one I want.

I've just come off of an 8 hour coding spree and my brain is just shot at the moment. I really think I just need a new pair of eyes. I'm sure that it's something simple, but I can't see it atm. I'm attaching the User class file which handles login, registration, and anything that I think would be affecting this.

Thanks for the help.

I am trying to insert with a form the information below into the database, but I don't know how to link it to a specific user in the DB. Below is the SQL dump and my code for the insert page. Any help greatly appreciated.

also, I get the error ,"duplicate entry for key 2"


Code: [Select]
- phpMyAdmin SQL Dump
-- version 2.8.0.1
-- http://www.phpmyadmin.net
--
-- Host: custsql-sl07.eigbox.net
-- Generation Time: Aug 28, 2011 at 07:42 AM
-- Server version: 5.0.91
-- PHP Version: 4.4.9
--
-- Database: `photo_artists`
--

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

--
-- Table structure for table `images`
--

CREATE TABLE `images` (
  `image_id` bigint(20) unsigned NOT NULL auto_increment,
  `gallery_user` int(11) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `mime_type` varchar(255) NOT NULL,
  `file_size` int(11) NOT NULL,
  `file_data` longblob NOT NULL,
  PRIMARY KEY  (`image_id`),
  UNIQUE KEY `image_id` (`image_id`),
  KEY `filename` (`filename`),
  KEY `gallery_user` (`gallery_user`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ;

--


 Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `status` varchar(20) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `dob` date NOT NULL,
  `gender` varchar(10) NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(20) NOT NULL,
  `activationkey` varchar(100) NOT NULL,
  `sex` varchar(10) NOT NULL,
  `race` varchar(40) NOT NULL,
  `job` varchar(40) NOT NULL,
  `country` varchar(30) NOT NULL,
  `state` varchar(30) NOT NULL,
  `town` varchar(30) NOT NULL,
  `hobbies` mediumtext NOT NULL,
  `business` varchar(50) NOT NULL,
  `religion` varchar(40) NOT NULL,
  `social_groups` mediumtext NOT NULL,
  `political_groups` varchar(200) NOT NULL,
  `other_affiliations` varchar(200) NOT NULL,
  `buying` longtext NOT NULL,
  `selling` longtext NOT NULL,
  `links` varchar(100) NOT NULL,
  `likes` mediumtext NOT NULL,
  `dislikes` longtext NOT NULL,
  `firstName` varchar(30) NOT NULL,
  `zip` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `activationkey` (`activationkey`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1 AUTO_INCREMENT=54 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (49, 'verify', '', '0000-00-00', '', 'user2', '32b3491336522e073489725b5daf298cd749007a', '1@aol.com', '34705256317118179901016541118525481318594628324', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
INSERT INTO `users` VALUES (53, '', '', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
INSERT INTO `users` VALUES (52, 'activated', '', '0000-00-00', '', 'user', 'e8ee80e34d93d48813b08949c07fc826a1c82b63', '1@hotmai', '153967352213317143421219669520489072241284552824', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');

and here is the code. note I don't know how to link the insert to the current user.

Code: [Select]
<?php
require_once('globals.php');
include('bouncer.php');


$first = $_POST['first'];
$last = $_POST['last'];
$first = $_POST['dob'];
$last = $_POST['gender'];
$first = $_POST['ethnic'];
$last = $_POST['country'];
$first = $_POST['state'];
$last = $_POST['town'];
$first = $_POST['zip'];
$last = $_POST['mail'];
$first = $_POST['job'];
$last = $_POST['business'];
$first = $_POST['religion'];
$last = $_POST['social'];
$first = $_POST['political'];
$last = $_POST['affiliations'];
$first = $_POST['political'];
$last = $_POST['affiliations'];
$first = $_POST['buying'];
$last = $_POST['selling'];
$first = $_POST['likes'];
$last = $_POST['dislikes'];
$first = $_POST['links'];
 

$currentUser = $_SESSION['myusername'];
mysql_query ( "INSERT INTO users (lastname, firstName, dob, gender, email, race, job, country, state, town, zip, hobbies, business, religion, social_groups, political_groups, other_affiliations, buying, selling, links, likes, dislikes) VALUES ('$last','$first','$dob','$gender','$email','$ethnic','$job','$country','$state','$town','$zip','$hobbies','$business',
'$religion','$social','$political','$affiliations','$buying','$selling','$links','$likes','$dislikes')");

echo mysql_error();
mysql_close();
?>
<html>
<body>
<p>Login Successful</p>
<p>&nbsp;</p>

<form action="login_success.php" method="post">
  <p>
    <input type="text" name="first" size="20" />
  First name<br />
    <input type="text" name="last" size="20" />
    Last name<br />
    <input name="dob" type="text" size="20" id="dob" />
    Date of Birth<br />
    <input type="text" name="gender" size="20" id="gender" />
  Gender <br />
  <input type="text" name="ethnic" size="20" id="ethnic" />
  Ethnicity
  <br />
  <input type="text" name="country" size="20" id="country" />
  Country<br />
  <input type="text" name="state" size="20" id="state" />
  State<br />
    <input type="text" name="town" size="20" id="town" />
    Town<br />
<input type="text" name="zip" size="20" id="zip" />
  Zip Code<br />
  <br />
    <input type="text" name="email" size="40" id="email" />
    Email<br />
<textarea name="job" cols="40" id="job"></textarea>
  Job<br />
    <textarea name="business" cols="40" id="business"></textarea>
    Business<br />
    <input type="text" name="religion" size="60" id="religion" />
  Religion</p>
  <p><br />
    <textarea name="social" cols="100" id="social"></textarea>
    Social Groups<br />
    <textarea name="political" cols="100" id="political"></textarea>
    Political groups<br />
    <textarea name="affiliations" cols="100" id="affiliations"></textarea>
    Other Affiliations<br />
    <textarea name="buying" cols="100" id="buying"></textarea>
    Items I am buying<br />
    <textarea name="selling" cols="100" id="selling"></textarea>
    Items I am selling<br />
    <textarea name="likes" cols="100" id="likes"></textarea>
    My likes <br />
    <textarea name="dislikes" cols="100" id="dislikes"></textarea>
    My dislikes <br />
    <textarea name="links" cols="100" id="links"></textarea>
    My links <br />
    <input type="submit" value="Store in database and search" />
    <input type="reset" value="Reset fields" />
  </p>
  </p>
</form>
<p><a href="index.php">HOME</a></p>
<p>Welcome to members page. to log out click <a href="Logout.php">HERE. </a></p>
 
</body>
</html>

I got some help from this forum previously and am having some more issues.  I created a database class that works and returns a PDO object.  I am having trouble figuring out how to use the object in another class I want to use to access some CRUD functions.  I get to the point where I  start using the pdo object I create in the database class and my code fails.  I am obviously calling the pdo object property incorrectly.  I guess I don't understand the proper syntax.  I've included the code for the database class and the CRUD class.  The problem starts at the point where I try to run a prepared statement.  I have include a comment "Problem stars here" to indicate that point.  There is a lot of debug stuff still in the code.

Thanks,

--Kenoli

<?php
	
	class Db {
	
	public $pdo = '';
	public $message = 'A message from db!<br><br>';

function __construct() {
	
	$servername = "localhost";
	$username = "root";
	$password = "";
	$dbname = "tio-local";
	
	$db_options = array(
	PDO::ATTR_EMULATE_PREPARES => false, 
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, 
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
	);
	
	try {
		
	  $this->pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password, $db_options);
	  // set the PDO error mode to exception
	  $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	}  // End Try
	
	catch(PDOException $e) {
		  echo "Error: " . $e->getMessage();
		}
		
	} // End __construct
	
} // End class definition DB.php

$db = new Db;

$pdo = $db->pdo;

print_r ($pdo);

?>

<?php // Db_functions.php

include_once ('Db.php'); // $db instantiated in class file

$pdo = $db->pdo;

class Db_functions {
	
	public $pdo = '';
	
	function __construct($pdo) { 
		
		$this->pdo = $pdo;
		
	} // Close __construct
  
  public function insert($table_name, $white_list, $insert_array) {
		
			if ($white_list == '' && $table_name == 'Sites') {
		
			$white_list = array('gone'=>'','site_name' =>'', 'site_address' =>'', 'grommets' =>'', 'tape' =>'', 'site_image' =>'',  'description' =>'', 'surface' =>'', 'tio_contact' =>'', 'site_contact' =>'','owner' =>'', 'lessee' =>'',  'contact_phone' =>'', 'contact_email' =>'', 'contact_date' =>'', 'comments' =>'');
			} elseif  ($white_list == '' && $table_name == 'Persons') {
				
				$white_list = array('gone'=>'', 'fname'=>'', 'lname'=>'', 'tio'=>'', 'volunteer'=>'', 'general'=>'', 'artist_pic'=>'', 'email'=>'', 'website'=>'', 'telephone'=>'', 'address'=>'', 'city'=>'', 'state'=>'', 'zip'=>'', 'statement'=>'');
			}
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
			
			echo '<p>The following is the $white_list:<br>';
			
			echo '<pre>';
			print_r ($white_list);
			echo '</pre>';
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
			
			/** Test and remove any unpermitted columns **/
			
			$insert_array = array_intersect_key($insert_array, $white_list);
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
		
		/** Generate variables to create prepared statements **/
		
			foreach($insert_array as $key => $value) {  
				$col .= $key . ', ';  
				$val .= ':' .$key . ', ';
			}
			
			echo '$col = ' . $col . '<p>';
			echo '$val = ' . $val . '<p>';
			
			echo '<strong>***The following is the PDO object: </strong>';
		print_r ($this->pdo);
		
		/** Remove ', ' at end of arrays and create prepared statement */
		
			$col = substr_replace($col ,"",-2);
			$val = substr_replace($val ,"",-2);
			$sql = "INSERT INTO Sites ($col) VALUES ($val)";
			
			echo "<p>SQL = $sql<br><br>"; /** Debug **/
		
		echo '<h3>More</h3<br>';	
			
		/** Register prepared statement */
		
		/****** PROBLEM STARTS HERE *****/
		
			$stmt = $this->pdo->prepare($sql);
			
		echo '<h3>More2</h3>';	
		
		/** Create [:field, $value] pairs. */
		
			foreach($insert_array as $key => $value) {
				$param = ':' . $key;
				$stmt->bindParam($param, $$value);
				
			//}
		
		/** Create [field => value] array */
		
			foreach($insert_array as $key => $value) {
				$insert[$key] =  $value;
			}
			
		
		/** Execute statement using $insert array. **/
		
			$stmt->execute($insert);
	
	} // End insert function
	
	
} // Close class definition

$db_functions = new Db_functions($pdo);

$insert_array = array('fname' => 'John', 'lname' => 'Hancock');

$db_functions->insert('Persons', '', $insert_array);

echo '<pre>';
print_r ($db_functions);
echo '</pre>';

?>

 

Edited February 6 by kenoli