Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How could I break up this string?

Posted on 2014-12-01
Medium Priority
Last Modified: 2014-12-05
Here's an insert statement:

insert into twitter_test(actor_id,actor_display_name,posted_time,geo_coords_lat,geo_coords_lon,location_name, session_id) VALUES ('actor_id','actor_display_name','posted_time','lat','lon','location_name', '')

I want to isolate the values that are listed after "twitter_test" and convert them into an array. In other words, I want to grab everything between the first set of parenthesis and then store " actor_id, actor_display, posted_time, geo_coords_lat, geo_coords_lon, location_name and session_id as array[0], array[1] and so on.

How could I do that?

I started working with preg_split and some other things, but I figured it was time to bring the ninjas to the table.

What do you think?
Question by:brucegust
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40474803
Why do you want to do that?  How are you going to use it?

Assisted Solution

ltpitt earned 200 total points
ID: 40474816
We really miss pieces here...

Are those variables coming from a form?

In that case you simple have to build your array using variables.

Please be clearer with your request (a little more history about what's going on and what you want to obtain).

Author Comment

ID: 40474819
Here's the dilemma: You've got some network tools that are "fed" by CSV files from around a region. The challenge is that some of these regions have different naming conventions so when they go to upload their CSV file, there's a disconnect between the data in the CSV file and the table they're attempting to upload their data to.

To solve that, I built an app that gives the user the chance to "connect the dots." You can see it on the attached screenshot. The column headings as they appear on their CSV file are listed and then to the right of each field is a pulldown menu that has all of the fields in the table that file is getting ready to be uploaded to. Once they make their selections and click on "submit," an insert statement is crafted based on their selection and all is well.

I wanted to create a dynamic where they can login and choose from a list of tools they've used in the past and they've saved their preferences. If they choose to "save" their work, the insert statement they built on the fly is saved and that's what I've got here:

insert into twitter_test(actor_id,actor_display_name,posted_time,geo_coords_lat,geo_coords_lon,location_name, session_id) VALUES ('actor_id','actor_display_name','posted_time','lat','lon','location_name', '')

The attached screen shot is where I want to list every one of their saved preferences as a "selected" option within the list of possibilities. And that brings us to where I'm at now: How can I break up the first part of the saved insert statement, save the values as an array and then display them as "selected" options within the pulldown?

I'm telling ya...

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 58

Expert Comment

ID: 40474887
What is the point of having the column names in an array? Don't see a reason for that.

What are you using - PDO, MySQLi...?
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 40475689
If a "region" represents a reasonable abstraction layer, (ie: It would be easy to get this right for one region, but considering more than one region at a time makes it complicated) then the program design is probably best explored by using an interface.  The interface will prescribe the methods that must be implemented for the differing data across the regions.  One method of the interface will return the data mapping for the region.

If a "region" is too high-level, you may want to consider the data mapping on a person-by-person basis. It's the same design pattern, just finer grained in deployment.
LVL 34

Accepted Solution

Slick812 earned 1600 total points
ID: 40476908
greetings  brucegust, , I think I see what you are trying to do, as you need the ability to have "Custom" user created database Tables with the column names as they specify in some sort of "CSV upload column name" set-up page, so "the insert statements are built on the fly".

OK, here's my thoughts on this, I would have a "storage" PHP array (saved to database or file), and would store in it the "database Table Name" and the "SQL" INSERT column names, using your example maybe like -
$tableAry = array();
// each array element would have an array with TWO strings nd one number, the Table name in [0], the columns in [1] , and the amount of columns in [2]
$tableAry['southTwitter'] = array('twitter_test',
    'actor_id,actor_display_name,posted_time,geo_coords_lat,geo_coords_lon,location_name, session_id', 7);
$tableAry['southFacebook'] = array('face_test',
    'muscian_id,muscian_name,posted_date,location,lat,lon, area_id,comt', 8);

Open in new window

and then you get the region and the table from the Post and then set the proper columns to the proper Table for the INSERT, maybe like -
// get Array from file storage
$file = file_get_contents('brucegust.tables');
if (!$file) die('No brucegust.tables file'); // error control

  make array wid unserialize
$tableAry = unserialize($file);
// get user Inputs
$TableInput = $_POST['region'].$_POST['table'];
if (!isset($tableAry[$TableInput])) die('tableAry does NOT contain '.$TableInput); // error control

//Now build the INSERT
$vals = '?';
for ($i = 0; $i < $tableAry[$TableInput][2] - 1; ++$i) $vals .= ',?'; // build the Values for Prepare
// combine all into SQL INSERT
$sql = 'insert into '.$tableAry[$TableInput][0].'('.$tableAry[$TableInput][1].') VALUES ('.$vals.')';

$stmt = $mysqli->prepare($sql);

//Now get the upload CSV file and and use explode($csv) to get array that u use in $stmt-> bind_param
// $stmt->bind_param('ssss', $values[0], $values[1], $values[2], $values[3], $values[4]);

Open in new window

That's my view on this, , BUT u can use another Array for the column names if nessary -
$tableAry['southNew'] = array('new_test',
    array('id','name','date','area'), 4);
LVL 111

Expert Comment

by:Ray Paseur
ID: 40477543
Please be clearer with your request (a little more history about what's going on and what you want to obtain).
No kidding!  Or maybe a test data set so we can simulate the issues!

Expert Comment

ID: 40478827

I'm glad I've posted so I've detonated such a great link sharing!

Thanks, Ray!

Author Comment

ID: 40483359
OK, guys, I got it to work.

I apologize if I didn't provide enough info for my dilemma to make sense. There's really no "test data" other than the insert statement I referenced in my first post: "insert into twitter_test(actor_id,actor_display_name,posted_time,geo_coords_lat,geo_coords_lon,location_name, session_id) VALUES ('actor_id','actor_display_name','posted_time','lat','lon','location_name', '')"

The thing I wanted to do was programmatically reduce the insert statement to "actor_id, actor_display_name, posted_time" geo_coords_lat, geo_coords_lon, location_name, session_id." I did by using this code:

function table_fields($tool_id) {

	global $mysqli;
	$sql="select * from preferences where email ='$_SESSION[email]' and tool_id='$tool_id'";
			$err = "your login function didn't work because of...";
			$err .=$mysqli->errno.': '.$mysqli->error;
			trigger_error($err, E_USER_WARNING);
			//here's where I'm breaking the insert statement down into a manageable array	
			$stage_one = strstr($statement, ')', true); //shows everything to the left of the second parenthesis
			$stage_two=strstr($stage_one, '('); //gets rid of the "insert into table_name" verbiage
			$stage_three = ltrim($stage_two, "("); //trims the "(" from the left end of the string
			$array = explode(',', $stage_three); //breaks up the remaining string into an array based on the comma between each value
			return $array;

Open in new window

I just used what you see at $stage_one, $stage_two etc to the insert statement string incrementally until I had what I needed.

Thanks again for looking it and weighing in with your wisdom!

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
This article discusses how to create an extensible mechanism for linked drop downs.
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

715 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