Solved

PDO PJHP Query Syntax

Posted on 2014-02-03
7
415 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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

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

INSERT INTO myTable ( thing ) VALUES ('a'), ('b'), ('c')
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
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
Comment Utility
@Chris

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

Expert Comment

by:Chris Stanyon
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now