asked on

PDO PJHP Query Syntax

Is there a better way to write the following?

What is happening is that a specific user id has been created prior to this function.

This function is to insert multiple values into a table. Each value has the same user_id.

//  START INSERT INTO XPROFILE
	$xprofile_a = ("INSERT INTO wp_bp_xprofile_data
			 	(field_id, user_id, value, last_updated)
			 	VALUES('27', '$uid', '$cn', NOW())"); 
				
	$xprofile_b = ("INSERT INTO wp_bp_xprofile_data
			 		(field_id, user_id, value, last_updated)
			 		VALUES('29', '$uid', '$pp', NOW())"); 

	$xprofile_c = ("INSERT INTO wp_bp_xprofile_data
			 		(field_id, user_id, value, last_updated)
			 		VALUES('30', '$uid', '$bp', NOW())"); 
			 		
	$xprofile_d = ("INSERT INTO wp_bp_xprofile_data
			 		(field_id, user_id, value, last_updated)
			 		VALUES('31', '$uid', '$ua', NOW())"); 
			 		
	$xprofile_e = ("INSERT INTO wp_bp_xprofile_data
			 		(field_id, user_id, value, last_updated)
			 		VALUES('32', '$uid', '$uaa', NOW())"); 				
				
					
	$pdo_xp_a = $pdo->prepare($xprofile_a, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
	$pdo_xp_b = $pdo->prepare($xprofile_b, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));	
	$pdo_xp_c = $pdo->prepare($xprofile_c, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));	
	$pdo_xp_d = $pdo->prepare($xprofile_d, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));	
	$pdo_xp_e = $pdo->prepare($xprofile_e, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));			

//  START FIRST QUERY TRY GRAB USER ID			
	try {
    	$pdo_xp_a->execute();
		$pdo_xp_b->execute();
		$pdo_xp_c->execute();
		$pdo_xp_d->execute();
		$pdo_xp_e->execute();

		}  //  END TRY			          	
			catch(PDOException $e) {
   			echo 'ERROR: ' . $e->getMessage();
		} 
//  END INSERT INTO XPROFILE

Open in new window

Please let me know if you need more information

Ray Paseur

Can you please describe the process in plain, non-technical language? From this code snippet I do not understand what you want to achieve. Maybe you can help us with some visualization of the data set?

Thanks, ~Ray

Robert Granlund

ASKER

Here is the Table Data:

CREATE TABLE `wp_bp_xprofile_data` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `field_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `value` longtext NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `field_id` (`field_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Open in new window

I have 7 fields that I need to insert and they all have the same $user_id
so, instead of running 7 queries is there anyway to combine them into one query since it is only the field_id and the value that changes?

Ray Paseur

Yes, a single INSERT query can insert many rows. Something like this, IIRC

INSERT INTO myTable ( thing ) VALUES ('a'), ('b'), ('c')

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Granlund

ASKER

@Chris

What does the colon signify? :fieldID, :uid, :cn

Chris Stanyon

They're called named parameters and indicate that data substitution will occur. When the query is executed, you need to pass in data in place of these named parameters:

$stmt->execute(
      array(
            'fieldID' => 27,
            'uid' => $uid,
            'cn' => 'Another Value'
      )
);

You can call them what you like but the named parameters must match the array keys when you execute.

Ray Paseur

A lot of this material is covered in this article if you read it carefully, for understanding.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

If you do read it and find anything that is unclear, please let me know, thanks. ~Ray