Improve company productivity with a Business Account.Sign Up

x
?
Solved

PDO PJHP Query Syntax

Posted on 2014-02-03
7
Medium Priority
?
481 Views
Last Modified: 2014-02-22
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
0
Comment
Question by:rgranlund
  • 3
  • 2
  • 2
7 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39831240
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
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39831261
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?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39831890
Yes, a single INSERT query can insert many rows.  Something like this, IIRC

INSERT INTO myTable ( thing ) VALUES ('a'), ('b'), ('c')
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
LVL 46

Accepted Solution

by:
Chris Stanyon earned 2000 total points
ID: 39832797
The idea here is that you only prepare the statement once, using named parameters in place of the data. You then execute the prepared statement and pass in the data. Something along these lines

<?php 
//assuming $dbh is your database connection
$uid = 'some User ID';		
                                  
$insertSQL = "INSERT INTO wp_bp_xprofile_data (field_id, user_id, value, last_updated) VALUES(:fieldID, :uid, :cn, NOW()";
$stmt = $dbh->prepare($insertSQL);

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

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 39833444
@Chris

What does the colon signify? :fieldID, :uid, :cn
0
 
LVL 46

Expert Comment

by:Chris Stanyon
ID: 39833472
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.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39833678
A lot of this material is covered in this article if you read it carefully, for understanding.
http://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
0

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question